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.SqlClient
Imports 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.
Prev. page
[1]
2
next page 
