Before SQL Server 2008 came out, there was always a big debate about how we should store BLOBs such as images, PDFs, word documents, etc. Our options were very limited. Either we stored the BLOB on the file system or file server, and stored a reference path to that BLOB in the database, or we went bold, and stored the actual BLOB in the database. Tons of online articles have been written about how you should never store BLOBs in the database, but storing them on the file system had it’s own disadvantages. For example, if someone deleted an image on your file system which is referenced in the database, the database would never know that the link is now broken. Similarly, if one deleted the database record with the link reference to an image on the file system, the image would just sit there as an orphan.
SQL Server 2008 solved these problems by introducing FILESTREAM, which integrates the Database Engine with an NTFS file system by storing varbinary(max) BLOB data as files on the file system, and at the same time maintaining security and referential integrity between the BLOB and the path to that BLOB through SQL Server. Now we can insert, update, query, search, and back up FILESTREAM data through T-SQL commands. Visual Studio 2008 now also has all of the necessary name spaces and API’s to so save and retrieve BLOB’s to and from the database. In this post, I’m going to try to walk you through, from start to finish, how to create an ASP.NET front end, and SQL Server 2008 backend to deal with BLOB’s.
STEP 1: Make sure your database is set up for Filestream:
We are all familiar with the fact that when we create a new database, at least two files are created: a Data file (.MDF) and a Log file (.LDF). We also know that those files exist on one or more filegroups. SQL Server 2008 now has a special section for Filestream filegroups, and a new File Type Filesetream Data. Without setting up a specific Filegroup and File for your filestream data, you will not be able to create a table to hold your filestream.
So first, create a new filegroup as shown below:
Second, add a new database file of type Filestream Data, and specify the newly created Filegroup:
If you scroll to the right in the screen above, you’ll be able to specify the location for your filestream, similarly to the locations you have for your data and your log files. Now, your database is ready for a table that will hold your BLOBs.
STEP 2: CREATE YOUR TABLE FOR YOUR MEDIA AS SHOW BELOW:
1: CREATE TABLE [dbo].[media](
2: [mediaId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
3: [fileName] [nvarchar](256) NOT NULL,
4: [contentType] [nvarchar](256) NOT NULL,
5: [file] [varbinary](max) NULL,
6: PRIMARY KEY CLUSTERED
7: (
8: [mediaId] ASC
9: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
10: ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
11: ) ON [PRIMARY]
12:
13: GO
14:
15: ALTER TABLE [dbo].[media] ADD DEFAULT (0x) FOR [file]
16: GO
STEP 3: CREATE AN ASP.NET APPLICATION FROM WHERE WE WILL UPLOAD FILES
ASPX file: in your default page, drop a simple FileUpload control and a button:
1: <html xmlns="http://www.w3.org/1999/xhtml" >
2: <head runat="server">
3: <title></title>
4: </head>
5: <body>
6: <form id="form1" runat="server">
7: <div>
8: <h1 style="color:Red">File Stream Example</h1><br /><br />
9: <asp:FileUpload ID="FileUpload1" runat="server" />
10: <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
11: </div>
12: </form>
13: </body>
14: </html>
Code Behind:
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Web;
5: using System.Web.UI;
6: using System.Web.UI.WebControls;
7: using System.IO;
8: using System.Data;
9: using System.Data.SqlClient;
10: using System.Data.SqlTypes;
11: using Microsoft.Win32;
12: using Microsoft.Win32.SafeHandles;
13: using System.Security.Permissions;
14:
15:
16: namespace FileStreamExample
17: {
18: public partial class _Default : System.Web.UI.Page
19: {
20: protected void Page_Load(object sender, EventArgs e)
21: {
22:
23: }
24:
25: protected void btnSave_Click(object sender, EventArgs e)
26: {
27: #region Database Connection
28: //Open a connection to the database
29: SqlConnectionStringBuilder conString = new SqlConnectionStringBuilder();
30: conString.DataSource = @"YourServer";
31: conString.IntegratedSecurity = true;
32: conString.InitialCatalog = "YourDB";
33: SqlConnection con = new SqlConnection(conString.ConnectionString);
34: con.Open();
35: #endregion Database Connection
36:
37: //Start up a database transaction
38: SqlTransaction tran = con.BeginTransaction();
39:
40: //Create a Guid to be used on insert.
41: Guid mediaID = Guid.NewGuid();
42:
43: //Insert a row into the table to create a handle for streaming write.
44: SqlCommand cmd = new SqlCommand("INSERT [dbo].[media]([mediaId], [fileName], [contentType]) VALUES( @mediaId, @fileName, @contentType);", con, tran);
45: cmd.Parameters.Add("@mediaId", SqlDbType.UniqueIdentifier).Value = mediaID;
46: cmd.Parameters.Add("@fileName", SqlDbType.NVarChar, 256).Value = FileUpload1.FileName;
47: cmd.Parameters.Add("@contentType", SqlDbType.NVarChar, 256).Value = FileUpload1.PostedFile.ContentType;
48: cmd.ExecuteNonQuery();
49:
50: // Get a filestream PathName token and filestream transaction context.
51: // These items will be used to open up a file handle against the empty blob instance.
52: cmd = new SqlCommand("SELECT [file].PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM [dbo].[media] WHERE [mediaId] = @mediaId;", con, tran);
53: cmd.Parameters.Add("@mediaId", SqlDbType.UniqueIdentifier).Value = mediaID;
54:
55: // Read in results of query.
56: SqlDataReader rdr;
57: rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
58: rdr.Read();
59: SqlString sqlFilePath = rdr.GetSqlString(0);
60: SqlBinary transactionToken = rdr.GetSqlBinary(1);
61: rdr.Close();
62:
63: SqlFileStream sqlFile = new SqlFileStream(sqlFilePath.Value,transactionToken.Value,FileAccess.Write);
64: byte[] buffer = new byte[512 * 1024]; //512kb
65: int bytesRead = FileUpload1.PostedFile.InputStream.Read(buffer, 0, buffer.Length);
66: while (bytesRead > 0)
67: {
68: sqlFile.Write(buffer, 0, bytesRead);
69: bytesRead = FileUpload1.PostedFile.InputStream.Read(buffer, 0, buffer.Length);
70: }
71: #region Connection cleanup
72: // Commit transaction, cleanup connection.
73: sqlFile.Close();
74: FileUpload1.PostedFile.InputStream.Close();
75: tran.Commit();
76: con.Close();
77: #endregion Connection cleanup
78: }
79: }
80: }
STEP 4: GO AND CHECK WHAT IS IN YOUR TABLE!
Now, you are wondering, is the file column really where your file is? Well, the answer is not really. If you browse on your computer to the folder where you placed your filestream file in step 1, you will notice that there is a folder in there there your file is. The name will be encrypted, and your media table holds an encrypted pointer to that file.
Now…do this experiment for me. Delete the record from your media table, and see if the file disappeared from your file folder.
Did you do it?
If you did, you will notice that the answer is no. Now think for a minute why not! Now realize that the file is not a regular file on your file system. It is a file stream data controlled by the database. Therefore, if you wanted to restore the deleted row from the transaction log, and the actual file was deleted, where would it get the file from? Now…if you truncate your transaction log, you will see that the file will actually disappear. Works like a charm!
CREDITS:
I would like to thank Guy Burnstein and his blog for helping me get started with file stream. He has a lot of good stuff in there on filestream, including information not included in this blog. You can check it out at this link:
http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/12/sqlfilestream-managed-api-for-sql-server-2008-filestream.aspx