Download the Code

The most underrated feature in .NET development is how you can leverage your skills in one area to another. You use the same tools and libraries for desktop applications, Web applications, Web services, and mobile applications. Yes, there are some differences between the .NET Framework proper and the Compact Framework, but you can very easily move from one to the other and leverage your design skills, language skills, and knowledge of the library and database engines.

Even more important, applications on mobile devices don’t usually work in isolation: the form factor and the size restrictions mean that you create a full-featured desktop application and a limited mobile application. The ability to use the same tools becomes more important. Transferring information, your data, between the desktop and Pocket PC as part of a synch operation is even more important. The simplest solution is to store your Pocket PC data using SQL Server CE, and transfer that data to the desktop using SQL Server Remote Data Access (RDA). In this article, I’ll discuss the best practices for designing an application for both the desktop and Pocket PC, and how to use SQL Server CE and RDA to keep the data synched between the two machines.

I built a pocket time tracker that shows the best choices you will make when you create applications designed for both the Pocket PC and desktop. In both applications, the end goal is the same: keeping track of how much time was spent on different work activities during the day (see FIGURE 1).

FIGURE 1: Tracking time on the desktop

I use the tracker to define clients, projects, and tasks. Then, I use the buttons to work on a particular task. The tracker adds a record in my database that stores the start and stop time for each task. The Pocket PC version is much simpler, as shown in FIGURE 2.

FIGURE 2: Tracking time on the Pocket PC

I did not add the capability to create clients, projects, and tasks. Those features are only available on the desktop version. The result is a simpler user interface. Also, the Start / Stop button is large enough that I can use my thumb. I don’t need to get the stylus out to start or stop a particular task.

You need to have the capability for the desktop and mobile application to communicate and transfer data. You can create tasks and projects only on the desktop, and they must find their way to the Pocket PC. You can create work records on the mobile device, and they must find their way to the master list on the desktop.

To achieve this goal, you’ll use SQL Server CE as the data storage mechanism on the Pocket PC. You’ll pull the relevant data from the desktop computer (or any central server). After working with the data on the mobile device, you’ll push the data back to the desktop machine using RDA.

SQL Server CE is a reasonable database engine for the Pocket PC. It has some size limitations, but those are reasonable given the size of the device. A SQL Server CE Database can be 2 GB. You will run into the hardware restrictions of the device far more quickly than you will run into the database limitations. Even so, it is important for you to minimize the amount of data you pull to the Pocket PC. Your application will not scale if you attempt to transfer your enterprise data to the Pocket PC. When you design your application, you’ll need to create a smaller working set of data for the Pocket PC version. Only pull what’s necessary.

The way you’ll transfer data between the main server and the Pocket PC is RDA. RDA is the simpler of the two synchronization mechanisms for mobile devices. The other is SQL Replication. I use RDA more often because it is a lightweight solution that is more than sufficient for most of my needs. SQL RDA pulls data from a desktop server into a local table. You have a local copy of the data that resided on the main SQL Database. To update the same data, you push that data back to the server. The RDA transfer agent merges the changes into the main database. It uses optimistic concurrency and reports conflicts. You need to develop your own application-specific strategy to merge any conflicts. RDA is best for those applications that have simple merge and conflict resolution strategies, where entire rows are chosen when conflicts occur. If you need to merge data where different columns are chose from different versions of your database, you need to use SQL Replication.

That’s enough of the abstract discussion, let’s build the sample. I’m not going to discuss the desktop version in any sort of detail because I’ll assume you know how to create a desktop forms application that targets a SQL Server database. There are only two design decisions on the desktop application that have a bearing on the mobile application. Those deserve some discussion.

Take a look at FIGURE 3. First, the database design uses unique identifiers (GUIDs) as keys on the database rows. This decision makes it much easier to avoid conflicts when the database tables are changed on both the desktop and mobile device. The other common practice of creating auto-number columns for keys will make it far more likely that two rows created on two different devices have the same ID. This problem would only be far more common on an enterprise application when several hundred users are using their own devices against a single enterprise database. GUIDs let me finesse this whole issue: two rows should not be the same even if created on different devices.

FIGURE 3: Use GUIDs to avoid conflicts

Second, the database contains one extra link that enables me to limit the amount of data that I transfer to the device. I added a link to assign a task to one user, and one user only. I’ll admit this design is somewhat limiting, but this one is for illustration purposes. Before you start coding the Pocket PC application, make sure you have all the RDA prerequisites installed and configured.

There are a few tricks and prerequisites that you need in order to create and work with SQL Server CE applications.

The first gotcha comes when you install the desktop version of SQL Server. The RDA application does not connect to a named SQL Server instance. You must use a default unnamed SQL Server installation on the desktop. This took me a long time to diagnose and correct. The error messages only indicate that the SQL Server CE agent cannot access the desktop server. In fact, I only found it by luck. When I moved from my development machine to a deployment machine, the application stopped working. The only difference was the deployment machine had a named SQL instance. My development machine does not.

