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();
}
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 
Add a database name.
