Introduction
This article shows how Windows CE property databases can be used from the .NET Compact Framework through a mix of managed and unmanaged code. A sample application is presented that both implements the discussed managed and unmanaged classes and implements a very simple contacts database editor.
Windows CE Property Databases
Windows CE property databases, also known as CEDB, are a very simple means to persist application data. Each database comprises only a single table that has no preset structure. Records may have a variable number of fields and only four sort orders are allowed per database. These databases may be created directly on the object store, or mounted on a file.
Although they seem to be quite limited in their definition as well as in their use (one may experience problems on tables with over 1000 records), these databases are quite ubiquitous on the Pocket PC: they support all the PIM applications, support the popular “Pocket Access” format and are directly accessible from the desktop via RAPI.
The unmanaged application programming interface for CEDB is quite simple and it is a bit surprising not to find a managed version of it for the Compact Framework. After a first look at it, one wonders why there is no implementation of CEDB wrapper. There are some high-level wrappers for these databases but they rely on ADOCE – a COM component that Microsoft is discontinuing. So, here is an interesting challenge: wrap the low-level CEDB API on a managed library.
Modeling Property Databases
Property databases comprise a few very simple concepts that need to be understood before a managed wrapper is built.
- Volume
Databases are grouped in volumes. A volume may either be stored in a file, in which case it is a mounted volume, or may be the object store itself. When databases are stored on the object store, they are not directly visible as files. Mounted volumes are regular files and are managed as such. A volume is identified by a value stored in the CEGUID structure.
- Database
A database is actually a single table that contains data records. The major difference between a property database and a SQL table is the absence of schema information.
- Record
Record stores related data organized as properties. Each record may have a variable number of properties and none of them is required to be present. This makes for a very loose structure.
- Property
A property is the basic data storage unit. It is has a unique identifier, a data type and the data itself. The unique identifier and the data type are combined into a 32 bit property id or PROPID.
- Sort order
A sort order determines how the records in a database may be sorted, behaving as a non-unique index. There is a maximum limit of 4 sort orders per database.
Now, we can start designing classes around these concepts. On the sample application the following classes were implemented:
CeDbApi
Contains all the imported API functions used by the other classes.
CeDbException
Type of exceptions thrown by the wrapper.
CeDbInfo
Wraps a CEDBASEINFO structure, needed to create new databases and to query the existing ones.
CeDbProperty
Models a property value.
CeDbPropertyCollection
A collection of properties, searchable by property id.
CeDbPropertyID
Static class to manage property ids.
CeDbRecord
Models a database record.
CeDbRecordSet
Implements data access and navigation in the database.
CeDbTable
Identifies a database in a volume.
CeDbVolume
Models a database volume.
CeOidInfo
Retrieves information about an existing database (may be generalized to other object store items).
Database Volumes
Databases may be created on either the object store (no visible file) or mounted on files, named volumes. To identify the location where the database exists, the API uses a CEGUID structure. Its state may either be invalid, identify the object store or a mounted volume. This structure is easily mapped to C# code:
public struct CEGUID
{
public int Data1;
public int Data2;
public int Data3;
public int Data4;
public static CEGUID InvalidGuid()
{
CEGUID ceguid;
ceguid.Data1 = -1;
ceguid.Data2 = -1;
ceguid.Data3 = -1;
ceguid.Data4 = -1;
return ceguid;
}
public static CEGUID SystemGuid()
{
CEGUID ceguid;
ceguid.Data1 = 0;
ceguid.Data2 = 0;
ceguid.Data3 = 0;
ceguid.Data4 = 0;
return ceguid;
}
}
The SystemGuid static method creates an instance of the structure with a value that identifies a database on the object store. To specify a database on a file, you must first mount it as a volume. Volumes are mounted through the CeMountDBVol API that returns a CEGUID value by reference:
public static extern
bool CeMountDBVol(ref CEGUID ceguid, string strDbVol, FileFlags flags);
The FileFlags enumeration contains the standard file open and creation flags:
public enum FileFlags
{
CreateNew = 1,
CreateAlways = 2,
OpenExisting = 3,
OpenAlways = 4,
TruncateExisting = 5
}
Use the OpenExisting flag to open an existing database volume and use the CreateAlways flag to create a new one (this will delete an existing volume file with the same name).
Volumes are managed by the CeDbVolume class on the sample project. This class implements the IDisposable interface because it is handling an unmanaged resource. Volumes can be mounted using the Mount method and un-mounted through the Unmount method. The object store volume is selected by calling the UseSystem method.
Creating, Opening and Closing Databases
Opening a database is very easy, once you have the database name and the volume where it resides – you use CeOpenDatabaseEx:
public static extern
IntPtr CeOpenDatabaseEx(ref CEGUID ceguid, ref int oid, string strName,
uint propid, uint flags, IntPtr pRequest);
The first parameter is the CEGUID structure that identifies the volume. The second is a reference to the database identifier that is returned by reference. The third parameter is the database name, such as “Contacts Database”. The fourth parameter is the property identifier of the sort order (more on this later). The fifth parameter is a flag that indicates how records are read:
public enum CeDbOpenFlags : uint
{
None = 0,
AutoIncrement = 1
}
The AutoIncrement flag means that whenever a record is read, the record pointer is immediately incremented. The final parameter is a pointer to a CENOTIFYREQUEST structure that contains notification request information. In this sample we will not use this feature so the value of the parameter will be IntPtr.Zero.
The function returns a handle to the opened database in an IntPtr value. This is the value we use to close the database through the CloseHandle function. If the function fails, this value is IntPtr.Zero:
public static extern bool CloseHandle(IntPtr hHandle);
Creating databases is somewhat more complex because we have to provide some creation information through a CEDBASEINFO structure. This structure, along with a database volume CEGUID value is fed to the CeCreateDatabaseEx function:
public static extern int CeCreateDatabaseEx(ref CEGUID ceguid, byte[] info);
As you can see on the import declaration, there is no reference to the CEDBASEINFO structure, but to a byte array instead. As a matter of fact, this is not an easy structure to marshal on the Compact Framework because it has one embedded string and a SORTORDERSPEC array:
typedef struct _CEDBASEINFO {
DWORD dwFlags;
WCHAR szDbaseName[CEDB_MAXDBASENAMELEN];
DWORD dwDbaseType;
WORD wNumRecords;
WORD wNumSortOrder;
DWORD dwSize;
FILETIME ftLastModified;
SORTORDERSPEC rgSortSpecs[CEDB_MAXSORTORDER];
} CEDBASEINFO;
Using a technique already described by Alex Yakhnin, we convert the structure into a flat byte array and feed it to the function that will happily consume it as being generated from a native code consumer.
But before we can put all this to work, we need to create a wrapper class that will hide all implementation details of the CEDBASEINFO marshalling, while retaining a proper interface for a managed consumer. This class is implemented on the sample application as CeDbInfo.
This class is implemented as a 120 element byte array, the exact size of the CEDBASEINFO structure. All methods and properties manipulate managed types and convert to and from a serialized byte array format. For instance, let’s see the property that handles the database name - the szDbaseName character array of CEDBASEINFO. This array is located at offset 4 from the start of the byte array and is 64 bytes long (32 characters including the null terminator):
public string Name
{
get
{
string strName = BitConverter.ToString(m_data, 4, 64);
char[] cTrim = {'\0', ' '};
return strName.Trim(cTrim);
}
set
{
string strName;
byte[] name;
if(value.Length > 31)
strName = value.Substring(0, 31) + '\0';
else
strName = value + '\0';
name = UnicodeEncoding.Unicode.GetBytes(strName);
Buffer.BlockCopy(name, 0, m_data, 4, name.Length);
}
}
This is obviously not the only approach to this problem. We might have stored the name property as a managed string in the class and only render it as a byte array when conversion was needed.
On the sample application, a property database is represented by the CeDbTable class. It contains a reference to a volume and the database name and its major purpose is to create a class that helps in updating the table: the CeDbRecordSet class.
Updating the Database
Now that we managed to get a handle to a database (a table, really) we need to access the information stored there. Databases are structured in rows of records, each containing a variable number of fields. Each field has a unique identifier and may carry a limited number of data types:
public enum CeDbType : ushort
{
Int16 = 2,
UInt16 = 18,
Int32 = 3,
UInt32 = 19,
FileTime = 64,
String = 31,
Blob = 65,
Bool = 11,
Double = 5
}
The numeric value of the data type is combined with a unique id (a 16 bit integer) to produce the 32 bit property identifier. Instead of using C macros to manage these, a static class is used for this purpose:
namespace Primeworks.CeDb
{
public class CeDbPropertyID
{
public static uint Create(CeDbType type, ushort id)
{
return (uint)type + ((uint)id << 16);
}
public static uint Create(byte[] data, int iOffset)
{
return BitConverter.ToUInt32(data, iOffset);
}
public static CeDbType GetCeDbType(uint propid)
{
return (CeDbType)(propid & 0x0000ffff);
}
public static ushort GetId(uint propid)
{
return (ushort)((propid & 0xffff000) >> 16);
}
}
}
Now, let us look inside a property and see how we can model it using C#. Natively, properties are stored as 16 byte structures:
typedef struct _CEPROPVAL {
CEPROPID propid; WORD wLenData; WORD wFlags; CEVALUNION val; } CEPROPVAL;
The propid value stores the property identifier and the val member stores the value. Property values are stored as a C union:
typedef union _CEVALUNION {
short iVal; USHORT uiVal; long lVal; ULONG ulVal; FILETIME filetime; LPWSTR lpwstr; CEBLOB blob; BOOL boolVal double dblVal } CEVALUNION;
Most of these types are quickly converted to managed types with two exceptions: the Unicode string pointer and the BLOB. Both of them contain pointers to memory blocks and these must be correctly handled when reading and writing.
When a database record is read, a single block of memory is returned from the local heap. This block contains all the information of the retrieved record and any string or BLOB pointers also point to it. Reading this type of data would be relatively easy on the desktop .NET Framework because of its advanced marshalling code. The Compact Frameworks has far more limited marshalling resources, so we use a little help from unmanaged C++ code by converting pointers to array offsets. This has to be done both when reading and writing a record. Let’s start with the code to read a record:
CEDBNET_API CEOID CeDbNetReadRecord(HANDLE hDbase,
WORD* pProps,
BYTE** ppBuffer,
DWORD* pSize)
{
BYTE* pBuffer = NULL;
CEOID ceoid;
ceoid = CeReadRecordPropsEx(hDbase, CEDB_ALLOWREALLOC,
pProps, NULL, &pBuffer, pSize,
NULL);
if(ceoid)
{
DWORD dwOffset = 0;
CEPROPVAL* pCur = (CEPROPVAL*)pBuffer;
WORD iProp,
nProps = *pProps;
for(iProp = 0; iProp < nProps; ++iProp, ++pCur)
{
switch(TypeFromPropID(pCur->propid))
{
case CEVT_BLOB:
dwOffset = (DWORD)pCur->val.blob.lpb;
dwOffset -= (DWORD)pBuffer;
pCur->val.blob.lpb = (LPBYTE)dwOffset;
break;
case CEVT_LPWSTR:
pCur->val.blob.lpb = (LPBYTE)
((wcslen(pCur->val.lpwstr) + 1) * sizeof(WCHAR));
dwOffset = (DWORD)pCur->val.lpwstr;
dwOffset -= (DWORD)pBuffer;
pCur->val.lpwstr = (LPWSTR)dwOffset;
break;
}
}
}
*ppBuffer = pBuffer;
return ceoid;
}
What we do here is call the API to read the next database record and loop through its properties changing all pointers into array offsets. This is straightforward in the case of the BLOB: it carries both the pointer (now offset) and a byte size. The string is somewhat more complex to address because C strings do not carry an explicit length – it must be inferred from the position of the null terminator. This code handles this by calculating the string length (plus terminator) and storing it right after the offset. This is done using the BLOB pointer member because it is placed right after the string pointer. Confused? Here is how a BLOB is stored:
typedef struct _CEBLOB {
DWORD dwCount;
LPBYTE lpb;
} CEBLOB;
The way a C compiler looks at this structure when packed in the CEVALUNION union is that blob.dwCount and lpwstr share the same offset, so blob.lpb occupies the next four bytes – the last one in the property structure. What the code above is doing is, storing the string length after the string pointer (now converted to offset) in the reverse order that these are stored for the BLOB.
Reading this information is now much simpler, but we have yet to marshal it to the managed world. First, we need to map this function to a C# method:
public static extern
int CeDbNetReadRecord(IntPtr hDbase, ref short nProps, ref IntPtr pBuffer,
ref int nSize);
Besides returning the record’s OID, the method returns via reference parameters the number of properties on the record, the pointer to those properties and the buffer size. Note that this function will always retrieve a complete record. To retrieve only parts of the record, two more parameters would have to be provided (number and array of property identifiers).
Now, the property buffer can be read into a managed byte array and then it can be split into the individual properties. The marshalling procedure is helped by a very simple native function:
CEDBNET_API void CeDbNetLocalToArray(BYTE *pLocal, BYTE *pArray, int nSize)
{
memcpy(pArray, pLocal, nSize);
LocalFree(pLocal);
}
This function takes the buffer returned by the previous one, copies it to the managed byte array and frees it. Its managed signature is:
public static extern
void CeDbNetLocalToArray(IntPtr hLocal, byte[] data, int nSize);
After retrieving the property buffer, it must be split into individual properties stored in a collection. The class that handles this chore is CeDbRecord. An individual record is read on the Read method, so let’s take a look at it:
public void Read(IntPtr hDbase)
{
int nSize = 0;
short nProps = 0;
IntPtr pBuffer = IntPtr.Zero;
m_arrProp.Clear();
m_oid = CeDbApi.CeDbNetReadRecord(hDbase, ref nProps,
ref pBuffer, ref nSize);
if(m_oid != 0)
{
int iProp;
byte[] data = new byte[nSize];
CeDbApi.CeDbNetLocalToArray(pBuffer, data, nSize);
for(iProp = 0; iProp < (int)nProps; ++iProp)
{
CeDbProperty prop = new CeDbProperty(data, iProp * 16);
m_arrProp.Add(prop);
}
}
}
The record is read by calling the two previous functions sequentially, and then by looping through them all and building new objects of type CeDbProperty, the class that represents a single property. Note how the byte index is advanced in 16 byte chunks. What we are not seeing in this code is how a string or a BLOB is read. The answer lies in the constructor:
public CeDbProperty(byte[] data, int iOffset)
{
int iData = 0;
int nSize = 0;
m_propid = CeDbPropertyID.Create(data, iOffset);
Buffer.BlockCopy(data, iOffset, m_prop, 0, 16);
switch(CeDbPropertyID.GetCeDbType(m_propid))
{
case CeDbType.Blob:
nSize = BitConverter.ToInt32(data, iOffset + 8);
iData = BitConverter.ToInt32(data, iOffset + 12);
m_data = new byte[nSize];
Buffer.BlockCopy(data, iData, m_data, 0, nSize);
break;
case CeDbType.String:
nSize = BitConverter.ToInt32(data, iOffset + 12);
iData = BitConverter.ToInt32(data, iOffset + 8);
m_data = new byte[nSize];
Buffer.BlockCopy(data, iData, m_data, 0, nSize);
break;
default:
m_data = null;
break;
}
}
The m_prop variable is a byte array with 16 elements that is manipulated by the class’ methods and properties. It is kept in the native format to ease both reading and writing, which is enough for simple data types. Strings and BLOBs are stored in their native format on the m_data byte array. The code to allocate this array is displayed above and shows how the reversing of offset and length words is handled between a string and a BLOB.
Writing a record to the database is a bit more complex as the above process must be reversed by building a single byte buffer containing all properties as well as their respective strings and BLOBs. This process is completed in two phases: the building of the managed byte array and its conversion into a correctly-formatted record buffer by converting all array offsets into native pointers. Let’s start with the Write method of the CeDbRecord class:
public int Write(IntPtr hDbase, int oid)
{
int iProp;
int nSize = 0;
int iData = 0;
byte[] data = null;
foreach(CeDbProperty prop in m_arrProp)
{
nSize = AddOffset(nSize, 16);
nSize = AddOffset(nSize, prop.DataSize);
}
data = new byte[nSize];
iData = m_arrProp.Count * 16;
iProp = 0;
foreach(CeDbProperty prop in m_arrProp)
{
int nDataSize = prop.DataSize;
Buffer.BlockCopy(prop.GetPropBytes(), 0, data, iProp * 16, 16);
if(nDataSize > 0)
{
Buffer.BlockCopy(prop.GetDataBytes(), 0, data,
iData, nDataSize);
if(CeDbPropertyID.GetCeDbType(prop.PropID) == CeDbType.String)
{
Buffer.BlockCopy(BitConverter.GetBytes(iData), 0,
data, iProp * 16 + 8, 4);
}
else
{
Buffer.BlockCopy(BitConverter.GetBytes(iData), 0,
data, iProp * 16 + 12, 4);
}
iData = AddOffset(iData, nDataSize);
}
++iProp;
}
return CeDbApi.CeDbNetWriteRecord(hDbase, oid,
(ushort)m_arrProp.Count, data);
}
Although a bit large, the method is not too complex. It starts by calculating the total size of the byte array that will hold the record. Size calculations are made with the help of the AddOffset function that correctly calculates all offsets to lie on a four-byte boundary (shamelessly borrowed from the ATL OLE DB Consumer Templates code):
private int AddOffset(int nCurrent, int nAdd)
{
int nAlign = 4,
nRet,
nMod;
nRet = nCurrent + nAdd;
nMod = nRet % nAlign;
if(nMod != 0)
nRet += nAlign - nMod;
return nRet;
}
After calculating the byte array size, it is filled with the individual properties in the second for each loop. The iData variable contains the offset of the string or BLOB data and is incremented with the help of the AddOffset function. When this loop finishes, the byte array is correctly filled and ready to be marshaled to CEDB API. This cannot be done directly, though. A little bit of native code magic is required:
CEDBNET_API CEOID CeDbNetWriteRecord(HANDLE hDbase,
CEOID oidRecord,
WORD nProps,
CEPROPVAL* pPropVal)
{
CEPROPVAL* pCur = pPropVal;
WORD iProp;
for(iProp = 0; iProp < nProps; ++iProp, ++pCur)
{
DWORD dwOffset = 0;
switch(TypeFromPropID(pCur->propid))
{
case CEVT_BLOB:
dwOffset = (DWORD)pCur->val.blob.lpb;
dwOffset += (DWORD)pPropVal;
pCur->val.blob.lpb = (LPBYTE)dwOffset;
break;
case CEVT_LPWSTR:
dwOffset = (DWORD)pCur->val.lpwstr;
dwOffset += (DWORD)pPropVal;
pCur->val.lpwstr = (LPWSTR)dwOffset;
break;
}
}
return CeWriteRecordProps(hDbase, oidRecord, nProps, pPropVal);
}
What this native function does is the exact reverse of the first – it converts all offsets into pointers so that the CEDB API can use them.
Record updating and property storage is handled by the CeDbRecord class. The Write method can be used to either update the record or to create a new one, according to the value of the oid parameter. A value of zero inserts a new record in the database where using the record’s id updates that record.
These methods are used by the CeDbRecordSet class to implement the Update and Insert methods. The Delete method directly calls the CEDB API:
public void Delete(CeDbRecord record)
{
CeDbApi.CeDeleteRecord(m_hTable, record.Id);
}
public void Delete(int id)
{
CeDbApi.CeDeleteRecord(m_hTable, id);
}
Navigation methods such as MoveFirst and MoveNext are implemented through the CeDbApi.CeSeekDatabase and the CeDbSeek enumeration:
[Flags]
public enum CeDbSeek : uint
{
SeekCEOID = 1,
SeekBeginning = 2,
SeekEnd = 4,
SeekCurrent = 8,
SeekValueSmaller = 16,
SeekValueFirstEqual = 32,
SeekValueGreater = 64,
SeekValueNextEqual = 128
}
Please note that CeDbRecordSet objects manage the handle returned when a database is opened. Being an unmanaged resource, this class must implement the IDisposable interface.
Sample Project
The sample project uses the CDEB managed API to edit the Pocket PC contacts database. The application consists of a main form with an embedded list view where all contacts are displayed. The code to load the list is quite straightforward:
private void LoadList()
{
bool bRead = true;
int oid = 0;
Cursor oldCur = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
listCont.Items.Clear();
m_volume.UseSystem();
m_table = new CeDbTable(m_volume, "Contacts Database");
CeDbRecordSet recset = m_table.Open(CeDbOpenFlags.AutoIncrement,
0x4013001F);
listCont.BeginUpdate();
for(bRead = true; bRead; bRead = (oid != 0))
{
CeDbRecord rec = recset.Read();
oid = rec.Id;
if(oid != 0)
{
ContactItem item = new ContactItem(rec);
listCont.Items.Add(item);
}
}
recset.Close();
listCont.EndUpdate();
Cursor.Current = oldCur;
}
This small function clearly shows how the CeDbVolume, CeDbTable, CeDbRecordSet and CeDbRecord are related and used. Note how opening the database with the auto increment flag forces the engine to automatically advance the record pointer when one is read. To help store the records on the list, a ContactItem class is derived from ListViewItem in order to store an instance of a Contact class. A contact is built from a CeDbRecord and maps its properties to the CeDbRecord’s own CeDbPropertyCollection items.
The application also briefly shows how records are updated, inserted and deleted.
One word of caution: Make sure you back up your device’s contacts database when using this application.
