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.