This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

April 2000

Interactive Developer

Finally! An Application-Scope Dictionary Object

Use the new LookupTable object to cache data at application scope without compromising scalability.

by A. Russell Jones

Reprinted with permission from Visual Basic Programmer's Journal, April 2000, Volume 10, Issue 4 Copyright 2000, 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.

Fortunately, an independent team at Microsoft recently released a Dictionary-like object called the LookupTable object that is both-threaded, so you can use it at application scope to store and retrieve values. In this column, I'll explain the object's methods and properties, and show you how to use LookupTables to cache data in your ASP applications (download the code from the VBPJ Web site; see the Go Online box for details).

Unfortunately, the LookupTable object acts like a Dictionary only after you load it with data, which you must do from a text file. In addition, it works only with String and Integer values, not with Variants like a Dictionary object. Despite its limitations, the LookupTable object is extremely useful, although it hasn't been well publicized.

Use an <object> tag in your application's global.asa file to create an application-scope LookupTable object. Place the <object> tag at the beginning of the file, outside any event routines. This code creates a LookupTable object called "empEmail" accessible from any page in the ASP application. Substitute the name you want to use instead of empEmail in the <object> tag:

<object id="empEmail" 
   progid="IISSample.LookupTable" 
runat="Server" 
scope="Application">
</object>

Create a Text File

The simplest way to use the LookupTable object is to create a text file to hold the data you want to load. The text file is a simple list of comma-delimited values. For example, suppose you have a list of employees whose e-mail addresses you want to make available on a Web site. You can create a text file that looks like this:

Juan Alvarez,jalvarez@mycompany.com
Karla Boniface,cboniface@mycompany.com
John Byerly,jbyerly@mycompany.com

The LookupTable object treats the first comma in any line of the file as the separator between the key and the value. It treats subsequent commas as part of the value, so you can have commas embedded in the value string. Use the LoadValues method to load the contents of a file into the LookupTable object. The first parameter is the name of the file you want to load. For example, if your list of names and e-mail addresses is in the c:\EmpDirectory\empEmail.txt file, you'd write:

Call empEmail.LoadValues( _
   "c:\EmpDirectory\empEmail.txt", 0)

The second parameter to the LoadValues method (the 0 in the preceding script) tells the LookupTable object how to interpret the values as strings or as integers. There are four constants, one for each of the possible combinations (see Table 1).

By default, the LookupTable object requires unique keys. You can add the value 10 to any format constant to ignore duplicate keys. When you instruct LookupTable to ignore duplicates, it loads only the first value with a specific key, and ignores repeated keys and values further down in the file.

After loading the LookupTable object, you can use it in your ASP application. For example, to display the e-mail address for the employee "John Byerly", you can write:

Response.Write _
   empEmail.LookupValue("John Byerly")

The LookupTable object returns a Variant with a value of Empty if the LookupValue method fails, and LookupTable doesn't raise an error if the requested key is missing. (See Table 2 for the LookupTable object's properties and methods.)

The LookupTable object is relatively primitive by VB and ASP component standards (see the method list in Table 2). Nevertheless, with a little help the object can store Dictionary-like key/value pairs as well as entire recordsets. I've created a VB DLL called CLookupTable that contains several functions to improve the LookupTable object's functionality. The DLL lets you add individual items to a LookupTable object, load the LookupTable with data from an array or recordset, retrieve all the keys or values as a Variant array with a single call, and obtain an HTML-formatted selection list containing the items from the LookupTable.

The key to extending the LookupTable object: The text file you use to load the object doesn't have to be a static text file. Instead, you create a temporary file, write the data you want to load, then call the LoadValue method to load the data. Then you can easily load the LookupTable object with the result of a database query.

Store Multiple Values

Similarly, you aren't limited to storing single values as the value portion of the key/value items in the LookupTable object. You can just as easily store multiple values as a single string, and retrieve the individual values at run time. Granted, none of this is quite as fast as using LookupTable with static files or single-item values, but the loss in speed is more than offset by the increase in functionality.

Suppose you load the following values into an existing LookupTable called "empInfo" (see Table 3 for the LookupTable's methods):

Juan Alvarez,jalvarez@mycompany.com
Carla Boniface,cboniface@mycompany.com 

You can create a CLookupTable object and call its AddItem method to add additional items to the file (see Listing 1):

Dim CLT 
Set CLT = Server.CreateObject _
   ("LookupTable.CLookupTable")
Call CLT.AddItem(empInfo, _
   "John Byerly", _
   "jbyerly@worldnet.att")

Note that adding items doesn't change the original empInfo file, because the AddItem method uses a temporary file. You can use the CLookupTable object to load a LookupTable object with data from a database query rather than from a static file. To do that, you write the information in the recordset to a temporary file, load that file into an application-scope LookupTable object, then retrieve individual values from the object as required for your application. For maximum flexibility, you can specify an optional value field and a separator character. By default, the method loads all the fields, separating the values with a vertical bar (|) character. I've included an Access database with this column's code suitable for testing. You can use this code to load all the fields from the Employees table:

' assume conn is an open database ADO 
' connection and empInfo is an 
' application-scope LookupTable object.
dim R
dim CLT
' open a recordset of all employee data
set R = conn.Execute( _
   "SELECT * FROM Employees " _
   & "ORDER BY LastName, FirstName"
set CLT = Server.CreateObject( _
   "LookupTable.CLookupTable")
CLT.LoadRecordset(empInfo, R, "EmpID")

After the LoadRecordset method completes, you can retrieve all the information for a specific user by ID:

s = empInfo.LookupValue("5102")
Response.Write s
' writes 
' "Hicks,John|jhicks@mycompany.com"

Improve LookupTable's Functionality

After you load a LookupTable object with data, you can improve its functionality in other ways. For example, the preceding example would be more useful if you could retrieve just the eMail field. You can use the CLookupTable object:

set CLT = Server.CreateObject( _
   "LookupTable.CLookupTable")
s = CLT.getSubValue(empInfo, "5102", 2)
Response.Write s
' writes " jhicks@mycompany.com"

Key/value pairs are commonly used in a Web application to present the user with a list of choices. In HTML, you create a <select> tag, then wrap each item in <option> tags. The CLookupTable.AsSelectList function returns a formatted selection list from a loaded LookupTable object. You provide values for the <select> tag's name and size attributes (the size attribute controls the height of the list, in lines):

set CLT = Server.CreateObject( _
   "LookupTable.CLookupTable")
s = CLT.getSubValue(empInfo, "5102", 2)
Response.Write CLT.AsSelectList _
   (empInfo, "employees", 15)

The AsSelectList method returns this string:

<select name="employees" 
   size="15">
<option value='5529'>Abbott,William,
   wabbott@mycompany.com</option>
<option value='5375'>Acosta,Charles,
cacosta@mycompany.com</option>
</select>

With a little creativity, you can often extend and simplify existing objects with limited functionality, even if you don't have access to the source code.

A. Russell Jones, Ph.D. is a senior systems analyst for VF Services Corp. located in Greensboro, NC. He's a former reptile keeper and professional musician who now composes computer applications, and is the author of The VB Developer's Guide to ASP and IIS (Sams). Reach him by e-mail at arj1@northstate.net.