How to use FILESTREAM

In order to use FILESTREAM, there are couple of steps that should be done before start using it. Here are steps to set it up and use FILESTREAM feature in SQL Server.

Enable FILESTREAM in SQL Server Service

First, FILESTREAM should be enabled in SQL Server. This can be done by either using SQL Configuration Manager or SQL WMI. The following picture shows how to set it up in SQL Configuration Manager.

(1) Doubleclick SQL Server Service. In this example, it's SQL Server(MSSQLSERVER).
(2) Click FILESTREAM tab
(3) Click checkbox(ex) to enable features.
(4) Click OK

Set SQL Server property [Filestream Access Level]

There is a SQL Server property called Filestream Access Level which is disabled by default. There is 3 options for this access level : Disabled, TSQL access enabled, Full access enabled.

NOTE: Filestream data can be accessed either from TSQL or from external Win32 API. If you want to access FileStream from both TSQL and Win32, you have to select [Full access enabled].

(1) Rum SSMS.exe
(2) Connect to SQL Server
(3) In Object Explorer, rightclick Server node and select [Properties]
(4) In [Server Properties] dialog, click [Advanced]
(5) Change [Filestream Access Level]
(6) Click OK


Create FILESTREAM enabled Database

To use FILESTREAM, a Database should have separate FileGroup which is dedicated to FILESTREAM. So when creating a database, FILESTREAM filegroup should be included in DDL.

In SSMS, this task can be done in the following steps.

(1) Run SSMS.exe
(2) Connect to SQL Server
(3) In Object Explorer, rightclick Databases node and choose [New Database...]

(4) Type a DB name
(5) Click [Filegroups]

(6) Click [Add] button under FileStream grid
(7) Type FileStream name
(8) Click [General]

(9) In General tab, click [Add] and type new database file name (ex: TestDB_files) and select [Filestream Data] from File Type column. Select filegroup if needed.

(10) If you click OK here, you will see the following error message:
        "The path cannot be used for FILESTREAM files."
        "CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
         (Microsoft SQL Server, Error: 5135)"

    This error occurs since you don't specify file path for the filestream database file.

(11) Move Horizontal scroll bar to the right and find <Select the filestream location...>

     Set the Path value and click OK. Now, FILESTREAM enabled database will be created correctly.


Create FILESTREAM enabled Table

In SQL Server 2008 and 2008 R2, SSMS does not support FILESTREAM attribute in CREATE TABLE designer. That means you cannot create a table that has FILESTREAM column by using SSMS. So TSQL is used to create FILESTREAM enabled table.

CREATE TABLE FSTable
(
   -- ROWGUID column is required
   [RowId] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,     
   [Id] int NOT NULL,
   -- This is FILESTREAM column
   [Data] VARBINARY(MAX) FILESTREAM NULL  
)

Using FILESTREAM Table with TSQL

FileStream table can be used or handled just like other table. This means we can treat FileStream column as normal varbinary column. One can select, insert, update, delete the same way as other table. Here are some examples of DML statements.

(1) INSERT

INSERT FSTable VALUES (NEWID(), 1, CAST('Hello, World' AS varbinary(max)))
INSERT FSTable VALUES (NEWID(), 2, CAST('' AS varbinary(max)))

(2) UPDATE

UPDATE FSTable SET Data=CAST('ABC' AS varbinary(max)) 
WHERE [Id]=1

(3) DELETE

DELETE FSTable WHERE [Id]=2

(4) SELECT

SELECT Data FROM FSTable

Result of  "SELECT Data.PathName() FROM FSTable WHERE [Id]=1"
file://server1/MSSQLSERVER/v1/TestDB/dbo/FSTable/Data/EC2821EA-6C4B-4638-8C7B-A42649F0746D

Using FILESTREAM Table in ADO.NET
One way of accessing FILESTREAM data is through normal SQL data access layer such as ADO.NET. In this case, SQL Server handles FILESTREAM data access in the middle. The same TSQL statements above are used in ADO.NET. A sample can be found in http://sqlbeyond.blogspot.com/2011/09/import-binary-data-to-filestream-column.html

Using FILESTREAM with SqlFileStream
Namespace System.Data.SqlTypes contains SqlFileStream class which provides read/write functions for FILESTREAM in native mode. Unlike ADO.NET access method, the SqlFileStream directly access file system so the performance is faster. SqlFileStream constructor requires FILESTREAM PathName and current transaction context (these are common requirement for FILESTREAM when using Win32). Here is an example.

using System;
using System.IO;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

namespace MyFile
{
   class Program
   {
      static void Main(string[] args)
      {
         string connStr = "Data Source=.;Integrated Security=true;Initial Catalog=TestDB";
         using (SqlConnection conn = new SqlConnection(connStr))
         {
            conn.Open();

            string sql = "SELECT Data.PathName() FROM FSTable WHERE [Id]=1";
            SqlCommand cmd = new SqlCommand(sql, conn);
            object path = cmd.ExecuteScalar();
            if (path == DBNull.Value) throw new InvalidOperationException();

            SqlTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;

            sql = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
            cmd.CommandText = sql;
            byte[] txCtx = cmd.ExecuteScalar() as byte[];

            SqlFileStream sqlFS = new SqlFileStream(path.ToString(), txCtx, FileAccess.ReadWrite);
            byte[] bytes = new byte[1024];
            int nbytes = sqlFS.Read(bytes, 0, bytes.Length);
            if (nbytes > 0)
               Console.WriteLine(Encoding.ASCII.GetString(bytes));

            sqlFS.Close();
            tx.Commit();
         }
      }
   }
}