HOW TO: Read and Write a File to and from a BLOB Column by Using ADO.NET and Visual Basic .NET

• This article provides examples of both the SqlClient and the OleDb DataAdapter. The only differences, apart from class names, are the connection strings and the declaration of SQL parameters. The fundamental technique for retrieving the BLOB data is the same.
• The example code uses the Northwind sample database. In the Northwind database, the "Test" record in the Categories table does not exist. You must add a record with a CategoryName value of Test. To add this record, run the following code in SQL Server Query Analyzer:

  Insert into categories(categoryname) values ('Test') 
  					

Back to the top

How to Read from a BLOB Column

Private Sub SqlBlob2File(ByVal DestFilePath As String)
  Dim PictureCol As Integer = 0 ' the column # of the BLOB field
  Dim cn As New SqlConnection("server=localhost;integrated security=yes;database=NorthWind")
  Dim cmd As New SqlCommand("SELECT Picture FROM Categories WHERE CategoryName='Test'", cn)
  cn.Open()
  Dim dr As SqlDataReader = cmd.ExecuteReader()
  dr.Read()
  Dim b(dr.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
  dr.GetBytes(PictureCol, 0, b, 0, b.Length)
  dr.Close()
  cn.Close()
  Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
  fs.Write(b, 0, b.length)
  fs.Close()
End Sub

Private Sub OlDbBlob2File(ByVal DestFilePath As String)
  Dim PictureCol As Integer = 0 ' the column # of the BLOB field
  Dim cn As New OleDbConnection("provider=sqloledb;server=localhost;user id=myuser;password=mypassword;initial catalog=NorthWind")
  Dim cmd As New OleDbCommand("SELECT Picture FROM Categories WHERE CategoryName='Test'", cn)
  cn.Open()
  Dim dr As OleDbDataReader = cmd.ExecuteReader()
  dr.Read()
  Dim b(dr.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
  dr.GetBytes(PictureCol, 0, b, 0, b.Length)
  dr.Close()
  cn.Close()
  Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
  fs.Write(b, 0, b.Length)
  fs.Close()
End Sub
				

Back to the top

How to Write to a BLOB Column

Private Sub File2SqlBlob(ByVal SourceFilePath As String)
  Dim cn As New SqlConnection("server=localhost;integrated security=yes;database=NorthWind")
  Dim cmd As New SqlCommand("UPDATE Categories SET Picture=@Picture WHERE CategoryName='Test'", cn)
  Dim fs As New System.IO.FileStream(SourceFilePath, IO.FileMode.Open, IO.FileAccess.Read)
  Dim b(fs.Length() - 1) As Byte
  fs.Read(b, 0, b.Length)
  fs.Close()
  Dim P As New SqlParameter("@Picture", SqlDbType.Image, b.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, b)
  cmd.Parameters.Add(P)
  cn.Open()
  cmd.ExecuteNonQuery()
  cn.Close()
End Sub

Private Sub File2OleDbBlob(ByVal SourceFilePath As String)
  Dim cn As New OleDbConnection("provider=sqloledb;server=localhost;user id=myuser;password=mypassword;initial catalog=NorthWind")
  Dim cmd As New OleDbCommand("UPDATE Categories SET Picture=? WHERE CategoryName='Test'", cn)
  Dim fs As New System.IO.FileStream(SourceFilePath, IO.FileMode.Open, IO.FileAccess.Read)
  Dim b(fs.Length() - 1) As Byte
  fs.Read(b, 0, b.Length)
  fs.Close()
  Dim P As New OleDbParameter("@Picture", OleDbType.LongVarBinary, b.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, b)
  cmd.Parameters.Add(P)
  cn.Open()
  cmd.ExecuteNonQuery()
  cn.Close()
End Sub
				

Back to the top

Sample Application

1. Start Visual Studio .NET, and use the Server Explorer or use another database tool of your choice. Connect to your Northwind database. Open the Categories table, and then add a new record with a CategoryName of "Test".
2. Create a new Visual Basic .NET Windows Form application. Add two Button controls with the following properties to the form:
   Name: btnLoadFromFile
   Text: Load from file
   
   Name: btnSaveToFile
   Text: Save to file
   
3. Switch to Code view, and then add the following Imports declaration at the top of the Code window:

   Imports System.Data.SqlClient
   					

4. Add the following sample code to the Click event handler for the Load button:

   File2SqlBlob("c:\testfile.dat")
   'File2OleDbBlob("C:\test.dat") ' uncomment to use OLEDB
   					

   NOTE: Change the source file path to point to the data file that you want to copy into the SQL Server table.
   
   
5. Add the following sample code to the Click event handler for the Load button:

   SqlBlob2File("c:\copyoftestfile.dat")
   'OlDbBlob2File("C:\copyoftestfileoledb.dat") ' uncomment to use OLEDB
   					

   NOTE: You may want to change the destination file name to one that does not exist on your computer.
   
   
6. Copy the File2SqlBlob and SqlBlob2File procedures from the article text, and then paste them into your form code.
7. Run the application. Click Load and then click Save. You should see a new file on your hard disk identical to the source file.

Back to the top

Troubleshooting

• The sample code in this article may not be suitable for use against the LongVarChar or LongVarWChar columns without modification.
• Remember to modify the connection string and SQL statements to suit your own server.
• Add error checking in case your query returns no records.
• You must add an Imports statement at the top of the code file to expose the appropriate namespace:

  Imports System.Data.SqlClient   ' needed if using "Sql" functions
  Imports System.Data.OleDb       ' needed if using "OleDb" functions
  					

• Subtract 1 from the length of the data only when you declare the array in Visual Basic .NET. If you translate the code to a different language, you probably should not subtract 1.

Back to the top