Use File Stream in Sql Server 2008 R2 Edition

Introduction

SQL Server 2008 has a file stream data type. This data type allows users to store content on the disk instead of the database as a real file.
There are many articles on this topic on the net, however they are not all from the perspective of a developer. This article will just tell us enough on how to get started removing all the extra noise.
I will try to link more advanced concepts as they are required but this article will focus on getting a developer up and running with SQL server file stream data type.

Background

The audience of this article is expected to know how to use SQL server management studio, know about authentication mechanism available in SQL server, know how to create database and so on.
There are a whole bunch of articles which explain when to use and when not to use FileStream data type.

How Do I Configure the Database Server Instance

The first thing we need to do is enable the server instance to accept file stream data type. To do this, the following steps need to be followed.
Open SQL server Configuration manager:

Right click on SQL Server Service and click on properties:

Click on FileStream tab.
Enable all the check boxes. These enable file streaming both from within SQL server and clients.

The windows share name is the name of the folder which will have the files.
Restart the service.
Open the SQL Server management studio.
Execute.

Copy Code

EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO 

The filestream_access_level 2 means that it is available for both SQL Server and from a client application.

How Do I Create the Database

The next step is to create a new database with file streaming enabled. There can be many ways to do this, but we will use a GUI approach.
Open SQL Server Management Studio:
Right click on databases and click on New Database.
Click on FileGroups in left navigation pane. We need to create a separate file group for the file stream.
In the FileStream, add a new file group with Name FileStream and enable default.
Click on General Pane.
Add a database name.

Click on the Add button to add another database file.
Give logical name FileSystemDB_FileStream.
Select FileType as FileStream data.
Select FileGroup as FileStream which was created in step 4.
Fill in the Path.
Click on Ok.
This now allows us to create tables with file stream.

How Do I Create the Table

In this step, we create a table in the database which will allow us to access file stream:

Copy Code

CREATE TABLE dbo.PictureTable
(
PkId int Primary Key IDENTITY (1, 1),
Id uniqueidentifier NOT NULL Unique ROWGUIDCOL Default newid(),
Description nvarchar(64) NOT NULL,
FileSummary varbinary(MAX),
FileData varbinary(MAX) FileStream NULL
) 

The important points for this are as follows. The Table has a column Id which is of type uniqueidentifier (Guid) and is a RowGuid column. This is important because the files will be stored using this name.
The next important column is FileData which is of type VarBinary and FileStream. This will be used to stream data.

Testing using T-SQL

To test the table using TSql, we insert a row and then try to read the same:
Copy Code

Insert Into PictureTable([Description],[FileData])
Values('Hello World', Cast('Hello World' As varbinary(max)))
And then select using the statement
SELECT [PkId],[Id],[Description],[FileData],CAST([FileData] _
 As varchar(Max)) FROM [PictureTable] 

Where are the Files Actually Stored

In case you find all the shared folders on your machine, you will see that the folder name as described in Windows share during configuration of the instance is available. However, if you will try to access this location, you will not be able to do so.
To see the file, go to the SQL Server install location and then in the data directory which will be C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA by default. In this directory, there is a folder with name FileSystemDB_FileStream which represents the table in the DB.
The table contains a file with a guid name each representing the rows stored. This is the Guid with the same value as the row guid column created in the table above.


On opening the file in a text editor, you will observe that the text HelloWorld is a part of this file.

This concludes things on the DB end and we will now proceed with how to access the file stream from .NET code in C#.

Accessing the File using .NET

We are now in the final leg of this document. This describes how to read and write data for SQL File stream using .NET code. The only two operations of interest are file reading and file writing. Both insert and Update form parts of file writing. Delete can be done using regular SQL statements used for deleting rows.
Weather we are reading data or writing data to a SqlFileStream, it has to be done in a transition. This is because in regular SQL statements, data is returned back in an atomic manner however with SqlFileStream data type the data is streamed from server to client using a buffer and hence there is no atomic operation.
SqlFileStream data type works with Integrated authentication only.

