This is the third post of four about version 2 of the ASP.Net file upload component. This post describes a new (and much requested) feature which allows uploaded files to be written into a SQL Server database table in chunks as they are read in. This is accomplished through a custom IFileProcessor implementation and is configurable to accomodate different database table structures.

For a little background reading on how data is written into SQL Server in chunks take a look at this MSDN article. The article describes how to use the UPDATETEXT statement in SQL Server to append binary data onto the end of an image column. This model exactly follows the pattern used for file processors in the upload module so implementing a SQL Server provider is a fairly straight forward process.

To start with a database table is required to store the file data as it is uploaded. Such a table can be created with the following TSQL statement.

CREATE TABLE [dbo].[UploadedFile](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FileName] [varchar](100) NOT NULL,
	[ContentType] [varchar](50) NOT NULL,
	[FileContents] [image] NOT NULL,
 CONSTRAINT [PK_UploadedFiles] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The UploadedFile table contains an identity column which is used to provide a unique identifier to the upload. In version 2 of the upload module processors are able to provide custom domain specific identifiers to uploaded files above and beyond the file name. In the case of the SQL processor the identifier comes from the Id column in the table- this can be easily customised though.

To help understand a little better what’s going on we should look at the IFileProcessor interface as it stands in version 2 of the upload module.

/// <summary>
/// The IFileProcessor interface defines classes which are used to
/// process an individual file coming from a form stream.
///
/// The interface defines methods to start the file processing (with a file
/// name and content type), write data, and end the upload process.
///
/// IFileProcessor implementations are used to write uploaded data to
/// persistant storage such as the file system or a database.
/// </summary>
public interface IFileProcessor :IDisposable
{
    /// <summary>
    /// Starts a new file.
    /// </summary>
    /// <param name="fileName">File name.</param>
    /// <param name="contentType">The content type of the file.</param>
    /// <param name="headerItems">A dictionary of items pulled from the header of the field.</param>
    /// <returns>An optional object used to identify the item in the storage container.</returns>
    object StartNewFile(string fileName, string contentType, Dictionary<string, string> headerItems);

    /// <summary>
    /// Writes to the output file.
    /// </summary>
    /// <param name="buffer">Buffer to write from.</param>
    /// <param name="offset">Offset in the buffer to write from.</param>
    /// <param name="count">Count of bytes to write.</param>
    void Write(byte[] buffer, int offset, int count);

    /// <summary>
    /// Ends current file processing.
    /// </summary>
    void EndFile();

    /// <summary>
    /// Returns the name of the file that is currently being processed.
    /// Null if there is no file.
    /// </summary>
    /// <returns>The file name.</returns>
    string GetFileName();

    /// <summary>
    /// Gets the identifier in the container.
    /// </summary>
    /// <returns>The container identifier.</returns>
    object GetIdentifier();

    /// <summary>
    /// Gets the header items.
    /// </summary>
    Dictionary<string, string> GetHeaderItems();
}

The interface is very similar to that of version 1 of the module, with only a few basic methods being required. The broad strokes of the implementation for a SQL provider are as follows:

The core of the processor is in creating the SQL command objects to write the initial database record and to append chunks of data in the Write method. The base implementation of the processor creates SQL statements to handle the simple table structure at the beginning of this post. This is done using two methods- CreateInitialInsertCommand and CreateBlobAppendCommand.

/// <summary>
/// Creates a SQL command object which performs an initial insert
/// of the blob row into the database.
/// </summary>
/// <param name="fileName">File name.</param>
/// <param name="contentType">Content type.</param>
/// <returns>A SQL command which creates the initial row in the database.</returns>
protected virtual SqlCommand CreateInitialInsertCommand(string fileName, string contentType)
{
    SqlCommand cmd = new SqlCommand();

    cmd.CommandText = "INSERT INTO " + TableName + " (FileName, ContentType, FileContents) " +
        "Values(@FileName, @ContentType, 0x0);" +
        "SELECT @Identity = SCOPE_IDENTITY();" +
        "SELECT @Pointer = TEXTPTR(FileContents) FROM UploadedFile WHERE Id = @Identity";

    cmd.Parameters.Add(new SqlParameter("@FileName", System.IO.Path.GetFileName(fileName)));
    cmd.Parameters.Add(new SqlParameter("@ContentType", contentType));

    SqlParameter idParm = cmd.Parameters.Add("@Identity", SqlDbType.Int);
    idParm.Direction = ParameterDirection.Output;

    SqlParameter ptrParm = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    ptrParm.Direction = ParameterDirection.Output;

    return cmd;
}

