Web API File Upload with MS SQL SERVER FileTable

This article demonstates how the setup a file upload using Web API and save it to a MS SQL Server database using a FileTable. Because a FileTable is used, the files can be saved directly to the database, without creating an extra stream. The files can be then accessed using the database or directly in the file system.

Code: https://github.com/damienbod/WebApiFileUploadFileTable

Settings up the database:
To setup,, a new database is created which has Filestreams enabled. This feature only works with windows authentication. Firstly if not already configured, the Filestream access level is set to 2.

EXEC sp_configure filestream_access_level, 2

Once this has been set, create a new directory to save the files. In this example, C:\damienbod\WebApiFileTable is used. Now execute the following command:

(Name = WebApiFileTable,
FILENAME = 'C:\damienbod\WebApiFileTable\FTDB.mdf'),
(NAME = WebApiFileTableFS,
(Name = WebApiFileTableLog,
FILENAME = 'C:\damienbod\WebApiFileTable\FTDBLog.ldf')
DIRECTORY_NAME = N'WebApiFileTable');

Now you can check if your database settings are ok.

SELECT DB_NAME(database_id),
FROM sys.database_filestream_options;

The database should be configured as follows:

Now create a Table for the File Uploads:

USE WebApiFileTable
CREATE TABLE WebApiUploads AS FileTable
(FileTable_Directory = 'WebApiUploads_Dir');

The files can be saved, deleted or updated using the following path:


The files can also be accessed using plain SQL.

INSERT INTO [dbo].[WebApiUploads]
'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NUnit-2.6.1.msi', SINGLE_BLOB) AS FileData

Adding a Entity framework code first data access layer.

Now a file description table is created for searching and returning multiple records. This is used to setup a download link and provide a small description of the file. To create the table, Entity framework code first is used in this example. Download Entity Framework version 6 or later. Then create a Context for Entity framework.

using System.Data.Entity;
using DataAccess.Model;

namespace DataAccess
    public class FileContext : DbContext
        public DbSet<FileDescription> FileDescriptions { get; set; }

Create a model class for the table.

using System;
using System.ComponentModel.DataAnnotations;

namespace DataAccess.Model
    public class FileDescription
        public int Id { get; set; }
        public string FileName { get; set; }
        public string Description { get; set; }
        public DateTime CreatedTimestamp { get; set; }
        public DateTime UpdatedTimestamp { get; set; }
        public string ContentType { get; set; }
        public string Name { get; set; }

Now the string connection needs to be added to the config file. The name name must match the Context Name, otherwise the this must be manually mapped.

    <add name="FileContext"
          connectionString="Data Source=.;Initial Catalog=WebApiFileTable;Integrated Security=True;"/>

The first time the application starts, a new database table is created:

USE [WebApiFileTable]

/****** Object:  Table [dbo].[FileDescriptions]    Script Date: 29.03.2014 21:59:42 ******/


CREATE TABLE [dbo].[FileDescriptions](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FileName] [nvarchar](max) NULL,
	[Description] [nvarchar](max) NULL,
	[CreatedTimestamp] [datetime] NOT NULL,
	[UpdatedTimestamp] [datetime] NOT NULL,
	[ContentType] [nvarchar](max) NULL,
	[Name] [nvarchar](max) NULL,
	[Id] ASC

Now a simple repository is created for the file description access.

using System;
using System.Collections.Generic;
using System.Linq;
using DataAccess.Model;

namespace DataAccess
    public class FileRepository : IFileRepository, IDisposable
        private FileContext _context;
        public FileRepository()
            _context = new FileContext();

        public IEnumerable<FileDescriptionShort> AddFileDescriptions(FileResult fileResult)
            List<string> filenames = new List<string>();
            for (int i = 0; i < fileResult.FileNames.Count(); i++)
                var fileDescription = new FileDescription
                    ContentType = fileResult.ContentTypes[i],
                    FileName = fileResult.FileNames[i],
                    Name = fileResult.Names[i],
                    CreatedTimestamp = fileResult.CreatedTimestamp,
                    UpdatedTimestamp = fileResult.UpdatedTimestamp,
                    Description = fileResult.Description


            return GetNewFiles(filenames);

        private IEnumerable<FileDescriptionShort> GetNewFiles(List<string> filenames)
            IEnumerable<FileDescription> x = _context.FileDescriptions.Where(r => filenames.Contains(r.FileName));            
            return x.Select( t => new FileDescriptionShort { Name = t.Name, Id = t.Id, Description = t.Description });

        public IEnumerable<FileDescriptionShort> GetAllFiles()
            return _context.FileDescriptions.Select(
                    t => new FileDescriptionShort {Name = t.Name, Id = t.Id, Description = t.Description});

        public FileDescription GetFileDescription(int id)
            return _context.FileDescriptions.Single(t => t.Id == id);

        public void Dispose()
            if (_context != null)
                _context = null;

Web API file upload/download with FileTable persistence.

