Working with FILESTREAM using VB .NET

SQL server 2008 introduced a new feature called FILESTREAM.
FILESTREAM is implemented as a storage attribute of varbinary(max). By enabling
this attribute, binary large object (BLOB) data is stored as files on the file
system rather than data pages in the database. This separates the storage of
unstructured data, such as image files, text files, videos, from the structured
data in tables, but still allows unstructured data to be queried, inserted,
updated, deleted, and backed up using Transact-SQL statements.

Why should you use FILESTREAM, not just the file system or the
regular varbinary(max) data type? Before
SQL Server 2000, due to the inconvenient manipulation and slow performance of
text and image data type, storing BLOB data as files in the file system and saving
the file paths in tables is a common approach. However, this approach complicates
data management. Since users can directly access the files in the file system,
you have to configure directory or file ACLs manually to match SQL Server
access controls. It is also difficult to maintain transactional consistency. If
a transaction adds a row with BLOB data to a database table but then the
transaction rolls back, the creation of the BLOB data should be rolled back as
well as the creation of the table row. However, if the BLOB data is stored in
the file system, you have to manually delete the file associated with the BLOB
data. It is easy to end up with unwanted data hanging in the file system. How about regular varbinary(max)? Regular varbinary(max)
data needs to be first read into SQL Server’s memory (the buffer pool) and then
passed back out through a client connection to the client application. In
comparison, FILESTREAM allows access to the BLOB data using the
high-performance Win32 streaming APIs. The FILESTREAM data is read directly
from the file and passed to the client application. If the average size of the
BLOB data is larger than 1MB and can be accessed use the Win32 streaming API,
FILESTREAM should be used. Besides, if the size of the BLOB data exceeds 2GB, the
maximum size of the regular varbinary(max) data type, then only FILESTREAM can
be used.

By default, FILESTREAM is
disabled. Before starting to use FILESTREAM on an SQL Server instance,
FILESTREAM needs to be enabled on the instance. You can enable FILESTREAM in
the SQL Server Configuration Manager for Transact-SQL access, for Transact-SQL
and local file system access, or for Transact-SQL, local file system access,
and remote file system access. Figure 1 shows that FILESTREAM is enabled for Transact-SQL
and local file system access in the Configuration Manager.

The Windows share name specifies the file share name that is
used to access FILESTREAM values through the file system. You still need to configure
the server option, filestream_access_level, to fully enable FILESTEAM. Possible
values for this option are listed below.

0 – Disables FILESTREAM support for this instance.

1 – Enables FILESTREAM for Transact-SQL access.

2 – Enables FILESTREAM for Transact-SQL and Win32 streaming
access.

To enable FILESTREAM for Win32 streaming access, run this
query on the SQL Server. This change takes effect immediately.


EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Let’s create a FILESTREAM-enabled database called testDB
that contains a FILESTREAM filegroup. Before you run the following script in
your environment, please change the file paths if needed.


— Before running this query, the directory D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA must exist
— However, the filestream directory under it cannot exist.
CREATE DATABASE testDB
ON
PRIMARY ( NAME = testDB_data,
FILENAME = ‘D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testDB_data.mdf’),
FILEGROUP TestFileStreamGroup CONTAINS FILESTREAM ( NAME = testDB_fs,
FILENAME = ‘D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\filestream’)
LOG ON ( NAME = testDB_log,
FILENAME = ‘D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testDB_log.ldf’)
GO

Now, let’s create a FILESTREAM-enabled table. For a table to have one or more FILESTREAM columns, it must
also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute.
This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint.


Use testDB

CREATE TABLE dbo.ScreenSavers
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[ssID] int NOT NULL,
[ScreenSaver] VARBINARY(MAX) FILESTREAM NULL
)
GO

You can use Win32 to read from and write to a FILESTREAM
BLOB. I have attached a VB project to this
article. In the project, an image file Spire.jpg under the local directory C:\
is first converted into a binary array. Next, the array is inserted to the
FILESTREAM column in the first row of the ScreenSavers table. Then the
FILESTREAM BLOB is read from the ScreenSavers table and displayed. To display
the image, I use the new GeneratedImage control in ASP.Net 3.5. To run the
project, you need to download the control (Microsoft.Web.GeneratedImage.dll)
from http://www.codeplex.com/aspnet/Release/ProjectReleases.aspx?ReleaseId=16449.
Your computer must have Visual Studio 2008 and .Net Framework 3.5 SP1 too. If
you don’t have the pre-requisites, don’t worry. You can still get an idea on
how to work with FILESTREAM by reading the code snippets from the project.

From Default.aspx.vb

‘ Demonstrate insertion of BLOB data into the FILESTREAM column.
Private Sub InsertImage()
Dim sqlConnection As New SqlConnection(“Integrated Security=true;server=(local)”)

Dim sqlCommand As New SqlCommand()
sqlCommand.Connection = sqlConnection

Try
sqlConnection.Open()

‘ Pre-populate the first row by inserting a zero-length record into the FILESTREAM column.
sqlCommand.CommandText = “TRUNCATE TABLE testDB.dbo.ScreenSavers;
INSERT INTO testDB.dbo.ScreenSavers VALUES (newid (), 1, CAST(” as varbinary(max)))”
sqlCommand.ExecuteNonQuery()

‘ Retrieve the file path of the SQL FILESTREAM BLOB in the first row.
sqlCommand.CommandText = “SELECT ScreenSaver.PathName() FROM testDB.dbo.ScreenSavers WHERE ssID=1”

Dim filePath As String = Nothing
Dim pathObj As Object = sqlCommand.ExecuteScalar()
If Not pathObj.Equals(DBNull.Value) Then
filePath = DirectCast(pathObj, String)
Else
Throw New System.Exception(“ScreenSaver.PathName() failed to read the path name for the ScreenSaver column.”)
End If