/// <summary>
/// Creates a SQL command object which appends incoming bytes onto the blob field.
/// </summary>
/// <param name="pointer">SQL pointer to the blob.</param>
/// <param name="offset">Offset in the blob.</param>
/// <param name="bytes">The bytes to write.</param>
/// <returns>A SQL command object which appends the bytes to the blob field.</returns>
protected virtual SqlCommand CreateBlobAppendCommand(byte[] pointer, long offset, byte[] bytes)
{
    SqlCommand cmd = new SqlCommand();

    cmd.CommandText = "UPDATETEXT " + TableName + ".FileContents @Pointer @Offset 0 @Bytes";

    SqlParameter ptrParm = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    ptrParm.Value = pointer;

    SqlParameter bytesParam = cmd.Parameters.Add("@Bytes", SqlDbType.Image, bytes.Length);
    bytesParam.Value = bytes;

    SqlParameter offsetParm = cmd.Parameters.Add("@Offset", SqlDbType.Int);
    offsetParm.Value = offset;

    return cmd;
}

Both are virtual methods allowing them to be overridden by inheriting classes in order to use different table structures. CreateInitialInsertCommand is called by the StartNewFile method whilst CreateBlobAppendCommand is called by the Write method for each new block of data that is to be written.

The final piece of the puzzle is how we identify uploaded files after they have been stored. In version 1 of the module the UploadStatus class contained a list of file names that were updated. Whilst that works fine for the file system processor it doesn’t really provide enough information to allow the upload to be matched to a SQL database table row. For that we really need the id of the table row. To accomplish this the IFileProcessor interface has a new method GetIdentifier. Each processor can return any object it likes from this method to provide a domain specific way of identifying an upload. In the case of the SQL processor this is simply the id of the database record. The UploadStatus class then allows access to the identifier after the uploads are complete because now instead of a list of file names a list of UploadedFile objects are made available both for completed files and ones where an error was encountered.

public class UploadStatus
{
    .......

    /// <summary>
    /// Gets/sets the list of uploaded files.
    /// </summary>
    public List<UploadedFile> UploadedFiles
    {
        get { return _uploadedFiles; }
        internal set { _uploadedFiles = value; }
    }

    /// <summary>
    /// Gets/sets the list of files which could not be uploaded due to an error.
    /// </summary>
    public List<UploadedFile> ErrorFiles
    {
        get { return _errorFiles; }
        internal set { _errorFiles = value; }
    }

    .......
}

public class UploadedFile
{
    .......

    /// <summary>
    /// Gets the file name.
    /// </summary>
    public string FileName
    {
        get { return _fileName; }
    }

    /// <summary>
    /// Gets the container identifier returned from the processor.
    /// </summary>
    public object Identifier
    {
        get { return _identifier; }
    }

    /// <summary>
    /// Gets a dictionary of all items in the header.
    /// </summary>
    public Dictionary<string, string> HeaderItems
    {
        get { return _headerItems; }
    }

    .......
}

To use the processor instead of the file system processor you need to modify the global.asax file- which is where the module configuration is carried out. At a minimum a connection string is required for the database.

void Application_Start(object sender, EventArgs e)
{
    UploadManager.Instance.ProcessorType = typeof(SQLProcessor);
    UploadManager.Instance.ProcessorInit += new FileProcessorInitEventHandler(Processor_Init);
}

