As handheld computers continue to make inroads into the corporate world, development shops must tailor their applications to meet the needs of the new devices. One of the biggest challenges will be building databases for these mobile units. In this article, I will provide the necessary foundation for writing a database application that resides on a Windows CE device.

Take a few steps back

If you’re new to Windows CE, you can catch up by reading these earlier installments:

The Database API
Windows CE has supported a proprietary Database API since Windows CE 1.0. In Windows CE 2.1, Microsoft introduced a set of duplicate functions with the extension Ex that offered some additional features. The primary change involved the database’s not being exclusively in the object store but optionally on the file system or perhaps on a Compact Flash card.

However, you have to realize the limitations of the Database API. It supports only one level of hierarchy, which means that tables can’t appear underneath other tables. You can’t reference one table based on the value of an element from another table, either, and the databases can’t share records. Every record has a unique ID and can be contained in only one database. If you can’t develop your application given these limitations, I suggest that you move toward a more full-fledged database, such as Pocket Access or SQL Server for CE.

Prior to Windows CE 3.0, the Database API was limited to four indices for sorting. This was bumped to eight after the release of Windows CE 3.0. With eight indices available, the Database API becomes a perfect candidate for storing a collection of objects that needs to be compatible across a wide variety of Windows CE platforms and versions. For example, Microsoft uses the Database API to store the contact lists, the e-mail, and the task list in Pocket PC/Pocket PC 2002.

ADOCE
ADOCE, Microsoft’s trimmed-down version of ActiveX Data Objects (ADO), is part of the Microsoft Universal Data Access (UDA) strategy. ADO is an ActiveX-packaged front end for OLEDB that is a generic interface to many databases. The programmer can use ADOCE to store information in the object store, using Pocket Access or with Microsoft’s SQL Server 2000 Windows CE edition.

ADO and ADOCE have the Connection and Recordset object in common. ADOCE removed some of the redundant methods of creating and connecting to a database, and the database providers have shrunk. ADOCE is also missing the Property object, persistence of the Recordset object, and asynchronous queries.

Apart from these issues, the process of opening a database in ADO vs. ADOCE is similar, including such aspects as updating the database and adding and deleting records.

The implementation: Movie Tracker Database
To demonstrate the process of creating a database application, we’ll walk through an app that tracks movie information, such as a synopsis and ratings. Figure A outlines the class inheritance hierarchy and provides an overview of the application architecture. Notice the addition of an independent CDatabase class and CRecord class.

Figure A
Class inheritance hierarchy

The three main property pages are:

  • CPropPage1: Overview—This is used to view the list of movies and their ratings. The user can also delete records from this dialog by tapping and holding on a movie (Figure B).
  • CPropPage2: View Movies—This allows the user to navigate backward and forward through the records in a read-only fashion (Figure C).
  • CPropPage3: Add Movie—This allows the user to add a record to the database (Figure D).

Figure B
Deleting a record from the database

Figure C
Viewing movies

Figure D
Adding a movie to the database

The Database API encompasses the CDatabase class and the movie record structure within the CRecord class. Figure E illustrates the composition of the components.

Figure E
Class association and composition

Opening the database
With the advent of the Ex functions mentioned above, Microsoft introduced the concept of volumes. Before creating a database, you mount a database volume. A volume is merely a specialized version of CreateFile that defines the location of an area where databases can be created. The concept of mounting a volume was fashioned so that databases could be located in any file system that can be interpreted by the OS vs. just the object store. Upon unmounting a volume, all buffers to the file are flushed.

You mount the volume on the file system, the object store, or on a removable file system. To mount a database, use the following code snippet:
BOOL CeMountDBVol(
PCEGUID pceguid,
LPWSTR lpszDBVol,
DWORD dwFlags );

Now that you have an open volume, you create a database:
CEOID CeCreateDatabaseEx(PCEGUID pceguid, CEDBASEINFO * lpCEDBInfo );

