Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 308042 - Last Review: March 29, 2007 - Revision: 4.3
How To Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET
This article was previously published under Q308042
The
GetChunk and the
AppendChunk methods are not available in ADO.NET to read and write binary
large object (BLOB) fields. This article describes how to use the
FileStream object and a byte array to read and to write BLOB data from
Microsoft SQL Server to a file.
Requirements
The following list outlines the recommended hardware, software,
network infrastructure, and service packs that are required:
- Microsoft Windows 2000 Professional, Windows 2000 Server,
Windows 2000 Advanced Server, or Windows NT 4.0 Server
- Microsoft Visual Studio .NET
- Microsoft SQL Server
Create the Project
- Add a table named MyImages to your SQL Server Northwind database. Include the following fields in your table:
- Identity field that is named "ID" of type Int.
- Field that is named "Description" of type VarChar with a length of 50.
- Field that is named "ImgField" of type Image.
- Start Visual Studio .NET, and then create a new Visual
Basic Windows Application project.
- Add two Button controls to the default form, Form1.
- In the Properties window, change the Text property of Button1 to Save to Database (from File), and then
change the Text property of Button2 to Save to File (from
Database).
- Add the following code to the top of the Code window:
Imports System.Data.SqlClient
Imports System.IO
- Double-click Button1, and then add the following code to the Button1_Click event handler:
Note You must change uid<username> and pwd =<strong
password> to the correct values before you run this code. Make sure that
User ID has the appropriate permissions to perform this operation on the
database.
Dim con As New SqlConnection _
("Server=YourServer;uid=<username>;pwd=<strong password>;database=northwind")
Dim da As New SqlDataAdapter _
("Select * From MyImages", con)
Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim ds As New DataSet()
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim fs As New FileStream _
("C:\winnt\Gone Fishing.BMP", FileMode.OpenOrCreate, _
FileAccess.Read)
Dim MyData(fs.Length) As Byte
fs.Read(MyData, 0, fs.Length)
fs.Close()
con.Open()
da.Fill(ds, "MyImages")
Dim myRow As DataRow
myRow = ds.Tables("MyImages").NewRow()
myRow("Description") = "This would be description text"
myRow("imgField") = MyData
ds.Tables("MyImages").Rows.Add(myRow)
da.Update(ds, "MyImages")
fs = Nothing
MyCB = Nothing
ds = Nothing
da = Nothing
con.Close()
con = Nothing
MsgBox ("Image saved to database")
- Double-click Button2, and then add the following code to the Button2_Click event handler:
Note You must change uid <username> and pwd =<strong
password> to the correct values before you run this code. Make sure that
User ID has the appropriate permissions to perform this operation on the
database.
Dim con As New SqlConnection _
("Server=YourServer;uid=<username>;pwd=<strong password>;database=northwind")
Dim da As New SqlDataAdapter _
("Select * From MyImages", con)
Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim ds As New DataSet()
con.Open()
da.Fill(ds, "MyImages")
Dim myRow As DataRow
myRow = ds.Tables("MyImages").Rows(0)
Dim MyData() As Byte
MyData = myRow("imgField")
Dim K As Long
K = UBound(MyData)
Dim fs As New FileStream _
("C:\winnt\Gone Fishing2.BMP", FileMode.OpenOrCreate, _
FileAccess.Write)
fs.Write(MyData, 0, K)
fs.Close()
fs = Nothing
MyCB = Nothing
ds = Nothing
da = Nothing
con.Close()
con = Nothing
MsgBox ("Image retrieved")
- Press F5 to compile and to run the application.
- Click Save to Database (from File) to load
the image, C:\WinNT\Gone Fishing.bmp, into the SQL Server Image field. After you receive the confirmation message that the image
has been saved, check your table to verify.
- Click Save to File (from Database) to save
the data from the SQL Server Image field back to a file. Verify that C:\WinNT\Gone Fishing2.bmp now
exists.
For additional
information about a similar topic in Visual Basic 6.0, click the following
article number to view the article in the Microsoft Knowledge Base:
258038Â
(http://kbalertz.com/Feedback.aspx?kbNumber=258038/EN-US/
)
How To Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
For more general information about ADO.NET or Visual
Basic .NET, refer to the following MSDN newsgroups:
microsoft.public.dotnet.framework.adonet
(http://msdn.microsoft.com/newsgroups/default.aspx?query=microsoft.public.dotnet.framework.adonet&dg=&cat=en-us-msdn&lang=en&cr=US&pt=&catlist=774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us)
microsoft.public.dotnet.languages.vb
(http://msdn.microsoft.com/newsgroups/default.aspx?query=microsoft.public.dotnet.languages.vb&dg=&cat=en-us-msdn&lang=en&cr=US&pt=&catlist=774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us)
APPLIES TO
- Microsoft ADO.NET (included with the .NET Framework)
- Microsoft ADO.NET 1.1
- Microsoft Visual Basic .NET 2002 Standard Edition
- Microsoft Visual Basic .NET 2003 Standard Edition
| kbhowtomaster kbio kbsqlclient kbsystemdata KB308042 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
Peter
- pppjjj444 NOSPAM-AT-NOSPAM yahoo.com
|
|
| Written:
3/26/2005 9:31 AM |
|
|
|
Peter
- pppjjj444 NOSPAM-AT-NOSPAM yahoo.com
|
|
| Written:
3/26/2005 9:34 AM |
|
|