Dot Net Thoughts

Few days back I wrote a Post about FileStream feature in SQL 2008.(Filestream in SQL Server 2008). In this post I am trying to write the how to manage or use the FileStream feature from .Net managed code.

For adding a File to FileStream enabled table, we are using a new Data Type, SqlFileStream, which comes with .Net 3.5.

I am using the Table structure as in the Previous post, as the example, that Table contains 3 columns, FileID(PK), FileName and FileContents.
For inserting a File you need to begin a transaction, insert an row in to the Table, without the contents of the File, for reading the Transaction context. Then using FileStream, write the contents of the File to the Row. After writting the stream, need to commit the transaction to update the file to the Database. I am writing the code in VB.Net.

Code for saving the File to the Database

Private Sub AddFile(ByVal fileName As String)
	Dim _Transaction As SqlTransaction
	Dim _Command As SqlCommand
	Dim _DataReader As SqlDataReader
	Dim _Connection As SqlConnection
	Dim _FileId As String
	Dim _FilePath As String
	Dim _FileNameParameter As SqlParameter
	Dim _FileIdParameter As SqlParameter
	Dim _FilePathParameter As SqlParameter

	Dim _SQLFileStream As SqlTypes.SqlFileStream
	Dim _Context As Byte()

	Try
		_Connection = New SqlConnection("Server=.\SQL2008;Integrated Authentication=SSPI;Database=FileSystemExample;")
		_Connection.Open()
		_Transaction = _Connection.BeginTransaction()
		_Command = New SqlCommand("xsp_InsertFile", _Connection, _Transaction)
		_Command.CommandType = CommandType.StoredProcedure
		_Command.Parameters.AddRange(New Object() {_FileIdParameter, _FileNameParameter, _FilePath})
		_FileId = Guid.NewGuid().ToString

		_FileIdParameter = New SqlParameter("@FileId", SqlDbType.UniqueIdentifier)
		_FileIdParameter.Value = _FileId

		_FileNameParameter = New SqlParameter("@FileName", SqlDbType.VarChar)
		_FileNameParameter.Value = fileName

		_FilePathParameter = New SqlParameter("@FilePath", SqlDbType.VarChar)
		_FilePathParameter.Direction = ParameterDirection.Output

		_DataReader = _Command.ExecuteReader(CommandBehavior.SingleRow)
		If _DataReader.HasRows Then
			_FilePath = _DataReader("FilePath").ToString
		End If
		If Not _DataReader.IsClosed Then
			_DataReader.Close()
		End If

		_Command = New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT() FROM SQLFileSystem", _Connection, _Transaction)
		_Context = _Command.ExecuteScalar()

		_SQLFileStream = New SqlFileStream(_FilePath, _Context, FileAccess.Write)
		_SQLFileStream.Write(_Context, 0, _Context.Length)
		_SQLFileStream.Close()

		_Transaction.Commit()
	Catch ex As Exception
		If _Transaction IsNot Nothing Then
			_Transaction.Rollback()
		End If
	Finally
		If _Connection IsNot Nothing Then
			_Connection.Close()
		End If
	End Try
End Sub

Code for reading the File from the Database

