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

SUMMARY

This step-by-step article describes how to use the Microsoft SQL Server READTEXT and UPDATETEXT statements to read and write data from BLOB (LongVarBinary) columns in a database table.

Unlike with ADO 2.6 and later, ADO.NET does not support reading and writing BLOB objects by using Stream objects. ADO.NET data providers do not have GetChunk and AppendChunk methods available to the Data Access Object (DAO) and ActiveX Data Objects (ADO) Recordset objects. To read a BLOB field for in-memory manipulation, you can use a DataReader object to select the row, or you can cache the data in a DataSet.

However, if you want to stream the data to a different medium, such as disk or Web response, then you can read the BLOB from the server in smaller chunks to minimize the amount of memory that the process consumes. This is especially important in Web Forms applications, where you may have multiple concurrent applications running at the same time and you want to conserve memory resources.

NOTES:

Examples are shown for both the SqlClient .NET Data Provider and the OLE DB .NET Data Provider. The only differences, apart from class names, are the connection strings and the declaration of SQL parameters. The fundamental technique to retrieve the READTEXT and UPDATETEXT statements is the same.
The Test record in the Categories table of the Northwind sample database does not exist. You must use the Server Explorer or another tool to add a record with the CategoryName "Test".

back to the top

Read Chunks from a BLOB Column

The following functions use the SQL Server READTEXT statement and DataReader object to retrieve a portion of the BLOB value in a single-row, single-column rowset. Two commands perform this task: the first command retrieves the size of the BLOB field and a pointer to its location; the second command executes the READTEXT command, which retrieves the chunk of data into a Byte array, and then increments an Offset. The System.IO.Filesream object writes the Byte array to disk.

NOTE: In Visual Basic, you must subtract 1 from the BUFFER_LENGTH when declaring 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 without subtracting 1. Also, make sure that you modify your connection string for your environment.

Private Sub SqlChunkBLOB2File(ByVal DestFilePath As String)
  Dim PictureCol As Integer = 0  ' position of Picture column in DataReader
  Dim BUFFER_LENGTH As Integer = 32768 ' chunk size
  Dim cn As New SqlConnection("Data Source=mySQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;")
  '
  ' Make sure that Photo is non-NULL and return TEXTPTR to it.
  '
  Dim cmdGetPointer As New SqlCommand("SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn)
  Dim PointerOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
  PointerOutParam.Direction = ParameterDirection.Output
  Dim LengthOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Length", SqlDbType.Int)
  LengthOutParam.Direction = ParameterDirection.Output
  cn.Open()
  cmdGetPointer.ExecuteNonQuery()
  If PointerOutParam.Value Is DBNull.Value Then
    cn.Close
    ' Add code to deal with NULL BLOB.
    Exit Sub
  End If
  '
  ' Set up READTEXT command, parameters, and open BinaryReader.
  '
  Dim cmdReadBinary As New SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", cn)
  Dim PointerParam As SqlParameter = cmdReadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
  Dim OffsetParam As SqlParameter = cmdReadBinary.Parameters.Add("@Offset", SqlDbType.Int)
  Dim SizeParam As SqlParameter = cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int)
  Dim dr As SqlDataReader
  Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
  Dim Offset As Integer = 0
  OffsetParam.Value = Offset
  Dim Buffer(BUFFER_LENGTH - 1) As Byte

  '
  ' Read buffer full of data and write to the file stream.
  '
  Do
    PointerParam.Value = PointerOutParam.Value
    '
    ' Calculate the buffer size - may be less than BUFFER_LENGTH for the last block.
    '
    If Offset + BUFFER_LENGTH >= LengthOutParam.Value Then
      SizeParam.Value = LengthOutParam.Value - Offset
    Else
      SizeParam.Value = BUFFER_LENGTH
    End If
    dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult)
    dr.Read()
    dr.GetBytes(PictureCol, 0, Buffer, 0, SizeParam.Value)
    dr.Close()
    fs.Write(Buffer, 0, SizeParam.Value)
    Offset += SizeParam.Value
    OffsetParam.Value = Offset
  Loop Until Offset >= LengthOutParam.Value

  fs.Close()
  cn.Close()
End Sub

Private Sub OleDbChunkBLOB2File(ByVal DestFilePath As String)
  Dim PictureCol As Integer = 0 ' position of picture column in DataReader
  Dim BUFFER_LENGTH As Integer = 32768 ' chunk size
  Dim cn As New OleDbConnection("Provider=SQLOLEDB;Data Source=mySQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;")
  '
  ' Make sure that Photo is non-NULL and return TEXTPTR to it.
  '
  Dim cmdGetPointer As New OleDbCommand("SELECT ?=TEXTPTR(Picture), ?=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn)
  Dim PointerOutParam As OleDbParameter =  cmdGetPointer.Parameters.Add("@Pointer", OleDbType.VarBinary, 100)
  PointerOutParam.Direction = ParameterDirection.Output
  Dim LengthOutParam As OleDbParameter = cmdGetPointer.Parameters.Add("@Length", OleDbType.Integer)
  LengthOutParam.Direction = ParameterDirection.Output
  cn.Open()
  cmdGetPointer.ExecuteNonQuery()
  If PointerOutParam.Value Is DBNull.Value Then
    cn.Close
    ' Add code to deal with NULL BLOB.
    Exit Sub
  End If
  '
  ' Set up READTEXT command, parameters, and open BinaryReader.
  '
  Dim cmdReadBinary As New OleDbCommand("READTEXT Categories.Picture ? ? ? HOLDLOCK", cn)
  Dim PointerParam As OleDbParameter = cmdReadBinary.Parameters.Add("@Pointer", OleDbType.Binary, 16)
  Dim OffsetParam As OleDbParameter = cmdReadBinary.Parameters.Add("@Offset", OleDbType.Integer)
  Dim SizeParam As OleDbParameter = cmdReadBinary.Parameters.Add("@Size", OleDbType.Integer)
  Dim dr As OleDbDataReader
  Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
  Dim Offset As Integer = 0
  OffsetParam.Value = Offset
  Dim Buffer(BUFFER_LENGTH - 1) As Byte
  '
  ' Read buffer full of data and write to the file stream.
  '
  Do
    PointerParam.Value = PointerOutParam.Value
    '
    ' Calculate buffer size - may be less than BUFFER_LENGTH for the last block.
    '
    If Offset + BUFFER_LENGTH >= LengthOutParam.Value Then
      SizeParam.Value = LengthOutParam.Value - Offset
    Else
      SizeParam.Value = BUFFER_LENGTH
    End If
    dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult)
    dr.Read()
    dr.GetBytes(PictureCol, 0, Buffer, 0, SizeParam.Value)
    dr.Close()
    fs.Write(Buffer, 0, SizeParam.Value)
    Offset += SizeParam.Value
    OffsetParam.Value = Offset
  Loop Until Offset >= LengthOutParam.Value

  fs.Close()
  cn.Close()
End Sub

back to the top

Write Chunks to a BLOB Column

The following functions use the Command object, the Parameter object, and the SQL Server UPDATETEXT statement to write chunks of data from a Byte array to a BLOB column. The BLOB column cannot be null with this method, therefore a single byte is assigned to the column before the function retrieves the TEXTPTR.

On the first execution of the UPDATETEXT statement, the DeleteParam.Value is set to 1 to delete the existing byte from the column before the function inserts the chunk. This assignment prevents extraneous data from being appended to the BLOB. The UPDATETEXT statement is executed multiple times, and the offset increments with the size of the buffer after each call.

NOTE: In Visual Basic, you must subtract 1 from the length of the file when declaring 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 without subtracting 1. Also, make sure that you modify your connection string for your environment.

Private Sub ChunkFile2SqlBLOB(ByVal SourceFilepath As String)
  Dim BUFFER_LENGTH As Integer = 32768 ' chunk size
  Dim cn As New SqlConnection("Data Source=mySQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;")
  '
  ' Make sure that Photo is non-NULL and return TEXTPTR to it.
  '
  Dim cmdGetPointer As New SqlCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" & _
    "SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn)
  Dim PointerOutParam As SqlParameter = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
  PointerOutParam.Direction = ParameterDirection.Output
  cn.Open()
  cmdGetPointer.ExecuteNonQuery()
  '
  ' Set up UPDATETEXT command, parameters, and open BinaryReader.
  '
  Dim cmdUploadBinary As New SqlCommand("UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn)
  Dim PointerParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
  Dim OffsetParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Offset", SqlDbType.Int)
  Dim DeleteParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Delete", SqlDbType.Int)
  DeleteParam.Value = 1  ' delete 0x0 character
  Dim BytesParam As SqlParameter = cmdUploadBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH)
  Dim fs As New IO.FileStream(SourceFilePath, IO.FileMode.Open, IO.FileAccess.Read)
  Dim br As New IO.BinaryReader(fs)
  Dim Offset As Integer = 0
  OffsetParam.Value = Offset

  '
  ' Read buffer full of data and execute the UPDATETEXT statement.
  '
  Dim Buffer() As Byte = br.ReadBytes(BUFFER_LENGTH)
  Do While Buffer.Length > 0
    PointerParam.Value = PointerOutParam.Value
    BytesParam.Value = Buffer
    cmdUploadBinary.ExecuteNonQuery()
    DeleteParam.Value = 0 ' don't delete any other data
    Offset += Buffer.Length
    OffsetParam.Value = Offset
    Buffer = br.ReadBytes(BUFFER_LENGTH)
  Loop

  br.Close()
  fs.Close()
  cn.Close()
End Sub

Private Sub ChunkFile2OleDbBLOB(ByVal SourceFilePath As String)
  Dim BUFFER_LENGTH As Integer = 32768 ' chunk size
  Dim cn As New OleDbConnection("Provider=SQLOLEDB;Data Source=mySQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;")
  '
  ' Make sure that Photo is non-NULL and return TEXTPTR to it.
  '
  Dim cmdGetPointer As New OleDbCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" & _
     "SELECT ?=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn)
  Dim PointerOutParam As OleDbParameter = cmdGetPointer.Parameters.Add("@Pointer", OleDbType.VarBinary, 100)
  PointerOutParam.Direction = ParameterDirection.Output
  cn.Open()
  cmdGetPointer.ExecuteNonQuery()
  '
  ' Set up UPDATETEXT command, parameters, and open BinaryReader.
  '
  Dim cmdUploadBinary As New OleDbCommand("UPDATETEXT Categories.Picture ? ? ? WITH LOG ?", cn)
  Dim PointerParam As OleDbParameter = cmdUploadBinary.Parameters.Add("@Pointer", OleDbType.Binary, 16)
  Dim OffsetParam As OleDbParameter = cmdUploadBinary.Parameters.Add("@Offset", OleDbType.Integer)
  Dim DeleteParam As OleDbParameter = cmdUploadBinary.Parameters.Add("@Delete", OleDbType.Integer)
  DeleteParam.Value = 1  ' delete 0x0 character
  Dim BytesParam As OleDbParameter = cmdUploadBinary.Parameters.Add("@Bytes", OleDbType.Binary, BUFFER_LENGTH)
  Dim fs As New IO.FileStream(SourceFilePath, IO.FileMode.Open, IO.FileAccess.Read)
  Dim br As New IO.BinaryReader(fs)
  Dim Offset As Integer = 0
  OffsetParam.Value = Offset

  '
  ' Read buffer full of data and execute the UPDATETEXT statement.
  '
  Dim Buffer() As Byte = br.ReadBytes(BUFFER_LENGTH)
  Do While Buffer.Length > 0
    PointerParam.Value = PointerOutParam.Value
    BytesParam.Value = Buffer
    cmdUploadBinary.ExecuteNonQuery()
    DeleteParam.Value = 0 ' don't delete any other data
    Offset += Buffer.Length
    OffsetParam.Value = Offset
    Buffer = br.ReadBytes(BUFFER_LENGTH)
  Loop

  br.Close()
  fs.Close()
  cn.Close()
End Sub

back to the top

Create the 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 "Test."
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:
```
ChunkFile2SqlBLOB("c:\testfile.dat")
'ChunkFile2OleDbBLOB("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.
Change the source file path to point to the data file that you want to copy into the SQL Server table. Add the following sample code to the Click event handler for the Load button:
```
SqlChunkBLOB2File("c:\copyoftestfile.dat")
'OleDbChunkBLOB2File("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.
Change the source file path to point to the data file that you want to copy into the SQL Server table. Paste the ChunkFile2SqlBLOB and SqlChunkBLOB2File procedures from the article text into your form code.
Change the source file path to point to the data file that you want to copy into the SQL Server table. Run the application. Click Load, and then click Save. You should see a new file on your hard disk that is 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.
READTEXT and UPDATETEXT are specific to Microsoft SQL Server. If you use a different database system, you may have similar commands available to you. If you do not have similar commands available, see the following Knowledge Base articles, which describe reading and writing BLOB data without chunking.
316887 HOW TO: Read and Write a File to and from a BLOB Column by Using ADO.NET and Visual Basic .NET
317017 HOW TO: Read and Write a File to and from a BLOB Column by Using ADO.NET and Visual C++ .NET
317016 HOWTO: Read and Write a File to/from a BLOB Column using ADO.NET and Visual C# .NET

back to the top

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

IN THIS TASK

SUMMARY

Read Chunks from a BLOB Column

Write Chunks to a BLOB Column

Create the Sample Application

Troubleshooting

REFERENCES