More and more, enterprise users are moving into mobile work scenarios in which a network connection is not always possible and the traditional PC is too cumbersome. In order to reach such users, we as developers need to get our skills and systems up to speed with mobile development. .NET provides the tools and infrastructure to do just that with the addition of the .NET Compact Framework and Smart Device Extensions for Visual Studio .NET 2003.

Since mobile users don't always have access to data servers, mobile systems need to store enterprise data locally to the device and have a means to get data modifications back to the server. SQL Server CE 2.0 (SSCE 2.0) provides both of these capabilities to VS.NET 2003 developers.

In this article we'll show you how to use the .NET Compact Framework and SQL Server CE 2.0 to create enterprise data applications for Pocket PC devices. We'll show you how the managed provider for SSCE 2.0, System.Data.SqlServerCe, provides the functionality you need to work with both local and remote data., and we'll also show you how SSCE 2.0 allows you to work with enterprise data.

Working with Enterprise Data and SSCE 2.0
SSCE 2.0 offers the ability to work with enterprise data by providing a sophisticated means to bring enterprise data down to a device, modify that data locally while offline, and propagate changes back to the server. This represents what most of us will be doing with data-centric mobile devices. SSCE 2.0 and the System.Data.SqlServerCe library offer two methods for providing this enterprise data support.

The first method of moving data is Remote Data Access (or RDA). RDA is by far the simplest method of performing the synchronization function. It uses simple SQL statements to allow you to filter out only the data you want (conserving resources on the device, as well as making the most of a limited connection). RDA does not support any type of conflict resolution. When data is transferred back to the server, it simply overwrites whatever data matches the primary key on the server. This makes RDA suitable for simple data scenarios where the data schema is very simple and there is no possibility of multiple users modifying the same data.

The second method of data synchronization is Replication. Replication leverages the replication abilities of SQL Server 2000 by making a SSCE database a replication client. This gives your application very sophisticated data filtering capabilities by allowing you to leverage column and row filtering as well as multiple table joins. This allows you to filter based on values in a parent table and then filter child tables based on relationships such as foreign keys. Conflict resolution is quite sophisticated with Replication in that you have access to SQL Server 2000's resolver. This allows you to use built-in resolution functionality either in a T-SQL stored procedure or by coding custom functionality.

Both methods leverage compression to make moving data over the wire as fast as possible. Both methods also support encryption to protect your data over the wire. Replication has the added functionality of being able to resume replicating data if the connection is lost during transmission, whereas RDA forces the programmer to catch this event and handle it accordingly.

You can use RDA and Replication in the same project. RDA is easier to configure, but it's only appropriate for simple scenarios and is slightly more difficult to code. Replication is more sophisticated and requires more configuration. But coding Replication is very straightforward. We've often used both in our projects. We've used RDA to download simple lookup tables and then used Replication for situations in which we've needed sophisticated data filtering or conflict resolution.

Configuring Your Database for Multiple Disconnected Users
When implementing disconnected data support we need to implement a means of creating primary keys that are guaranteed to be unique across users. This allows users to insert rows into tables while disconnected and then push those new rows back to the server without two keys colliding. In cases where you have unique values for each user, such as a region code or sales account code that can be used with an identity column as a composite key, your table is ready to go. In areas where you would normally want to use an identity column, you need to replace that column with the data type uniqueidentifier. Uniqueidentifier is a special data type that guarantees that each value created by the engine (either SQL Server 2000 or SSCE 2.0) is unique.

The following code shows an INSERT statement used to insert a new row into a table that uses uniqueidentifier as its primary key. The statement uses the NEWID() function, available on both SSCE and SQL Server 2000, to generate a uniqueidentifier value for each new row.

INSERT INTO TableName
(UniqueIDCol, Col1, Col2)
VALUES
(NEWID(), ?, ?)

After we make sure that the primary keys of our tables are ready for disconnected mobile users we need to make sure that all our datatypes are SSCE 2.0 ready. SSCE 2.0 supports a subset of the SQL Server 2000 datatypes in order to keep the engine simple and efficient. SQL Server CE 2.0 Books Online (www.microsoft.com/sql/ce/techinfo/20bol.asp) details these differences, so check there for more information on the supported types and the simple mapping of types.

Configuring Your Infrastructure for Devices and Data
Once we've prepared our database for multiple disconnected clients, we need to prep the infrastructure for data synchronization. RDA and Replication use IIS in order to communicate between the device and SQL Server 2000. This has a number of advantages, the first of which is that your existing Web infrastructure can be leveraged in your SSCE 2.0 synchronization. IIS authentication and authorization are leveraged in order to secure access to your data. Take a look at Figure 1 to get a better idea of the topology of RDA and Replication.

