Using an Unbound Hierarchical Grid Control
Bound controls make displaying binary image data from a SQL Server table easy, but not every control supports data binding. Sometimes even if the control supports data binding, you can't use data binding with that control because you might want to display the data differently than data binding allows. For example, when binding an image to a grid control, you can't control the size that the grid automatically assigns to the cell. The DisplayBLOBGrid subroutine, which Listing 3, page 36, shows, demonstrates how to retrieve binary image information from the Pubs database's pub_info table and display it in an unbound Hierarchical FlexGrid control.
In this listing, note that using ADO to display binary data is much like working with standard character data, but the two have some differences. This sample subroutine begins by creating the required ADO objects—in this case, a Recordset object, a Field object, and a Stream object. In this example, the Recordset object contains the pub_id and logo columns from the pub_info table. So far, the code seems fairly standard. However, the logo column is an image data type containing a graphical image for each publisher's logo. The ADO Field object represents the current column data, and the Stream object reads the binary data from the image column. Next, the code creates the sFileName String variable that will hold the temporary filename and sets the variable's value to tempBLOB.bmp, the name of the temporary file that will contain the BLOB images before the LoadPicture function loads them to the Hierarchical FlexGrid.
After opening the Recordset object, the code initializes the grid that it will use to display the data. First, it turns off the Redraw property to improve performance and clears the grid of any existing data. Then, the code initially sizes the grid for two columns and uses the ColWidth property of the two grid columns to set the initial column size. Because the first column is a standard character data type, the Field object's ActualSize property specifies the column width. However, because the second column contains graphical data, the ActualSize property won't reflect the length required to display the data. To ensure that the column is large enough to display the binary image, the code sizes the second column for 200 characters. Next, the grid's RowHeightMin property increases the height for all the grid's rows; in this case, the original row height is tripled. After setting up the grid, the code assigns the column headings for the grid, using a For Each loop to iterate through the Fields collection. The column headings for binary data types are the same as the column headings for standard character columns.
Next, a Do...Until loop reads all the Recordset object's rows. For every row, a For...Each loop retrieves the column data and puts it into the grid. Because binary data requires different handling than text data, the code checks each Field object's Type property before moving the data to the grid. If the Field object contains binary data, the Field object's Type property will equal the constant adLongVarBinary, and the code will use the Stream object, stm, to read data from the current Field object. Inside the With block, the code first sets the Stream object's Type property to adTypeBinary, specifying that the Stream object will contain binary data; then the Open method executes, followed by the Write method. In this case, the Write method writes the fld Field object's value into the Stream object. Next, the Stream object's SaveToFile method writes the Stream object's contents into the temporary OS file that the code identified in the sFileName variable. Using the value of adSaveCreateOverWrite in the second parameter of the SaveToFile method ensures that running this code will overwrite any existing file with the same name. After the code writes the binary data to the temporary file, the LoadPicture function reads the binary data from the temporary file and loads it into the grid's CellPicture property. Figure 3 shows the results of the DisplayBLOBGrid subroutine.
ADO and SQL Server can handle BLOB objects just as well as they handle traditional text and numeric data types. Using ADO 2.6's new Stream object makes working with binary data easier than ever. Although dealing with binary objects is more difficult than handling standard data types, the ability to add graphics and sounds to your database applications makes the effort worthwhile.
End of Article
Prev. page
1
2
[3]
next page -->
