Microsoft KB Archive/317044

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

Article ID: 317044

Article Last Modified on 2/7/2005

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual C++ .NET 2002 Standard Edition
 * Microsoft Visual C++ .NET 2003 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 Q317044





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

For a Microsoft Visual Basic .NET version of this article, see 317034.

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

IN THIS TASK

 * SUMMARY
 * Requirements
 * Read Chunks from a BLOB Column
 * Write Chunks to a BLOB Column
 * Create a Project and Add Code
 * 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.

Because of network constraints, sometimes you may need to retrieve a large BLOB file in smaller chunks and then piece the chunks together rather than retrieve the whole BLOB all at once. However, 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. This article describes alternate ways to retrieve data in smaller chunks.

NOTES:  Examples are shown for both the SqlClient 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 &quot;Test&quot;.

After you use the following samples, you may want to remove this record from the database. To remove the record, type the following command in SQL Query Analyzer and then press F5. use Northwind delete from Categories where CategoryName = 'Test' 

back to the top

Requirements
The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs you will need:
 * Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
 * Microsoft Visual Studio .NET
 * Microsoft SQL Server 7.0 or later

This article assumes that you are familiar with the following topics:
 * Visual Studio .NET
 * ADO.NET fundamentals and syntax

back to the top

Read Chunks from a BLOB Column
The following functions use the SQL Server READTEXT statement and DataReader 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. void SqlChunkBlob2File(String *DestFilePath) {  try {   int PictureCol  = 0;  // position of Picture column in DataReader int BUFFER_LENGTH = 32768; // chunk size SqlConnection *cn = new SqlConnection(&quot;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;&quot;); // make sure Photo is non-NULL and return TEXTPTR to it        SqlCommand *cmdGetPointer = new SqlCommand(&quot;SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture) FROM Categories WHERE CategoryName='Test'&quot;, cn); SqlParameter *PointerOutParam = cmdGetPointer->Parameters->Add(&quot;@Pointer&quot;, SqlDbType::VarBinary, 100); PointerOutParam->Direction = ParameterDirection::Output; SqlParameter *LengthOutParam = cmdGetPointer->Parameters->Add(&quot;@Length&quot;, SqlDbType::Int); LengthOutParam->Direction = ParameterDirection::Output; cn->Open; cmdGetPointer->ExecuteNonQuery; if(PointerOutParam->Value == 0) {       cn->Close; // add code to deal with NULL BLOB return; }   // Set up READTEXT command, parameters, and open BinaryReader SqlCommand *cmdReadBinary = new SqlCommand(&quot;READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK&quot;, cn); SqlParameter *PointerParam = cmdReadBinary->Parameters->Add(&quot;@Pointer&quot;, SqlDbType::Binary, 16); SqlParameter *OffsetParam = cmdReadBinary->Parameters->Add(&quot;@Offset&quot;, SqlDbType::Int); SqlParameter *SizeParam = cmdReadBinary->Parameters->Add(&quot;@Size&quot;, SqlDbType::Int); SqlDataReader *dr; FileStream *fs = new FileStream(DestFilePath, FileMode::Create, FileAccess::Write); int Offset= 0; OffsetParam->Value = __box(Offset); Byte Buffer[] = __gc new Byte[BUFFER_LENGTH ]; // 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 last block if( (Offset + BUFFER_LENGTH) >= Convert::ToInt32(LengthOutParam->Value)) SizeParam->Value = __box(Convert::ToInt32(LengthOutParam->Value) - Offset); else SizeParam->Value = __box(BUFFER_LENGTH); dr = cmdReadBinary->ExecuteReader(CommandBehavior::SingleResult); dr->Read; dr->GetBytes(PictureCol, 0, Buffer, 0,Convert::ToInt32(SizeParam->Value)); dr->Close; fs->Write(Buffer, 0, Convert::ToInt32(SizeParam->Value)); Offset += Convert::ToInt32(SizeParam->Value); OffsetParam->Value = __box(Offset); }while(Offset Value));

fs->Close; cn->Close; Console::WriteLine(&quot;SqlChunkBlob2File executed successfully.\nPress return to continue.&quot;); Console::ReadLine; }catch(SqlException *ex) {   Console::WriteLine(ex->Message); } }