SQL Server CE 2.0 Books Online has more information on how to configure this infrastructure. In our examples, we'll set everything up using IIS anonymous access to keep things simple, and we'll dedicate another article to ways to protect your data, both on the device and over public and private networks.

Setting Up Your Server for RDA
When setting up your server for RDA or Replication, you'll need to run the setup for the SQL Server CE Server Tools and configure a virtual directory for the server agent. Replication requires the additional steps of configuring the publication of your data as well as the creation of a snapshot folder for moving snapshots of data between the client and server.

To set up your development workstation as the server (with VS.NET 2003, IIS, and SQL Server 2000 already installed), navigate to \Program Files\Microsoft Visual Studio .NET 2003\CompactFrameworkSDK\v1.0.5000 and run either sqlce20sql2ksp1.exe (for use with SQL Server 2000 SP1) or sqlce20sql2ksp2.exe (SP2 or later). This will install the server agents, sscesa20.dll and sscerp20.dll. You can have sscesa20.dll in multiple virtual directories, allowing you to configure security differently for each application. On the other hand, sscerp20.dll is a COM DLL and can only reside in one location on your machine.

The next step is to create a virtual directory for the server agent. We'll do this by running a server utility, the SQL Server CE Connectivity Management Utility, for all of our configuration settings. Figure 2 shows the main screen of this utility. To run this utility, select Start/All Programs/Microsoft SQL Server CE 2.0/Configure Connectivity Support in IIS. We'll run the wizard and enter the values in Table 1 (with an explanation of each setting) in order to set up RDA for the sample code we'll write later.

At this point we have an HTTP endpoint that we'll use within our code to move data across the network. When writing this code, just make sure your device either has connectivity to the network via ActiveSynch 3.5 or later or is on the network via a wireless card.

Writing the Code for RDA
We will access RDA programmatically by creating an instance of the SqlRemoteDataAccess class. SqlRemoteDataAccess supports three methods for working with enterprise data running on SQL Server 2000. The paradigm for using RDA is very simple. Pull down the data you need; mark the tables you'd like to track for changes; then push those tracked tables back when you are ready to synchronize your data. Table 2 describes the methods that support this.

When pulling down data, SqlRemoteDataAccess allows you to record any transmission errors via an errors table by specifying the ErrorTableName parameter. You can query these tables after a Push in order to handle any errors. Let's get started writing some RDA code. First, go into SQL Server 2000 and create a database named SimpleSSCEExample with the tables as described in Tables 3, 4, and 5. Add some Customer, Invoice (with various SalesAgentIDs, the value we'll use to filter data in both samples), and InvoiceDetail records so we have some sample data to pull down.

Now open Visual Studio .NET 2003 and create a Smart Device Application and call it SSCEExample. Add references to the System.Data.SqlServerCe and System.Data.Common libraries. On the form, drop a button and double-click on the button to create an event procedure. We'll put our RDA code here.

Coding against SqlRemoteDataAccess is very straightforward. First we create an instance of the class and give it the information that it needs in order to know which server to talk to (see Listing 1). We're using anonymous access, so we won't provide values for the InternetLogin/InternetPassword properties. If we were using Basic/ Integrated Windows, we'd put that information there (and use HTTPs to protect those values over the network!).

Next, we need to get rid of any existing database and create a new one (see Listing 2). Now we're going to run some SQL against the remote server. In order to do so, we need a valid connection string for that server.

Dim remoteConnString As String =
"Provider=sqloledb;
DataSource=MyServerName;
InitialCatalog=SSCEExample;
UserId=sa;
Password = password"

From there, we can just create some SQL statements to run against the remote server using SubmitSql.

' Insert some new data.
Dim sql As String
sql = "INSERT INTO Customer
(CustomerID, FirstName, LastName)
VALUES
(NEWID(), 'Jon', 'Roshenberger')"
Rda.SubmitSql(sql, remoteConnString)

Now we're ready to start pulling down data into our local database. We do that by calling the Pull method. Note that RDA does not allow joins when tracking is turned on. We don't change customer data on the client, so we're not using tracking on this table.

' Pull the data down to the local SSCE database.
' We don't change customers, so we can do a multiple
table join and no tracking.
sql = "SELECT Customer.CustomerID,Customer.FirstName,Customer.LastName
FROM Customer
INNER JOIN Invoice
ON
Customer.CustomerID = Invoice.CustomerID
WHERE
Invoice.SalesAgentID = 2"
Rda.Pull("Customer", sql, remoteConnString,
RdaTrackOption.TrackingOffWithIndexes, "CustomerErrors")