CeCreateDatabaseEx takes the guid from the above function, along with the name of the database, and creates a database in that volume. As shown in Figure F, our database volume is called \CEDB.clb. Volumes located on file systems have the default extension of CLB and are simply hidden files.

Figure F
Database volume

The database, named CEDB, has only one volume. After creating the database, you still need to open it. The following code snippet opens the database:
HANDLE CeOpenDatabaseEx(
PCEGUID pceguid,
PCEOID poid,
LPWSTR lpszName,
CEPROPID propid,
DWORD dwFlags,
CENOTIFYREQUEST *pRequest hwndNotify );

Windows CE does support the ability to have multiple threads accessing the same database, although you have to manage all the synchronization. The CENOTIFYREQUEST tells you of database changes in an asynchronous manner via a callback function.

After opening the database, the best thing to do is seek to the beginning of the database to prepare for a sequential read, as outlined in this code:
CEOID CeSeekDatabase(
HANDLE hDatabase,
DWORD dwSeekType,
DWORD dwValue,
LPDWORD lpdwIndex );

You might notice that many of the functions have an association with a CEOID type. A CEOID is an object identifier to the database. You can search for records based on value, as well as the CEOID, a unique ID for each individual record contained in a given database. Think of it as a pointer to the record.

The CeSeekDatabase function allows you to seek from the beginning, the end, and the current location of the database. You can also seek based on a certain value of a certain field of the record. The Database API doesn’t support Structured Querying Language (SQL), which allows you to conduct searches based on specific search strings. Consequently, the Database API had to create a method for the developer to navigate through the records manually via the CeSeekDatabase function.

Grab the code

You can download the code, including the x86 binary and StrongARM binary, to run on your emulator or PDA.

Writing to the database
The CeWriteRecordProps function adds a record to the database, as shown in the following code snippet:
CEOID CeWriteRecordProps(
HANDLE hDbase,
CEOID oidRecord,
WORD cPropID,
CEPROPVAL * rgPropVal );

You need to fill the CEPROPVAL structure with the properties that its table is going to contain. This is akin to assigning data types to columns in a standard Access or SQL database. Table A outlines the legal data types.
Table A


Value
Description
CEVT_BLOB A CEBLOB structure
CEVT_BOOL A Boolean value
CEVT_FILETIME A FILETIME structure
CEVT_12 A 16-bit signed integer
CEVT_14 A 32-bit signed integer
CEVT_LPWSTR A null-terminated string
CEVT_R8 A 64-bit signed integer
CEVT_UI2 A 16-bit unsigned integer
CEVT_UI4 A 32-bit unsigned integer

Our application stores the following properties in the CEDB database as shown in the following list:

  • strTitle—256-character Unicode string
  • nHour—short int
  • nMinute—short int
  • nRating—float
  • strDescription—1,024-character Unicode string

Reading from the database
You must take care to set the “Seek” of the database to the right position before reading. The Overview tab enumerates all the records in the database from start to finish whenever that Overview property page receives focus.

The CeReadRecordPropsEx requires a handle to the database and a buffer to dump all the contents of the record into. CeReadRecordPropsEx can even allocate an appropriate amount of RAM on the heap, which you have to free after the function call. There is no need to respecify the CEPROPVAL.
CEOID CeReadRecordPropsEx (
HANDLE hDbase,
DWORD dwFlags,
LPWORD lpcPropID,
CEPROPID * rgPropID,
LPBYTE * lplpBuffer,
LPDWORD lpcbBuffer,
HANDLE hHeap );

Closing the database
You close the database by unmounting the volume that the database is located on. The function CeUnmountDBVol helps accomplish that, as shown in the following code:
BOOL CeUnmountDBVol (
PCEGUID pceguid );

Conclusion
You now have the foundation and code to write a simple, quick database application using Windows CE. In the next installment, I will cover an ADOCE client application that hits SQL Server via a wireless connection located on a corporate backbone.