A BLOB of a Different Color

Converting legacy ADO applications to ADO.NET requires a lot of work, and one of the hardest parts of moving to ADO.NET is converting BLOB import or export code. Although many features in ADO.NET are closely related to those in ADO, BLOB access isn't one of them. In ADO, you manipulate BLOB data by using the standard Recordset and Field objects with either the Field object's chunking methods or the Stream object. However, none of those objects exists in ADO.NET. In ADO.NET, you can use the SqlDataReader to retrieve BLOB data from your SQL Server database, then use the ADO.NET DataSet and DataField objects to import BLOB data from the file system into SQL Server. Before looking at the code you use to access BLOBs in ADO.NET, let's review BLOBs.

BLOBs, CLOBs, and LOBs—Oh My!

A BLOB is a binary large object that you can store in your database. BLOBs are sometimes also called character large objects (CLOBs) or just large objects (LOBs). The term LOB can refer to both BLOBs and CLOBs, but strictly speaking, a CLOB can contain only text data such as an XML document, whereas BLOBs can contain any type of binary data. Database developers typically use BLOBs to store graphical images such as product and employee photos—.bmp, .jpg, or .tif files—in the database. Although a graphical file is the most common type of BLOB, the BLOB storage that most modern databases such as SQL Server, Oracle, and IBM DB2 provide can accommodate all types of binary objects, including Microsoft Word documents, Microsoft PowerPoint presentations, standard executable (.exe) files, and text documents such as XML documents.

Although SQL Server can store BLOB data, the potentially huge size of these objects means that you have to access and manage them differently than you do typical text and numeric data types. SQL Server 2000, 7.0, and 6.5 use three data types for BLOB storage: text, ntext, and image. You can use the text and ntext data types to store variable-length text data. The text data type can accommodate as much as 2GB of non-Unicode text data, and the ntext data type can accommodate as much as 1GB of Unicode text data. Image, the most versatile SQL Server BLOB data type, can store as much as 2GB of binary data or standard text data. For more information about how SQL Server stores these data types, see Kalen Delaney's Inside SQL Server column "Text in Row Internals," March 2003, InstantDoc ID 37635.

If I Only Had a BLOB

Using the SqlDataReader to retrieve BLOB data is similar to retrieving character and number data but with some important differences. The main difference is that in ADO.NET, you need to use the CommandBehavior.SequentialAccess flag on the Command object's ExecuteReader method to enable the SqlDataReader to retrieve BLOB data. When your application calls the ExecuteReader method, which instantiates the SqlDataReader, the CommandBehavior.SequentialAccess flag changes the default behavior of the SqlDataReader in a couple of ways.

First, CommandBehavior.SequentialAccess lets the application skip over selected columns that the SqlDataReader returns. Typically, when you use the SqlDataReader, you have to access the columns in the order the SqlDataReader returns them in. But when you use the CommandBehavior.SequentialAccess flag, your application can jump over one or more of the returned columns in the data stream. This feature lets you skip any unneeded column in the result set and go directly to the BLOB column you want. However, after your application has read past any given location in the returned data stream, it can no longer read anything that comes before that location.

The second way that the Command-Behavior.SequentialAccess flag changes the SqlDataReader's default behavior is that it turns off the normal buffering mode that lets the SqlDataReader return one row at a time. Instead, the flag causes results to stream from the database to the application. Turning off data buffering lets the SqlDataReader retrieve large BLOB objects.

Let's look at an example that shows how to use the SqlDataReader to retrieve photo images stored in a SQL Server image column. The BLOBDataReaderExport subroutine in Listing 1 shows how to use the SqlDataReader to retrieve BLOB data and write the data to the file system. The subroutine begins by creating a new SqlConnection object named cn and a new SqlCommand object named cmd. In Listing 1, the SqlCommand object contains a SELECT statement that retrieves from BLOBTable the blob_object column in which the value of the blob_id column is 1. BLOBTable uses the following schema, in which the blob_object column is an image data type:

CREATE TABLE BLOBTable  (blob_id int IDENTITY(1,1),  blob_filename varchar(256),  blob_object image)

