In my last post, I gave a tutorial of SQL Server 2008’s new FILESTREAM feature. I described what it is, why it should be used for BLOB data storage, and released some TSQL scripts to enable FILESTREAM and create a table to hold some media. In this post, I will show how to insert and read FILESTREAM data from a SQL 2008 Server using ADO.NET.
ADO.NET comes with the SqlClient data provider (System.Data.SqlClient). It supports FILESTREAM operations by using the SqlFileStream class which resides in the System.Data.SqlTypes namespace. As its name suggests, the SqlFileStream is a stream and thus inherits from System.IO.Stream. The SqlFileStream will provide methods to allow us to read and write streams of data to the SQL Server. I will show 2 examples: inserting and reading FILESTREAM data with ADO.NET (the SqlFileStream object). The program I created allows the user to save and view .jpg images from the SQL Server using FILESTREAM. Here is a picture of the final example program.
Overview:
The SqlFileStream requires 2 arguments which are supplied from the SQL Server: the read/write path to the server’s file and the transactional context/token. These 2 items are acquired via a preliminary SQL query before the the SqlFileStream is employed to read or write to the NTFS file system.
Inserting FILESTREAM Data
I first INSERT a record with a NULL value for the varbinary(max) field. I use this blank record to acquire the server file path and also return the current FILESTREAM transactional context/token. After this is complete, I can employ the SqlFileStream to write to the server file from my local file. Below is my stored proc I used along with the .NET code.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_InsertDocument')
BEGIN
DROP PROCEDURE dbo.usp_InsertDocument
END
GO
CREATE PROCEDURE dbo.usp_InsertDocument
WITH ENCRYPTION
AS
SET NOCOUNT ON;
DECLARE @DocumentID INT;
--insert NULL first to acquire ID & the transaction context values
INSERT INTO dbo.Document (Document) VALUES (0x00);
SELECT @DocumentID = SCOPE_IDENTITY();
SELECT
doc.DocumentID,
doc.DocumentGUID,
doc.Document.PathName(0) AS [PathName],
GET_FILESTREAM_TRANSACTION_CONTEXT() AS [TransactionContext]
FROM dbo.Document AS doc WITH (NOLOCK)
WHERE doc.DocumentID = @DocumentID;
SET NOCOUNT OFF;
GO
Private Sub InsertDocument(ByVal clientFilePath As String)
Dim dt As DataTable = New DataTable()
Using conn As SqlConnection = New SqlConnection(_connectionString)
Dim reader As SqlDataReader = Nothing
Dim context() As Byte = Nothing
Dim serverFilePath As String = ""
conn.Open()
Dim transaction As SqlTransaction = conn.BeginTransaction()
Try
Dim cmd As SqlCommand = conn.CreateCommand()
With cmd
.Transaction = transaction
.CommandType = CommandType.StoredProcedure
.CommandText = "dbo.usp_InsertDocument"
End With
reader = cmd.ExecuteReader()
dt.Load(reader)
If dt.Rows.Count > 0 Then
serverFilePath = dt.Rows(0)("PathName").ToString()
context = DirectCast(dt.Rows(0)("TransactionContext"), Byte())
Using serverStream As SqlFileStream = New SqlFileStream(serverFilePath, context, FileAccess.Write)
Using clientStream As FileStream = New FileStream(clientFilePath, FileMode.Open, FileAccess.Read)
Dim bytesRead As Integer = 0, bufferSize As Integer = 4096
Dim buffer As Byte() = New Byte(bufferSize - 1) {}
Do
bytesRead = clientStream.Read(buffer, 0, bufferSize)
If bytesRead = 0 Then
Exit Do
End If
serverStream.Write(buffer, 0, bytesRead)
serverStream.Flush()
Loop While True
clientStream.Close()
End Using
serverStream.Close()
End Using
End If
transaction.Commit()
Catch ex As Exception
Try
'attempt to rollback the transaction
transaction.Rollback()
Catch ex2 As Exception
'rollback was unsuccessful (connection may be closed)
End Try
MessageBox.Show(ex.Message)
Finally
If reader IsNot Nothing AndAlso Not reader.IsClosed Then reader.Close()
End Try
End Using
LoadDocuments()
End Sub
Reading FILESTREAM Data
Similar to the insertion of a file, reading a file requires getting the server file path and then current FILESTREAM transactional context/token. After those items are gathered from the SQL Server, we can connect to and stream the data anyway we like. Below I streamed .jpg data from the SQL Server and loaded them into a WPF Image control for viewing purposes.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_SelectDocument')
BEGIN
DROP PROCEDURE dbo.usp_SelectDocument
END
GO
CREATE PROCEDURE dbo.usp_SelectDocument (
@DocumentID INT
)
WITH ENCRYPTION
AS
SET NOCOUNT ON;
SELECT
doc.DocumentID,
doc.DocumentGUID,
doc.Document.PathName(0) AS [PathName],
GET_FILESTREAM_TRANSACTION_CONTEXT() AS [TransactionContext]
FROM dbo.Document AS doc WITH (NOLOCK)
WHERE doc.DocumentID = @DocumentID;
SET NOCOUNT OFF;
GO
Private Sub LoadDocument(ByVal documentID As Integer)
Dim dt As DataTable = New DataTable()
Using conn As SqlConnection = New SqlConnection(_connectionString)
Dim reader As SqlDataReader = Nothing
Dim context() As Byte = Nothing
Dim serverFilePath As String = ""
conn.Open()
Dim transaction As SqlTransaction = conn.BeginTransaction()
Try
Dim cmd As SqlCommand = conn.CreateCommand()
With cmd
.Transaction = transaction
.CommandType = CommandType.StoredProcedure
.CommandText = "dbo.usp_SelectDocument"
.Parameters.Add(New SqlParameter("@DocumentID", documentID))
End With
reader = cmd.ExecuteReader()
dt.Load(reader)
If dt.Rows.Count > 0 Then
serverFilePath = dt.Rows(0)("PathName").ToString()
context = DirectCast(dt.Rows(0)("TransactionContext"), Byte())
Using memStream As MemoryStream = New MemoryStream()
Using serverStream As SqlFileStream = New SqlFileStream(serverFilePath, context, FileAccess.Read)
Dim bytesRead As Integer = 0, bufferSize As Integer = 4096
Dim buffer As Byte() = New Byte(bufferSize - 1) {}
Do
bytesRead = serverStream.Read(buffer, 0, bufferSize)
If bytesRead = 0 Then
Exit Do
End If
memStream.Write(buffer, 0, bytesRead)
memStream.Flush()
Loop While True
serverStream.Close()
End Using
memStream.Seek(0, SeekOrigin.Begin)
Dim bmpImage As BitmapImage = New BitmapImage()
With bmpImage
.BeginInit()
.StreamSource = memStream
.CreateOptions = BitmapCreateOptions.None
.CacheOption = BitmapCacheOption.OnLoad
.EndInit()
.Freeze()
End With
Me.imgView.Source = bmpImage
memStream.Close()
End Using
End If
transaction.Commit()
Catch ex As Exception
Try
'attempt to rollback the transaction
transaction.Rollback()
Catch ex2 As Exception
'rollback was unsuccessful (connection may be closed)
End Try
MessageBox.Show(ex.Message)
Finally
If reader IsNot Nothing AndAlso Not reader.IsClosed Then reader.Close()
End Try
End Using
End Sub
So there you have it. The basics of writing and reading to a FILESTREAM on a SQL 2008 Server using ADO.NET. Below you’ll find my scripts and .NET source code.
SqlFileStreamExample_Soln.zip (559.27 kb)