Adding and Reading files from SQL Server 2008 Filestream
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.
