SQLCE Architecture

Once you understand the role that SQLCE plays in a solution, it is important to understand its architecture and features. The diagram shown in Figure 5-2 illustrates the architecture of SQLCE and the various software components that make up a solution that uses it.

As you can see from Figure 5-2, SQLCE itself is implemented as a DLL and an OLE DB provider (OLE DB CE) that can be accessed from both managed code using the .NET Data Provider for SQL Server CE (SqlServerCe is discussed in the following section) and eVB using ADOCE and directly using eVC++. This provider encapsulates the SQL Server Client Agent that is responsible for replication and RDA, discussed further in Chapter 7, and the SQL Server CE Engine.

SQL Server CE Engine

Unlike the server version, the SQLCE database engine is implemented in a DLL for performance reasons, even though the engine will be loaded in each process, using SQLCE on the device. However, because typically only one application using SQLCE will be active at any one time and because SQLCE supports only one concurrent connection, it is not a significant issue.

The database engine consists of two components: the storage engine that manages the data stored on the device in 4K pages and the query processor that processes (compiles, optimizes, and generates query plans) queries sent from applications. Together these two components support the following features, among others:

• Query processor supports SQL, including SELECT, MAX, MIN, COUNT, SUM, AVG, INNER/OUTER JOIN, GROUP BY/HAVING, ORDER BY, UNION, and operators including ALL, AND, ANY, BETWEEN, EXISTS, NOT, SOME, OR, LIKE, IN, also Transact-SQL including DATEADD, DATEDIFF, GETDATE, COALESCE, SUBSTRING, and @@IDENTITY^[5] among others

  ^[5] Both IDENTITY columns and uniqueidentifier can be used to create system-assigned primary key values in a SQLCE table. However, if all rows in the table are deleted and the database compacted, the identity counter is reset to its original value. This behavior can affect applications that need to synchronize with a back-end database.

• 249 indexes per table, multicolumn indexes

• Databases of up to 2GB

• BLOBs of up 1GB

• 255 columns per table

• 128 character identifiers

• Unlimited nested subqueries

• Nested transactions

• Support for NULL values

• Parameterized queries

• Data Manipulation Language (DML): INSERT, UPDATE, and DELETE

• Data Definition Language (DDL): CREATE, DROP, ALTER on databases, tables, and indexes

• 17 data types, including Unicode (nchar, ntext) and GUID (uniqueidentifier)

• PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints

• Replication tracking capability that tracks changed data on the device as discussed in Chapter 7

Query Analyzer

Although not shown in Figure 5-2, SQLCE also ships with an improved Query Analyzer that runs on the device and can be used to create databases, tables, and indexes; query data; insert and delete rows; compact and repair a database. It is generally used by developers to ensure that their local database is accessible.

When using the Compact Framework, Query Analyzer (Isqlw20.exe) is deployed to the device automatically using a .cab file if the application references the SqlServerCe provider, and a shortcut is placed in the Start menu on the device.^[6]

^[6] eVB and eVC++ developers must manually copy the Query Analyzer to the device, along with the appropriate supporting files, as noted in the documentation.

To use the Query Analyzer, a developer need simply navigate to the database file (typically with an .sdf extension) and tap the green arrow on the bottom of the screen. The tables and their structures can then be navigated, as shown in Figure 5-3. Developers can then query the data in a table by tapping on the arrow or tapping the SQL pane and writing the SQL directly.