Next, the subroutine declares a group of working variables. Because this subroutine exports the contents of a BLOB column to the file system, the subroutine needs a mechanism for writing binary files, and that's precisely what the fs FileStream and bw BinaryWriter objects do. To use these objects, you need to import into your application both the System.Data.SqlClient namespace that supports SQL Server database access and import the .NET System.IO namespace by adding the following import directives to your project:

Imports System.Data.SqlClientImports System.IO

You use the next set of variables in Listing 1's subroutine to transfer data from the SqlDataReader to the FileStream. The bufferSize variable sets the amount of data to read, and outbyte is a byte array that acts as a temporary holding area. As I explain in a moment, you use the retval and startIndex variables with the SqlDataReader to retrieve the BLOB data.

After you declare the working variables, the next section in the subroutine creates the fs FileStream object by passing three parameters to the FileStream's constructor (the mechanism that creates an instance of an object). The first parameter specifies the filename for the output. The second parameter uses the FileMode enumerator FileMode.OpenOrCreate to specify that if the file already exists, the FileStream object will open it; otherwise, the FileStream object will create a new file. The third parameter uses the FileAccess.Write enumerator to open the file for writing, letting the subroutine write binary data to the output file. The subroutine then creates the BinaryWriter object named bw and attaches it to the fs File-Stream object. Next, the code declares a new SqlDataReader named rdr and uses a Try block to open the connection and execute the SqlDataReader. If an error occurs inside the Try block, the code executes the Catch block at the end of the subroutine to display a message box that alerts you about the error.

In this example, the most important part of the code is at callout A in Listing 1, where I use the ExecuteReader's CommandBehavior.SequentialAccess option to enable streaming access to BLOB data. Then, I use a While loop to read the data that the query associated with the SQLCommand object returns. In this case, the data is the contents of the Picture column. Although for simplicity this example retrieves only one image column, SQL Server imposes no restriction on mixing image columns with character and numeric data in the same result set. Inside the While loop, the code reads a chunk of binary data from the Picture column and writes the data a chunk at a time to the bw BinaryWriter object. The startIndex variable specifies the column in the data stream that marks the spot where the subroutine will read the binary data.

The second most important part of the code is at callout B, where the subroutine uses the rdr SqlDataReader's GetBytes method to read the data from the SqlDataReader. The first parameter of the GetBytes method specifies the index value of the column that the result set returns. This parameter's value is set at 0 because this example returns only one column. The second parameter specifies the column in the result set where the GetBytes method will begin retrieving data. The third parameter is a byte array named bBLOBStorage that will store the binary data that the GetByte method returns. The fourth parameter indicates the point in the data stream where the SqlDataReader will begin reading the data. The fifth GetByte method parameter specifies the number of bytes that the GetBytes method will retrieve. If the number of bytes you specify in the fifth parameter exceeds the available buffer size that you specified in the third parameter, the GetBytes method will throw an exception.

Next, at callout C, a While loop writes the binary data from the rdr SqlDataReader to the bBLOBStorage array until the retval variable equals the bufferSize, meaning that the GetBytes method has read all the data from the SqlDataReader. Next, the code calls the BinaryWriter's Write method to write the last chunk of binary data to the file system. The code calls the Flush method to clear all the data from the bw BinaryWriter's internal buffer and write it to disk. This cleanup is important because you need to return to the system the memory that your application uses. Then, the code closes the bw BinaryWriter and the associated fs FileStream objects.

After ADO.NET reads all the BLOB data from the SqlDataReader and writes it to the output file, the outer While loop ends, and the SqlDataReader uses the Close method to end its connection to the SQL Server database. Then, the subroutine uses the Image class's FromFile method to read the tempExportBLOBFile from disk and assigns the file's contents to the image property of a Picture Box control. Figure 1, page 36, shows the result of the BLOBDataReaderExport subroutine.

The code in Listing 1 first exported the BLOB to a file before displaying the BLOB in a Picture Box control. However, you might want to put the contents of the graphic image into the picture box without using a temporary file because creating a temporary file requires extra time and incurs additional system I/O. Fortunately, you can make your application faster and more efficient by using the .NET WinForms workaround I describe in the sidebar "Using WinForm Data Binding with BLOBs," page 37.

