It's taken a little while, but I’ve finally come to terms with the idea of a relational database on a handheld device. This acceptance required two things: The first was the realization that a current generation Pocket PC has more storage than a 1980s minicomputer; the second was SQL Server CE.
SQL Server CE is available as a download for use with SQL Server 2000 from the Microsoft Web site and as part of Microsoft’s eMbedded Visual Tools 3.0. If you don’t have SQL Server 2000 and just want to take SQL Server CE out for a spin, use the eMbedded Visual Tools version. Both come with the Windows CE version of Query Analyzer (Figure A), which provides a way to create and administer databases on the Windows CE device. Those unfamiliar with Query Analyzer will find its GUI relatively intuitive.
|Query Analyzer for Windows CE|
Installing Query Analyzer
The task of installing the Windows CE Query Analyzer comes down to first installing the software on the desktop and, second, seeing how well you know your handheld device. As with a lot of Windows CE applications, you'll need to know the processor type. Fortunately, multiple versions of the software for various processors are supplied when SQL Server CE is installed on the host machine. Unfortunately, finding the folders on the host machine is something of a treasure hunt, which is why Table A shows the default software locations for the various processors.
You need to take the files from the appropriate folder and move them from the desktop to a folder on the handheld device. Then you need to execute dllregister.exe, which is included, to register the DLLs on the handheld device. The Query Analyzer is isqlw20.exe; I’d recommend creating a shortcut from your device’s Start menu.
SQL Server CE vs. Access CE
The first reason to use SQL Server CE instead of Access CE can be summed up in a single word: consistency. SQL Server is an enterprise database and, therefore, many companies use it. Odds are that most developers have at least encountered it before. Access, on the other hand, is used primarily for demonstrations or quick-and-dirty applications. And as an enterprise-class database, SQL Server CE is more robust than Access CE. Table B lists the data types supported by SQL Server CE.
The second reason to use SQL Server CE is the aforementioned Query Analyzer. Personally, I find using a GUI much easier than writing SQL to create a table. For me, when given a choice between handling raw SQL like this:
CREATE TABLE new_table (new_column INT UNIQUE)
vs. the GUI approach in Figures B and C, the figures will always win. In addition, Query Analyzer provides a way to test queries to make sure that my SQL will work.
|Using Query Analyzer|
|Defining a new field (column) in Query Analyzer|
The final reason to use SQL Server CE instead of Access CE is RDA (Remote Data Access) and merged replication. These are both methods of connecting to and exchanging data with a SQL Server 2000 database using Microsoft IIS (Internet Information Services).
Remote Data Access
RDA provides a way for a Windows CE application to pull data from a remote SQL Server database table and store the information in a local SQL Server CE database table. The application can then process using the local copy of the data, which can later push the changes back to the remote SQL Server database table. For wireless applications or some other form of connected application, RDA can be used to submit SQL commands for execution against the remote SQL Server database table.
Merged replication is for applications where data can be updated on either the disconnected Windows CE device or the server. When the Windows CE device is connected again, the data is merged on both the device and SQL Server. Unlike RDA, merged replication requires that the data be “published” by SQL Server and that the Windows CE has a subscription before the device can access the data.
The better choice
Regardless of whether the application is stand-alone or uses RDA or merged replication, SQL Server CE is a robust database. When developing handheld applications with a database such as SQL Server CE, the only limitations are the device’s available memory and our imaginations.