How SQL Server FileStream with progress is accessed

Detailed reference for SQL Server FileStream features online help design and implement FileStream storage
This is just a bit of tweaking the code that uses WIN32 to manage FILESTREAM data, enabling access with progress, which is more useful for accessing larger files.
To use FILESTREAM, first open the FILESTREAM option in SQL Server Configuration Manager: SQL Server Configuration Manager –sql Server service – Locate the SQL Server service in the list of services on the right-properties –filestream – Allows remote clients to access the FILESTREAM data as needed, and the other two are selected. After the configuration is complete, you need to restart the SQL Server service for the settings to take effect.
Then use the following script to create the test database and test table

 --=========================================================--Enable filestream_access_level--======================     ===================================exec sp_configure ' filestream_access_level ', 2; --0 = disabled 1 = enabled for T-SQL access FILESTREAM 2 = enable filestreamreconfigure for T-SQL and WIN32 stream access; go--=========================================================--Creating a test database--====================================== ===================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--=================================== ======================--=================================================--Create a table that contains FileStream data----------------- ----------------------------------CREATE TABLE _test.dbo.tb_fs (ID uniqueidentifier ROWGUIDCOL--Required DEFAULT Newsequenti Alid () PRIMARY KEY, name nvarchar (260), content varbinary (max) FILESTREAM); GO

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 SqlC            Onnection as New SqlConnection ("Integrated security=true;server=localhost") Try Sqlconnection.open () Console.WriteLine ("Save File to FileStream") Write (sqlConnection, "test", "F:\temp\re.csv") Consol E.writeline ("Save from FileStream read data to File") Read (sqlConnection, "test", "F:\temp\re_1.csv") Catch ex as System . Exception Console.WriteLine (ex.        ToString ()) Finally sqlconnection.close () End Try Console.WriteLine ("Processing ends, press Enter to exit") Console.ReadLine () End Sub ' <summary> ' save file to database ' </summary> ' ' <param name= ' C Onn "> Database connection </param>" <param name= "name" > Name </param> ' <param name= "file" > file name &lT;/param> Sub Write (ByVal conn as SqlConnection, ByVal name as String, ByVal file As String) Dim buffersize A s Int32 = 1024x768 Using sqlCmd as New SqlCommand sqlcmd.connection = conn ' Transaction Dim tra Nsaction as SqlTransaction = conn. BeginTransaction ("maintranaction") sqlcmd.transaction = Transaction ' 1. Read FILESTREAM file path (note function case) 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 A s Object = Sqlcmd.executescalar () If not pathobj.equals (dbnull.value) Then FilePath = DirectCast            (Pathobj, String) Else Throw New System.Exception ("content. PathName () failed toRead the path name for the content column. ") End If ' 2. Reads the current transaction context Sqlcmd.commandtext = "Select Get_filestream_transaction_context ()" Dim obj as Object = sql Cmd.executescalar () Dim txcontext As Byte () = Nothing Dim contextlength as UInteger If No T obj. Equals (dbnull.value) Then Txcontext = DirectCast (obj, Byte ()) Contextlength = Txcontext.leng Th () Else Dim message as String = "Get_filestream_transaction_context () failed" T Hrow New System.Exception (message) End If ' 3. Gets the Win32 handle and uses the handle to read and write data in the FILESTREAM BLOB using SqlFileStream as New sqlfilestream (FilePath, Txcontext, File                 Access.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}-&gt ;                {2} ", Fsread.position, Sqlfilestream.position, numbytes)) End while Fsread.close ()        End using Sqlfilestream.close () end using SqlCmd.Transaction.Commit () End Using End Sub ' <summary> ' read data from database save to file ' </summary> ' ' <param name= ' conn "> Database connection </param>" <param name= "name" > Name </param> "<param name=" file "> file name </param&gt    ;        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. Read the FILESTREAM file path (note function capitalization) 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 A s 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. Reads the current transaction context 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 () FA Iled "Throw New System.Exception (message) End If ' 3. Gets the Win32 handle and uses the handle to read and write data in the FILESTREAM BLOB using SqlFileStream as New sqlfilestream (FilePath, Txcontext, File                Access.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

This article explains how SQL Server FileStream with progress can be accessed, and more about the PHP Chinese web.

Related recommendations:

What to do when you forget the SQL Server Administrator password

Analysis on the use of concat and Group_concat in MySQL

Introducing MySQL Graphical management tools