email article printer friendly get the code more resources

Create Mobile Database Apps
Use the .NET Compact Framework and SQL Server CE 2.0 to give Windows CE and Pocket PC apps access to corporate databases.
by Doug Thews

October 2003 Issue

Technology Toolbox: VB.NET, SQL Server 2000, Visual Studio .NET 2003, SQL Server CE 2.0, .NET Compact Framework, Pocket PC device, Internet Information Services

Visual Studio .NET 2003 provides a truly integrated platform for developing mobile applications for the Windows CE and Pocket PC OSs. It also includes SQL Server CE 2.0—a mobile version of the traditional SQL Server database platform. Now, you can easily develop enterprise-class mobile applications that access corporate databases.

I'll show you how to use Visual Studio .NET 2003 and SQL Server CE 2.0 to develop a simple contact-management application that runs on a Pocket PC ( download the sample code). One of its requirements is that the user can be disconnected, so you'll use SQL Server CE to host a replicated copy of the database on the device. The application synchronizes the local SQL Server CE database with a back-end SQL Server 2000 database when the user connects to the network. You'll learn how to design the back-end database for use with SQL CE 2.0, and how to choose and set up database replication so that remote devices can edit information in parallel. Then, you'll build the application and test it with the .NET Compact Framework emulator and on a Pocket PC device.

The contact-management application's goal is to enable multiple Pocket PC users to synchronize with a master list of contacts, update the contact list, then resynchronize the changes to the master database. This requires you to host a SQL Server CE database on each Pocket PC device that contains a replica of the master database. Then, you must synchronize the local database with the master database programmatically.

SQL Server 2000 supports two methods of replicating information from a back-end master to a remote client database: Remote Data Access (RDA) and merge replication. RDA is by far the easier method to set up and use, but it doesn't provide any support for conflict resolution. If multiple clients update a common table row, the last client overwrites any changes the previous clients made, without giving you any way of determining which data should be accepted.

Merge replication creates a publication on the back-end master database server that each client subscribes to. Replication creates a snapshot that contains the current back-end database schema and data. As subscribers make changes to the data, the Merge Agent keeps them separate. When subsequent requests come in for subscription information, the Merge Agent merges the original snapshot with any changes made since the last subscription call for the subscriber.

Merge replication looks for potential conflicts when changes are replicated. You can develop your own conflict-resolution business code through Transact-SQL (T-SQL), stored procedures, or your own COM add-in. SQL Server stores the conflicting rows in a separate table if you don't implement custom conflict resolution, so you can use SQL Enterprise Manager to examine them and determine a desired action. You'll use SQL merge replication for the sample application to minimize the potential effects of synchronization conflicts from multiple Pocket PC devices.

Merge replication requires an Internet Information Services (IIS) Web server to communicate between the mobile device and SQL Server. The Web server's use of standard HTTP and/or HTTPS protocols makes the communication easy, even through network firewalls (see Figure 1).