A Web API controller method is created for the file upload. This method uses an action filter to validate the MIME type, saves the file to the database using a MultipartFormDataStreamProvider instance, creates the file description objects, saves them to the database and returns these to the UI. This method will only work if the client sends a valid content. For this the name must be defined on the multiple input html control.

    public class FileUploadController : ApiController
        private IFileRepository _fileRepository = new FileRepository();
        private static readonly string ServerUploadFolder = "\\\\yourPCName\\mssqlserver\\WebApiFileTable\\WebApiUploads_Dir"; //Path.GetTempPath();

        public async Task<IEnumerable<FileDescriptionShort>> UploadFiles()
            var streamProvider = new MultipartFormDataStreamProvider(ServerUploadFolder);
            await Request.Content.ReadAsMultipartAsync(streamProvider);

            var files =  new FileResult
                FileNames = streamProvider.FileData.Select(entry => entry.LocalFileName.Replace(ServerUploadFolder + "\\","")).ToList(),
                Names = streamProvider.FileData.Select(entry => entry.Headers.ContentDisposition.FileName).ToList(),
                ContentTypes = streamProvider.FileData.Select(entry => entry.Headers.ContentType.MediaType).ToList(),
                Description = streamProvider.FormData["description"],
                CreatedTimestamp = DateTime.UtcNow,
                UpdatedTimestamp = DateTime.UtcNow, 
            return _fileRepository.AddFileDescriptions(files);

The html page for the multiple upload can be defined like this:

<!doctype html>
        <form enctype="multipart/form-data" method="post" action="http://localhost:8081/api/test/files" id="ajaxUploadForm" novalidate="novalidate">

                <legend>Upload Form</legend>
                        <label>Description </label>
                        <input type="text" style="width:317px" name="description" id="description">
                        <label>upload </label>
                        <input type="file" id="fileInput" name="fileInput" multiple>
                        <input type="submit" value="Upload" id="ajaxUploadButton" class="btn">

The page looks like this: (No style.)

Once the files have been upload a view all files action method can be used to select the file descriptions from the database.
Viewing the files:

        public ActionResult ViewAllFiles()
            var fileRepository = new FileRepository();
            var model = new AllUploadedFiles {FileShortDescriptions = fileRepository.GetAllFiles().ToList()};
            return View(model);

The file descriptions are parsed into html using razor.

@model DataAccess.Model.AllUploadedFiles

<fieldset class="form">
    <legend>My Docs</legend>
    <table width="100%">

        @foreach (var doc in Model.FileShortDescriptions)
                <td title="@doc.Name"><a href="http://localhost:8081/api/test/download/@doc.Id">@doc.Name</a></td>
                <td><a href="http://localhost:8081/api/test/download/@doc.Id">@doc.Description</a></td>


Each file link can then be clicked. The link requests the download link in the Web API controller and the file is downloaded.

The download method:

        public HttpResponseMessage Download(int id)
            var fileDescription = _fileRepository.GetFileDescription(id);

            var path = ServerUploadFolder + "\\" + fileDescription.FileName;
            var result = new HttpResponseMessage(HttpStatusCode.OK);
            var stream = new FileStream(path, FileMode.Open);
            result.Content = new StreamContent(stream);
            result.Content.Headers.ContentType = new MediaTypeHeaderValue(fileDescription.ContentType);
            return result;

Conclusions, what’s missing
Now the application can upload files to a database and also download the files again from the database. All that needs to be done now is implement a nice frontend using javascript and provide some security for the download links and probably the upload links.




  1. […] and FileTable in SQL Server 2012, Working with Entity Framework Code First and SQL FILESTREAM, Web API File Upload with MS SQL SERVER FileTable and WCF Large Data and […]

  2. Have to apply minor changes to ConsoleClient to make it work as WebAPIFileUpload UploadFiles expects ContentType from MultipartFileData and description from FormData

    1. Thanks for this info, I will fix this

      Greetings Damien

    2. Anderson · · Reply

      I’m facing these errors you’re talking about, can you help me please what change you have made to get it working, Please help me..

  3. Sorry but I don’t get it. You create table by running sql and after that you are telling us
    “The first time the application starts, a new database table is created”
    What is true?

  4. Hi Eduard, thanks, I’ll clean up the text a bit. The FileDescriptions table is created by EntityFramework when the application starts. The rest was created directly by running the sql scripts. Maybe I should create the whole database using the SQL scripts and not use the migrations.

    Hope this makes sense

    Greetings Damien

  5. great article, can you please show how you have your media type formatter setup?
    I have this exception Formatters

    “unexpected end of mime multipart stream. mime multipart message is not complete.”

  6. Kamalpreet Singh · · Reply

    How is FileDescriptions table linked to WebApiUploads or WebApiUploads_Dir?

  7. ashahwan · · Reply

    How is FileDescriptions table linked to WebApiUploads or WebApiUploads_Dir?

    1. This is the File tables from SQL server. I will create a new example and set this up using Azure Blob storage as a different way of doing this, will make a demo soon. What do you need, would this help?

      Greetings Damien

  8. Sylvain · · Reply

    Hi, I reproduced your code so all things seem OK.
    So, I didn’t see all file in filetable. I see only the file that I’ve uploaded.
    Is it possible to see all files in filetable?
    How can I do that?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: