VARBINARY(MAX) Tames the BLOB

Using ADO.NET 2.0 to Import BLOBs
You can also use ADO.NET to import BLOB data into VARBINARY(MAX) columns. As I noted earlier, for the IMAGE data type, ADO.NET is the only effective way to load BLOB data. However, earlier versions of ADO.NET require a different programming model to work with BLOBs than to work with other character and numeric data types, which makes BLOB processing more difficult. In ADO.NET versions before 2.0, you essentially have to use either chucking techniques or Stream objects to access BLOB data. (See "BLOB Further Reading" for articles that discuss these techniques.) However, Microsoft has enhanced ADO.NET 2.0 to support the new VARBINARY(MAX) data type, greatly simplifying BLOB handling.

The code in Listing 2 uses ADO.NET 2.0 to load data into the blob_data column. First, the code reads the BLOB data from the file system into a variable. As callout A in Listing 2 shows, the code creates a new FileStream object named fs to read the file C:\tem\win2003.bmp". The fs FileStream object then reads the BLOB file’s contents into a byte array named bBLOBStorage.

After the code reads the BLOB data from the file system and assigns it to a variable, it writes the variable’s contents to the SQL Server database. Because the code requires I/O to the file system and access to the database, you need to add the following namespaces to your project:

• Imports System.IO
• Imports System.Data
• Imports System.Data.Sqlclient

At callout B in Listing 2, the code creates a new ADO.NET SqlConnection object named cn and a new ADO.NET SqlCommand object named cmd. As their names suggest, the cn SqlConnection object creates a connection to the SQL Server database, whereas the cmd SqlCommand object executes a T-SQL INSERT command that adds the BLOB data to the database. In this example, the INSERT statement uses two parameters: @blob_description to add a description of the BLOB to the database and @blob_data to add the BLOB itself to the database.

The code sets the Direction property of both parameters to input and sets the data type of the @blob_description parameter to SqlDbType.VarChar and the data type of @blob_data to SqlDbType.Image. Don’t be misled by the use of SqlDbType.Image for @blob_data; this data type isn’t restricted to just the IMAGE data type; it also works with VARBINARY(MAX). Next, the code assigns values to the parameters’ Value properties, assigning a string to @blob_description’s property and assigning the contents of the bBLOBStorage byte array, which was filled earlier, to @blob_data’s property. The code opens the cn SqlConnection object. Then, it uses the cmd SqlCommand object’s ExecuteNonQuery method to execute the T-SQL INSERT statement, which imports the BLOB data to MyBLOBTable’s blob_data column.

Retrieving Data from a VARBINARY(MAX) Column
After you’ve loaded BLOB data into the database, you need to be able to retrieve and display the data. The code in Listing 3 uses ADO.NET 2.0 to retrieve the BLOB data from MyBLOBTable’s VARBINARY(MAX) blob_data column and display it in a picture box. In this example, the code reads the BLOB into a MemoryStream, then assigns the MemoryStream to a picture box control’s Image property. This technique is fast and doesn’t require any intermediate files, eliminating the I/O required to first write the binary data to disk, then read it.

Listing 3’s RetrieveBLOB subroutine begins at callout A by creating a new SqlConnection object named cn and a new SqlDataAdapter object named da. The code constructs the SqlDataAdapter by using a T-SQL SELECT statement that retrieves the blob_data column from MyBLOBTable, where the value of the blob_id column equals 1. The code then creates a SqlCommandBuilder object and an empty DataSet. A Try block opens the connection and uses the SqlDataAdapter’s Fill method to populate the DataSet. The subroutine then evaluates the MyBLOBTable DataTable’s Rows.Count property to make sure some data was retrieved.

The real action begins at callout B with the creation of a byte array called bBLOBStorage, which is assigned the contents of the binary image in the DataSet’s blob_data column. The code then creates a new MemoryStream object named ms and assigns it the contents of the bBLOBStorage byte array. Finally, the code uses the picture box control’s FromStream method to assign the binary image data from the ms Memory-Stream object to the picture box control’s Image property. Figure 2 shows the results of the RetrieveBLOB subroutine.

BLOB with Ease
Although SQL Server 2005 still supports the TEXT, NTEXT,and IMAGE data types for backward compatibility, the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types give you important capabilities for working with BLOBs. As you’ve seen in these examples using VARBINARY(MAX), you can now use these data types as local variables and in T-SQL parameters, so you can easily work with BLOB data T-SQL and ADO.NET 2.0.