Maintain your Web site data on your Pocket PC using SQL Server CE

Microsoft SQL Server 2000 CE Edition is an enterprise-level DBMS that can be deployed on any Pocket PC device. We'll show you how to administer a SQL Server-driven Web site using a Pocket PC, including replicating data back and forth between servers.

Microsoft SQL Server 2000 CE Edition is an enterprise-level database management system that can be deployed on any Pocket PC device. This particular server has a unique list of features and functionality including:
  • .NET data provider: SQL Server CE can be used as a powerful data provider for any Windows CE or Windows Mobile application.
  • Remote administration: You can administer and modify SQL Server data "offline" using SQL CE, then synchronize the data from your Pocket PC back to a remote SQL Server 2000.
  • Inexpensive development tool: Microsoft SQL Server CE is available as a free download from the Microsoft MSDN Web site.
  • Mobile Web development: Combining PocketASP and Microsoft SQL Server CE allows you to develop and run ASP-driven Web sites right off your Pocket PC.

The following article will provide step-by-step instructions on how you can administer a SQL server-driven Web site using a Pocket PC and replicate data back and forth between database servers.

Manually installing Microsoft SQL Server CE on your Pocket PC
The easiest way to deploy SQL Server CE is to install it manually on your Pocket PC device. To start, download the following applications and install them on your home PC:

There are two sets of files you will need to install SQL CE on your handheld. The first set of files is found here (assuming you installed SQL Server CE on your C: drive):
C:\Program Files\Microsoft SQL Server CE 2.0\Device\

Microsoft SQL Server CE is available for a variety of chipsets including ARM, MIPS, SHX, and X86. The main executable is the SQL Server Query Analyzer (isqlw20.exe). The three supporting DLL files include a Client Agent (Ssceca20.dll), Engine (Ssce20.dll), and Error Handling Library (Ssceerror20en.dll). The file rootcert.exe is used to create a root certificate in Windows CE if you're planning to use SSL connections along with Remote Data Access. Using the ActiveSync Explorer window, drag the following files to your Pocket PC:
  • isqlw20.exe
  • dllregister.exe
  • rootcert.exe
  • Ssce20.dll
  • Ssceerror20en.dll
  • Ssceca20.dll

In order to query the SQL CE databases, we also require a set of DLLs to provide ADO functionality. You can find the files in the following directory:
C:\Windows CE Tools\wce300\Pocket PC 2002\dataaccess31\target\

The ADO 3.1 DLLs are supported on both ARM and X86 chipsets. In case you were wondering, Adoxce31.dll allows you to manipulate the database structure. Adoce31.dll permits the manipulation of data within the SQL CE database. Adoceoledb31.dll provides ADO OLEDB support, and Msdaeren.dll is an error-handling library. Copy the following files to your Pocket PC:
  • Adoxce31.dll
  • Adoce31.dll
  • Adoceoledb31.dll
  • Msdaeren.dll

Once all the files are copied, you must register all the DLLs in the Windows CE registry. The file dllregister.exe is provided to do that very task, but I've had more success manually registering each of the DLLs using a tool called Regsvrce.exe found in the Pocket PC 2002 SDK. Bring over this utility to your Pocket PC, type in the name of each DLL, and voila! Once you have registered all the DLLs, simply click isqlw20.exe to launch the SQL CE Query Analyzer. To make it readily accessible, you can create a shortcut and place it in the Start Menu folder in the Pocket PC Windows directory.

Microsoft SQL Server 2000 and SQL Server Connectivity Management
Now that we've installed SQL Server CE, the next step involves configuring SQL Server 2000. Remote Data Access relies on both SQL Server 2000 and IIS, so we need to set up both applications. IIS 5.0 is available as a free component of Windows 2000 and Windows XP Professional. Microsoft SQL Server 2000 is a commercial product available for purchase. You can also download an evaluation copy.

As you're installing SQL Server and IIS, be sure that you create an Internet account with sufficient security permissions to access the database. Please refer to SQL Server CE Books Online available in Microsoft HTML Help (.CHM) format for more details.

Next, you need to create a virtual directory using the SQL Server CE Connectivity Management Console. This application is installed by default along with Microsoft SQL CE. Be sure that IIS is running when you launch the management console from the Start Menu (as shown in Figure A).

Figure A
Creating a virtual directory

The first thing we need to do is create a Virtual Directory that will serve as a conduit to our database. In this article, I created a small Northwind-inspired database called FooStore which contains tables called Products, Customers, and Orders. Click Create a Virtual Directory. This will launch the SQL Server CE Virtual Directory Creation Wizard. Click Next to bring up the window in Figure B.

Figure B
Virtual Directory wizard

