帶進度的SQL Server FileStream如何存取

SQL Server FileStream 功能的詳細參考線上說明設計和實現 FILESTREAM 儲存
這裡只是把使用 Win32 管理 FILESTREAM 資料的代碼調整了一下，實現帶進度的存取，這對於存取較大的檔案比較有意義
要使用FileStream，首先要在 SQL Server組態管理員中開啟FileStream選項：SQL Server組態管理員–SQL Server服務–右邊的服務列表中找到SQL Server服務–屬性–FILESTREAM–允許遠程用戶端訪問FILESTREAM資料根據需要選擇，其他兩薦都選上。配置完成後，需要重新啟動SQL Server服務使設定生效。
然後使用下面的指令碼建立測試資料庫和測試表

 -- =========================================================-- 啟用 filestream_access_level-- =========================================================EXEC sp_configure 'filestream_access_level', 2;     -- 0=禁用  1=針對 T-SQL 訪問啟用 FILESTREAM  2=針對 T-SQL 和 WIN32 流訪問啟用 FILESTREAMRECONFIGURE;GO-- =========================================================-- 建立測試資料庫-- =========================================================EXEC master..xp_create_subdir 'f:\temp\db\_test';CREATE DATABASE _testON    PRIMARY(        NAME = _test, FILENAME = 'f:\temp\db\_test\_test.mdf'),    FILEGROUP FG_stream CONTAINS FILESTREAM(        NAME = _test_file_stream, FILENAME = 'f:\temp\db\_test\stream')    LOG ON(        NAME = _test_log, FILENAME = 'f:\temp\db\_test\_test.ldf');GO-- =========================================================-- FileStream-- =========================================================-- =================================================-- 建立 包含 FileStream 資料的表-- -------------------------------------------------CREATE TABLE _test.dbo.tb_fs(    id uniqueidentifier ROWGUIDCOL      -- 必需        DEFAULT NEWSEQUENTIALID ( ) PRIMARY KEY,    name nvarchar(260),    content varbinary(max) FILESTREAM);GO

