Importing BLOB Data into SQL Server
Before you think about retrieving binary data from SQL Server, you need to get that data into the database in the first place. Binary files are typically produced by applications outside of SQL Server and are most commonly stored as standard OS files. Some of the most common binary file types contain image data and carry .bmp, .jpeg, .gif, and .tif extensions. Other binary data takes the form of sound files, which typically have the file extension .wav or .mp3. Another type of binary data that you might want to store in SQL Server is video data, which consists of both image and sound data. Video files typically end in the extension .avi, .mpeg, or .asx. Although these various BLOB files contain different types of data, SQL Server stores them all in the same way. The code in Listing 1 illustrates how you can use the ADO Stream object to import a .jpeg image into a SQL Server table. (Although this discussion centers on multimedia data, you can use SQL Server's image data type to store any type of binary object, including Microsoft Word .doc files, PowerPoint .ppt files, or even .exe executable programs.)
The subroutine in Listing 1 begins by passing in an instance of an active Connection object; it then creates a Recordset object named rs and an instance of the Stream object named stm. The Stream object reads the binary data from the OS file, then writes the data to the SQL Server database. Next, the code drops and recreates a three-column table named BinaryObject in the Pubs database. The first column is an Identity column that gives each row a unique identifier; the second, a varchar column, contains the path and name of the file that you're exporting; and the third is an image column that holds the binary object.
One thing to note about storing binary data is that storing the original filename or the data type extension can be useful when an image column contains more than one type of data. Storing the file type gives you an easy way to determine how to process each type of binary information in your application. For example, by examining the extension, you can decide whether to use an image control to display the data or use Microsoft Windows Media Player to play it.
After the subroutine creates the ADO objects, the next section of code opens the rs Recordset object, which will insert data into the BinaryObject table. The Where 1=2 clause ensures that the code returns no extraneous data to the application. Then, the code uses the Stream object to read the binary file in from disk. First, the code sets the stm Stream object's Type property to adTypeBinary, which specifies that the Stream object will contain binary data. If the Stream object contained text data, you'd use the adTypeText constant. The Stream object's Open method then opens the stream, essentially preparing it to be written to or read from by the object's methods. In this example, the code uses the Stream object's LoadFromFile method to load the Stream object from disk. The LoadFromFile method takes one parameter, which provides the path and name of the OS file that will be loaded into the Stream object. Here, the file named BLOBSample.jpg in the current application path will be loaded into the Stream object.
After loading the Stream object, the code assigns the object's contents to the image column in the rs Recordset object. First, the code executes the Recordset object's AddNew method to create a new record buffer, then assigns values to the blob_filename and blob_object columns. The code sets the blob_filename column to the name of the file, then assigns the contents of the Stream object to the blob_object column. The stm Stream object's Read method reads the data out of the Stream object and into the ADO Field object. Then, the code calls the Recordset object's Update method to write the data to the SQL Server BinaryObject table. After writing the binary data, the code closes the Recordset and Stream objects and releases their resources.
Using a Bound Image Control
After you store the binary data in SQL Server, you can retrieve that data and use it in your applications. Using a bound control to display binary data is the easiest way to incorporate multimedia information into your applications. The code in Listing 2 shows how you can use an ADO Recordset object to retrieve binary data and display that data in a bound image control.
As in the previous example, the subroutine passes an active instance of the Connection object to the subroutine, then creates a new instance of the Recordset object. From the BinaryObject table, the Open method then selects one row, in which the value of the blob_id column equals 1. (This row will be the first row in the BinaryObject table.)
After populating the recordset, the code assigns the DataSource property of the imgBinaryData image control to the rs Recordset object. Then, it sets the imgBinaryData object's DataField property to the recordset's blob_object column, which contains the binary data. Assigning the DataSource and DataField properties automatically loads the image into the image control, where the application can display it. Note that each data-bound interface object can display only the data types that it supports. In the case of the image control, the object supports displaying bitmap, icon, metafile, JPEG, and GIF files. Figure 2, page 36, shows a binary image of my favorite SQL Server author, retrieved as a data-bound image.
Prev. page
1
[2]
3
next page 
