Potty Little Details

Just another WordPress.com weblog

Obtaining BLOB Values from a Database [C#]

leave a comment »

The default behavior of the DataReader is to load incoming data as a row as soon as an entire row of data is available. Binary large objects, or BLOBs, need to be treated differently, however, because they can contain gigabytes of data that cannot be contained in a single row. The Command.ExecuteReader method has an overload which will take a CommandBehavior argument to modify the default behavior of the DataReader. You can pass CommandBehavior.SequentialAccess to the ExecuteReader method to modify the default behavior of the DataReader so that instead of loading rows of data, it will load data sequentially as it is received. This is ideal for loading BLOBs or other large data structures.

When setting the DataReader to use SequentialAccess, it is important to note the sequence in which you access the fields returned. The default behavior of the DataReader, which loads an entire row as soon as it is available, allows you to access the fields returned in any order until the next row is read. When using SequentialAccess however, you must access the different fields returned by the DataReader in order. For example, if your query returns three columns, the third of which is a BLOB, you must return the values of the first and second fields before accessing the BLOB data in the third field. If you access the third field before the first or second fields, the first and second field values will no longer be available. This is because SequentialAccess has modified the DataReader to return data in sequence and the data will not be available after the DataReader has read past it.

When accessing the data in the BLOB field, use the GetBytes typed accessor of the DataReader, which fills a byte array with the binary data. You can specify a specific buffer size of data to be returned, and a starting location for the first byte read from the returned data. GetBytes will return a long value, which represents the number of bytes returned. If you pass a null byte array to GetBytes, the long value returned will be the total number of bytes in the BLOB. You can optionally specify an index in the byte array as a start position for the data being read.

The following example returns the publisher id and logo from the pubs sample database in Microsoft SQL Server. The publisher id (pub_id) is a character field, and the logo is an image, which is a BLOB. Notice that the publisher id is accessed for the current row of data before the logo, because the fields must be accessed sequentially.

SqlConnection pubsConn = new SqlConnection(“Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;”);

SqlCommand logoCMD = new SqlCommand(“SELECT pub_id, logo FROM pub_info”, pubsConn);

FileStream fs; // Writes the BLOB to a file (*.bmp).

BinaryWriter bw; // Streams the BLOB to the FileStream object.

int bufferSize = 100; // Size of the BLOB buffer.

byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes.

long retval; // The bytes returned from GetBytes.

long startIndex = 0; // The starting position in the BLOB output.

string pub_id = “”; // The publisher id to use in the file name.

// Open the connection and read data into the DataReader.


SqlDataReader myReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())


// Get the publisher id, which must occur before getting the logo.

pub_id = myReader.GetString(0);

// Create a file to hold the output.

fs = new FileStream(“logo” + pub_id + “.bmp”, FileMode.OpenOrCreate, FileAccess.Write);

bw = new BinaryWriter(fs);

// Reset the starting byte for the new BLOB.

startIndex = 0;

// Read the bytes into outbyte[] and retain the number of bytes returned.

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

// Continue reading and writing while there are bytes beyond the size of the buffer.

while (retval == bufferSize)




// Reposition the start index to the end of the last buffer and fill the buffer.

startIndex+= bufferSize;

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);


// Write the remaining buffer.



// Close the output file.




// Close the reader and the connection.




Written by oneil

September 11, 2008 at 3:01 am

Posted in ADO dot net

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: