I’ve become interested in a few aspects of SQL Server 2008. One is the new FILESTREAM datatype.

The basic idea is to have a SQL datatype you can use in queries and searches and is managed by SQL but which does not have any particular size limitation.

There are a number of articles on the multiple  steps it takes to setup a database  to utilize the new FILESTREAM features. I’m not going to cover that in this article. I highly recommend reading Paul Randal’s white paper on the subject, now published on MSDN: http://msdn.microsoft.com/en-us/library/cc949109.aspx. Both he and Kim  Tripp have lots of great SQL server content online at www.sqlskills.com. Paul also has some performance graphs of that will give you an idea of when to use FILESTREAM vs image or text.

Unfortunately there is very little on how to actually utilize the FILESTREAM from a programming standpoint. Most of them show how to utilize Win32 API and the SafeFileHandle, I only found one (I’m sure there are more) that talked about using the managed interface, SQLFileStream.  You can find that paper at: http://blogs.microsoft.co.il/blogs/bursteg , this was also all C#, not VB.

So I have created a vb project which illustrates using FILESTREAM… The presentation, visual  studio project and sql script can all be found here: http://www.fladotnet.com/downloads/SQL2k8TidBits_20081104.zip

One thing I found is that the old Text, nText and image sql types are being replace by varchar(max) and varbinay(max)  which all have a size limitation of 2 gb.

The FIlestream datatype is really just a flagged varbinary(max) column with a tag of FILESTREAM. The sample I've made uses a table declared as follows:

CREATE TABLE FSDemo.dbo.FSDemoTable
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Description] varchar(50) null,
    [JPG] VARBINARY(MAX) FILESTREAM NULL
)

Besides the FILESTREAM tag on the column, each table containing a filestream  column must have a uniqueidentifier (guid) column.

After inserting a row (see the 1_3_InsertData.sql script) the program will need two things in order to access the filestream data.

First is the path as defined by sql server. This is a reference to the file (not accessible directly through normal system.io) that contains the data for the row/column selected. A simple query of this for the table above is:

Select JGP.PathName() from FSDemoTable Where ID = @ID

This will give you a path: \\PLOVER2V3\SQL2K8\v1\FSDemo\dbo\FSDemoTable\JPG\0925394D-79D7-41FB-B438-9EA85C0FA232

The trailing 79D7-41FB-B438-9EA85C0FA232 is the @ID above but you won’t find a file by that name.

The second piece of information is the transaction context. To utilize filestream for reading or writing, you need to establish a transaction and then get the transaction context, a 16 byte array back from the db to pass into the SQLFileStream call. You can see this in the 1_4_FSGet.sql script and it looks like this:

select JPG.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()  from dbo.FSDemoTable WHERE id = @id

Now for code… to write to a file stream the annotated code is below. I’m sure there are many improvements to be made but the idea is to illustrate the steps.

For this sample, a description of the file (photos) and the actual byte array containing the jpg file are passed to this routine for writing and an array of bytes returned after the read. Once  you get the path and context, the sqlfilestream operations are basically normal file IO.

Public Sub WriteFileStream(ByVal Description As String, ByVal SourceBytes As Byte())
    Dim cn As SqlConnection
    Dim tx As SqlTransaction = Nothing
    '
    Try
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Open a connection and start a transaction
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        cn = New SqlConnection(ConnectionStrings("conFSDemo").ToString)
        cn.Open()
        tx = cn.BeginTransaction
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Insert empty blob in the database
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        Dim cmd As New SqlCommand("FSInsert", cn, tx)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("Description", Description))

        Dim r As SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow)
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Read the id and sql path back from the proc
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        r.Read()
        Dim ID As String = r(0).ToString
        Dim path As String = r(1).ToString
        r.Close()
        '
        ''''''''''''''''''''''''''''''''''''''''''''''
        ' Get the transaction token, required for filestream operations  should add this from the previous proc…
        ''''''''''''''''''''''''''''''''''''''''''''''
        '
        Dim cmd2 As New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", cn, tx)
        Dim obj As Object = cmd2.ExecuteScalar()
        '
        Dim txCtx As Byte()
        txCtx = CType(obj, Byte())
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Open the special managed "SQLFileStream" and write the bytes
        '''''''''''''''''''''''''''''''''''''''''''''
        Dim fs As New SqlTypes.SqlFileStream(path, txCtx, FileAccess.Write)
        '
        fs.Write(SourceBytes, 0, SourceBytes.Length)
        fs.Close()
        '
        tx.Commit()
        '
    Catch ex As Exception
        '
        tx.Rollback()
        '
    End Try
    '
End Sub

 

To Read the image the code is:

 

Public Function ReadFileStream(ByVal ID As Guid) As Byte()
    Dim cn As SqlConnection
    Dim tx As SqlTransaction = Nothing
    Dim txCtx As Byte()
    Dim SQLpath As String
    '
    Try
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Open connection and establish transaction context
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        cn = New SqlConnection(ConnectionStrings("conFSDemo").ToString)
        cn.Open()
        tx = cn.BeginTransaction
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Retrieve Path and transaction context of the filestream
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        Dim cmd As New SqlCommand("FSGet", cn, tx)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@ID", ID)

        Dim r As SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow)
        '
        r.Read()
        SQLpath = r(0).ToString()
        txCtx = r(1)
        r.Close()
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Read the file
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        Dim fs As New SqlTypes.SqlFileStream(SQLpath, txCtx, FileMode.Open, FileOptions.None, 0)
        Dim Buf(fs.Length) As Byte
        '
        fs.Read(Buf, 0, Buf.Length)
        fs.Close()
        '
        tx.Commit()
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Return the bytes
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        Return Buf ' return the bytes
        '
    Catch ex As Exception
        '
        tx.Rollback()
        '
        Return Nothing
        '
    End Try
    '
End Function