Obtaining BLOB Values from a Database

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 (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. Note that this behavior may differ depending on your data source.

SequentialAccess Provides a way for the DataReader to handle rows that contain columns with large binary values BLOBs. Rather than loading the entire row, SequentialAccess enables the DataReader to load data as a stream. 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 or GetChars typed accessors of the DataReader, which fill an array with data. You can also use GetString for character data, however to conserve system resources you may not want to load an entire BLOB value into a single string variable. You can specify a specific buffer size of data to be returned, and a starting location for the first byte or character to be read from the returned data. GetBytes and GetChars will return a long value, which represents the number of bytes or characters returned. If you pass a null array to GetBytes or GetChars, the long value returned will be the total number of bytes or characters in the BLOB. You can optionally specify an index in the array as a starting position for the data being read.

Writing BLOB Values to a Database

You can write a binary large object (BLOB) to a database as either binary or character data, depending on the type of field at your data source. To write a BLOB value to your database, issue the appropriate INSERT or UPDATE statement and pass the BLOB value as an input parameter. If your BLOB is stored as text, such as a SQL Server text field, you can pass the BLOB as a string parameter. If the BLOB is stored in binary format, such as a SQL Server image field, you can pass an array of type byte as a binary parameter.
