Microsoft KB Archive/316887

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

Article ID: 316887

Article Last Modified on 4/5/2004

-

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 Q316887



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.SqlClient
 * System.Data.OleDb

IN THIS TASK

 * SUMMARY
 * How to Read from a BLOB Column
 * How to Write to a BLOB Column
 * Sample Application
 * Troubleshooting



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 &quot;Test&quot; 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(&quot;server=localhost;integrated security=yes;database=NorthWind&quot;) Dim cmd As New SqlCommand(&quot;SELECT Picture FROM Categories WHERE CategoryName='Test'&quot;, 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(&quot;provider=sqloledb;server=localhost;user id=myuser;password=mypassword;initial catalog=NorthWind&quot;) Dim cmd As New OleDbCommand(&quot;SELECT Picture FROM Categories WHERE CategoryName='Test'&quot;, 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(&quot;server=localhost;integrated security=yes;database=NorthWind&quot;) Dim cmd As New SqlCommand(&quot;UPDATE Categories SET Picture=@Picture WHERE CategoryName='Test'&quot;, 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(&quot;@Picture&quot;, 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(&quot;provider=sqloledb;server=localhost;user id=myuser;password=mypassword;initial catalog=NorthWind&quot;) Dim cmd As New OleDbCommand(&quot;UPDATE Categories SET Picture=? WHERE CategoryName='Test'&quot;, 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(&quot;@Picture&quot;, 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.  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 &quot;Test&quot;. 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

  Switch to Code view, and then add the following Imports declaration at the top of the Code window: Imports System.Data.SqlClient   Add the following sample code to the Click event handler for the Load button: File2SqlBlob(&quot;c:\testfile.dat&quot;) 'File2OleDbBlob(&quot;C:\test.dat&quot;) ' 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.

</li>  Add the following sample code to the Click event handler for the Load button: SqlBlob2File(&quot;c:\copyoftestfile.dat&quot;) 'OlDbBlob2File(&quot;C:\copyoftestfileoledb.dat&quot;) ' uncomment to use OLEDB NOTE: You may want to change the destination file name to one that does not exist on your computer.

</li> Copy the File2SqlBlob and SqlBlob2File procedures from the article text, and then paste them into your form code.</li> Run the application. Click Load and then click Save. You should see a new file on your hard disk identical to the source file.</li></ol>

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.</li> Remember to modify the connection string and SQL statements to suit your own server.</li> Add error checking in case your query returns no records.</li>  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 &quot;Sql&quot; functions Imports System.Data.OleDb      ' needed if using &quot;OleDb&quot; functions </li> 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.</li></ul>

back to the top

<div class="references_section">