‘ Obtain a transaction context. All FILESTREAM BLOB operations occur
within a transaction context to maintain data consistency.
Dim transaction As SqlTransaction = sqlConnection.BeginTransaction(“mainTranaction”)
sqlCommand.Transaction = transaction

sqlCommand.CommandText = “SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()”

Dim obj As Object = sqlCommand.ExecuteScalar()
Dim txContext As Byte() = Nothing
If Not obj.Equals(DBNull.Value) Then
txContext = DirectCast(obj, Byte())
Else
Throw New System.Exception(“GET_FILESTREAM_TRANSACTION_CONTEXT() failed”)
End If

‘Obtain a handle that can be passed to the Win32 FILE APIs.
Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Write)

‘ Converting the image to a byte array.
‘ Please change C:\Spire.jpg to your image file path.
Dim byteImg As Byte()
byteImg = File.ReadAllBytes(“C:\Spire.jpg”)

‘Write the image file to the FILESTREAM BLOB.
sqlFileStream.Write(byteImg, 0, byteImg.Length)

‘ Close the FILESTREAM handle.
sqlFileStream.Close()

‘ Commit the write operation that was performed on the FILESTREAM BLOB.
sqlCommand.Transaction.Commit()

Catch ex As System.Exception

‘ Roll back the write operation that was performed on the FILESTREAM BLOB.
sqlCommand.Transaction.Rollback()

Response.Write(ex.ToString())

Finally

‘ Close SQL Server connection.
sqlConnection.Close()

End Try

End Sub

From ImageHandler1.ashx

‘ Demonstrate retrieval of BLOB data from the FILESTREAM column.
Public Overrides Function GenerateImage(ByVal parameters As NameValueCollection) As ImageInfo

‘Initialize the ImageInfo object
Dim objImgInfo As ImageInfo = New ImageInfo(Net.HttpStatusCode.NotFound)

Dim sqlConnection As New SqlConnection(“Integrated Security=true;server=(local)”)

Dim sqlCommand As New SqlCommand()
sqlCommand.Connection = sqlConnection

Try
sqlConnection.Open()

‘Retrieve the file path of the SQL FILESTREAM BLOB in the first row.
sqlCommand.CommandText = “SELECT ScreenSaver.PathName() FROM testDB.dbo.ScreenSavers WHERE ssID=1”

Dim filePath As String = Nothing
Dim pathObj As Object = sqlCommand.ExecuteScalar()
If Not pathObj.Equals(DBNull.Value) Then
filePath = DirectCast(pathObj, String)
Else
Throw New System.Exception(“ScreenSaver.PathName() failed to read the path name for the ScreenSaver column.”)
End If

‘ Obtain a transaction context. All FILESTREAM BLOB operations
occur within a transaction context to maintain data consistency.
Dim transaction As SqlTransaction = sqlConnection.BeginTransaction(“mainTranaction”)
sqlCommand.Transaction = transaction

sqlCommand.CommandText = “SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()”

Dim obj As Object = sqlCommand.ExecuteScalar()
Dim txContext As Byte() = Nothing
If Not obj.Equals(DBNull.Value) Then
txContext = DirectCast(obj, Byte())
Else
Throw New System.Exception(“GET_FILESTREAM_TRANSACTION_CONTEXT() failed”)
End If

‘Obtain a handle that can be passed to the Win32 FILE APIs.
Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Read)

‘Read the data from the FILESTREAM BLOB.
Dim buffer As Byte() = New Byte(sqlFileStream.Length) {}

sqlFileStream.Read(buffer, 0, buffer.Length)

objImgInfo = New ImageInfo(buffer)

‘ Close the FILESTREAM handle.
sqlFileStream.Close()

‘ Commit the read operation that was performed on the FILESTREAM BLOB.
sqlCommand.Transaction.Commit()

Catch ex As System.Exception

‘ Roll back the read operation that was performed on the FILESTREAM BLOB.
sqlCommand.Transaction.Rollback()

Finally

‘ Close SQL Server connection.
sqlConnection.Close()

End Try

‘ Return the ImageInfo object that contains the BLOB data
Return objImgInfo

End Function

If you run the project, you will see the image in the Default.aspx.

If you run the project, you will see the image in the Default.aspx.

Figure 2

Please download the project here FileStreamDemo.zip.

Conclusion

FILESTREAM is a useful new feature in SQL Server 2008 that
allows you to work more efficiently and easily with unstructured data, such as image
files, text files, and videos. This article discussed the benefits of
FILESTREAM, and demonstrated how to enable FILESTREAM on a SQL Server instance
and create FILESTREAM-enabled database and table. Sample VB .NET code is also
presented to demonstrate how to stream FILESTREAM data with the SqlFileStream
class.

»


See All Articles by Columnist
Yan Pan






Yan Pan
Yan Pan
Yan Pan (MCITP SQL Server 2008, MCITP SQL Server 2005, MCDBA SQL Server 2000, OCA Oracle 10g) is a Senior DBA for a leading global financial services firm, where her daily duties include administering hundreds of SQL Server and Oracle servers of every possible version, working with business units on software development, troubleshooting database issues, and tuning database performance. She has written a Wrox book titled “Microsoft SQL Server 2008 Administration with Windows PowerShell” with MAK who is also a columnist for DatabaseJournal.com. You can check out the book at many book stores, such as Barnes & Noble, Borders. Previously, Yan worked as a SQL Server DBA and a .NET developer at Data Based Ads, Inc., and developed two .NET Web applications. Before that, she worked at AT&T Research Labs. She designed OLAP cubes with SQL Server Analysis Services and developed PivotTable reports for regional managers. She has master’s degrees in Computer Science and Physics.

Latest Articles