下面的 VB 指令碼實現帶進度顯示的檔案存(Write方法）取（Read方法）

Imports System.IOImports SystemImports System.Collections.GenericImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesModule Module1    Public Sub Main(ByVal args As String())        Dim sqlConnection As New SqlConnection("Integrated Security=true;server=localhost")        Try            sqlConnection.Open()            Console.WriteLine("將檔案儲存到 FileStream")            Write(sqlConnection, "test", "f:\temp\re.csv")            Console.WriteLine("從 FileStream 讀取資料儲存到檔案")            Read(sqlConnection, "test", "f:\temp\re_1.csv")        Catch ex As System.Exception            Console.WriteLine(ex.ToString())        Finally            sqlConnection.Close()        End Try        Console.WriteLine("處理結束，按 Enter 退出")        Console.ReadLine()    End Sub    ''' <summary>    ''' 將檔案儲存到資料庫    ''' </summary>    ''' <param name="conn">資料庫連接</param>    ''' <param name="name">名稱</param>    ''' <param name="file">檔案名稱</param>    Sub Write(ByVal conn As SqlConnection, ByVal name As String, ByVal file As String)        Dim bufferSize As Int32 = 1024        Using sqlCmd As New SqlCommand            sqlCmd.Connection = conn            '事務            Dim transaction As SqlTransaction = conn.BeginTransaction("mainTranaction")            sqlCmd.Transaction = transaction            '1. 讀取 FILESTREAM 檔案路徑 ( 注意函數大小寫 )            sqlCmd.CommandText = "UPDATE _test.dbo.tb_fs SET content = 0x WHERE name = @name;IF @@ROWCOUNT = 0 INSERT _test.dbo.tb_fs(name, content) VALUES( @name, 0x );SELECT content.PathName() FROM _test.dbo.tb_fs WHERE name = @name;"            sqlCmd.Parameters.Add(New SqlParameter("name", name))            Dim filePath As String = Nothing            Dim pathObj As Object = sqlCmd.ExecuteScalar()            If Not pathObj.Equals(DBNull.Value) Then                filePath = DirectCast(pathObj, String)            Else                Throw New System.Exception("content.PathName() failed to read the path name for the content column.")            End If            '2. 讀取當前事務上下文            sqlCmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"            Dim obj As Object = sqlCmd.ExecuteScalar()            Dim txContext As Byte() = Nothing            Dim contextLength As UInteger            If Not obj.Equals(DBNull.Value) Then                txContext = DirectCast(obj, Byte())                contextLength = txContext.Length()            Else                Dim message As String = "GET_FILESTREAM_TRANSACTION_CONTEXT() failed"                Throw New System.Exception(message)            End If            '3. 擷取 Win32 控制代碼，並使用該控制代碼在 FILESTREAM BLOB 中讀取和寫入資料            Using sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Write)                Dim buffer As Byte() = New Byte(bufferSize - 1) {}                Dim numBytes As Integer = 0                Using fsRead As New FileStream(file, FileMode.Open)                    While True                        numBytes = fsRead.Read(buffer, 0, bufferSize)                        If numBytes = 0 Then Exit While                        sqlFileStream.Write(buffer, 0, numBytes)                        Console.WriteLine(String.Format("{0} -> {1} -> {2}", fsRead.Position, sqlFileStream.Position, numBytes))                    End While                    fsRead.Close()                End Using                sqlFileStream.Close()            End Using            sqlCmd.Transaction.Commit()        End Using    End Sub    ''' <summary>    ''' 從資料庫讀取資料儲存到檔案    ''' </summary>    ''' <param name="conn">資料庫連接</param>    ''' <param name="name">名稱</param>    ''' <param name="file">檔案名稱</param>    Sub Read(ByVal conn As SqlConnection, ByVal name As String, ByVal file As String)        Dim bufferSize As Int32 = 1024        Using sqlCmd As New SqlCommand            sqlCmd.Connection = conn            '1. 讀取 FILESTREAM 檔案路徑 ( 注意函數大小寫 )            sqlCmd.CommandText = "SELECT content.PathName() FROM _test.dbo.tb_fs WHERE name = @name;"            sqlCmd.Parameters.Add(New SqlParameter("name", name))            Dim filePath As String = Nothing            Dim pathObj As Object = sqlCmd.ExecuteScalar()            If Not pathObj.Equals(DBNull.Value) Then                filePath = DirectCast(pathObj, String)            Else                Throw New System.Exception("content.PathName() failed to read the path name for the content column.")            End If            '2. 讀取當前事務上下文            Dim transaction As SqlTransaction = conn.BeginTransaction("mainTranaction")            sqlCmd.Transaction = transaction            sqlCmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"            Dim obj As Object = sqlCmd.ExecuteScalar()            Dim txContext As Byte() = Nothing            Dim contextLength As UInteger            If Not obj.Equals(DBNull.Value) Then                txContext = DirectCast(obj, Byte())                contextLength = txContext.Length()            Else                Dim message As String = "GET_FILESTREAM_TRANSACTION_CONTEXT() failed"                Throw New System.Exception(message)            End If            '3. 擷取 Win32 控制代碼，並使用該控制代碼在 FILESTREAM BLOB 中讀取和寫入資料            Using sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Read)                Dim buffer As Byte() = New Byte(bufferSize - 1) {}                Dim numBytes As Integer = 0                Using fsRead As New FileStream(file, FileMode.Create)                    While True                        numBytes = sqlFileStream.Read(buffer, 0, bufferSize)                        If numBytes = 0 Then Exit While                        fsRead.Write(buffer, 0, numBytes)                        Console.WriteLine(String.Format("{0} -> {1} -> {2}", sqlFileStream.Position, sqlFileStream.Position, numBytes))                    End While                    fsRead.Close()                End Using                sqlFileStream.Close()            End Using            sqlCmd.Transaction.Commit()        End Using    End SubEnd Module

本文講解了帶進度的SQL Server FileStream如何存取 ，更多相關內容請關注php中文網。

相關推薦：

當忘記 SQL Server 管理員密碼該如何處理

淺析MySQL中concat以及group_concat的使用

介紹MySQL圖形化管理工具