/// <summary>
/// Initialises the file processor.
/// </summary>
/// <param name="sender">Sender</param>
/// <param name="args">Arguments</param>
void Processor_Init(object sender, FileProcessorInitEventArgs args)
{
    if (args.Processor is SQLProcessor)
    {
        SQLProcessor processor;

        processor = args.Processor as SQLProcessor;

        // Set up the connection string
        processor.ConnectionString = "server=(local);initial catalog=FileUpload;integrated security=true";
    }
}

I’m anticipating that people will implement their own table structure and override parts of the SQL processor to suit their own needs. The class is designed to be inherited and modified so it shouldn’t be too difficult to change for a variety of different situations. So far I’ve tested uploads of up to about 100Mb but I don’t see why larger uploads wouldn’t be perfectly possible.

Of course once the files have been uploaded into the database it’s entirely up to the application what to do with them. The upload module itself doesn’t provide any mechanism to download or stream the stored file data although I will post some sample code which does that soon.

In the meantime the changes outlined here and the new SQL processor should make the upload module more felxible and allow it to be used in more situations. The code for the SQL processor is shown below, I guess if anyone wants to they could use it in v1 of the module with a few changes but I’d recommend waiting for version 2.

/// <summary>
/// Implements the IFileSystemProcessor interface to provide a processor for streaming uploads into a SQL database.
/// </summary>
public class SQLProcessor : IFileProcessor
{
    #region Declarations

    SqlConnection _connection;
    SqlTransaction _tran;
    string _fileName;
    string _contentType;
    string _tableName = "UploadedFile";
    string _connectionString = null;
    bool _errorState;
    Dictionary<string, string> _headerItems;

    #endregion

    #region Properties

    /// <summary>
    /// Gets or sets the connection string.
    /// </summary>
    /// <value>The connection string.</value>
    public string ConnectionString
    {
        get { return _connectionString; }
        set { _connectionString = value; }
    }

    /// <summary>
    /// Gets/sets the table name to store the files in.
    /// </summary>
    public string TableName
    {
        get { return _tableName; }
        set { _tableName = value; }
    }

    #endregion

    #region Constructor

    /// <summary>
    /// Constructor.
    /// </summary>
    public SQLProcessor()
    {
    }

    #endregion

    #region Data access

    /// <summary>
    /// Creates a SQL command object which performs an initial insert
    /// of the blob row into the database.
    /// </summary>
    /// <param name="fileName">File name.</param>
    /// <param name="contentType">Content type.</param>
    /// <returns>A SQL command which creates the initial row in the database.</returns>
    protected virtual SqlCommand CreateInitialInsertCommand(string fileName, string contentType)
    {
        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = "INSERT INTO " + TableName + " (FileName, ContentType, FileContents) " +
            "Values(@FileName, @ContentType, 0x0);" +
            "SELECT @Identity = SCOPE_IDENTITY();" +
            "SELECT @Pointer = TEXTPTR(FileContents) FROM UploadedFile WHERE Id = @Identity";

        cmd.Parameters.Add(new SqlParameter("@FileName", System.IO.Path.GetFileName(fileName)));
        cmd.Parameters.Add(new SqlParameter("@ContentType", contentType));

        SqlParameter idParm = cmd.Parameters.Add("@Identity", SqlDbType.Int);
        idParm.Direction = ParameterDirection.Output;

        SqlParameter ptrParm = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
        ptrParm.Direction = ParameterDirection.Output;

        return cmd;
    }

    /// <summary>
    /// Creates a SQL command object which appends incoming bytes onto the blob field.
    /// </summary>
    /// <param name="pointer">SQL pointer to the blob.</param>
    /// <param name="offset">Offset in the blob.</param>
    /// <param name="bytes">The bytes to write.</param>
    /// <returns>A SQL command object which appends the bytes to the bob field.</returns>
    protected virtual SqlCommand CreateBlobAppendCommand(byte[] pointer, long offset, byte[] bytes)
    {
        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = "UPDATETEXT " + TableName + ".FileContents @Pointer @Offset 0 @Bytes";

        SqlParameter ptrParm = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
        ptrParm.Value = pointer;

        SqlParameter bytesParam = cmd.Parameters.Add("@Bytes", SqlDbType.Image, bytes.Length);
        bytesParam.Value = bytes;

        SqlParameter offsetParm = cmd.Parameters.Add("@Offset", SqlDbType.Int);
        offsetParm.Value = offset;

        return cmd;
    }

    /// <summary>
    /// Closes the connection and cleans up.
    /// </summary>
    /// <param name="commit">True to commit the transaction.</param>
    void CleanUp(bool commit)
    {
        if (_tran != null)
        {
            if (commit)
            {
                _tran.Commit();
            }
            else
            {
                _tran.Rollback();
            }

            _tran = null;
        }

        if (_connection != null)
        {
            _connection.Close();
            _connection.Dispose();
            _connection = null;
        }
    }

    #endregion

    #region IFileProcessor Members

    byte[] _pointer = null;
    long _blobOffset = 0;
    int _rowId;

    /// <summary>
    /// Starts a new file.
    /// </summary>
    /// <param name="fileName">File name.</param>
    /// <param name="contentType">The content type of the file.</param>
    /// <param name="headerItems">A dictionary of items pulled from the header of the field.</param>
    /// <returns>An optional object used to identify the item in the storage container.</returns>
    public object StartNewFile(string fileName, string contentType, Dictionary<string, string> headerItems)
    {
        SqlCommand insertCommand;

        _rowId = -1;
        _errorState = false;

        _fileName = fileName;
        _headerItems = headerItems;
        _contentType = contentType;

        try
        {
            _connection = new SqlConnection(_connectionString);
            _connection.Open();
            _tran = _connection.BeginTransaction();

            insertCommand = CreateInitialInsertCommand(fileName, contentType);
            insertCommand.Connection = _connection;
            insertCommand.Transaction = _tran;
            insertCommand.ExecuteNonQuery();

            _pointer = (byte[])insertCommand.Parameters["@Pointer"].Value;
            _rowId = (int)insertCommand.Parameters["@Identity"].Value;

        }
        catch(Exception ex)
        {
            _errorState = true;
            CleanUp(false);
            throw ex;
        }

        return _rowId;
    }

    /// <summary>
    /// Writes to the output file.
    /// </summary>
    /// <param name="buffer">Buffer to write from.</param>
    /// <param name="offset">Offset in the buffer to write from.</param>
    /// <param name="count">Count of bytes to write.</param>
    public void Write(byte[] buffer, int offset, int count)
    {
        SqlCommand blobCommand;
        byte[] toWrite;

        if (_errorState) return;

        toWrite = new byte[count];
        Buffer.BlockCopy(buffer, offset, toWrite, 0, count);

        blobCommand = CreateBlobAppendCommand(_pointer, _blobOffset, toWrite);

        try
        {
            blobCommand.Connection = _connection;
            blobCommand.Transaction = _tran;
            blobCommand.ExecuteNonQuery();
            _blobOffset += count;
        }
        catch(Exception ex)
        {
            _errorState = true;
            CleanUp(false);
            throw ex;
        }
    }

    /// <summary>
    /// Ends current file processing.
    /// </summary>
    public void EndFile()
    {
        if (_errorState) return;

        CleanUp(true);
    }

    /// <summary>
    /// Returns the name of the file that is currently being processed.
    /// Null if there is no file.
    /// </summary>
    /// <returns>The file name.</returns>
    public string GetFileName()
    {
        return _fileName;
    }

    /// <summary>
    /// Returns the container identifier.
    /// </summary>
    /// <returns>The container identifier.</returns>
    public virtual object GetIdentifier()
    {
        return _rowId;
    }

    /// <summary>
    /// Gets the header items.
    /// </summary>
    public Dictionary<string, string> GetHeaderItems()
    {
        return _headerItems;
    }

    /// <summary>
    /// Called if the upload is cancelled.
    /// </summary>
    public void Cancel()
    {
        if (_errorState) return;

        CleanUp(false);
    }

    #endregion

    #region IDisposable Members

    /// <summary>
    /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
    /// </summary>
    public void Dispose()
    {
        CleanUp(false);
    }

    #endregion
}