The Role of SQLCE
In Chapter 3 the discussion focused on how to handle file, XML, and relational data locally on a smart device. Part of that discussion showed how data could be persisted on the device and later reretrieved and displayed to the user. Although these techniques are acceptable for some applications, many applications require more sophisticated local storage. To address this need, this chapter focuses on the third essential architectural concept: robust data caching.
History of SQLCE
Along with the release of the Pocket PC 2000 platform, Microsoft anticipated the need to extend the data-management capabilities of enterprise-to-mobile devices. As a result, they shipped SQL Server 2000 Windows CE Edition 1.0 (SQLCE 1.0), code-named "Pegasus," in late 2000 to coincide with the release of SQL Server 2000.[2] SQLCE provides a local relational database of multiple tables running on a device that can be queried with a subset of the Transact-SQL syntax supported in SQL Server 2000. In addition, the first version supported referential integrity, transactions, and even accessing data remotely from SQL Server 6.5, as well as merge replication with SQL Server 2000. Developers could access SQLCE 1.0 using the ActiveX Data Objects for Windows CE 3.1 (ADOCE 3.1) library that shipped with the product.[3] This library, analogous to the ADO 2.x components used with VB 6.0 to access server-based relational data, allowed eVB developers to manipulate SQLCE databases on the device, while eVC++ developers could make OLE DB calls directly using the OLE DB provider for SQL Server CE (OLE DB CE).
[2] Prior to this, developers had to rely on proprietary schemes, Pocket Access, or the Windows CE database (CEDB) format accessible through Windows CE APIs or through ADOCE, which, to say the least, lacked many of the features developers expect in a relational database system.
[3] In addition, Microsoft released the ActiveX Data Object Extensions for Data Definition Language and Security (ADOXCE) to extend ADOCE for creating, deleting, and modifying schema objects.
Although SQLCE was well received, in order to access remote servers in version 1.0, the device had to be connected to the network via a modem or network card. Microsoft released version 1.1 in June of 2001 and added the SQL Server CE Relay product, which allowed the device to access remote servers when cradled using ActiveSync 3.1. In addition, version 1.1 was included in Microsoft Platform Builder 3.0 as a component and could be deployed as part of an embedded device, as described in Chapter 1.
With the planned release of the .NET Compact Framework in early 2003, it became essential that Compact Framework applications be able to take advantage of SQLCE easily. In September of 2002 Microsoft officially released SQL Server 2000 Windows CE Edition 2.0 (SQLCE 2.0, or from here on out simply SQLCE), which added integration with the Compact Framework through the System.Data.SqlServerCe namespace and also added important new functionality in the query processor and storage engine. As a result, applications built on the Compact Framework can use SQLCE for robust data caching directly from managed code and needn't use ADOCE or the OLE DB CE API.
NOTE
As you might imagine, because SQLCE and the Compact Framework are separate products, they do not always overlap. For example, SQLCE can be run on Handheld PC 2000 (H/PC 2K) devices, including the HP Jornada 720 and Intermec 6651, and embedded devices built with the Platform Builder 3.0, such as the Intermec 5020. For using SQLCE on these devices, developers will need to use eVB and eVC++.
Robust Data Caching
So what does the term "robust data caching" actually mean? This concept addresses several key elements, including the following:
Local relational database access: SQLCE supports a programming model on a smart device, which most developers are familiar with on the desktop. This allows developers to leverage their skills when creating applications and organizations to extend their data-management capabilities to devices. This is particularly the case for SQL Server developers because SQLCE supports familiar Transact-SQL syntax. Desktop Framework developers will also be able to get up to speed quickly because access to SQLCE is provided through a .NET Data Provider, using the standard classes and interfaces of ADO.NET.
Disconnected data integrity: SQLCE provides a robust cache for storing data on the device for use when the device is disconnected from the network. Because SQLCE supports relational database features such as unique indexes and foreign keys, the data can be manipulated on the device while it is disconnected and still maintain its integrity.
Built-in synchronization: SQLCE includes two different synchronization mechanisms that are built in, thereby saving developers from having to write complex infrastructure code for occasionally connected applications. These techniques will be addressed in Chapter 7.
Managed or unmanaged access: SQLCE is architected as an OLE DB CE provider and, therefore, can be accessed using either the native SqlServerCe .NET Data Provider in the Compact Framework or ADOCE; therefore it can be used with both VS .NET 2003 and eVB/eVC++.
Security: A key part of being a robust data cache is securing the data. As will be discussed later in this chapter, SQLCE supports password protection and data encryption so that data stored on the device is secure.
Obviously, applications that can utilize these features run the gamut, but typically they fall into the occasionally connected scenario where data is downloaded to the device, stored in SQLCE, accessed from SQLCE and updated on the device, and then later synchronized with a back-end data store such as SQL Server 2000, as shown in Figure 5-1.
Figure 5-1. The Role of SQLCE. This diagram shows the role of SQLCE in storing local copies of data that are then modified on the device and later synchronized with the remote server.
 | As you can imagine, an architecture like that shown in Figure 5-1 is useful in a variety of application scenarios, including sales-force automation, where mobile users are downloading and updating customer information; field-service automation, where delivery drivers and maintenance workers download and process deliveries and work orders; real estate, where agents download MLS listings; and medical applications, where doctors and nurses download and update patient and prescription information. Many of these solution scenarios have been implemented and documented as cases studies on the SQL Server CE Web site referenced in the "Related Reading" section at the end of the chapter. |
Differences with Local Data Handling
As discussed in Chapter 3, Compact Framework developers can use the ADO.NET DataSet object to persist data on the device.[4] As covered in Chapter 4, the DataSet can be populated from a remote server by calling an XML Web Service or by connecting to a remote SQL Server directly, using the SqlClient .NET Data Provider. While these techniques are well suited to some applications, there are several important advantages that a robust data-caching product such as SQLCE provides that ADO.NET cannot:
[4] Although not mentioned in Chapter 3, the Compact Framework does not support typed DataSet objects, which are classes derived from the DataSet class and generated through a visual designer and code generator in VS .NET.
Secure and efficient data storage: As mentioned previously, SQLCE supports encryption and database passwords. Data stored in DataSet objects is persisted to XML and has no such protection. In addition, XML is a verbose format and consumes more memory on the device than does data stored natively in SQLCE.
Query access to multiple tables: Although the DataSet object can include multiple DataTable objects and can even include primary and foreign keys, it does not provide the ability to query those tables using SQL and JOIN clauses. SQLCE, as a relational database, makes it easy to query multiple tables through joins.
Query performance for large data sets: As a corollary to the previous point, DataSet objects must be programmatically manipulated and, therefore, are much more processor-intensive to query. SQLCE is well suited to querying large amounts of data using a data reader, whereas DataSet objects are useful when manipulating between 10 and 100 rows.
Performance when populating: Populating a DataSet from an XML Web Service does not offer any support for compression during the transmission of the data, whereas SQLCE does compress data when using its synchronization techniques. As a result, a larger amount of bandwidth is required for accessing the same amount of data with a DataSet and XML Web Service.
