Upload Large Files Using MVC4 Web API and SQL Server FILESTREAM

For the last couple of weeks I was struggling to develop a Web API which could provide the feature of complete data streaming, while uploading a file, right from the API caller to the SQL Server. I looked a lot of articles on file upload but all talked about streaming to File System and none suggested how to achieve streaming for saving contents to SQL Server database right from the API calls. After a lot of experimentation I finally landed up to the below implementation which fully satisfies my initial requirement. One needs to follow all of the below steps to fully utilize the streaming capabilities.

Please Note: The implementation is very raw and should be modified as per the application requirement

Database

1. If you are using SQL Server 2008 and above you can enable FILESTREAM by following my earlier blog How to Enable SQL Server FILESTREAM?
2. Create a FILESTREAM enabled database

   CREATE DATABASE [FileStreamDatabase] ON  PRIMARY 
   ( NAME = N'FileStreamGroup', FILENAME = N'C:\MSSQL\Data\FileStreamDatbase_data.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
    FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM  DEFAULT 
   ( NAME = N'FileStreamDatbase_blobs', FILENAME = N'C:\MSSQL\Data\filestream11' )
    LOG ON 
   ( NAME = N'FileStreamDatbase_log', FILENAME = N'C:\MSSQL\Data\FileStreamDatbase_data.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
   GO

3. Create a table with a FILESTREAM column

   CREATE TABLE FileStreamDatabase.dbo.Attachments
   (
    [Guid] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
    [Name] VARCHAR(100) NOT NULL,
    [Content] VARBINARY(MAX) FILESTREAM NULL
   )

Web API

1. Set NoBufferPolicy: As a default behavior, a .Net Web application hosted on IIS buffers the content of input stream coming in the upload request. For large file uploads this would mean caching a lot of data in the server memory and would result in "Out of Memory Exception". To avoid this we should enable NoBufferPolicy for the File Upload API.

   public class NoBufferPolicySelector : WebHostBufferPolicySelector
       {
           public override bool UseBufferedInputStream(object hostContext)
           {
               var context = hostContext as HttpContextBase;
   
               if (context != null)
               {
                   if (string.Equals(context.Request.RequestContext.RouteData.Values["controller"].ToString(), "values", StringComparison.InvariantCultureIgnoreCase))
                       return false;
               }
   
               return true;
           }
       }

   
   Modify Global.asax.cs to include this new policy.

   public class WebApiApplication : System.Web.HttpApplication
       {
           protected void Application_Start()
           {
               AreaRegistration.RegisterAllAreas();
   
               WebApiConfig.Register(GlobalConfiguration.Configuration);
               FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
               RouteConfig.RegisterRoutes(RouteTable.Routes);
               BundleConfig.RegisterBundles(BundleTable.Bundles);
   
   
               GlobalConfiguration.Configuration.Services.Replace(typeof(IHostBufferPolicySelector), new NoBufferPolicySelector());
           }
       }
   



2. Increase MaxRequestLenght in Web.config: Use the below code to set the acceptable request length as per your requirement. (I have kept it as 1GB)  



3. Custom "MultipartStreamProvider": Create a custom MultipartStreamProvider and call it "MultipartDbFileStreamProvider". The code is as given below:

   public class MultipartDbFileStreamProvider : MultipartStreamProvider
       {
           SqlTransaction _transaction = null;
           private SqlConnection _connection = null;
           private SqlCommand _command = null;
           private SqlFileStream _sqlFileStream = null;
   
           public override Stream GetStream(HttpContent parent,
                                            System.Net.Http.Headers.HttpContentHeaders headers)
           {
               string fileName = !string.IsNullOrEmpty(headers.ContentDisposition.FileName)
                                     ? headers.ContentDisposition.FileName.Replace("\"", string.Empty).Replace(" ", "_")
                                     : "temp.txt";
   
               _connection =
                   new SqlConnection(
                       @"Data Source=(local);Initial Catalog=FileStreamDatabase;Integrated Security=True;MultipleActiveResultSets=True");
   
               _connection.Open();
               _transaction = _connection.BeginTransaction(IsolationLevel.ReadUncommitted);
   
               const string insertSql = @"
                       INSERT INTO Attachments([Id], [Name], [Image]) VALUES(@Id, @Name, Cast('' As varbinary(Max)));
                       SELECT Image.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
                       FROM Attachments
                       WHERE Id = @Id";
   
               _command = new SqlCommand(insertSql, _connection)
                   {
                       Transaction = _transaction
                   };
   
               var id = Guid.NewGuid();
               _command.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = id;
               _command.Parameters.Add("@Name", SqlDbType.VarChar).Value = Path.GetFileName(fileName);
   
               using (var reader = _command.ExecuteReader())
               {
                   while (reader.Read())
                   {
                       // Get the pointer for file
                       var path = reader.GetString(0);
                       byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
   
                       _sqlFileStream = new SqlFileStream(path, transactionContext, FileAccess.Write,
                                                          FileOptions.SequentialScan, 0);
                   }
               }
   
               return _sqlFileStream;
           }
   
           public override System.Threading.Tasks.Task ExecutePostProcessingAsync()
           {
               _transaction.Commit();
               _command.Dispose();
               _connection.Dispose();
               return base.ExecutePostProcessingAsync();
           }
       }
   

   
   The function "GetStream" does the job of creating a row in table for the fileupload request (with blank content) and returning back the handle of the FILESTREAM column. 


4. The Web API: Below is the API for file upload finally.

           public async Task Post(CancellationToken ct)
           {
               if (!Request.Content.IsMimeMultipartContent())
                   return Request.CreateErrorResponse(HttpStatusCode.BadRequest, "Incorrect content");
   
               var streamProvider = new MultipartDbFileStreamProvider();
               await Task.Run(() => Request.Content.ReadAsMultipartAsync(streamProvider), ct);
   
               return Request.CreateResponse(HttpStatusCode.OK);
           }

Invoking the File Upload API

The file upload Api can be invoked using fiddler and the below request structure
(select "Upload File" link at top right)

Also one can invoke it using the below piece of code to make the Api call.

        public async Task PostFile()
        {
            var fileName = txtFileName.Text;
            var filePath = @"{Folder Name}" + fileName;

            lblMessage.Text = "";

            var httpClient = new HttpClient
                {
                    Timeout = new TimeSpan(1, 0, 0)
                };

            if (!File.Exists(filePath))
                return;

            FileStream fs = File.OpenRead(filePath);

            using (var content = new MultipartFormDataContent())
            {
                content.Add(new StreamContent(fs), "attachment", fs.Name);

                var cts = new CancellationTokenSource();

                try
                {

                    var response = await httpClient.PostAsync("http://localhost:51634/v1/api/values", content, cts.Token);

                    if (response.StatusCode == HttpStatusCode.OK)
                    {
                        lblMessage.Text = hdnMessage.Value + "\n" + "File " + fileName + " uploaded successfully!";
                        hdnMessage.Value = lblMessage.Text;
                    }
                }
                catch (OperationCanceledException ex)
                {
                    //Your cancellation code goes here..
                }
            }
        }

There can be many improvement over and above the above implementation but this will surely set the basic setup for a File Upload Api supporting file streaming and saving the content using SQL Server.