当然可以，就是存储二进制文件呀。 HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object The information in this article applies to: ActiveX Data Objects (ADO) 2.5 Microsoft Visual Basic Professional Edition for Windows 6.0 Microsoft Visual Basic Enterprise Edition for Windows 6.0 Microsoft OLE DB Provider for SQL Server 7.0This article was previously published under Q258038 SUMMARY The Stream object introduced in ActiveX Data Objects (ADO) 2.5 can be used to greatly simplify the code that needs to be written to access and modify Binary Large Object (BLOB) data in a SQL Server Database. The previous versions of ADO [ 2.0, 2.1, and 2.1 SP2 ] required careful usage of the GetChunk and AppendChunk methods of the Field Object to read and write BLOB data in fixed-size chunks from and to a BLOB column. An alternative to this method now exists with the advent of ADO 2.5. This article includes code samples that demonstrate how the Stream object can be used to program the following common tasks: Save the data stored in a SQL Server Image column to a file on the hard disk. Move the contents of a .gif file to an Image column in a SQL Server table. MORE INFORMATION The following code samples are 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 point to your SQL Server installation. Example 1 : Saving the Data in a SQL Server Image Column to a File on the Hard Disk The code in this example opens a recordset on the pub_info table in the pubs database and saves the binary image data stored in the logo column of the first record to a file on the hard disk, as follows: Open a new Standard EXE Visual Basic project. On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library. Place a CommandButton control on Form1. Make the following declarations in the form's General declarations section: Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim mstream As ADODB.StreamCut and paste the following code into the Click event of the CommandButton that you added to the form: Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>; Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"Set rs = New ADODB.Recordset rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Stream mstream.Type = adTypeBinary mstream.Open mstream.Write rs.Fields("logo").Value mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWriters.Close cn.CloseSave and run the Visual Basic project. Click the CommandButton to save the binary data in the logo column of the first record to the file c:\publogo.gid. Look for this file in Windows Explorer and open it to view the saved image.The code in this example declares an ADODB Stream object and sets its Type property to adTypeBinary to reflect that this object will be used to work with Binary data. Following this, the binary data stored in the logo column of the first record in the pub_info table is written out to the Stream object by calling its Write method. The Stream object now contains the binary data that is saved to the file by calling its SaveToFile method and passing in the path to the file. The adSaveCreateOverWrite constant passed in as the second parameter causes the SaveToFile method to overwrite the specified file if it already exists. Example 2 : Transfer the Image Stored in a .gif File to an Image Column in a SQL Server Table The code in this example saves an image stored in a .gif file to the logo column in the first record of the pub_info table by overwriting its current contents, as follows: Open a new Standard EXE Visual Basic project. On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library. Place a CommandButton on Form1. Make the following declarations in the form's General declarations section:Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim mstream As ADODB.StreamCut and paste the following code in the Click event of the CommandButton that you added to the form:Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>; Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"Set rs = New ADODB.Recordset rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Stream mstream.Type = adTypeBinary mstream.Open mstream.LoadFromFile "<path to .gif file>" rs.Fields("logo").Value = mstream.Read rs.Updaters.Close cn.CloseSave and run the Visual Basic project. Click on the CommandButton to run the code to stream the contents of the .gif file to the ADO Stream object, and save the data in the Stream to the logo column in the first record of the recordset. Verify that the image in the logo column has been modified by using the code in Example 1.
'******************将图片文件保存到数据库中************************* '******************需要添加一个commondialog控件 Sub SavePicToDb(cn As ADODB.Connection, table1 As String, field1 As String, file1 As String, id1 As String) On Error Resume Next Dim stm As ADODB.Stream Set stm = New ADODB.Stream Set rs1 = New ADODB.Recordset rs1.Open "select * from " & table1 & " where id = " & id1, cn, adOpenKeyset, adLockOptimistic With stm .Type = adTypeBinary .Open .LoadFromFile file1 'DLG.FileName End With With rs1 .Fields(field1) = stm.Read .Update End With rs1.Close Set rs1 = Nothing End Sub
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
The information in this article applies to:
ActiveX Data Objects (ADO) 2.5
Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0
Microsoft OLE DB Provider for SQL Server 7.0This article was previously published under Q258038
SUMMARY
The Stream object introduced in ActiveX Data Objects (ADO) 2.5 can be used to greatly simplify the code that needs to be written to access and modify Binary Large Object (BLOB) data in a SQL Server Database. The previous versions of ADO [ 2.0, 2.1, and 2.1 SP2 ] required careful usage of the GetChunk and AppendChunk methods of the Field Object to read and write BLOB data in fixed-size chunks from and to a BLOB column. An alternative to this method now exists with the advent of ADO 2.5. This article includes code samples that demonstrate how the Stream object can be used to program the following common tasks:
Save the data stored in a SQL Server Image column to a file on the hard disk.
Move the contents of a .gif file to an Image column in a SQL Server table.
MORE INFORMATION
The following code samples are 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 point to your SQL Server installation.
Example 1 : Saving the Data in a SQL Server Image Column to a File on the Hard Disk
The code in this example opens a recordset on the pub_info table in the pubs database and saves the binary image data stored in the logo column of the first record to a file on the hard disk, as follows:
Open a new Standard EXE Visual Basic project.
On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.
Place a CommandButton control on Form1.
Make the following declarations in the form's General declarations section: Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.StreamCut and paste the following code into the Click event of the CommandButton that you added to the form: Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("logo").Value
mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWriters.Close
cn.CloseSave and run the Visual Basic project.
Click the CommandButton to save the binary data in the logo column of the first record to the file c:\publogo.gid. Look for this file in Windows Explorer and open it to view the saved image.The code in this example declares an ADODB Stream object and sets its Type property to adTypeBinary to reflect that this object will be used to work with Binary data. Following this, the binary data stored in the logo column of the first record in the pub_info table is written out to the Stream object by calling its Write method. The Stream object now contains the binary data that is saved to the file by calling its SaveToFile method and passing in the path to the file. The adSaveCreateOverWrite constant passed in as the second parameter causes the SaveToFile method to overwrite the specified file if it already exists.
Example 2 : Transfer the Image Stored in a .gif File to an Image Column in a SQL Server Table
The code in this example saves an image stored in a .gif file to the logo column in the first record of the pub_info table by overwriting its current contents, as follows:
Open a new Standard EXE Visual Basic project.
On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.
Place a CommandButton on Form1.
Make the following declarations in the form's General declarations section:Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.StreamCut and paste the following code in the Click event of the CommandButton that you added to the form:Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "<path to .gif file>"
rs.Fields("logo").Value = mstream.Read
rs.Updaters.Close
cn.CloseSave and run the Visual Basic project.
Click on the CommandButton to run the code to stream the contents of the .gif file to the ADO Stream object, and save the data in the Stream to the logo column in the first record of the recordset.
Verify that the image in the logo column has been modified by using the code in Example 1.
'******************需要添加一个commondialog控件
Sub SavePicToDb(cn As ADODB.Connection, table1 As String, field1 As String, file1 As String, id1 As String)
On Error Resume Next
Dim stm As ADODB.Stream
Set stm = New ADODB.Stream
Set rs1 = New ADODB.Recordset
rs1.Open "select * from " & table1 & " where id = " & id1, cn, adOpenKeyset, adLockOptimistic
With stm
.Type = adTypeBinary
.Open
.LoadFromFile file1 'DLG.FileName
End With
With rs1
.Fields(field1) = stm.Read
.Update
End With
rs1.Close
Set rs1 = Nothing
End Sub