FILESTREAM is a great feature in SQL Server 2008. The data type allows you to easily store large data objects with the overhead needed in earlier SQL Server versions.

FILESTREAM provides the best of both worlds when working with large binary objects (BLOBs); that is, BLOBs are stored within a FILESTREAM column so they are controlled and maintained by SQL Server and will be backed up, but the data is stored on the file system. The FILESTREAM data type is set up at the database server level, though it does require the server to be set up to handle it as well. The SQL Server Configuration Manager snap-in can be used to enable FILESTREAM support – you view the properties for a SQL Server instance and use the FILESTREAM tab (Figure A).
Figure A

Enabling FILESTREAM support on a SQL Server instance

Once FILESTREAM support is enabled, you can configure a database to utilize it. The FILESTREAM type requires its own SQL Server filegroup. When the filegroup is set up, it can be assigned to a FILESTREAM. The following SQL script sets up an example database that includes a filegroup (TechRepublicFG) that is used for the FILESTREAM (TechRepublicFS).

CREATE DATABASE TechRepublicTest

ON


PRIMARY (


NAME = TechRepublicDB,


FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.BASELINE\MSSQL\DATA\TechRepublicDB.mdf'


), FILEGROUP TechRepublicFG CONTAINS FILESTREAM(


NAME = TechRepublicFS,


FILENAME = 'C:\Program Files\Microsoft SQL


Server\MSSQL10.BASELINE\MSSQL\DATA\TechRepublicFS')


LOG ON (


NAME = TechRepublicLOG,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.BASELINE\MSSQL\DATA\TechRepublicLOG.ldf')

With the FILESTREAM set up on the database, it can be used in a database table. The following SQL script creates a new table to store a person’s information, including a column called Picture to store FILESTREAM data. The actual column is defined as varbinary(MAX) with column property FILESTREAM included. In addition, a UNIQUEIDENTIFIER column with ROWGUIDCOL is required to use a FILESTREAM column. This GUID value will be used to store the BLOB on the file system.

CREATE TABLE [dbo].[People]

(


[ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,


[FirstName] VARCHAR(20),


[LastName] VARCHAR(50),


[Picture] VARBINARY(MAX) FILESTREAM NULL

)

After the table is created, you can use the FILESTREAM column to store BLOB objects.

The next script stores a graphic file (jpg) in the column along with other values. The script begins with the declaration of a variable to store image data. Next, it loads the data using OPENROWSET. The OPENROWSET bulk rowset provider is accessed by calling the OPENROWSET function and specifying the BULK option. The OPENROWSET(BULK…) function allows you to access remote data by connecting to a remote data source, such as a data file. In this case, it pulls an image file from the local C drive. The BLOB is stored in the table via a simple INSERT INTO statement.

DECLARE @img AS VARBINARY(MAX)

SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))


FROM OPENROWSET(


BULK


'C:\TechRepublicProfile.jpg',


SINGLE_BLOB ) AS x


INSERT INTO People (ID, FirstName, LastName, Picture )

SELECT NEWID(), 'Tony','Patton', @img

You can examine the local file system of your SQL Server installation to see how FILESTREAM objects are stored. There will be a directory for the Filegroup, which contains subdirectories for the objects stored within it (the directories have GUID values for names).

Access BLOBs within C#

While using SQL Server to store and manage your image files is wonderful, using them within your application code is even better. The key to using this functionality is pulling the image path from the FILESTREAM column — that is, getting the location of the image on the file system. The following SQL does this for our sample row of data, as it uses the PathName method of the FILESTREAM column type.

SELECT Picture.PathName() AS PathName FROM dbo.People WHERE LastName = 'Patton'

This query returns the following value in my sample database. It gives the server name followed by the SQL Server instance name, database name, table, and so forth all the way to the GUID assigned to the image. It is not the actual physical path on the server, but rather the UNC path that points to the network share created for the SQL Server instance when FILESTREAM was enabled.

\\LHX00CNU0215WG8\BASELINE\v1\TechRepublicTest\dbo\People\Picture\C5D9672A-A6C7-40C0-A654-AD8A24A15FDA

The following query that is used to access FILESTREAM calls the GET_FILESTREAM_TRANSACTION_CONTEXT() function. This function returns a handle to the NTFS (NT File System since the file system is used by FILESTREAM) transaction. This call must be made within a transaction, or a NULL value is returned.

SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()

Using the FILESTREAM object within your C# code requires using the SqlFILESTREAM (within System.Data.SqlTypes namespace) and reading the bytes of the image file into memory. The following code snippet shows how to approach pulling a BLOB from a FILESTREAM column. It establishes a connection to the database server and uses a SqlCommand object to query the database. The SqlFILESTREAM object uses data from the queries to access the FILESTREAM object stored within the table. The example does not dive into how to work with the binary data of the object.

string cString;

cString = "Data Source=SQLSERVER\\InstanceName;Initial Catalog=TechRepublicTest;Integrated Security=True";


SqlConnection conn = null;


SqlCommand comm. = null;


SqlTransaction  tran = null;


string sql = "SELECT Picture.PathName() AS PathName FROM items WHERE LastName = 'Patton'";


System.Data.SqlTypes.SqlFILESTREAM fs = null;


try {


conn =new SqlConnection(cString);


conn.Open();


comm = new SqlCommand();


comm.Connection = conn;


comm.CommandText = sql;


string filePath = comm.ExecuteScalar().ToString();


tran = conn.BeginTransaction("TRTran");


comm.Transaction = tran;


comm.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";


byte[] txContext = (byte[])comm.ExecuteScalar();


fs = new System.Data.SqlTypes.SqlFILESTREAM(filePath, txContext, FileAccess.Read);


byte[] buffer = new byte[fs.Length];


fs.Read(buffer, 0, buffer.Length);


// Manipulate the binary data as needed


catch (Exception ex) {


// handle the exception


} finally {


fs.Close();


comm.Transaction.Commit();


conn.Close();

}

Work with large data types

The FILESTREAM feature in SQL Server 2008 removes the 2 GB limit imposed on previous SQL Server versions. It also allows you to easily store and manage BLOB data, thus making it available to your applications. It is one of the many features in SQL Server 2008 that helps database administrators and developers build more robust solutions.