Private Function ReadFile(ByVal fileId As String) As Byte()
	Dim _Transaction As SqlTransaction
	Dim _Command As SqlCommand
	Dim _DataReader As SqlDataReader
	Dim _Connection As SqlConnection
	Dim _FilePath As String
	Dim _FileIdParameter As SqlParameter

	Dim _SQLFileStream As SqlTypes.SqlFileStream
	Dim _Context As Byte()

	Try
		_Connection = New SqlConnection("Server=.\SQL2008;Integrated Authentication=SSPI;Database=FileSystemExample;")
		_Connection.Open()
		_Transaction = _Connection.BeginTransaction()
		_Command = New SqlCommand("SELECT FileStreamData.PathName() AS [FilePath],GET_FILESTREAM_TRANSACTION_CONTEXT() AS [Context] FROM SQLFileSystem WHERE FileId=@FileId", _Connection, _Transaction)
		_Command.CommandType = CommandType.StoredProcedure

		_FileIdParameter = New SqlParameter("@FileId", SqlDbType.UniqueIdentifier)
		_FileIdParameter.Value = fileId
		_Command.Parameters.Add(_FileIdParameter)

		_DataReader = _Command.ExecuteReader(CommandBehavior.SingleRow)
		If _DataReader.HasRows Then
			_FilePath = _DataReader("FilePath").ToString()
			_Context = TryCast(_DataReader("Context"), Byte())
		End If

		_SQLFileStream = New SqlFileStream(_FilePath, _Context, FileAccess.Read)
		_SQLFileStream.Read(_Context, 0, _Context.Length)
		_SQLFileStream.Close()

		_Transaction.Commit()
	Catch ex As Exception
		If _Transaction IsNot Nothing Then
			_Transaction.Rollback()
		End If
	Finally
		If _Connection IsNot Nothing Then
			_Connection.Close()
		End If
	End Try
	Return _Context
End Function

Stored Procedure

CREATE PROCEDURE xsp_InsertFile
(
@FileName VARCHAR(255),
@FileId UNIQUEIDENTIFIER,
@FilePath VARCHAR(MAX) OUTPUT)
AS
BEGIN
SET NOCOUNT OFF
INSERT INTO SQLFileSystem (FileId, FileName) VALUES(@FileId, @FileName)
SELECT @FilePath = SystemFile.PathName() from SQLFileSystem where FileId = @FileId
END

Note: I didn’t tested the code. Please let me know if you found any issues in the implementation.

SQL Server 2008 comes with lots of new features compared to the previous versions of SQL Server. One of the new feature is FileStream, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

You can get more details about this in MSDN : FILESTREAM Storage in SQL Server 2008

Enable Filestream in SQL Server

By default the Filestream feature will be disabled. You can enable the filestream using SQL Server Configuration Manager under SQL Server 2008 > Configuration Tools. In this you will get all the SQL Server services. Select the Properties of the instance and select the Tab “FileStream”, from that you can enable the FileStream, you can also specifiy the instance name also.

How to enable FileStream

You can also do it via T-SQL statement also

EXEC sys.sp_configure N'filestream access level', N'2'
RECONFIGURE

After doing this SQL Server will create a shared folder in your machine(or in Server) with the instance name specified. (Or it will create the Windows Share name we are specifying in the textbox) Only SQL Server can access the contents.

Network

You can check this via command prompt, using “Net Share”, you will get an output like this.

Net share command output

Using Filestream in the Database.

For using Filestream in your database you have to add file group in New Database screen.

Enable filestream for new Database

Or you can do this via TSQL like this

CREATE DATABASE FileStreamDemo
ON PRIMARY
   (NAME = FileStreamDemo,
      FILENAME = N'D:\DB\FileStreamDemo_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
   (NAME = FileStreamDemo,
      FILENAME = N'D:\DB\FileStreamDemo')
LOG ON
   (NAME = 'FileStreamDemo_log',
      FILENAME = N'D:\DB\FileStreamDemo_log.ldf');
go

After doing this, SQL Server will create Folder in “D” drive, with name FileStreamDemo under DB directory. This FileStreamDemo folder will contains two files

1. filestream.hdr – This is the FILESTREAM metadata for the data container.
2. The directory $FSLOG. This is the FILESTREAM equivalent of a database’s transaction log.

Creating a Table with FILESTREAM Data
You can create a Table for consuming FileStream like this.

CREATE TABLE SQLFileSystem
(
FileId UNIQUEIDENTIFIER  ROWGUIDCOL UNIQUE DEFAULT NEWID() PRIMARY KEY,
FileName VARCHAR(255),
FileContents VARBINARY(MAX) FILESTREAM  NULL default (0x)
)

Thats it, you have created SQL Server Database and Table with Filestream.