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
as
begin
Select UM.UserId, UM.UserName, UM.FirstName, UM.LastName, UM.EmailAddress, PP.ProductPhotoId, PP.LargePhoto
from
UserMaster UM INNER JOIN ProductPhotoWithFileStream PP
On
PP.ProductPhotoId=UM.PhotoId
end
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
1: var connString = ConfigurationManager.ConnectionStrings["FileStreamConn"].ConnectionString;
2: var query = "GetUserDetails";
3: var daProducts = new SqlDataAdapter(query,connString);
4: 5: productsGridView.DataSource = null;
6: productsGridView.Refresh(); 7: var binding = new BindingSource()
8: productsGridView.DataSource = binding; 9: 10: // Populate a new data table and bind it to the BindingSource.
11: var table = new DataTable();
12: daProducts.Fill(table); 13: binding.DataSource = table; 14: 15: // Resize the DataGridView columns to fit the newly loaded content.
16: productsGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader); 17: productsGridView.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders);
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
1: //1. Call the procedure to insert an empty value as photo and get transaction context
2: var connString = ConfigurationManager.ConnectionStrings["FileStreamConn"].ConnectionString;
3: var query = "GetUserDetails";
4: string filePath = String.Empty;
5: string selPhotoPath = txtFilePath.Text;
6: SqlCommand daInsertCommand=null;
7: 8: try
9: { 10: 11: //1.1open a new connection
12: SqlConnection conn = new SqlConnection(connString);
13: conn.Open(); 14: 15: 16: SqlTransaction tranPhoto = conn.BeginTransaction("photoInsert");
17: 18: //1.2 Execute the command to insert the photo and get the photo id
19: daInsertCommand = new SqlCommand(query,conn);
20: daInsertCommand.Transaction = tranPhoto; 21: var PhotoId = daInsertCommand.ExecuteScalar(); 22: 23: //1.3 Get the Path Name for the newly created file.
24: query = "Select LargePhoto.PathName() from ProductPhotoWithFilestream where ProductPhotoId = " + PhotoId;
25: 26: daInsertCommand.CommandText = query; 27: var PhotoPath = daInsertCommand.ExecuteScalar(); 28: if (!PhotoPath.Equals(DBNull.Value))
29: { 30: filePath = (string)PhotoPath;
31: } 32: 33: //1.4 Get the transaction context for writing to the file.
34: daInsertCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
35: var txContext = daInsertCommand.ExecuteScalar(); 36: if (!txContext.Equals(DBNull.Value))
37: { 38: //convert the transaction into a bytearray
39: byte[] transContext = (byte[])txContext;
40: //create a photostream object to write
41: SqlFileStream photoStream = new SqlFileStream(filePath, transContext, System.IO.FileAccess.Write);
42: 43: //read the selected image as a byte array
44: byte[] imgByte = File.ReadAllBytes(selPhotoPath);
45: 46: //write to the sql filestream
47: photoStream.Write(imgByte, 0, imgByte.Length); 48: 49: //close the filestream and commit transaction
50: photoStream.Close(); 51: 52: 53: //2. Insert the User record with the PhotoId
54: daInsertCommand.CommandText = "InsertUser";
55: daInsertCommand.CommandType = CommandType.StoredProcedure; 56: daInsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 10);
57: daInsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 10);
58: daInsertCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 20);
59: daInsertCommand.Parameters.Add("@emailAdd", SqlDbType.VarChar, 100);
60: daInsertCommand.Parameters.Add("@PhotoId", SqlDbType.Int, 32);
61: 62: daInsertCommand.Parameters[0].Value = txtFirstName.Text; 63: daInsertCommand.Parameters[1].Value = txtLastName.Text; 64: daInsertCommand.Parameters[2].Value = txtUserName.Text; 65: daInsertCommand.Parameters[3].Value = "test@add.com";
66: daInsertCommand.Parameters[4].Value = PhotoId; 67: 68: daInsertCommand.ExecuteNonQuery(); 69: 70: daInsertCommand.Transaction.Commit(); 71: 72: } 73: } 74: catch(Exception ex)
75: { 76: daInsertCommand.Transaction.Rollback(); 77: MessageBox.Show(ex.Message); 78: }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
Using FILESTREAM with Other SQL Server Features
