How to use ADO Stream object to access and modify SQL Server BLOB data
Suitable
Summary Use ActiveX Data Objects (ADO) 2.5
Stream objects can greatly simplify the code you need to write and modify the binary large object (blob) data in the SQL Server database. Use in previous versions of ADO (2.0, 2.1, and 2.1 sp2)
Field Object
Getchunk and
The Appendchunk method reads BLOB data from the blob column in a fixed block area, or carefully writes the blob data to it. An alternative method is now available in ADO 2.5. The code example presenting this article demonstrates how to use
Stream object written programs to perform the following common tasks:
Save the data stored in the SQL Server Image column into the file on the hard disk. Move the contents of the .GIF file to the Image column of the SQL Server table.
MORE INFORMATION The following code example is based on the data stored in the PUB_INFO table in the SQL Server 7.0 PUBS sample database. You need to modify the ADO connection string to your own SQL Server.
Example 1: Saving the data in the SQL Server Image column in the file in the file on the hard disk This example will open a recordset in the PUB_INFO table in the PUBS database, and store the binary image stored in the first recorded log column The data is saved to the file on the hard disk, as shown below:
Open a new standard EXE Visual Basic project. On the project menu, click to select a reference, then set a reference to the Microsoft ActiveX Data Object 2.5 object library. Place a CommandButton control on Form1. The following declarations are made in the General declaration part of the form: DIM CN As Adodb.Connection
DIM RS as adodb.recordset
Dim mstream as adodo.stream
Cut the following code and paste the Click event from the CommandButton control you add to your form: SET CN = New AdoDb.Connection
Cn.open "provider = SQLOLEDB; DATA SOURCE =
Initial catalog = pubs; user ID =
SET RS = New Adodb.Recordset
Rs.Open "Select * from Pub_INFO", CN, AdopenkeySet, AdlockOptimistic
Set mstream = new adoDb.stream
MStream.Type = adtypebinary
MSTream.open
MSTream.write Rs.fields ("LOGO"). Value
MSTream.savetofile "C: /Publogo.gif", AdsavecreateOverWrite
Rs.close
Cn.close
Save and run the Visual Basic project. Click CommandButton to save the binary data in the first recorded log column to the C: /Publogo.gid file. Find and open this file in Windows Explorer to view saved images. The code in this example declares an ADODB Stream object and sets its TYPE attribute to adtypebinary to indicate that the object will be used to process binary data. Then, writing the binary data stored in the log column of the first record of the PUB_INFO table to the object by calling the WRITE method of the Stream object. Next, by calling the STREAM object SaveTofile method and passes into the file path, the binary data contained in the object is saved to the file. The AdsaveCreateOverWrite constant that is incorporated as the second parameter causes the SaveTofile method to overwrite the specified file (if the file exists). Example 2: Transferring the image stored in the .GIF file to the code in the image column of the SQL Server table, saves the image stored in the Pub_info table in the image of the image of the Pub_info table to the first record of the first record in the Pub_INFO table. The content is as follows:
Open a new standard EXE Visual Basic project. On the project menu, click to select a reference, then set a reference to the Microsoft ActiveX Data Object 2.5 object library. Place a CommandButton control in Form1. The following declarations are made in the General declaration part of the form: DIM CN As Adodb.Connection
DIM RS as adodb.recordset
Dim mstream as adodo.stream
Cut the following code and paste the Click event from the CommandButton control you add to your form: SET CN = New AdoDb.Connection
Cn.open "provider = SQLOLEDB; DATA SOURCE =
Initial catalog = pubs; user ID =
SET RS = New Adodb.Recordset
Rs.Open "Select * from Pub_INFO", CN, AdopenkeySet, AdlockOptimistic
Set mstream = new adoDb.stream
MStream.Type = adtypebinary
MSTream.open
MStream.LoadFromfile "
Rs.fields ("logo"). Value = mstream.read
Rs.Update
Rs.close
Cn.close
Save and run the Visual Basic project. Click the CommandButton run code, transfer the contents of the .gif file to the ADO Stream object in the data stream, and then save the data in the stream to the logo column of the first record in the recordset. Using the code verify that the image in the emblem tap has been modified in Example 1.
Refer to additional information, click the article number below to see the article in the Microsoft Knowledge Base:
308042 How to use ADO.NET to read and write BLOB data in Visual Basic .NET
