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

SUMMARY

This article explains how to read and write data from BLOB (LongVarBinary) columns in a database table.

NOTES:

• 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

The following functions use the DataReader to retrieve the BLOB value and assign to a byte array. Because the BLOB is already completely in memory, there is no need for chunking the data, and therefore the value is assigned to a Byte array. There are two calls to the GetBytes method; the first receives the length of the BLOB in bytes and is used to dimension the Byte array. The second call retrieves the data. The FileStream object is used to write the Byte array to disk.

NOTE: In Visual Basic, you must subtract 1 from the length of the BLOB when you declare the Byte array because Visual Basic declares the upper bound of the array, as opposed to the length. In other languages, such as C# or JScript, use the length value without subtracting 1.

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

The following functions use the Command and Parameter objects to write data from a Byte array to a BLOB column. The following technique reads the file completely into memory and therefore does not need to chunk the data when writing to the server. The Byte array and the length of the array are passed to the Parameter constructor; most of the other arguments are filler values.

NOTE: In Visual Basic, you must subtract 1 from the length of the file when you declare the Byte array because Visual Basic declares the upper bound of the array, as opposed to the length. In other languages, such as C# or JScript, use the length value without subtracting 1.

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

The following sample application demonstrates how to use the SqlClient .NET Data Provider versions of the functions presented previously in this article.

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