I'll Get You, My BLOB

Using the SqlDataReader to read BLOB data lets you access the BLOB data that's already in your SQL Server database. However, to read BLOB data, you first have to get the data from the file system into the database. Let's look at how you use ADO.NET to import BLOB data into SQL Server. The BLOBDataSetImport subroutine in Listing 2 shows how to write BLOB data to a SQL Server database by using the ADO.NET DataSet object.

Listing 2's subroutine imports the contents of a file into the blob_object column of the sample BLOBTable table. As you did in the previous example, you first need to import the .NET System.IO namespace into your application to enable access to the file system. The BLOBDataSetImport subroutine starts by creating an instance of a SqlConnection object named cn whose ConnectionString property is the constructor's argument. The next statement creates a SqlDataAdapter object named da. The job of the SqlDataAdapter is essentially to connect a DataSet to a SQL Server data source. The SqlDataAdapter's constructor takes two arguments. The first argument is a SELECT statement that defines the data you want to use in the DataSet, and the second argument is the cn SqlConnection object.

The code's next statement creates a SqlCommandBuilder object named cb and takes as an argument the SqlDataAdapter object da. I use the SqlCommandBuilder object in Listing 2 to automatically create the appropriate INSERT, UPDATE, and DELETE commands for the specified SELECT statement in the SqlDataAdapter. SQL Server stores these commands in the SqlDataAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties. The code then creates an empty DataSet named ds, which the code will later populate with the results of the SELECT query.

Next, the subroutine creates a FileStream object called fs that you set with the filename, open method, and access method for opening and that works with the file you specified in the Text properties of a text box named txtImportBLOBFile. The FileMode.OpenOrCreate flag specifies that if the file exists, the FileStream object will open it. Otherwise, the FileStream object will create the file. The FileAccess.Read flag indicates that the subroutine will open the file for reading. At callout A in Listing 2, you can see the first important section of code for dealing with BLOB objects when you're using the DataSet. First, the code sets a Byte array (which will temporarily store the returned data) to the length of the fs FileStream object. Then, a Try block starts, and the FileStream object's Read method reads the contents of the file into the bBLOBStorage Byte array. After reading all the data into the Byte array, the subroutine closes the FileStream object. Then, the code opens the ADO.NET SqlConnection object and fills the DataSet by using the SqlDataAdapter's Fill method. Next, the code instantiates a new DataRow object named rw, calls the DataTable's NewRow method to create an empty record in the BLOBTable DataTable, and returns a new DataRow object.

The next important section of code is at callout B, where I assign to the blob_filename column the text name of the file that was imported; I also assign the blob_object column the byte array that contains the binary information that the subroutine reads in from the file system. At this point, the rw DataRow object contains the text and binary data that you want to insert into the SQL Server database, and you need to add the DataRow to the table's Rows collection by using the DataRow object's Add method. Finally, the subroutine calls the SqlDataAdapter's Update method, which evaluates the changes to the DataTable in the DataSet and determines which commands to execute. In this case, the DataTable's Table.Rows.RowState property contains the value of Added, which indicates that a new row has been inserted. The DataAdapter uses the RowState property to determine what action to take when the Update method executes. Because the value of Added in the RowState property shows that a row was added to the Data-Table, the DataAdapter needs to execute its internal InsertCommand to add the new record to the BLOBTable table. Listing 2's Finally block contains all the cleanup code, which sets the subroutine's objects to Nothing and closes the SqlConnection object.

Although the BLOB import technique in Listing 2 uses only client-side code, you can also combine a server-side stored procedure with the ADO.NET SqlCommand object to import BLOB data to a SQL Server database. The sidebar "Using the SqlCommand Object to Import BLOBs" describes this alternative technique.

We're Not in Kansas Anymore

As you can see, working with BLOBs in ADO.NET is different than it was in ADO—and somewhat more difficult. However, ADO.NET is still a new technology, and I'm sure that as the .NET Framework matures, Microsoft will make working with BLOBs easier. Meanwhile, you can use these techniques to incorporate BLOBs into your ADO.NET applications.