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.