带进度的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 访问启用 FILESTREAM2=针对 T-SQL 和 WIN32 流访问启用 FILESTREAMRECONFIGURE;GO-- =========================================================-- 创建测试数据库-- =========================================================EXEC master..xp_create_subdir 'f:tempdb_test';CREATE DATABASE _testONPRIMARY(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-- =========================================================-- =================================================-- 创建 包含 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 Module1Public Sub Main(ByVal args As String())Dim sqlConnection As New SqlConnection("Integrated Security=true;server=localhost")TrysqlConnection.Open()Console.WriteLine("将文件保存到 FileStream")Write(sqlConnection, "test", "f:tempre.csv")Console.WriteLine("从 FileStream 读取数据保存到文件")Read(sqlConnection, "test", "f:tempre_1.csv")Catch ex As System.ExceptionConsole.WriteLine(ex.ToString())FinallysqlConnection.Close()End TryConsole.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 = 1024Using sqlCmd As New SqlCommandsqlCmd.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 = NothingDim pathObj As Object = sqlCmd.ExecuteScalar()If Not pathObj.Equals(DBNull.Value) ThenfilePath = DirectCast(pathObj, String)ElseThrow 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() = NothingDim contextLength As UIntegerIf Not obj.Equals(DBNull.Value) ThentxContext = DirectCast(obj, Byte())contextLength = txContext.Length()ElseDim 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 = 0Using fsRead As New FileStream(file, FileMode.Open)While TruenumBytes = fsRead.Read(buffer, 0, bufferSize)If numBytes = 0 Then Exit WhilesqlFileStream.Write(buffer, 0, numBytes)Console.WriteLine(String.Format("{0} -> {1} -> {2}", fsRead.Position, sqlFileStream.Position, numBytes))End WhilefsRead.Close()End UsingsqlFileStream.Close()End UsingsqlCmd.Transaction.Commit()End UsingEnd 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 = 1024Using sqlCmd As New SqlCommandsqlCmd.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 = NothingDim pathObj As Object = sqlCmd.ExecuteScalar()If Not pathObj.Equals(DBNull.Value) ThenfilePath = DirectCast(pathObj, String)ElseThrow 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 = transactionsqlCmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"Dim obj As Object = sqlCmd.ExecuteScalar()Dim txContext As Byte() = NothingDim contextLength As UIntegerIf Not obj.Equals(DBNull.Value) ThentxContext = DirectCast(obj, Byte())contextLength = txContext.Length()ElseDim 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 = 0Using fsRead As New FileStream(file, FileMode.Create)While TruenumBytes = sqlFileStream.Read(buffer, 0, bufferSize)If numBytes = 0 Then Exit WhilefsRead.Write(buffer, 0, numBytes)Console.WriteLine(String.Format("{0} -> {1} -> {2}", sqlFileStream.Position, sqlFileStream.Position, numBytes))End WhilefsRead.Close()End UsingsqlFileStream.Close()End UsingsqlCmd.Transaction.Commit()End UsingEnd SubEnd Module本文讲解了带进度的SQL Server FileStream如何存取 ，更多相关内容请关注小潘博客。

相关推荐：

当忘记 SQL Server 管理员密码该如何处理

浅析MySQL中concat以及group_concat的使用

介绍MySQL图形化管理工具以上就是带进度的SQL Server FileStream如何存取的详细内容，更多请关注小潘博客其它相关文章！