void OleDbChunkBlob2File(String *DestFilePath) {  try {   int PictureCol  = 0;  // position of Picture column in DataReader int BUFFER_LENGTH = 32768; // chunk size OleDbConnection *cn = new OleDbConnection(&quot;Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;&quot;); // make sure Photo is non-NULL and return TEXTPTR to it        OleDbCommand *cmdGetPointer = new OleDbCommand(&quot;SELECT ?=TEXTPTR(Picture), ?=DataLength(Picture) FROM Categories WHERE CategoryName='Test'&quot;, cn); OleDbParameter *PointerOutParam = cmdGetPointer->Parameters->Add(&quot;@Pointer&quot;, OleDbType::VarBinary, 100); PointerOutParam->Direction = ParameterDirection::Output; OleDbParameter *LengthOutParam = cmdGetPointer->Parameters->Add(&quot;@Length&quot;, OleDbType::Integer); LengthOutParam->Direction = ParameterDirection::Output; cn->Open; cmdGetPointer->ExecuteNonQuery; if(PointerOutParam->Value == 0) {       cn->Close; // add code to deal with NULL BLOB return; }   // Set up READTEXT command, parameters, and open BinaryReader OleDbCommand *cmdReadBinary = new OleDbCommand(&quot;READTEXT Categories.Picture ? ? ? HOLDLOCK&quot;, cn); OleDbParameter *PointerParam = cmdReadBinary->Parameters->Add(&quot;@Pointer&quot;, OleDbType::Binary, 16); OleDbParameter *OffsetParam = cmdReadBinary->Parameters->Add(&quot;@Offset&quot;, OleDbType::Integer); OleDbParameter *SizeParam = cmdReadBinary->Parameters->Add(&quot;@Size&quot;, OleDbType::Integer); OleDbDataReader *dr; FileStream *fs = new FileStream(DestFilePath, FileMode::Create, FileAccess::Write); int Offset= 0; OffsetParam->Value = __box(Offset); Byte Buffer[] = __gc new Byte[BUFFER_LENGTH ]; // 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 last block if( (Offset + BUFFER_LENGTH) >= Convert::ToInt32(LengthOutParam->Value)) SizeParam->Value = __box(Convert::ToInt32(LengthOutParam->Value) - Offset); else SizeParam->Value = __box(BUFFER_LENGTH); dr = cmdReadBinary->ExecuteReader(CommandBehavior::SingleResult); dr->Read; dr->GetBytes(PictureCol, 0, Buffer, 0,Convert::ToInt32(SizeParam->Value)); dr->Close; fs->Write(Buffer, 0, Convert::ToInt32(SizeParam->Value)); Offset += Convert::ToInt32(SizeParam->Value); OffsetParam->Value = __box(Offset); }while(Offset Value));

fs->Close; cn->Close; Console::WriteLine(&quot;OleDbChunkBlob2File executed successfully.\nPress return to continue.&quot;); Console::ReadLine; }catch(OleDbException *ex) {   Console::WriteLine(ex->Message); } } 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. void ChunkFile2SqlBlob(String *SourceFilePath) {  try {   int BUFFER_LENGTH = 32768; // chunk size SqlConnection *cn = new SqlConnection(&quot;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;&quot;); // make sure Photo is non-NULL and return TEXTPTR to it   String *sqlstr = &quot;SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'&quot;; SqlCommand *cmdGetPointer = new SqlCommand(sqlstr, cn); SqlParameter *PointerOutParam = cmdGetPointer->Parameters->Add(&quot;@Pointer&quot;, SqlDbType::VarBinary, 100); PointerOutParam->Direction = ParameterDirection::Output; cn->Open; cmdGetPointer->ExecuteNonQuery;

// Set up UPDATETEXT command, parameters, and open BinaryReader SqlCommand *cmdUploadBinary = new SqlCommand(&quot;UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes&quot;, cn); SqlParameter *PointerParam = cmdUploadBinary->Parameters->Add(&quot;@Pointer&quot;, SqlDbType::Binary, 16); SqlParameter *OffsetParam= cmdUploadBinary->Parameters->Add(&quot;@Offset&quot;, SqlDbType::Int); SqlParameter *DeleteParam = cmdUploadBinary->Parameters->Add(&quot;@Delete&quot;, SqlDbType::Int); DeleteParam->Value = __box(1); // delete 0x0 character SqlParameter *BytesParam = cmdUploadBinary->Parameters->Add(&quot;@Bytes&quot;, SqlDbType::Binary, BUFFER_LENGTH); FileStream *fs = new FileStream(SourceFilePath, FileMode::Open, FileAccess::Read); BinaryReader *br = new BinaryReader(fs); int Offset = 0; OffsetParam->Value = __box(Offset); // Read buffer full of data and execute UPDATETEXT statement Byte Buffer[] = br->ReadBytes(BUFFER_LENGTH); 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 = __box(Offset); Buffer = br->ReadBytes(BUFFER_LENGTH); }

br->Close; fs->Close; cn->Close;

Console::WriteLine(&quot;ChunkFile2SqlBlob completed successfully.\nPress return to continue.&quot;); Console::ReadLine; }catch(SqlException *ex) {   Console::WriteLine(ex->Message); } }

