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


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET (included with the .NET Framework 1.1)
  • Microsoft Visual C++ .NET (2002)
  • Microsoft Visual C++ .NET (2003)
  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 2000 64 bit (all editions)
  • Microsoft SQL Server 7.0
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

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 "Test".
    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("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
		
	// make sure Photo is non-NULL and return TEXTPTR to it		
	SqlCommand *cmdGetPointer = new SqlCommand("SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn);
	SqlParameter *PointerOutParam = cmdGetPointer->Parameters->Add("@Pointer", SqlDbType::VarBinary, 100);
	PointerOutParam->Direction = ParameterDirection::Output;
	SqlParameter *LengthOutParam  = cmdGetPointer->Parameters->Add("@Length", 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("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", cn);
	SqlParameter *PointerParam  = cmdReadBinary->Parameters->Add("@Pointer", SqlDbType::Binary, 16);
	SqlParameter *OffsetParam  = cmdReadBinary->Parameters->Add("@Offset", SqlDbType::Int);
	SqlParameter *SizeParam  = cmdReadBinary->Parameters->Add("@Size", 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 <Convert::ToInt32(LengthOutParam->Value));

	fs->Close();
	cn->Close();
	Console::WriteLine("SqlChunkBlob2File executed successfully.\nPress return to continue.");
	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("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
		
	// make sure Photo is non-NULL and return TEXTPTR to it		
	OleDbCommand *cmdGetPointer = new OleDbCommand("SELECT ?=TEXTPTR(Picture), ?=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn);
	OleDbParameter *PointerOutParam = cmdGetPointer->Parameters->Add("@Pointer", OleDbType::VarBinary, 100);
	PointerOutParam->Direction = ParameterDirection::Output;
	OleDbParameter *LengthOutParam  = cmdGetPointer->Parameters->Add("@Length", 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("READTEXT Categories.Picture ? ? ? HOLDLOCK", cn);
	OleDbParameter *PointerParam  = cmdReadBinary->Parameters->Add("@Pointer", OleDbType::Binary, 16);
	OleDbParameter *OffsetParam  = cmdReadBinary->Parameters->Add("@Offset", OleDbType::Integer);
	OleDbParameter *SizeParam  = cmdReadBinary->Parameters->Add("@Size", 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 <Convert::ToInt32(LengthOutParam->Value));

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

	// Set up UPDATETEXT command, parameters, and open BinaryReader		
	SqlCommand *cmdUploadBinary = new SqlCommand("UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn);
	SqlParameter *PointerParam  = cmdUploadBinary->Parameters->Add("@Pointer", SqlDbType::Binary, 16);
	SqlParameter *OffsetParam= cmdUploadBinary->Parameters->Add("@Offset", SqlDbType::Int);
	SqlParameter *DeleteParam = cmdUploadBinary->Parameters->Add("@Delete", SqlDbType::Int);			
			
	DeleteParam->Value = __box(1);  // delete 0x0 character
	SqlParameter *BytesParam  = cmdUploadBinary->Parameters->Add("@Bytes", 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("ChunkFile2SqlBlob completed successfully.\nPress return to continue.");
	Console::ReadLine();
   }catch(SqlException *ex)
   {
	Console::WriteLine(ex->Message);
   }
		
}

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

	// Set up UPDATETEXT command, parameters, and open BinaryReader		
	OleDbCommand *cmdUploadBinary = new OleDbCommand("UPDATETEXT Categories.Picture ? ? ? WITH LOG ?", cn);
	OleDbParameter *PointerParam  = cmdUploadBinary->Parameters->Add("@Pointer", OleDbType::Binary, 16);
	OleDbParameter *OffsetParam= cmdUploadBinary->Parameters->Add("@Offset", OleDbType::Integer);
	OleDbParameter *DeleteParam = cmdUploadBinary->Parameters->Add("@Delete", OleDbType::Integer);		
			
	DeleteParam->Value = __box(1);  // delete 0x0 character
	OleDbParameter *BytesParam  = cmdUploadBinary->Parameters->Add("@Bytes", 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("ChunkFile2OleDbBlob completed successfully.\nPress return to continue.");
	Console::ReadLine();
   }catch(OleDbException *ex)
   {
		Console::WriteLine(ex->Message);
   }
		
}
back to the top

Create a Project and Add Code

  1. Open SQL Query Analyzer.
  2. Type the following command, and then press F5 to change the default database to Northwind.
    use Northwind
  3. 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.
  4. Start Visual Studio .NET.
  5. In Visual .NET 2002, create a new Managed C++ Application.

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

    Name the project "BLOBChunkReadWrite."
  6. 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 "stdafx.h"
   #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 = "c:\\mytest.bmp"; //File we will create from database
	String *SourceFilePath = "c:\\windows\\coffee bean.bmp"; //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;   
   }
  7. 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.
  8. 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.
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 the SQL statements to suit your own server.
  • Add error checking in case your query returns no records.
  • 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

back to the top

REFERENCES

For more information about working with BLOBs in ADO.NET, visit the following Microsoft Web site or click the article link to view the article in the Knowledge Base:

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

317016 HOWTO: Read and Write a File to/from a BLOB Column using ADO.NET and Visual C# .NET

back to the top
Modification Type:MajorLast Reviewed:2/7/2005
Keywords:kbHOWTOmaster KB317044 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.