Using Remote Data Access with SQL Server CE 2.0by Wei-Meng Lee
Microsoft SQL Server CE edition is the database server built by Microsoft to run on mobile devices. Besides being a standalone database for mobile applications, SQL Server CE also allows you to connect to your desktop SQL Server 2000 and perform remote data access and merge replication. In this article, you will learn how to build a .NET Compact Framework mobile application using Visual Studio .NET 2003 and how it can perform remote data access using SQL Server CE 2.0.
For more information on .NET Compact Framework, see my previous article, "Developing Pocket PC Apps with SQL Server CE."
Features of SQL Server CE 2.0
Figure 1 shows the main components in SQL Server CE and its relationship to SQL Server 2000 (on the desktop).
Note: Figure is from SQL Server CE Books Online
|Figure 1. Architecture of SQL Server CE and its relationship to SQL Server|
On the client's end (mobile devices), the SQL Server CE Engine takes care of the data stored in the local SQL Server CE database. It also tracks records that are inserted, modified, or deleted when there is a need to maintain connectivity with the SQL Server on the desktop. The SQL Server Client Agent serves as the layer for programmatically manipulating SQL Server CE. It implements SQL Server CE objects such as the SQLCEEngine and Remote Data Access (RDA). This is the layer that your application interacts with when programming SQL Server CE.
On the server side, the SQL Server CE Server Agent acts as the middleman, mediating between SQL Server and SQL Server CE for connectivity issues. All of these interactions are done via HTTP, through the Web server.
Our Sample Application
In this article, I will build a mobile application that runs on Windows CE .NET devices (the code discussed will also work on Pocket PCs, as well). This application allows a teacher to take attendance in class using a mobile device and updates the information on a SQL Server 2000 database when he returns to the office.
The following diagram shows the flow of the system:
|Figure 2. Flow of the system|
- User pulls students record from the SQL Server 2000 database, using ActiveSync, and stores the records in the local SQL Server CE database.
- User takes attendance in class and modifies the local SQL Server CE database.
- User pushes the records to SQL Server 2000 when he gets back to office.
To get started, let's now configure IIS for SQL Server CE's use:
|Figure 3. Configuring virtual directory using the "Configure Connectivity Support in IIS" tool|
Essentially, you want to create a virtual directory that contains the SQL Server CE Server Agent. As discussed, the use of the SQL Server Agent is to handle all requests via HTTP. It acts as a middleman between SQL Server and the SQL Server CE Client Agent. In our case, I will create a virtual directory named SQLCE and map it to the directory C:\Program Files\Microsoft SQL Server CE 2.0\Server\, which contains the sscesa20.dll SQL Server CE Server Agent file. You also need to configure the security for this virtual directory. You have the options of choosing Anonymous, Basic Authentication, or Integrated Windows Authentication. For simplicity, I have chosen Anonymous access, but you really should consider carefully against this option if you are deploying the application.
Visual Studio .NET 2003 comes with Smart Device Extension (SDE) integrated, so you can immediately start creating a .NET Compact Framework mobile application.
|Figure 4. Visual Studio .NET 2003 now comes with Smart Device Application support|
For this article, I will create a Windows CE application (though you can choose Pocket PC if you are targeting Pocket PC users):
|Figure 5. Choosing the platform -- Pocket PC or Windows CE|
On the design pane, populate the default Form1 with the following controls:
|Figure 6. Populating the form|
Also, add a Context Menu control with the following menu items:
|Figure 7. Adding items to the Context Menu|