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
- Insert a row into the table containing metadata
- Select the PathName from SQL Server which will be used to get a handle
- Open a handle for writing using sqlncli10.dll
- Use that handle within System.IO classes
- Commit the transaction
Now that the basic steps are laid out, let's take a closer look.
// Start up a database transaction.
SqlTransaction txn = cxn.BeginTransaction();
No need for explanation there.
// Insert a row into the table to create a handle for streaming write.
SqlCommand cmd = new SqlCommand("INSERT [dbo].[media]([mediaId], [fileName],
[contentType]) VALUES( @mediaId, @fileName, @contentType);", cxn, txn);
This is worth a bit talking about. Why do you need to insert a row with metadata? The answer is that in order to get a handle to the FILESTREAM column the row cannot have a NULL value in the FILESTREAM column. This took some trial and error to discover.
If you look closely at the create table script in the sample code you will see that the varbinary(max) FILESTREAM column default is set to a zero byte binary value.
file varbinary(max) FILESTREAM DEFAULT(0x)
This should make a bit more sense once we look at the next step.
// Get a filestream PathName token and filestream transaction context.
// These items will be used to open up a file handle against the empty blob instance.
cmd = new SqlCommand("SELECT [file].PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM [dbo].[media] WHERE [mediaId] = @mediaId;", cxn, txn);...
// Read in results of file.PathName()
SqlString sqlFilePath = rdr.GetSqlString(0);
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...
Now what?
// Get a Win32 file handle to the empty blob instance using SQL Native Client call.
// This is required in order to write to the empty blob instance.
SafeFileHandle handle = SqlNativeClient.OpenSqlFilestream(
sqlFilePath.Value,
SqlNativeClient.DESIRED_ACCESS_WRITE,
0,
transactionToken.Value,
(UInt32)transactionToken.Value.Length,
new SqlNativeClient.LARGE_INTEGER_SQL(0));
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.
