How to read and write a file to and from a BLOB column by using chunking in ADO.NET and Visual C# .NET (317043)

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, you may have to retrieve a large BLOB file in smaller chunks and then piece the chunks together instead of retrieving the whole BLOB file at one time. 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 different ways to retrieve data in smaller chunks.

Notes:

This article contains examples 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 set to 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 that are required:

Microsoft Windows 2000 Professional, Windows 2000 Server, 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 ADO.NET fundamentals and syntax.

back to the top

Create a project and add code

Open SQL Query Analyzer.
Type the following command and press F5 to change the default database to Northwind:
```
use Northwind
```
Type the following command and press F5 to insert a new record in the Categories table of the Northwind database:
```
Insert into categories(categoryname) values ('Test')
```
Note You only have to add this record to the Categories table if you want to use this example without modifying any of the existing data in this table.
In Visual Studio .NET, create a new Visual C# .NET Windows Application project.
Athe top of your Form1.cs file, add the following two lines of code to add references to your project for System.Data.SQLClient and System.Data.OleDb:
```
using System.Data.SqlClient;
using System.Data.OleDb;
```
Add four buttons to Form1. Change the Text property of the buttons to SQLBlob2File, OlDbBlob2File, File2OleDbBlob, and File2SqlBlob, respectively.
Add the following string variable declarations under the Form1 public class:
```
string destfilepath;
string sourcefilepath;
					
```

Paste the following code under the form Load event:

destfilepath = @"c:\mytest.bmp";
sourcefilepath = @"c:\windows\coffee bean.bmp";

Call the procedures in the Click event for each button:

// Click event for the button labeled SqlBlob2File.
SqlChunkBlob2File(destfilepath);

// Click event for the button labeled OLDbBlob2File.
OlDbChunkBlob2File(destfilepath);

// Click event for the button labeled File2OleDbBlob.
ChunkFile2OleDbBlob(sourcefilepath);

//Click event for the button labeled File2SqlBlob.
ChunkFile2SqlBlob(sourcefilepath);

Paste the following four functions in Form1:

public 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 == null) 
	{
		cn.Close();
		// Add code to handle 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; 
	System.IO.FileStream fs = new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
	int Offset= 0;
	OffsetParam.Value = Offset;
	Byte []Buffer = 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) >= System.Convert.ToInt32(LengthOutParam.Value)) 
			SizeParam.Value = System.Convert.ToInt32(LengthOutParam.Value) - Offset;
		else SizeParam.Value = BUFFER_LENGTH;
				
		dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
		dr.Read();
		dr.GetBytes(PictureCol, 0, Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
		dr.Close();
		fs.Write(Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
		Offset += System.Convert.ToInt32(SizeParam.Value);
		OffsetParam.Value = Offset;
	}while(Offset < System.Convert.ToInt32(LengthOutParam.Value));

	fs.Close();
	cn.Close();
   }
   catch(SqlException ex)
   {
   MessageBox.Show (ex.Message);
   }
}

public 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 == DBNull.Value )
	{
		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;
	System.IO.FileStream fs = new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
	int Offset= 0;
	OffsetParam.Value = Offset;
	Byte[] Buffer = 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) >= System.Convert.ToInt32(LengthOutParam.Value))
			SizeParam.Value = System.Convert.ToInt32(LengthOutParam.Value) - Offset;
		else SizeParam.Value = BUFFER_LENGTH;
				
		dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
		dr.Read();
		dr.GetBytes(PictureCol, 0, Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
		dr.Close();
		fs.Write(Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
		Offset += System.Convert.ToInt32(SizeParam.Value);
		OffsetParam.Value = Offset;
	}while( Offset < System.Convert.ToInt32(LengthOutParam.Value));

	fs.Close();
	cn.Close();
   }
   catch(OleDbException ex)
   {
   MessageBox.Show (ex.Message);
   }
}

Press F5 to run the code, and then click File2OleDbBlob to make sure that you load an image in the SQL Server database before you try to write out to a .bmp file on the disk.

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 are used: the first retrieves the size of the BLOB field and a pointer to its location; the second executes the READTEXT command. The READTEXT command retrieves the chunk of data in a Byte array and increments an Offset. The Byte array is written to the disk through the System.IO.Filesream object.back to the top

Write chunks to a BLOB column

The following functions use the Command and Parameter objects 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, so a single byte is assigned to the column before TEXTPTR is retrieved. On the first execution of the UPDATETEXT statement, DeleteParam.Value is set to 1. This deletes the existing byte from the column before inserting the chunk and prevents the BLOB from having extraneous data appended to it. The UPDATETEXT statement is executed multiple times, incrementing the Offset with the size of the buffer after each call.

private 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.
		
	SqlCommand cmdGetPointer = new SqlCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" +
					"SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", 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 = 1;  // delete 0x0 character
				SqlParameter BytesParam  = cmdUploadBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH);
	System.IO.FileStream fs = new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
	System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
	int Offset = 0;
	OffsetParam.Value = 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; //Do not delete any other data.
		Offset += Buffer.Length;
		OffsetParam.Value = Offset;
		Buffer = br.ReadBytes(BUFFER_LENGTH);
	}

	br.Close();
	fs.Close();
	cn.Close();
   }
   catch(SqlException ex)
   {
   MessageBox.Show (ex.Message);
   }			
}

public 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.
		
	OleDbCommand cmdGetPointer = new OleDbCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" +
				"SELECT ?=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", 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 = 1;  // delete 0x0 character
	OleDbParameter BytesParam = cmdUploadBinary.Parameters.Add("@Bytes", OleDbType.Binary, BUFFER_LENGTH);
	System.IO.FileStream fs = new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
	System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
	int Offset= 0;
	OffsetParam.Value = 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;// Do not delete any other data.
		Offset += Buffer.Length;
		OffsetParam.Value = Offset;
		Buffer = br.ReadBytes(BUFFER_LENGTH);
	}

	br.Close();
	fs.Close();
	cn.Close();		
   }
   catch(OleDbException ex)
   {
   MessageBox.Show (ex.Message);
   }
}

Notes

The code that is described in this article may not be suitable for use against LongVarChar or LongVarWChar columns without modification.
You must modify the connection string and SQL statements to correspond to your own server. You must also add error checking if your query returns no records.
READTEXT and UPDATETEXT are specific to Microsoft SQL Server. Different database systems may have similar commands that you can use.

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 C# .NET (317043)

IN THIS TASK

SUMMARY

Requirements

Create a project and add code

Read chunks from a BLOB column

Write chunks to a BLOB column

REFERENCES