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.
1: IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_InsertDocument')
2: BEGIN
3: DROP PROCEDURE dbo.usp_InsertDocument
4: END
5: GO
6:
7: CREATE PROCEDURE dbo.usp_InsertDocument
8: WITH ENCRYPTION
9: AS
10: SET NOCOUNT ON;
11:
12: DECLARE @DocumentID INT;
13:
14: --insert NULL first to acquire ID & the transaction context values
15: INSERT INTO dbo.Document (Document) VALUES (0x00);
16:
17: SELECT @DocumentID = SCOPE_IDENTITY();
18:
19: SELECT
20: doc.DocumentID,
21: doc.DocumentGUID,
22: doc.Document.PathName(0) AS [PathName],
23: GET_FILESTREAM_TRANSACTION_CONTEXT() AS [TransactionContext]
24: FROM dbo.Document AS doc WITH (NOLOCK)
25: WHERE doc.DocumentID = @DocumentID;
26:
27: SET NOCOUNT OFF;
28: GO
1: Private Sub InsertDocument(ByVal clientFilePath As String)
2: Dim dt As DataTable = New DataTable()
3:
4: Using conn As SqlConnection = New SqlConnection(_connectionString)
5: Dim reader As SqlDataReader = Nothing
6: Dim context() As Byte = Nothing
7: Dim serverFilePath As String = ""
8:
9: conn.Open()
10:
11: Dim transaction As SqlTransaction = conn.BeginTransaction()
12:
13: Try
14: Dim cmd As SqlCommand = conn.CreateCommand()
15: With cmd
16: .Transaction = transaction
17: .CommandType = CommandType.StoredProcedure
18: .CommandText = "dbo.usp_InsertDocument"
19: End With
20:
21: reader = cmd.ExecuteReader()
22: dt.Load(reader)
23:
24: If dt.Rows.Count > 0 Then
25: serverFilePath = dt.Rows(0)("PathName").ToString()
26: context = DirectCast(dt.Rows(0)("TransactionContext"), Byte())
27:
28: Using serverStream As SqlFileStream = New SqlFileStream(serverFilePath, context, FileAccess.Write)
29: Using clientStream As FileStream = New FileStream(clientFilePath, FileMode.Open, FileAccess.Read)
30: Dim bytesRead As Integer = 0, bufferSize As Integer = 4096
31: Dim buffer As Byte() = New Byte(bufferSize - 1) {}
32:
33: Do
34: bytesRead = clientStream.Read(buffer, 0, bufferSize)
35: If bytesRead = 0 Then
36: Exit Do
37: End If
38:
39: serverStream.Write(buffer, 0, bytesRead)
40: serverStream.Flush()
41: Loop While True
42:
43: clientStream.Close()
44: End Using
45:
46: serverStream.Close()
47: End Using
48: End If
49:
50: transaction.Commit()
51: Catch ex As Exception
52: Try
53: 'attempt to rollback the transaction
54: transaction.Rollback()
55: Catch ex2 As Exception
56: 'rollback was unsuccessful (connection may be closed)
57: End Try
58:
59: MessageBox.Show(ex.Message)
60: Finally
61: If reader IsNot Nothing AndAlso Not reader.IsClosed Then reader.Close()
62: End Try
63: End Using
64:
65: LoadDocuments()
66: 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.
1: IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_SelectDocument')
2: BEGIN
3: DROP PROCEDURE dbo.usp_SelectDocument
4: END
5: GO
6:
7: CREATE PROCEDURE dbo.usp_SelectDocument (
8: @DocumentID INT
9: )
10: WITH ENCRYPTION
11: AS
12: SET NOCOUNT ON;
13:
14: SELECT
15: doc.DocumentID,
16: doc.DocumentGUID,
17: doc.Document.PathName(0) AS [PathName],
18: GET_FILESTREAM_TRANSACTION_CONTEXT() AS [TransactionContext]
19: FROM dbo.Document AS doc WITH (NOLOCK)
20: WHERE doc.DocumentID = @DocumentID;
21:
22: SET NOCOUNT OFF;
23: GO
1: Private Sub LoadDocument(ByVal documentID As Integer)
2: Dim dt As DataTable = New DataTable()
3:
4: Using conn As SqlConnection = New SqlConnection(_connectionString)
5: Dim reader As SqlDataReader = Nothing
6: Dim context() As Byte = Nothing
7: Dim serverFilePath As String = ""
8:
9: conn.Open()
10:
11: Dim transaction As SqlTransaction = conn.BeginTransaction()
12:
13: Try
14: Dim cmd As SqlCommand = conn.CreateCommand()
15: With cmd
16: .Transaction = transaction
17: .CommandType = CommandType.StoredProcedure
18: .CommandText = "dbo.usp_SelectDocument"
19: .Parameters.Add(New SqlParameter("@DocumentID", documentID))
20: End With
21:
22: reader = cmd.ExecuteReader()
23: dt.Load(reader)
24:
25: If dt.Rows.Count > 0 Then
26: serverFilePath = dt.Rows(0)("PathName").ToString()
27: context = DirectCast(dt.Rows(0)("TransactionContext"), Byte())
28:
29: Using memStream As MemoryStream = New MemoryStream()
30: Using serverStream As SqlFileStream = New SqlFileStream(serverFilePath, context, FileAccess.Read)
31:
32: Dim bytesRead As Integer = 0, bufferSize As Integer = 4096
33: Dim buffer As Byte() = New Byte(bufferSize - 1) {}
34:
35: Do
36: bytesRead = serverStream.Read(buffer, 0, bufferSize)
37: If bytesRead = 0 Then
38: Exit Do
39: End If
40:
41: memStream.Write(buffer, 0, bytesRead)
42: memStream.Flush()
43: Loop While True
44:
45: serverStream.Close()
46: End Using
47:
48: memStream.Seek(0, SeekOrigin.Begin)
49:
50: Dim bmpImage As BitmapImage = New BitmapImage()
51: With bmpImage
52: .BeginInit()
53: .StreamSource = memStream
54: .CreateOptions = BitmapCreateOptions.None
55: .CacheOption = BitmapCacheOption.OnLoad
56: .EndInit()
57: .Freeze()
58: End With
59:
60: Me.imgView.Source = bmpImage
61:
62: memStream.Close()
63: End Using
64: End If
65:
66: transaction.Commit()
67: Catch ex As Exception
68: Try
69: 'attempt to rollback the transaction
70: transaction.Rollback()
71: Catch ex2 As Exception
72: 'rollback was unsuccessful (connection may be closed)
73: End Try
74:
75: MessageBox.Show(ex.Message)
76: Finally
77: If reader IsNot Nothing AndAlso Not reader.IsClosed Then reader.Close()
78: End Try
79: End Using
80: 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)