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如何存取 ，更多相关内容请关注。
相关推荐：
当忘记 sql server 管理员密码该如何处理
浅析mysql中concat以及group_concat的使用
介绍mysql图形化管理工具
以上就是带进度的sql server filestream如何存取的详细内容。