Microsoft KB Archive/308042

Knowledge Base

How To Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET

Article ID: 308042

Article Last Modified on 3/29/2007

---

APPLIES TO

• Microsoft .NET Framework 1.1 Service Pack 1
• Microsoft ADO.NET 1.1
• Microsoft Visual Basic .NET 2002 Standard Edition
• Microsoft Visual Basic .NET 2003 Standard Edition

---

This article was previously published under Q308042

For a Microsoft Visual C# .NET version of this article, see 309158.
This article refers to the following Microsoft .NET Framework Class Library namespaces:

• System.Data.SqlClient
• System.IO

IN THIS TASK

SUMMARY

• Requirements
• Create the Project

REFERENCES

SUMMARY

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.

back to the top

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

back to the top

Create the Project

1. 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.

   
   

2. Start Visual Studio .NET, and then create a new Visual Basic Windows Application project.
3. Add two Button controls to the default form, Form1.
4. 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).

5. Add the following code to the top of the Code window:

         Imports System.Data.SqlClient
         Imports System.IO
                       

6. Double-click Button1, and then add the following code to the Button1_Click event handler:
   Note You must change uid<username> and pwd = 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")
                       

7. Double-click Button2, and then add the following code to the Button2_Click event handler:
   Note You must change uid <username> and pwd = 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")
                       

8. Press F5 to compile and to run the application.
9. 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.
10. 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.

back to the top

REFERENCES

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 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

microsoft.public.dotnet.languages.vb

back to the top

Keywords: kbhowtomaster kbio kbsqlclient kbsystemdata KB308042

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.