sql Server FileStream 功能的詳細參考聯(lián)機幫助設計和實現(xiàn) FILESTREAM 存儲
這里只是把使用 Win32 管理 FILESTREAM 數(shù)據(jù)的代碼調整了一下，實現(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:tempdb_test';CREATE?DATABASE?_testON ????PRIMARY( ????????NAME?=?_test,?FILENAME?=?'f:tempdb_test_test.mdf'), ????FILEGROUP?FG_stream?CONTAINS?FILESTREAM( ????????NAME?=?_test_file_stream,?FILENAME?=?'f:tempdb_teststream') ????LOG?ON( ????????NAME?=?_test_log,?FILENAME?=?'f:tempdb_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:tempre.csv") ????????????Console.WriteLine("從?FileStream?讀取數(shù)據(jù)保存到文件") ????????????Read(sqlConnection,?"test",?"f:tempre_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>數(shù)據(jù)庫連接????'''?<param>名稱 ????'''?<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>數(shù)據(jù)庫連接????'''?<param>名稱 ????'''?<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如何存取 ，更多相關內容請關注php中文網(wǎng)。
相關推薦：
? 版權聲明
文章版權歸作者所有，未經(jīng)允許請勿轉載。
THE END