Second, you need a special version of SQL Server CE to work with SQL Server 2000 SP3 and higher. You can get that version here: http://www.microsoft.com/sql/ce/downloads/sp3.asp The reason is that the version of SQL Server CE delivered by MSDN is not compatible with the security fixes in SQL Server 2000 SP3. When you install SQL Server CE, you need to install the server components and the development tools.

Once you’ve got the SQL Server CE tools installed, you must configure the connectivity support in Internet Information Server (IIS). IIS does not have to be installed on the desktop machine, but it must be installed on a machine in the same network so that the Pocket PC can access that machine through ActiveSynch. SQL Server CE adds a menu item to configure the connectivity between the device and the desktop instance of SQL Server. The device issues HTTP requests to an ISAPI DLL that communicates with the database server. You must configure that for your application to run. I simply configured IIS to use the default directory where the SQL Server CE Server Agent gets installed: C:\Program Files\Microsoft SQL Server CE 2.0\Server. You also need to configure IIS to use Allow anonymous access on that directory. Perform these tasks using the SQL Server CE plug-in, not the IIS manager.

Third, the Compact Framework does not have the capability to generate GUIDs. Guid.NewGuid() is missing, but the good folks at MSDN have created a sample we can use to correct this problem: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/PPCGuidGen.asp.

The only install that you don’t need to perform before you create the application is the SQL Server CE engine on the device. The deployment project for your Pocket PC automatically installs the SQL Server CE run time on the device.

The device application contains two forms: The login form and the main form. Create a new Mobile device project in VS.NET, and we’re ready to begin. The first step is to add a reference to the System.Data.SqlServerCE assembly. This assembly contains the classes that you will use to communicate to the SQL Server. In addition, adding this assembly to your list of references generates the necessary installation instructions to install the SQL Server CE run time on your device.

Our application will run off a single SQL CE database. You need to initialize the database when your application starts:

The SqlCeEngine creates the database. The connection string is the database file. In SQL Server CE, you control the location of the database files when you create the database itself. The SqlCeConnection also uses the file path name as the connection string. The SqlCeConnection has a subset of the SqlConnection methods as its API. You use it the same way. Now let’s build the login form (see FIGURE 4).

FIGURE 4: Login form

This form has three edit boxes: the host name, the user name, and the password. The user name and password identify the person running the program. The host identifies the URL where the SQL Server RDA connection point resides. When the user logs on, the application queries the server to see if this user is valid. If you have not created Compact Framework applications before, you may also notice the empty menu on the form. The login form contains a Software Input Panel (SIP) control. The SIP is the control on which the user writes text. The SIP does not display unless the form has a menu. No menu, no typing. Whenever you create a form that uses the SIP for input, that form must include a menu:

This code snippet that pulls user data shows a number of standard practices to use when you pull data from the desktop server. The SqlCeRemoteDataAccess class essentially moves data from one database to another. It either transfer data from the desktop to the device, or from device to desktop. You supply it with the connection strings for both databases. Second, you’ll notice that I am not using the usual app.config file to store either connection string. That’s because there is no support for config files on the Compact Framework. As I get a little further into the application, you’ll see that you can create a table on the device database to store your configuration information and other settings. In the interest of simplicity, I simply used hard-coded strings for the sample on the connection strings.

Next, you’ll notice that I am only retrieving the person record for the user that is logging on. There’s two reasons: First, as I mentioned earlier, mobile devices are memory constrained. If this were part of an enterprise logging system, you’d need to minimize the number of person records. Second, it’s a way to let the application run more easily when not connected. The RDA connection only works when the device is connected to the host. By caching the current user’s credentials, the application can ensure that only the current user can log in on the device. After all, only that user’s data is available on the device.

Finally, notice the Drop method call before pulling data from the server. RDA is a simple sync mechanism: A query executes, and the results are stored in a local table. In a moment, you’ll see that push does exactly the opposite: it pushes all the changes from a table to the server. To keep it simple, RDA mandates that a pull must populate a new table.

Once the user record has been pulled to the client machine, the application can validate the user against the local version: 

You’ve seen all the tools you need to build a viewer application: Pull data from the database and view it. Next, you’ll build the code to modify the local data and push that data back to the server. Push will send data to the server only from a table that has been pulled from the server. You can’t use push to create new tables on the server.

All of your data modification work on the device will go against the local database. I’m not covering that because it uses the same familiar SQL Adapter and SQL Command syntax from your desktop and enterprise apps. Any changes made to the local table can be pushed to the server. In fact, the device application uses a Dataset to work with the device data in memory.

Once you’ve made your changes, you push the data back to the server on the next ActiveSynch:

The ISAPI DLL examines all the changed data records and merges the changes with the existing server data. After any push, you should pull the same data back to get any updates at the server that have been made by others. You do that with the same code for the pull.

In this article, I’ve shown you the basics of RDA and the simple way to create database-driven Pocket PC applications. Remember to design your storage model to minimize collisions, and to minimize the amount of data you pull to the device. And, test a very small application first. The configuration is a bit challenging to get right, but once you can transfer a single table, you’re on your way. The development tasks are easy, and best of all, you’ll use familiar tools.