怎么存取帶進度的SQLServerFileStream

小編給大家分享一下怎么存取帶進度的SQL Server FileStream，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

創(chuàng)新互聯(lián)公司專注于網(wǎng)站建設|成都網(wǎng)站維護公司|優(yōu)化|托管以及網(wǎng)絡推廣，積累了大量的網(wǎng)站設計與制作經(jīng)驗，為許多企業(yè)提供了網(wǎng)站定制設計服務，案例作品覆蓋水泥攪拌車等行業(yè)。能根據(jù)企業(yè)所處的行業(yè)與銷售的產(chǎn)品，結合品牌形象的塑造，量身策劃品質網(wǎng)站。

SQL Server FileStream 功能的詳細參考聯(lián)機幫助設計和實現(xiàn) FILESTREAM 存儲
這里只是把使用 Win32 管理 FILESTREAM 數(shù)據(jù)的代碼調(diào)整了一下，實現(xiàn)帶進度的存取，這對于存取較大的文件比較有意義
要使用FileStream，首先要在 SQL Server配置管理器中打開FileStream選項：SQL Server配置管理器–SQL Server服務–右邊的服務列表中找到SQL Server服務–屬性–FILESTREAM–允許遠程客戶端訪問FILESTREAM數(shù)據(jù)根據(jù)需要選擇，其他兩薦都選上。配置完成后，需要重新啟動SQL Server服務使設置生效。
然后使用下面的腳本創(chuàng)建測試數(shù)據(jù)庫和測試表

 -- =========================================================-- 啟用 filestream_access_level-- =========================================================EXEC sp_configure 'filestream_access_level', 2;     -- 0=禁用  1=針對 T-SQL 訪問啟用 FILESTREAM  2=針對 T-SQL 和 WIN32 流訪問啟用 FILESTREAMRECONFIGURE;
GO-- =========================================================-- 創(chuàng)建測試數(shù)據(jù)庫-- =========================================================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-- =========================================================-- =================================================-- 創(chuàng)建 包含 FileStream 數(shù)據(jù)的表-- -------------------------------------------------CREATE TABLE _test.dbo.tb_fs(
    id uniqueidentifier ROWGUIDCOL      -- 必需        DEFAULT NEWSEQUENTIALID ( ) PRIMARY KEY,
    name nvarchar(260),
    content varbinary(max) FILESTREAM
);GO

下面的 VB 腳本實現(xiàn)帶進度顯示的文件存(Write方法）?。≧ead方法）

Imports System.IO
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Module 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 讀取數(shù)據(jù)保存到文件")
            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>
    ''' 將文件保存到數(shù)據(jù)庫    ''' </summary>
    ''' <param name="conn">數(shù)據(jù)庫連接</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 文件路徑 ( 注意函數(shù)大小寫 )
            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 中讀取和寫入數(shù)據(jù)            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>
    ''' 從數(shù)據(jù)庫讀取數(shù)據(jù)保存到文件    ''' </summary>
    ''' <param name="conn">數(shù)據(jù)庫連接</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 文件路徑 ( 注意函數(shù)大小寫 )
            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 中讀取和寫入數(shù)據(jù)
            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 Sub
End Module

以上是怎么存取帶進度的SQL Server FileStream的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

分享題目：怎么存取帶進度的SQLServerFileStream
文章URL：http://muchs.cn/article24/pdjoje.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供App開發(fā)、Google、品牌網(wǎng)站制作、外貿(mào)建站、品牌網(wǎng)站建設、建站公司

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容