For the Invoice and InvoiceDetail tables, all the data is created on the device. But we still want to track those newly created records so we can push them back. We'll do this by calling Pull with SQL that returns no rows and with the tracking option set to the RDATrackOption.TrackingOnWithIndexes value (see Listing 3).

Next, we'll write some code to open a connection to the local database and insert and modify some data in our tracked tables.

Dim mySSCEConn As New
SqlCeConnection
("DataSource=\ProgramFiles\SSCEExample\SSCEExample.sdf")
mySSCEConn.Open()

' Modify the data with some SqlServerCe code.

We then close the connection so the SSCE client DLL can use the connection for the Push back to the server.

' Close the connection prior to push
(only a single connection at a time).
mySSCEConn.Close()

We then perform the Push calls, stating what tables we want to push, the server we want to push them to (via a connection string), and whether we'd like the rows applied as one job (all or nothing with the RdaBatchOption.BatchingOn) or on a row-by-row basis (RdaBatchOption.BatchingOff).

' Push the data back.
Rda.Push("Invoice", remoteConnString, RdaBatchOption.BatchingOff)
Rda.Push("InvoiceDetail", remoteConnString, RdaBatchOption.BatchingOff)

Catch sce As SqlCeException
' Exquisite and thorough error handling goes here.
End Try

So that's RDA. Using SqlRemoteDataAccess is just a matter of deciding what data needs to be tracked, performing a Pull to get the data localing in SSCE 2.0, and calling a Push to get the changes back.

Replication: More Sophisticated Data, More Sophisticated Setup
Starting with the same database, implementing Replication is just a matter of modifying the virtual directory, implementing a publication of the data, and writing replication code.

Open up the SQL Server CE Connectivity Management console and double-click on the SSCEExampleSynch virtual directory. For Replication to work, you need to set NTFS permissions for the Snapshot folder, where the sscerp20.dll works with intermediate files. Since we're using anonymous Web access, we're running sscesa20.dll under IUSR_<machinename>. Click on the NTFS Permissions tab and then click on the Add/Modify NTFS Permissions for a User... button. Enter the values in Table 6.

Next, we need to configure a publication of my SSCEExample database. Again, we'll want to reduce the amount of data published. Replication provides a means of limiting columns (vertical filtering) as well as rows (horizontal filtering). For this example, we'll want to limit what rows are available to each subscription database (in this case, SSCE). We'll do so by passing information about the subscriber into the replication call.

I'll create the publication by selecting the SSCEExample database, right-clicking on it, and selecting New/Publication. To configure the publication, enter the values in Table 7.

In the Filters wizard tab, after you select Invoice as the table to filter, add the following WHERE clause:

CAST(SalesAgentID as nvarchar(1))= HOST_NAME()

This WHERE clause uses a special function, HOST_NAME(), to filter the rows in the Invoice table. We can place values in the HOST_NAME() function via a property of the SqlCeReplication class. Click next and set up the following filters of the remaining tables, as described in Table 8, and click Next until the wizard is completed.

Now we've actively filtered the Invoice table and joined it to the other tables to subset our data. Right-click on the SSCEExamplePub and select Properties. Select the Snapshot Location tab and set the snapshot location to \\<machinename>\SSCEExample Snapshot, the location we've configured for the server agent to use for intermediate files.

Now let's write the Replication code itself. Replication is supported programmatically by the SqlCeReplication class. If you look at Table 9, you'll see that it is mainly made up of properties to specify information about the Publication and the Synchronize method for getting data synchronized between the device and the server.

Open up the SSCEExample solution, drop another button on the forms design surface, and double-click on the button to add an event procedure. Enter the code in Listing 4. Note that the entire filtering scheme is based on getting some information into the SQL Server 2000 function HOST_NAME(). The HostName property of the SqlReplication class populates the value into the row filter of the WHERE on the Invoice table (see Listing 5).

Conclusion
In this article, we've shown you the basics of programming SqlServerCe in order to bring enterprise data down to an SSCE instance (and back). SSCE 2.0 provides all the functionality you need in order to run a database local to your PPC without significant changes to what you know about ADO.NET (either in skills or tools). More important, SSCE 2.0 and SqlServerCe provide a means of working with enterprise data from SQL Server 2000 while offline, as well as the ability to push changes back to the server when a connection is available. RDA and Replication provide you with two options for performing this function, each with unique attributes lending it to certain situations. SSCE and the .NET Compact Framework give you what you need to start serving the needs of mobile users, without a high price for entry in skills or infrastructure.

• Figure 1
• Figure 2
• Source Code
• Figure 3
• Figure 4
• Figure 5
• Figure 6
• Figure 7
• Figure 8
• Figure 9
• Figure 10
• Figure 11