Having done the necessary set up for configuring FILESTREAM on a SQL Server instance in Part - I, we will now look at how to query a FILESTREAM column from .NET code and insert new files.
For this purpose, I have created one more table called UserMaster. This has the usual details of a user like the first name, last name and a column PhotoId which is a foreignkey to the ProductPhotoId that we created in the previous part.
Querying files or data from the FILESTREAM is not too different from how you would query and display data from an ordinary table. I first create a stored procedure to get the user details and the photo associated with each user. The stored procedure is as below
create proc dbo.GetUserDetails
Select UM.UserId, UM.UserName, UM.FirstName, UM.LastName, UM.EmailAddress, PP.ProductPhotoId, PP.LargePhoto
UserMaster UM INNER JOIN ProductPhotoWithFileStream PP
As you can see, there is nothing special with the above stored procedure. The next step is to display these photos in a Windows form. I have a DataGridView which is going to display the pics. The code for Form.cs is
To insert a user, I create a form where the user will enter the details and select the picture associated with the user.
We will ignore the rest of the code in the form and concentrate on the Insert Button click. Below is the code for the complete button click event
I have tried and added comments so that the snippet makes sense but I will re-cap the steps to insert a new file. The first thing to do is to insert an empty string value into the FILESTREAM column. This is done so that the SQL Server creates a new file on the file system. Note that a null value does not create a file.
The next thing is to get the path of the new file created and wrap it around the same transaction. This is done between lines 24 and 35. Once we get the transaction context, then writing the file is like writing to any other file on the file system.
Also note the use of the SQLFileStream data type which is tightly integrated with the FILESTREAM attribute. The downside of the approach I have shown above is the number of round trips to the database. There are atleast three, one to insert the empty value into the FILESTREAM column, another one to get the path and the final trip to get the transaction context. Though I did read somewhere that this can now be reduced with the output clause I have not tried this yet. I will get around to that in a while and post an update to this post.
That concludes our two part post on the FILESTREAM attribute. Though it is easy to get excited by the ability of thee FILESTREAM attribute, there are as with a lot of other things, considerations that need to be taken into account when using this. I found the below links useful for further reading