We must create an alias for our virtual directory. I picked FooStore, but you can pick any name that's suitable for your own database. Click Next to set access permissions for the virtual directory, as shown in Figure C.

Figure C
Assigning access permissions

Then we must set access permissions for the virtual directory. I've set mine to Anonymous, but you may want to add more security to your own application. Next the wizard will prompt you to set up Merge Replication. In this instance, we won't be using this particular feature, so you can click Next to skip it.

Figure D
Summary screen

Finally, the summary screen (Figure D) will confirm your options. The virtual folder will point to a file called sscesa20.dll, which acts as a Server Agent file, a relay between SQL Server 2000 and SQL Server CE.

To test if the virtual directory has been configured correctly, type the following URL in your Web browser: http://localhost/FooStore/sscesa20.dll. If you get a message that says SQL Server CE Server Agent, then everything has been configured correctly and you can move on to the next step.

If you receive a prompt for a download location, open IIS and right-click the FooStore directory. Select Properties and make sure that the Execute permissions are set to both Scripts & Executables. If you get a message saying This page cannot be displayed, make sure that IIS is running properly.

In a nutshell, the application will work in a 3-tier architecture. SQL Server CE sends out all requests to IIS, and IIS dispatches the requests to SQL Server 2000. The remote SQL 2000 server will then either replicate the SQL CE data or respond to specific queries or requests.

Pulling in data from SQL Server 2000 to SQL Server CE
The transfer of data from one SQL server to the other is accomplished using the SQL Server CE Remote Data Access object. The default Microsoft SQL Server CE install comes with eMbedded VB samples including a simple RDA application. The samples are located in this directory:
C:\Program Files\Microsoft SQL Server CE 2.0\Samples\

We will create a VB application that will pull data from the SQL Server 2000 FooStore database and populate a Pocket PC database called FooStore.sdf. First, we must instantiate the RemoteDataAccess object:
Dim rdaObj As SSCE.RemoteDataAccess
Set rdaObj=CreateObject("SSCE.RemoteDataAccess.2.0")

Then we declare the address of our virtual directory and define a login and password if the directory requires authentication:

Then we create a connection string to our local data source:
rdaObj.LocalConnectionString="Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=FooStore.sdf"

We then use the Pull method to query the remote SQL server database and obtain all of the records from the Products table. The TRACKINGON option makes sure that the Primary Key constraints are maintained during the transfer.
rdaObj.Pull "Products", "SELECT * FROM Products", "Provider=SQLOLEDB; Initial Catalog=FooStore;Data Source=Foo", TRACKINGON

Finally, we clean up the object. The data will effectively be replicated from SQL Server 2000 to SQL Server CE.
Set rdaObj=Nothing

Administering Microsoft SQL Server CE using the Query Analyzer
Unlike other replication methods, Remote Data Access uses queries launched from Windows CE to transfer data from a remote server to the handheld and vice versa. The SQL CE Query Analyzer is a useful tool to manipulate the database using queries. The Objects browser, shown in Figure E, allows you to change the structure of the local database (for example, you can add and remove tables, columns, and fields):

Figure E
Object browser

You can query and change data in the databases using the SQL browser. If you're running local queries, the results will be displayed in the Grid browser. The Notes browser is reserved for alerts and error messages if problems occur.

You have several options for manipulating the replicated data. You can use the Query Analyzer, or you can build a custom application that can handle operations such as adding or deleting records into your database. The latter choice makes a great deal of sense because you have to programmatically control the replication process using a custom eVB app, so why not build in content manipulation functions while you're at it?

Pushing your changes back to SQL Server 2000
Once you have manipulated the data to your liking, the next step is to transfer the data back into SQL Server 2000. The RDA object has a method called Push that accomplishes this task very nicely. As before, we instantiate the RemoteDataAccess object and set the location of our virtual directory:
Dim rdaObj As SSCE.RemoteDataAccess
Set rdaObj=CreateObject("SSCE.RemoteDataAccess.2.0")
rdaObj.LocalConnectionString="Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=FooStore.sdf

Then we use Push to move the Products table from SQL Server CE back to SQL Server 2000:
rdaObj.Push "Products","Provider=SQLOLEDB;Initial Catalog=FooStore; Data Source=Foo"
Set rdaObj=Nothing

Microsoft SQL Server 2000 CE Edition can be used to manipulate SQL Server data while offline. All you need to do is build a simple mobile application to maintain your data on the Pocket PC. If you're SQL savvy, you can use the built-in SQL CE Query Analyzer to make all your changes.

Remote Data Access is a good choice if you want to create small mobile applications that replicate between two servers. For larger scale or Enterprise applications, be sure to look into Merge Replication. In any case, Microsoft SQL Server CE is an indispensable tool for the DBA on the go.

Editor's Picks