Introduction
We often need to use static data in our programs. By static, I mean our programs do not modify the data. For example, we can insert the list of the 50 US state names into a combobox so that the user can select his/her state using a mouse. The same goes with the names of months, the names of weekdays, etc. On other occasions, we need to "translate" between two different lists of static data. Say, we have to find the full name of a US state, given a two-character code, we may also have to find the corresponding name of the month, given a numeric value between 1 and 12.
In more complicated situations, our project may consist of multiple exes, DLLs, ActiveX controls, ASP pages, and they all share the same set of static data lists. I once worked on a project that uses 500+ static string lists. How do we efficiently manage these lists? Storing the static data lists in the source code may be inefficient or even impossible. For example, a single static list may have 1000+ items and has to be used in multiple components (VC++, VB, ASP pages, etc.).
An ideal solution would be to store the static data lists in various databases and write a single reusable component that manages all of them. In this article, I will introduce a tiny utility called XYListMan that does exactly that. I am sure people can think of many other ways to do the same thing and XYListMan isnot the most efficient way in all circumstances.
XYListMan
XYListMan is a COM object implemented in XYStaticListManager.dll. It is extremely easy to use. Suppose we have entered all the static data we need into a database and configured a data source called MyDataSource for this database. Here is how XYListMan works. First we need to open the database and load the static lists. Lets assume that there are two tables in the database called StateNames and WeekDays, and each table has two fields called name and code. Here is the sample code in VB script that loads the data into memory:
dim obj;
set obj = CreateObject("XYListMan.1")
obj.OpenDB("MyDataSource","MyName","MyPassword")
dim nStateNameList
nStateNameList = obj.LoadList("StateNameList", _
"select name, code from StateNames order by code")
dim nWeekDayList
nWeekDayList = obj.LoadList("WeekDayList", _
"select name, code from WeekDays order by code")
You can specify user name and password for your database with the OpenDB method. It returns true, if the database is opened successfully. In the LoadList method, you need to give a name for your list and a SQL statement that loads data from the database. The LoadList method returns a zero based integer index that can be used to access the list later. It returns -1 when the operation fails. The SQL statement in general has the following format:
select data_field, key_field from table_or_view_name order by key_field
The GetListIndex method can be used to obtain the index of a previously loaded list, it returns -1 if the list is not yet loaded:
dim nListIndex
nListIndex = obj.GetListIndex("StateNameList")
Once the data lists are loaded into the memory, we can use the GetData, GetKey, GetDataFromKey methods to access the items in the lists. The first parameter of these methods, nListIndex, is the return value of a previous call to the LoadList or the GetListIndex method. The GetData and GetKey methods also take the index of the requested item in the list as its second parameter. The GetDataFromKey method takes a key string as its second parameter. Here is the sample code in VB script (continue from the above code) that accesses data items in the lists:
dim strStateA
strStateA = obj.GetData(nStateNameList,0)
dim strStateB
strStateB = obj.GetData(nStateNameList,22)
dim strStateC
strStateC = obj.GetDataFromKey(nStateNameList,"MD")
dim strCodeA
strCodeA = obj.GetKey(nStateNameList,4)
dim strCodeB
strCodeB = obj.GetKey(nStateNameList,38)
Note that the GetDataFromKey method uses a binary search algorithm to find the data string with the given key string, therefore the list has to be sorted on the key field in order for this method to work correctly.
Some More Details
The data lists loaded into memory are global to the process
That is, if you call LoadList using an XYListMan object somewhere in a process, the loaded data can be accessed later anywhere in the process using another XYListMan object, as long as you specify the correct list name or list index. By the way, the methods of XYListMan are thread-safe.
How do I use this utility on numeric data types?
This is not a problem except that the SQL statement used in the LoadList method has to be sorted on alphabetic order (instead of numeric order). The following SQL statement cannot be used in the LoadList method (the GetDataFromKey method will not work correctly) if the key_field is of numeric type:
select data_field, key_field from table_or_view_name order by key_field
However, if the database is MS Access, then we can easily fix the statement as:
select data_field, format(key_field) from
table_or_view_name order by format(key_field)
What if my static data lists are stored in multiple databases?
No problem. You can call OpenDB to open one of the databases and call LoadList to load some data, then you can call OpenDB again to open another database and call LoadList to load more data .... If you don't want to keep the database connection open, you can call the CloseDB method after loading all the data you need.
We have seen examples of getting state name using the two-character code, how do we do the opposite: getting the code using state name?
A straight forward way to do this is load another list using the following SQL statement and call GetDataFromKey passing the state name as the key string:
select code, name from StateNames order by name
The GetListSize method returns the total number of elements in a list. The UnloadList method can be called to save memory if you no longer need the data. Runtime errors are written to log file XYListMan_yyyymmddHHMMSS_xxxx.log in the current working directory, where yyyymmddHHMMSS is a timestamp string and xxxx is the current process ID.
The implementation
XYListMan is implemented with VC++ 5.0 using ATL and MFC. The main reason for using MFC is that I want to take advantage of the CDatabase and CRecordset classes and I am not that familiar with string operations in ATL. In case you are interested in details, you need only to read one source file XYListMan.cpp, all the other files in the project are generated by the AppWizard. For your convenience, I have included a file XYListManWrapper.h, which contains a C++ wrapper class. If you want to use XYListMan in a C++ program, you need only to do the following: include this header file, declare a XYListManWrapper object, and call its methods.
Conclusion
Thank you for reading this article. Please visit my home page for my other articles and programs.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin