Executive Summary

Chapter 3 focused on how applications written with the Compact Framework could manipulate data locally on a mobile device. Although this capability is important, many application scenarios require more robust data caching. These scenarios comprise many applications of the occasionally connected type, including sales-force automation, field-service automation, real estate, and home-visit medical applications, among others. For these applications a local relational database that features data integrity, built-in synchronization, access from multiple development environments, and strong security is a must.

Shortly before VS .NET 2003 and the Compact Framework were released, Microsoft shipped SQL Server 2000 Windows CE Edition 2.0 (SQLCE 2.0),^[1] which fulfills these requirements. This local database engine consisting of a storage engine and query processor is implemented as an OLE DB provider and runs in-process with Compact Framework applications. SQLCE 2.0 includes a number of new features, including parameterized queries, index seeks, and the UNION clause.

^[1] Some prefer to use the acronym SSCE, but we prefer SQLCE because we believe it is easier to understand.

To provide access to SQLCE, Compact Framework developers can use the SqlServerCe .NET Data Provider. This provider is implemented using a common set of interfaces and classes and therefore allows developers to leverage their existing ADO.NET knowledge and begin writing applications for SQLCE. The classes in the provider, for example, SqlCeEngine, can be used to create databases, tables, and indexes programmatically, in addition to compacting databases and querying and modifying data in disconnected and connected scenarios. In particular, the ability of SqlServerCe to support index seeks directly on tables can greatly speed the performance of an application because it eliminates the overhead of the query processor. A common approach to manipulating data in a SQLCE database is to write data-access utility classes that encapsulate calls to the provider and distribute the classes to other developers in an organization in an assembly.

Because the Compact Framework supports accessing both a remote SQL Server through the SqlClient .NET Data Provider and local SQLCE databases, developers can use the Abstract Factory design pattern to create factory classes that can be utilized to abstract which provider is used at runtime. This is particularly effective for occasionally connected applications that require access to a remote SQL Server when connected to the network through a WLAN, for example.

SQLCE also offers a high level of security by supporting both password protection and encryption of the database on the device. This is important because the device on which SQLCE is running is inherently mobile and can easily fall into the wrong hands. The encryption algorithm is based on the password; therefore, passwords of eight or more characters are recommended.

SQLCE is included with VS .NET 2003 and automatically installs on the developer workstations. It is deployed automatically when a developer references the SqlServerCe .NET Data Provider in his or her application. It is also possible, and sometimes a preferred strategy, to prebuild SQLCE databases that will be included in RAM or on a storage card and that are large or will be deployed to a large number of devices.