In a previous article, I have shown how it is possible to use efficient streaming semantics when Downloading and uploading images from SQL Server via ASP.NET MVC. In this article, I will go over an alternative approach that relies on the
FILESTREAM column types introduced in SQL Server 2008.
What is FILESTREAM?
FILESTREAM storage is a new option available in SQL Server 2008 and later that allows for BLOB columns to be stored directly on the file system as individual files. As files, the data is accessible through Win32 file access API like
WriteFile. But at the same time, the same data is available through normal T-SQL operations like
UPDATE. Not only that, but the data is contained logically in the database so it will be contained in a database backup, it is subject to ordinary transaction commit and rollback behavior, it is searched by SQL Server FullText indexes and it follows the normal SQL Server security access rules: if you are granted SELECT permission on the table, then you can open the file to read. There are some restrictions, eg. a database with FILESTREAM cannot be mirrored. For a full list of restrictions and limitations, see Using FILESTREAM with Other SQL Server Features. Note that SQL Server Express edition does support FILESTREAM storage.
Another attribute of the FILESTREAM storage is the size limitation: normal BLOB column values have a maximum size of 2Gb. FILESTREAM columns are limited only by the volume size limit of the file system. 2Gb may seem like a large value, but consider that a media file like an HD movie stream can easily go up to a size of 5Gb.
One way to use FILESTREAM columns is to treat them as ordinary BLOB values and manipulate them through T-SQL. The one restriction in place is that the efficient partial update syntax for BLOBs is not supported. That is, one cannot issue
UPDATE table SET column.WRITE(...) WHERE ... on a FILESTREAM column. But where FILESTREAM storage begins to shine is when accessed through the system file API. This allows the application to efficiently read, write and seek in a large BLOB value, just as it would in a file. In fact, the application does read, write and seek in a file :-).
Native Win32 applications use a new API function
OpenSqlFilestream that opens a
HANDLE that can be then used with the file IO API functions. Managed applications use the new
SqlFileStream class that exposes a Stream based on the underlying FILESTREAM value. Both the native and the manged API require as input two special values, a
PathName and a
Transaction Context that must be obtained previously from the SQL Server using T-SQL.
The requirement to provide a Transaction Context when manipulating a FILESTREAM value through the file IO API highlights another aspect of working with this new type: a T-SQL transaction must be started and must be kept open while the file is manipulated, and then it must be committed. If you think about it such a requirement is to be expected, since we said that FILESTREAM columns are subject to normal T-SQL transaction commit and rollback semantics, including when they are manipulated through the Windows file read/write API.
FILESTREAM based images table
In order to have FILESTREAM column, we must have a special filegroup in our database, a FILESTREAM filegroup. You can either add a new filegroup to your existing database, or you can create the database with a FILESTREAM filegroup from scratch. Also the FILESTREAM feature must be enabled on the SQL Server instance. For all the details, see Getting Started with FILESTREAM Storage. For my project, I’m simply going to create a new database with a FILESTREAM filegroup:
create database images
on (name='images_data', filename='c:\temp\images.mdf')
, filegroup FS contains FILESTREAM
(name = 'images_files', filename='c:\temp\images_files')
log on (name='images_log', filename='c:\temp\images.ldf');
The media table used by our MVC project is going to be similar to the one used in the previous article but the
content column will have the FILESTREAM attribute added. A table that has FILESTREAM columns is required to have a
ROWGUIDCOL column, so we’re going to add one of those as well:
create table media (
[media_id] int not null identity(1,1),
[media_rowguid] uniqueidentifier not null
ROWGUIDCOL UNIQUE default newsequentialid() ,
[content] varbinary(max) filestream,
constraint pk_media_id primary key([media_id]),
constraint unique_file_name unique ([file_name]));
FILESTREAM based IMediaRepository
If you haven’t read the previous article Download and upload images from SQL Server via ASP.NET MVC yet, now is a good time to do it. I am going to reuse the same code and simply provide a new implementation for the
IMediaRepository interface, an implementation that works with FILESTREAM storage:
The implementation is pretty straightforward. The
PostFile methods starts a transaction, inserts a row in the images table, and obtains the PahtName and FILESTREAM transaction context to the newly inserted row, and then opens a
SqlFileStream and copies in the uploaded file directly into the file that backs the FILESTREAM column. The
GetFileByName method obtains the PathName and FILESTREAM transaction context of the desired file and then returns a
SqlFileStream that directly accesses the file in which the FILESTREAM value is stored.
The only thing requiring explanation is the
MvcResultSqlFileStream class. Because the
SqlFileStream uses a transaction context, it is required to keep the
SqlConnection and the
SqlTransaction open until the
SqlFileStream finishes accessing the content. In the
PostFile method, this requirement is achieved easily because the entire usage of the
SqlFileStream completes within the stack frame, but the
GetFileByName method has to return a
Stream that in turn is passed into a
FileStreamResult by the
MediaController class and there is no control over how the MVC framework will use this returned
ActionResult. My solution is to wrap the
SqlFileStream in a new
Stream derived class (since
sealed, it cannot be inherited...) and then handle the resource management in this derived class'
This extra class feels rather unnecessary, but unfortunately is required because the MVC framework has no proper handling of resources passed out from the controller. If you ask me,
ActionResult should implement
IDisposable, but then of course nobody asks me ;-).
You can download the latest source for the entire MVC project from here.
Handling media content stored in the database using FILESTREAM storage makes for a nicer developer experience. The FILESTREAM content can be accessed using streaming semantics based on the
SqlFileStream class, or using the Win32 file IO API for native applications. By comparison the previous article that relied on straight T-SQL to manipulate the BLOB values had to do some very unintuitive tricks in order to achieve streaming semantics, specially for uploading a file. FILESTREAM storage simplifies development and is supported in every SQL Server edition, including Express.
If you intend to deploy FILESTREAM in your environment I recommend going over some Knowledge Base and white paper articles first: