Click here to Skip to main content
11,717,570 members (80,771 online)
Click here to Skip to main content

Tagged as

FILESTREAM MVC: Download and upload images from SQL Server

, 20 Feb 2011 Apache 21.3K 467 10
Rate this:
Please Sign up or sign in to vote.
In this article I will go over an alternative approach that relies on the FILESTREAM column types introduced in SQL Server 2008.

Introduction

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 ReadFile and WriteFile. But at the same time, the same data is available through normal T-SQL operations like SELECT or 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.

Using FILESTREAM

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 Smile | :) .

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');
go

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),
    [file_name] varchar(256),
    [content_type] varchar(256),
    [content_coding] varchar(256),
    [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]));
go

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:

/// <span class="code-SummaryComment"><summary>
</span>

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 SqlFileStream is sealed, it cannot be inherited...) and then handle the resource management in this derived class' Dispose:

/// <span class="code-SummaryComment"><summary>
</span>

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 Wink | ;-) .

You can download the latest source for the entire MVC project from here.

Conclusion

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:

License

This article, along with any associated source code and files, is licensed under The Apache License, Version 2.0

Share

About the Author

rusanu
bugcollect.com
United States United States
Remus Rusanu is a developer with the SQL Server team.

You may also be interested in...

Comments and Discussions

 
QuestionTransaction for reading data? Pin
Fregate16-Mar-14 19:06
memberFregate16-Mar-14 19:06 
QuestionCould You help in applying File streaming in ASP.net not in Asp.net MVC? Pin
Mohammad A. Amer15-Jun-13 3:21
memberMohammad A. Amer15-Jun-13 3:21 
Questionhow to use search and advanced search to search my files Pin
midosoft110-Mar-13 0:11
membermidosoft110-Mar-13 0:11 
Generalthanks for sharing - have 5 Pin
Pranay Rana7-Feb-11 2:22
memberPranay Rana7-Feb-11 2:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150901.1 | Last Updated 20 Feb 2011
Article Copyright 2011 by rusanu
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid