Using WinForm Data Binding with BLOBs

If you've used ADO.NET for a while, you might think that using WinForm data binding with BLOBs is impossible. Although .NET lets you perform data binding with WebForms, it doesn't let you use WinForms to perform data binding with most standard controls such as the Picture Box control. However, you can use a workaround that lets you combine the BLOB data in your SQL Server database with image controls on .NET WinForms. The technique isn't as convenient as WebForms data binding, but it lets you take binary data directly from a DataSet and pipe it into a graphical control such as a Picture Box control without using an intermediate disk file. The mechanism that makes this technique work is the ADO.NET MemoryStream object.

Let's look at how to write a BLOB object to a memory stream, then assign that MemoryStream object directly to a Picture Box control's Image property without first writing the binary image to disk. This technique can improve performance by avoiding the extra I/O that's required to first write then read the binary data to disk. Note that this technique isn't the best for larger BLOB objects; some BLOB objects can be as large as 2GB, and attempting to put such large objects into memory will tax your system's resources. But this approach can be quite effective for smaller objects such as photos.

Because the subroutine in Listing A uses a memory stream to store the binary data, you need to import the .NET System.IO namespace into your application to enable access to the file system. To import the System.IO namespace, add the following code to your project:

Imports System.IO

The BLOB2PictureBox subroutine in Listing A begins by
creating a new SqlConnection object named cn and a new DataAdapter object named da. Listing A's code constructs the DataAdapter by using a SELECT statement that retrieves the blob_object column from the BLOBTable table in which the value of the blob_id column is 1. Next, the code creates a SqlCommandBuilder object and an empty DataSet and uses a Try block to open the connection and to use the SqlData- Adapter's Fill method to populate the DataSet. The code evaluates the BLOBTable DataTable's Row Count property to make sure that some data was retrieved. Then, the real action begins at callout A in Listing A.

First, the code at callout A creates a Byte array called bBLOBStorage and assigns the contents of the binary image in the DataSet's blob_object column to the Byte array. The code creates a new MemoryStream object named ms and assigns the contents of the bBLOBStorage Byte array. Then, the Picture Box control's FromStream method assigns the binary image data in the ms MemoryStream object to the Image property of the Picture Box control. Figure A shows the result of the BLOB2PictureBox subroutine.