Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 17, 2009

Working with FILESTREAM using VB .NET

By Yan Pan

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date