Creating a New Row

The line given below contains the code to create a single entry in the database. Let us analyse the same.
Copy Code

using (TransactionScope transactionScope = new TransactionScope())
{
SqlConnection sqlConnection1 = new SqlConnection("Data Source=.;
 Initial Catalog=FileSystemDB;Integrated Security=True");
SqlCommand sqlCommand1 = sqlConnection1.CreateCommand();
sqlCommand1.CommandText = "Insert Into PictureTable
 (Description,FileData) values('" + Guid.NewGuid().ToString() +
 "',Cast('' As varbinary(Max))); Select FileData.PathName()
 As Path From PictureTable Where PkId =@@Identity";
sqlConnection1.Open();
string filePath1 = (string)sqlCommand1.ExecuteScalar();
SqlConnection sqlConnection2 = new SqlConnection("Data Source=.;
 Initial Catalog=FileSystemDB;Integrated Security=True");
SqlCommand sqlCommand2 = sqlConnection2.CreateCommand();
sqlCommand2.CommandText = "Select GET_FILESTREAM_TRANSACTION_CONTEXT()
 As TransactionContext";
sqlConnection2.Open();
byte[] transactionContext1 =(byte[]) sqlCommand2.ExecuteScalar();
SqlFileStream sqlFileStream1 = new SqlFileStream
 (filePath1, transactionContext1, FileAccess.Write);
byte[] fileData = Guid.NewGuid().ToByteArray();
sqlFileStream1.Write(fileData, 0, fileData.Length);
sqlFileStream1.Close();
transactionScope.Complete();
} 

The first statement is using (TransactionScope transactionScope = new TransactionScope()) which starts a transaction scope.
We next open a connection using integrated authentication and execute a query of the format:
 Copy Code

Insert Into PictureTable (Description ,FileData ) Values(“Some String made using Guid”,
Cast(‘’ as varchar(max)) 

What we do here is that we create an empty file. This is done because we need the file path to upload the file.
This file path is found in the next statement using:
 Copy Code

Select FileData.Pathname() As Path From PictureTable where PkId = @@Identity 

This returns us the location of file path as a UNC share.
Next we execute the query Select GET_FILESTREAM_TRANSACTION_CONTEXT() this returns back a transaction context which corresponds to the transaction scope. It is in this transaction context that the file will be read.
Next a SqlFileStream is opened using the file path and transaction context. The code after this is a simple upload of byte array to a file stream.
Updating data will be a similar activity where we can find the file path based on some primary key.

Reading Data

Reading data is also fairly straight forward. We find the file path of a row of interest and then in a transaction scope get an instance of Transaction Context. A SqlFileStream is opened using this context and byte[] is read from the same. The code given below shows the same:
Copy Code

using (TransactionScope transactionScope2 = new TransactionScope())
{
SqlConnection sqlConnection3 = new SqlConnection("Data Source=.;
 Initial Catalog=FileSystemDB;Integrated Security=True");
SqlCommand sqlCommand3 = sqlConnection3.CreateCommand();
sqlCommand3.CommandText = "Select FileData.PathName() As Path,
 GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext
 From PictureTable Where PkId = (Select Max(PkId) From PictureTable)";
sqlConnection3.Open();
SqlDataReader reader = sqlCommand3.ExecuteReader();
reader.Read();
string filePath = (string)reader["Path"];
byte[] transactionContext2 = (byte[])reader["TransactionContext"];
SqlFileStream sqlFileStream2 = new SqlFileStream
 (filePath, transactionContext2, FileAccess.Read);
byte[] data = new byte[sqlFileStream2.Length];
sqlFileStream2.Read(data, 0, Convert.ToInt16(sqlFileStream2.Length));
Guid valueInserted = new Guid(data);
sqlFileStream2.Close();
}