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
RECONFIGURE
GO

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:

CREATE DATABASE WebApiFileTable
ON PRIMARY
(Name = WebApiFileTable,
FILENAME = 'C:\damienbod\WebApiFileTable\FTDB.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = WebApiFileTableFS,
FILENAME='C:\damienbod\WebApiFileTable\FS')
LOG ON
(Name = WebApiFileTableLog,
FILENAME = 'C:\damienbod\WebApiFileTable\FTDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'WebApiFileTable');
GO

Now you can check if your database settings are ok.

SELECT DB_NAME(database_id),
non_transacted_access,
non_transacted_access_desc
FROM sys.database_filestream_options;
GO

The database should be configured as follows:

Now create a Table for the File Uploads:

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

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

\\yourPCname\mssqlserver\WebApiFileTable\WebApiUploads_Dir

The files can also be accessed using plain SQL.

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

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
    {
        [Key]
        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.

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

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

USE [WebApiFileTable]
GO

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

SET QUOTED_IDENTIFIER ON
GO

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,
 CONSTRAINT [PK_dbo.FileDescriptions] 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]
GO

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
{
    [LifecycleTransient]
    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
                };

                filenames.Add(fileResult.FileNames[i]);
                _context.FileDescriptions.Add(fileDescription);
            }

            _context.SaveChanges();
            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.Dispose();
                _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.

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

        [Route("files")]
        [HttpPost]
        [ValidateMimeMultipartContentFilter]
        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>
<html>
    <head>
        <title>Test</title>
    </head>
    <body>
        <form enctype="multipart/form-data" method="post" action="http://localhost:8081/api/test/files" id="ajaxUploadForm" novalidate="novalidate">

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

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%">
        <tr>
            <th>Filename</th>
            <th>Description</th>

        </tr>
        @foreach (var doc in Model.FileShortDescriptions)
        {
            <tr>
                <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>
            </tr>
        }
    </table>

</fieldset>

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:

        [Route("download/{id}")]
        [HttpGet]
        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.

Links:

http://akshaysnotion.blogspot.in/2014/05/file-upload-using-mvc4-web-api-and-sql.html#more