FileStream data type is a very important feature of SQL Server 2008 and gradually getting popular amongst developer for it’s feasibility. And in the past few days specially after “Configure SQL Server 2008 for File Stream” post. I received several feedbacks regarding the usage of FileStream with Ado.net and Frankly there is not much stuff available on Google for this topic.
In this post, I will guide you to use FileStream Data type in Ado.net. But before we start make sure you have configure your SQL Server 2008 instance to use File Stream Data type and for this you can read this post.
Once you finish with the Configuration execute the following script
1: CREATE TABLE [dbo].[tbl_Files](
2: [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL,
3: [SystemNumber] [int] NOT NULL,
4: [SystemFile] [varbinary](max) FILESTREAM NULL,
5: [FileType] [varchar](5) NULL,
6: UNIQUE NONCLUSTERED
7: (
8: [Id] ASC
9: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
10: ) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]
11:
12: GO
13:
14: ALTER TABLE [dbo].[tbl_Files] ADD CONSTRAINT [DF_tbl_Files_Id] DEFAULT (newid()) FOR [Id]
This will create a table with FileStream Data type. Notice the FileType field I have used here to determine the type of file which we will use when we were downloading the file.
Now that we have created a table, lets now move on to the Stored Procedures by which we will access this newly created table.
Security Setting:
Don’t get confused with the heading, there is no additional setting required. You need to do one of the two available options. Either you need to specify “Integrated Security = true” in Connection String or you need to implement Asp.net Impersonation. It is there because SQL Server 2008 will not allow un authenticated user or instance to read/modify the file.
Most of the developers usually aware of Integrated Security stuff but let me give a little detail about asp.net impersonation. Actually, it is a way to Authorize the Instance of your asp.net application on SQL Server by using Credential Information.
Following links will help you to understand or implement impersonation.
http://blogs.msdn.com/shawnfa/archive/2005/03/21/400088.aspx
http://blogs.msdn.com/saurabhkv/archive/2008/05/29/windowsidentity-impersonation-using-c-code.aspx
http://www.west-wind.com/WebLog/posts/1572.aspx
Add Procedure:
Lets create a procedure call it “”FileAdd” and past the following script.
1: Create PROCEDURE [dbo].[FileAdd]
2: @SystemNumber int,
3: @FileType varchar(5),
4: @filepath varchar(max) output
5: AS
6: BEGIN
7: -- SET NOCOUNT ON added to prevent extra result sets from
8: -- interfering with SELECT statements.
9: SET NOCOUNT ON;
10:
11: DECLARE @ID UNIQUEIDENTIFIER
12: SET @ID = NEWID()
13:
14: INSERT INTO [dbo].[tbl_Files]
15: ([Id],[SystemNumber],SystemFile,FileType)
16: VALUES (@ID ,@SystemNumber,CAST('' AS VARBINARY(MAX)),@FileType)
17:
18: select @filepath = SystemFile.PathName() from tbl_Files where Id = @ID
19:
20:
21:
22: END
In the above procedure, we add new records in our table and just pass empty (null) to the FileStream field because we first want our SQL Server to create an empty file on NTFS location which we can access from our code behind by using the path which we have taken as Output Parameter here.
notice the SystemFile.PathName(), it is a new function introduced in SQL Server 2008 which will return the NTFS location of the file.
Get Procedure:
Create a procedure and call it “FileGet”
1: CREATE PROCEDURE [dbo].[FileGet]
2: @Id varchar(50)
3: AS
4: BEGIN
5: select SystemFile.PathName(),FileType from tbl_Files where Id = @ID
6: END
This is a simple stuff, we are returning PathName and FileType by specifying ID. Just to read the record.
Upload and Store:
To save the file in the file stream, we will use FileUpload control to upload the file and then save it to FileStream field. For that we have created a page and drag FileUpload control with an Upload button.
Now on the click event of the button write the following code.
1: byte[] buffer = new byte[(int)FileUpload1.FileContent.Length];
2: FileUpload1.FileContent.Read(buffer, 0, buffer.Length);
3:
4:
5: if (FileUpload1.FileContent.Length > 0)
6: {
7: SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
8: objSqlCon.Open();
9: SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
10:
11: SqlCommand objSqlCmd = new SqlCommand("FileAdd",objSqlCon,objSqlTran);
12: objSqlCmd.CommandType = CommandType.StoredProcedure;
13:
14: SqlParameter objSqlParam1 = new SqlParameter("@SystemNumber", SqlDbType.Int);
15: objSqlParam1.Value = "1";
16:
17: SqlParameter objSqlParam2 = new SqlParameter("@FileType", SqlDbType.VarChar,4);
18: objSqlParam2.Value = System.IO.Path.GetExtension(FileUpload1.FileName);
19:
20: SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1);
21: objSqlParamOutput.Direction = ParameterDirection.Output;
22:
23: objSqlCmd.Parameters.Add(objSqlParam2);
24: objSqlCmd.Parameters.Add(objSqlParam1);
25: objSqlCmd.Parameters.Add(objSqlParamOutput);
26:
27:
28: objSqlCmd.ExecuteNonQuery();
29:
30: string Path = objSqlCmd.Parameters["@filepath"].Value.ToString();
31:
32: objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
33:
34: byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
35:
36:
37: SqlFileStream objSqlFileStream = new SqlFileStream(Path, objContext, FileAccess.Write);
38:
39: objSqlFileStream.Write(buffer, 0, buffer.Length);
40: objSqlFileStream.Close();
41:
42: objSqlTran.Commit();
Well, in the first two lines we have saved the uploaded file in byte and call this variable “buffer”.
As we are simply using ADO.net, that is why in line 7 and 8 we have created and open a connection. Where as it is worth to mention here, we need to use transaction when we want to do any operation on FileStream field that is why we have begin a new transaction in line no 9.
On line number 11 to 30, we have a simply setup command object and parameter stuff and then execute the procedure and save the output parameter in a variable called “Path”.
This new variable will contain the NTFS location of the file which is stored on SQL Server FileStream. It should be clear that, this file is empty yet as we have not stored any thing in it.
Now on line number 32 we have reused command object and this time we are executing a simple statement “GET_FILESTREAM_TRANSACTION_CONTEXT”. It is also a newly added feature in SQL Server 2008 which will return current transaction context to be used in the next few lines. Now, on line number 34 we have stored the output of the above statement in byte.
In line number 37, here is some thing new which is called “SqlFileStream”. It is a new class which you can find under “System.Data.SqlTypes”. It seems more like FileStream of “System.IO” but it should be cleared here that the file stored in FileStream field cannot be access using regular file stream object of “System.IO” we need to use SqlFileStream to access those files which are stored in FileStream field.
In line no 39 and on, we are writing the file with the content of uploaded file (Remember we have stored our uploaded file in bytes and call it “buffer”). and that’s it.
Read The Stored File:
We have finished with storing the file, now lets see how can we read this file back. To do this, Drag a Grid View and make it similar to the following
1: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
2: onrowcommand="GridView1_RowCommand">
3: <Columns>
4: <asp:BoundField DataField="ID" HeaderText="ID" />
5: <asp:BoundField DataField="SystemNumber" HeaderText="System Id" />
6: <asp:TemplateField>
7: <ItemTemplate>
8: <asp:LinkButton ID="lbGetFile" runat="server" CommandName="GetFile" CommandArgument='<%#Eval("ID") %>' Text="Get File"></asp:LinkButton>
9: </ItemTemplate>
10: </asp:TemplateField>
11: </Columns>
12:
13: </asp:GridView>
And bind the GridView using the following code.
1: protected void bindData()
2: {
3: SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
4: objSqlCon.Open();
5:
6: SqlCommand objSqlCmd = new SqlCommand("Select * from tbl_Files", objSqlCon);
7: SqlDataAdapter objSqlDat = new SqlDataAdapter(objSqlCmd);
8: DataTable objdt = new DataTable();
9: objSqlDat.Fill(objdt);
10:
11: GridView1.DataSource = objdt;
12: GridView1.DataBind();
13: }
Well, the above markup and the code is enough self explaining but the little important stuff to mention here is the link button. We will use the same link button to download the stored file. Lets quickly move on to the RowCommand implementation of the GridView.
1: protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
2: {
3: if (e.CommandName == "GetFile")
4: {
5:
6: SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
7: objSqlCon.Open();
8: SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
9:
10: SqlCommand objSqlCmd = new SqlCommand("FileGet", objSqlCon, objSqlTran);
11: objSqlCmd.CommandType = CommandType.StoredProcedure;
12:
13: SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);
14: objSqlParam1.Value = e.CommandArgument;
15:
16: objSqlCmd.Parameters.Add(objSqlParam1);
17: string path = string.Empty;
18: string fileType = string.Empty;
19:
20: using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
21: {
22: while (sdr.Read())
23: {
24: path = sdr[0].ToString();
25: fileType = sdr[1].ToString();
26: }
27:
28: }
29:
30: objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
31:
32: byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
33:
34:
35: SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);
36:
37: byte[] buffer = new byte[(int)objSqlFileStream.Length];
38: objSqlFileStream.Read(buffer, 0, buffer.Length);
39: objSqlFileStream.Close();
40:
41: objSqlTran.Commit();
42: Response.AddHeader("Content-disposition", "attachment; filename=" + Path.GetFileName(path) + fileType);
43: // Here you need to manage the download file stuff according to your need
44: Response.ContentType = "application/octet-stream";
45:
46: Response.BinaryWrite(buffer);
47:
48:
49:
50: }
51: }
Well, in the first 8 lines, we have created and opened a connection and then begin the transaction. from line no 10 to 28, we are setting the parameter stuff, executing the procedure and save the output in the two variable called “path” and “fileType”.
In line no 30 to 32, we are executing the the transaction context statement and then save the output the bytes. (Same we have done when we were writing the file)
In line no 35 to 40, we have used the same SqlFileStream and instead of writing, we are reading the file this time(notice line no 38) and save the content of the file to the bytes. Now we have file content in bytes, So we have now commit the transaction in line no 41.
In line no 42 and 44, we are setting the content type and specifying the file name with the extension. That is why, we have also saved file type in the database so that at the time of downloading we can make it available in its original state.
And in line no 46, we are simply writing the binary of the file to the browser so that it can be downloaded.
Conclusion:
I have tried my best to explain the integration of FileStream field with ADO.net, and I found this is an easy way to accomplish the task. You can download the VS 2008 solution which contain the complete source code along with procedures and table SQL.
Since, it is a new featured in SQL Server 2008 which is still in CTP by the time I am posting this stuff that is why we can expect some modifications in the method of reading and saving files using FileStream. If somebody face any challenge in the above code.