void ChunkFile2OleDbBlob(String *SourceFilePath) {  try {   int BUFFER_LENGTH = 32768; // chunk size OleDbConnection *cn = new OleDbConnection(&quot;Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;&quot;); // make sure Photo is non-NULL and return TEXTPTR to it   String *sqlstr = &quot;SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';SELECT ?=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'&quot;; OleDbCommand *cmdGetPointer = new OleDbCommand(sqlstr, cn); OleDbParameter *PointerOutParam = cmdGetPointer->Parameters->Add(&quot;@Pointer&quot;, OleDbType::VarBinary, 100); PointerOutParam->Direction = ParameterDirection::Output; cn->Open; cmdGetPointer->ExecuteNonQuery;

// Set up UPDATETEXT command, parameters, and open BinaryReader OleDbCommand *cmdUploadBinary = new OleDbCommand(&quot;UPDATETEXT Categories.Picture ? ? ? WITH LOG ?&quot;, cn); OleDbParameter *PointerParam = cmdUploadBinary->Parameters->Add(&quot;@Pointer&quot;, OleDbType::Binary, 16); OleDbParameter *OffsetParam= cmdUploadBinary->Parameters->Add(&quot;@Offset&quot;, OleDbType::Integer); OleDbParameter *DeleteParam = cmdUploadBinary->Parameters->Add(&quot;@Delete&quot;, OleDbType::Integer); DeleteParam->Value = __box(1); // delete 0x0 character OleDbParameter *BytesParam = cmdUploadBinary->Parameters->Add(&quot;@Bytes&quot;, OleDbType::Binary, BUFFER_LENGTH); FileStream *fs = new FileStream(SourceFilePath, FileMode::Open, FileAccess::Read); BinaryReader *br = new BinaryReader(fs); int Offset = 0; OffsetParam->Value = __box(Offset); // Read buffer full of data and execute UPDATETEXT statement Byte Buffer[] = br->ReadBytes(BUFFER_LENGTH); 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 = __box(Offset); Buffer = br->ReadBytes(BUFFER_LENGTH); }

br->Close; fs->Close; cn->Close;

Console::WriteLine(&quot;ChunkFile2OleDbBlob completed successfully.\nPress return to continue.&quot;); Console::ReadLine; }catch(OleDbException *ex) {       Console::WriteLine(ex->Message); } } back to the top

Create a Project and Add Code
 Open SQL Query Analyzer.  Type the following command, and then press F5 to change the default database to Northwind. use Northwind </li>  Type the following command, and then press F5 to insert a new record into the Categories table of the Northwind database. Insert into categories(categoryname) values ('Test') NOTE: You add this test record to the Categories table only for the purpose of this example. With the test record in place, you do not need to modify any of the existing data in this table. </li> Start Visual Studio .NET.</li> In Visual .NET 2002, create a new Managed C++ Application.

In Visual .NET 2003, create a new Console Application (.NET).

Name the project &quot;BLOBChunkReadWrite.&quot;</li>  You add this test record to the Paste the following sample code in the BLOBChunkReadWrite.cpp file. Overwrite the existing code generated by Visual Studio .NET: #include &quot;stdafx.h&quot; #using <mscorlib.dll> #using <System.dll> #using <System.Data.dll>

using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Data::OleDb; using namespace System::IO;

//Prototypes for functions that will do the real work. void ChunkFile2SqlBlob(String *); void ChunkFile2OleDbBlob(String *); void SqlChunkBlob2File(String *); void OleDbChunkBlob2File(String *);

// This is the entry point for this application. #ifdef _UNICODE int wmain(void) #else int main(void) #endif {        String *DestFilePath = &quot;c:\\mytest.bmp&quot;; //File we will create from database String *SourceFilePath = &quot;c:\\windows\\coffee bean.bmp&quot;; //File we will insert into database

//Use the SQL Provider to insert BLOB into the database from the file. ChunkFile2SqlBlob(SourceFilePath);

//Use the OLE DB Provider to insert BLOB into database from the file. ChunkFile2OleDbBlob(SourceFilePath);

//Use the SQL Provider to read BLOB from the database into the file. SqlChunkBlob2File(DestFilePath);

//Use the OLE DB Provider to read BLOB from the database into the file. OleDbChunkBlob2File(DestFilePath);

return 0; }                   </li> You add this test record to the Paste the following four functions at the bottom of the BLOBReadWright.cpp file.NOTE: You may need to change the connection strings in the code as necessary for your environment. There are four connection strings.</li> You add this test record to the Press CTRL+F5 to execute the code. Notice that messages are written to the Console window that confirm the success of each function.</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 the SQL statements to suit your own server.</li> Add error checking in case your query returns no records.</li> 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, refer to the following Microsoft 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

</li></ul>

back to the top

<div class="references_section">