SQL Server 2008 FILESTREAM - Writing a file to a FILESTREAM column

Zach Skyles Owens - SQL Server 2008 FILESTREAM and WPF MediaElement - Part 2 (Writing FILESTREAM Data)

Wow... It's been a long time coming. I promised that I would explain in more detail how to write FILESTREAM data to SQL Server 2008. This is the second article in a series and uses the sample published on the SQL Server Community Samples site on CodePlex.

Writing data to a varbinary(max) FILESTREAM column in SQL is a bit more involved then just opening a file on the filesystem. SQL Server needs to manage this operation within a transaction which adds a bit of complexity. Here are the basic steps... These steps apply both to reading and writing.

Start a SQL Server transaction

1. Insert a row into the table containing metadata
2. Select the PathName from SQL Server which will be used to get a handle
3. Open a handle for writing using sqlncli10.dll
4. Use that handle within System.IO classes
5. Commit the transaction
6. Now that the basic steps are laid out, let's take a closer look

...

So... Here's where I think things get interesting. You can see that the SELECT statement above calls the PathName() method of the FILESTREAM column. Here's an example of the results from the query above. Note the use of UDTs and CLR!

\\ZOWENS-NB3\MSSQLSERVER\v1\FilestreamWpfHttp\dbo\media\file\4C3C9C2D-8268-43FF-8317-D507319FE21C

This is a "virtual" path managed by SQL Server. It consists of \\COMPUTER_NAME, followed by a configurable handler \MSSQLSERVER...

...

The code above uses a simple C# class "SqlNativeClient" that wraps the sqlncli10.dll I mentioned above. This C# class is key to working with FILESTREAM in managed code. You can see that we passed in the sqlFilePath variable from the PathName() query.

// Open up a new stream to write the file to the blob.
System.IO.FileStream destBlob = new System.IO.FileStream(handle, FileAccess.Write);

"Old school" System.IO file manipulation using the handle obtained from the SqlNativeClient class above.

// Commit transaction       
txn.Commit();

There you have it... It's not rocket science but there are a few tricks."

This article shows the magic behind the new FILESTREAM column attribute. It shows how it's not just some kind of uber-BLOB, but the delegating of responsibility based on capability. Showing how the SQL does what it is good at, while delegating to the file system to do what it is good it (i.e. file operations, reading, writing, etc)

Interesting... But I'm a little afraid of getting too much into this. What if it doesn't work out and MS kills it in a release or two? (Think Exchange M: drive, Notification Services, etc)

Still it's pretty darn cool and helps solve a number of problems we have today...

Related Past Post XRef:
More SQL Server 2008 FileStream Fun
Playing with the SQL Server 2008 FileStream Attribute