Microsoft KB Archive/317034

Knowledge Base

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

Article ID: 317034

Article Last Modified on 12/26/2003

---

APPLIES TO

• Microsoft ADO.NET 1.1
• Microsoft ADO.NET 1.0
• Microsoft Visual Basic .NET 2003 Standard Edition
• Microsoft Visual Basic .NET 2002 Standard Edition
• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 2000 64-bit Edition
• Microsoft SQL Server 7.0 Standard Edition

---

This article was previously published under Q317034

For a Microsoft Visual C++ .NET version of this article, see 317044.
For a Microsoft Visual C# .NET version of this article, see 317043.

For a Microsoft Visual Basic 6.0 version of this article, see 194975.

This article refers to the following Microsoft .NET Framework Class Library namespaces:

• System.Data
• System.Data.SqlClient
• System.Data.OleDb

IN THIS TASK

• SUMMARY
  • Read Chunks from a BLOB Column
  • Write Chunks to a BLOB Column
  • Create the Sample Application
  • Troubleshooting
• REFERENCES

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.

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:

   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.
   
   

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

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

REFERENCES

For more information about working with BLOBs in ADO.NET, visit the following Microsoft Web site:

Obtaining BLOB Values from a Database
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp

back to the top

Keywords: kbhowtomaster kbio kbsqlclient kbsystemdata kboledb KB317034

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.