February 2001
Getting Started
Store Images in Your Database
Create an easy way to save and retrieve images in your VB application and Microsoft Access database.
by Andy Rosebrock and Stan Schultes
Reprinted with permission from Visual Basic Programmer's Journal, February 2001, Volume 11, Issue 2, Copyright 2001, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange.
Whether you plan to include graphics within your program, create data entry forms, or design a database for your Web site, providing an easy method to modify and add images in a database allows your applications to be more flexible and portable. However, you need to decide carefully which method is best for your particular application. In this column, we'll focus on different ways to manipulate images as objects or file pointers within your application's database.
What you need:
Visual Basic 5.0 with Service Pack 3 or Visual Basic 6.0 with Service Pack 4
Access 97 or 2000
Microsoft Data Access Components (MDAC) 2.1
Customers often ask for a way to display their company logo and images in numerous locations within an application. Likewise, Web projects frequently require that each record have an image associated with the data. Images provide an easy way to visually associate an item to record data. The problem is that these images change constantly, and customers add new ones continuously. Your application should allow customers to have the flexibility they require.
Visual Basic offers several ways to store images, such as using the ImageList control, but these methods require that you recompile the application, and they're generally restricted to a limited number of images. The best way to store images is in a database. You can do this in a couple ways: by storing the image as a Binary Large Object (BLOB) in a database field, or by simply storing a "pointer" to the file location on disk. Each method has advantages and disadvantages. Storing an image as a BLOB can inflate your database size, although all your images will be in a single, central location. Storing the image as a file pointer allows your database to be considerably smaller, but missing or invalid files can cause problems later.
Figure 1 | Improve Your Image. Deciding how you want to save images to your database is an important consideration. Click here.
Depending on your preference, you need to balance database size and efficiency to decide which method is best for a particular application. We'll outline both methods using Microsoft ActiveX Data Objects (ADO) to save and retrieve images to and from an Access 97 database. The code is identical if you want to use an Access 2000 database, but you need to download and install the latest Microsoft Data Access Components (MDAC) package.
Create the Database
You'll create a simple application to store a rudimentary image library. Before getting started with the VB project, you need to create the database you'll use to store the data. Start Access and create a new table called ImageLibrary. Add the necessary fields and properties (see Table 1).
After creating the database, start VB, choose Project | References, and select Microsoft ActiveX Data Objects 2.1 Library (or you can use the ADO 2.5 library if it's installed). Name the default form of the project frmMain. Design your form to match your needs for storing images. The ImageLibrary project is set up to allow you to select how you'll store images to the database (see Figure 1, and download the sample project).
You need to add the variables that reference the database connection and recordset. You'll use these variables to navigate through the database, and also to add and edit records. Add these variables to frmMain's Declarations section:
Private Conn As ADODB.Connection
Private rs As ADODB.Recordset
Next, you need to establish the connection to the database and recordset. Make sure the ImageLib.mdb file is in your application path, and add this code to the frmMain_Load event:
'Establish the connection.
Set Conn = New ADODB.Connection
Conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB" & _
".3.51;Data Source=" & App.Path & _
"\ImageLib.mdb"
Conn.Open
'Open the recordset
Set rs = New ADODB.Recordset
rs.Open "ImageLibrary", Conn, _
adOpenKeyset, adLockPessimistic, adCmdTable
Now that you've opened the recordset, you can start viewing and modifying the record data. We'll start by looking at how to save and retrieve the actual image to the database using the GetChunk and Append-Chunk methods. You can use two methods to display a BLOB field stored in the database. In the first method, simply set the Image control's DataSource and Data-Field properties to the already opened recordset and BLOB field:
Set imgDBImage.DataSource = rs
imgDBImage.DataField = "ImageBLOB"
Following this method is generally the simplest way to start displaying the images stored in the database. However, larger files might start taxing your system memory as the Image control attempts to load from the database. If memory usage is a concern for your application because of large image files, you should use the second method—the ADO GetChunk method—to retrieve binary objects from the database instead. The GetChunk method allows you specify the number of bytes (chunks) to retrieve from the BLOB field. You can load the stored object in these smaller chunks (for larger images) or as a single chunk:
strData = rs("ImageBLOB"). _
GetChunk(rs("ImageBLOB").ActualSize)
The drawback to the GetChunk method: Because the Image control's picture property can't read the byte array you load from the database, you first need to save a temporary file from the field data, then read that file with the LoadPicture method. In spite of this shortcoming, the GetChunk method is actually faster than setting the Image control's data properties.
Determine the Best Method
We did a small test to determine which method was the fastest by creating a table with 100 records. Each record contained binary data that was approximately 50K in size. We looped through all the records in the table while displaying the stored data in an Image control. The DataField method completed in 58 seconds, while the GetChunk method completed in 27 seconds (with a chunk size of 100 bytes). Because these particular images weren't overly large, we retrieved the entire object in a single chunk. This method resulted in the quickest time of 18 seconds. You need to determine the best method to use in your particular applications. For the purpose of this article, use the GetChunk method with a chunk size of 100 bytes (see Listing 1).
As with retrieving stored images, saving an image to the database requires you to first convert the image into a byte array. Afterward, you can write the bytes to the database field using the AppendChunk method. In the code, you can do this by writing the data to the field in a single swipe by setting the chunk size equal to the image file's total size (see Listing 2).
In the sample project, only the file path is stored to the database if you choose to save the image as a file pointer, reducing the database size and increasing the application's speed. The FillFields procedure in Listing 1 first determines whether the database field ImagePath contains a value. If you saved a path with the record, then the image is stored as a file pointer. If you did not save a path, then the image is stored as a BLOB (see the GetChunk method described earlier). You can display the stored pointer easily by simply using the Image control's LoadPicture method.
The obvious advantage to storing images as a file pointer is that only the file path is saved. As a result, your database won't grow as dramatically as it would if you stored the image in a BLOB field. In the example described earlier, with 100 records of 50K images stored in BLOB fields, the database grew to more than 4 MB. The same database using file pointers instead was under 100K. In speed comparisons, the file pointer method is the winner, completing the test in five seconds. These advantages generally make file pointers the preferred method of saving images.
The main disadvantage to using file pointers is that if file locations change, your application can't load the image. Another disadvantage: lack of application portability. If you move the application elsewhere, you'd have to move the database and all the image files it points to. One way to alleviate these problems is to save the file's Universal Naming Convention (UNC) instead of the actual disk location (such as \\andyr\images\test.jpg instead of c:\images\test.jpg). Of course, if you were to send the application to outside customers, they probably wouldn't have access to the same resources as you (unless you saved the file pointer as an image located on the Internet, such as www.yoursite.com/images/test.jpg). This is probably the only situation in which saving the image to the database as a binary object could be recommended. The tradeoffs in speed and database size aren't worth it unless you anticipate that changing file locations will hinder your application.
Finally, you might be wondering if you can use these methods to store images in a SQL Server database. Although the Ap-pendChunk and GetChunk methods also work for SQL Server databases, Microsoft discourages this approach because BLOB columns in SQL tables cause performance degradation at your server. Microsoft's recommended method for using SQL tables is to store a pointer to the file location.
Getting all your application's images stored in a database might take some time at first, but the rewards will be apparent quickly. You can now design a quick data entry form to update your Web site's images. You no longer need to recompile when you want to change your application's graphics. Try using an image database in your next project, and see the difference it can make in flexibility and portability for your users.
Andy Rosebrock is the owner of Axe Inc. (www.axeinc.com), a Sarasota, Fla.-based contract programming and Web development company that works with VB and other development platforms. Reach Andy at andyr@axeinc.com.
Stan Schultes is a project manager and VB and Web enterprise application developer in Sarasota, Fla. Stan is an MCP in VB and spoke on VB development at Microsoft's DevDays conference. He is a contributing editor for VBPJ and writes regularly for the magazine. Reach Stan at Stan@VBExpert.com.
