Let's get to business; the goal of the application I am building here is to take the data of two tables (orders and order_details) offline. Both tables are located at the backend server which happen to be SQL Server 2005, no surprises here! With the data cached locally, users of my application can make changes as they move around without being so concerned whether the connection is lost or not. At some point, the user can click on the synchronize button to connect the server and bring his or her local cache in sync with the server.

The demo application has a simple user interface as shown in the snapshot below:

The main dialog presents the current content of orders and order_details tables. Typically, you would see the local content only, but to make things more convenient for you I added a selection for the server database. This way you don't need to switch to SQL Management Studio to see the server rows and compare them with what the client has.

Forge to mention that the client store in for Synchronization Services Framework is SQLCE 3.5 which is part of the CTP package. SQLCE is not a new member to SQL Server family, however, its use was limited to devices only. Now it works for desktop with special support for synchronization logic baked into its engine. Frankly, SQLCE is a god send; I maybe going overboard here but what's not to like? It is small, lightweight, in-proc database. You ship it with your application without the hassle of installing SQL Express..etc. What more can you ask for?

WARNING: At the time of this writing, the first CTP of sync services has a know integration issue with SQL Management Studio and SQLCE 3.5. Any change you make through SSMS to the SqlCe database file won't upload when you try to synchronize with the server. This is because the SSMS is referencing a old DLL. This makes it harder to make changes to the local database to experiment the upload functionality. For this reason, I added three buttons on the right under the gird view. One button for inserting a random row, the second for updating zero or more rows and the third is for deleting zero or more row.

Now, when you are ready to synchronize, just click on the 'Synchronize' button and a new dialog box will pop up with the synchronization progress. Here is another snapshot:
 

Not the most exciting progress indicator ever, but you get the idea.

The highlights of this demo are twofold:

First: Building Very Simple Change Tracking That Works

The demo.sql file under the setup directory contains set of TSQL statement to setup change tracking for each table on the server side. Change tracking is key part of developing any synchronization system. It is basically the infrastructure that will allow you to find the answer to questions like: "What was changed since last time I synchronized?". Well, there is more to that but I will save it for later demos. In this simple tracking approach, I will need few things for each table:

1. update_originator_id : this is an int column that indicate who made the last change to the row
2. update_timestamp: timestamp column to record the time when the row was updated
3. create_timestamp: timestamp column to record the time when the row insert was created
4. tombstone_table: to store the row when it gets deleted
5. update trigger: to maintain the value of the update_originator_id after an update operation
6. delete trigger: to copy the deleted row to the tombstone table

Here is how our simple tracking code looks like:

--
-- Add tracking columns
-- 1. Create update_originator_id column defaulted to 0 to indicate server change
ALTER TABLE pub..orders add update_originator_id int null default 0
ALTER TABLE pub..order_details add update_originator_id int null default 0
go
   
-- Add last update timestamp column
ALTER TABLE pub..orders add update_timestamp timestamp
ALTER TABLE pub..order_details add update_timestamp timestamp
go

-- Add create timestamp column (use bigint since one one timestamp column type is allowed per table)ALTER TABLE pub..orders add create_timestamp bigint default @@DBTS+1
ALTER TABLE pub..order_details add create_timestamp bigint default @@DBTS+1
go   
 

--
-- Create tombstone tables to store deletes
--
CREATE TABLE pub..orders_tombstone(
    order_id int NOT NULL primary key,
    order_date datetime NULL,
    update_originator_id int default 0, 
    update_timestamp timestamp,
    create_timestamp bigint)

CREATE TABLE pub..order_details_tombstone(
    order_id int NOT NULL primary key,
    order_details_id int NOT NULL,
    product nvarchar(100) NULL,
    quantity int NULL,
    update_originator_id int default 0, 
    update_timestamp timestamp,
    create_timestamp bigint)
go

--
-- Create Update and Delete Triggers
-- Since there will be changes on the server outside of the sync application
-- we need triggers to fix up update_originator_id back to 0 which designated
-- for server change

-- update triggers
use pub
go
CREATE TRIGGER orders_update_trigger on orders for update
as
    declare @key int
    select @key = order_id from inserted
    if not UPDATE(update_originator_id)
        update orders set update_originator_id = 0 where order_id = @key
go
 

CREATE TRIGGER order_details_update_trigger on order_details for update
as
    declare @key int
    select @key = order_id from inserted
    if not UPDATE(update_originator_id)
        update orders set update_originator_id = 0 where order_id = @key
go
 

-- delete triggers
use pub
go
CREATE TRIGGER orders_delete_trigger on orders for delete
as
    insert into pub..orders_tombstone (order_id, order_date, create_timestamp, update_originator_id)     select order_id, order_date, create_timestamp, 0 from deleted
go     

CREATE TRIGGER order_details_delete_trigger on order_details for delete
as
    insert into pub..order_details_tombstone (
        order_id,
        order_details_id,
        product, quantity,
        create_timestamp,
        update_originator_id)
    select
        order_id,
        order_details_id,
        product,
        quantity,
        create_timestamp,
        0
    from deleted
go   

Although I don't need to store more than the PK for the deleted rows. I preferred to store the content of the deleted rows. Why? will be clear in subsequent demos, but let me say that it has something to do with conflict resolution.

Second: The Magic Called SyncAdapterBuilder

The sync services was modeled after ADO.NET DataAdpater type. Unlike the DataAdapter, SyncAdapter has a total of eight commands. The adapters are added to a collection that hangs off ServerSyncProvider class which in turn exposes two more commands. That said, if you have two tables, as in this demo, you need to write (8 * 2) + 2 = 18 commands! That a lot of commands.

SyncAdapterBuilder is here to make developer life easier, especially as they get their hands wet with this new API. Later on, writing a SyncAdapter manually that does amazing things will be addiction (ask me) and then the builder will appear so primitive! So, let's see how I generated SyncAdapter objects for orders and order_details tables:

//
// orders table
//
SqlSyncAdapterBuilder ordersBuilder = new SqlSyncAdapterBuilder();               
ordersBuilder.Connection = serverConnection;
ordersBuilder.SyncDirection = SyncDirection.Bidirectional;

// base table
ordersBuilder.TableName = "orders";
ordersBuilder.DataColumns.Add("order_id");
ordersBuilder.DataColumns.Add("order_date");

// tombstone table
ordersBuilder.TombstoneTableName = "orders_tombstone";
ordersBuilder.TombstoneDataColumns.Add("order_id");
ordersBuilder.TombstoneDataColumns.Add("order_date");

// tracking\sync columns
ordersBuilder.CreationTrackingColumn = @"create_timestamp";
ordersBuilder.UpdateTrackingColumn = @"update_timestamp";
ordersBuilder.DeletionTrackingColumn = @"update_timestamp";
ordersBuilder.UpdateOriginatorIdColumn = @"update_originator_id";

SyncAdapter ordersSyncAdapter = ordersBuilder.ToSyncAdapter();
serverSyncProvider.SyncAdapters.Add(ordersSyncAdapter);

//
// order_details table
//  
SqlSyncAdapterBuilder orderDetailsBuilder = new SqlSyncAdapterBuilder();
orderDetailsBuilder.SyncDirection = SyncDirection.Bidirectional;
orderDetailsBuilder.Connection = serverConnection;

// base table
orderDetailsBuilder.TableName = "order_details";
orderDetailsBuilder.DataColumns.Add("order_id");
orderDetailsBuilder.DataColumns.Add("order_details_id");
orderDetailsBuilder.DataColumns.Add("product");
orderDetailsBuilder.DataColumns.Add("quantity");

// tombstone table
orderDetailsBuilder.TombstoneTableName = "order_details_tombstone";
orderDetailsBuilder.TombstoneDataColumns.Add("order_id");
orderDetailsBuilder.TombstoneDataColumns.Add("order_details_id");
orderDetailsBuilder.TombstoneDataColumns.Add("product");
orderDetailsBuilder.TombstoneDataColumns.Add("quantity");

// tracking\sync columns
orderDetailsBuilder.CreationTrackingColumn = @"create_timestamp";
orderDetailsBuilder.UpdateTrackingColumn = @"update_timestamp";
orderDetailsBuilder.DeletionTrackingColumn = @"update_timestamp";
orderDetailsBuilder.UpdateOriginatorIdColumn = @"update_originator_id";

SyncAdapter orderDetailsSyncAdapter = orderDetailsBuilder.ToSyncAdapter();
serverSyncProvider.SyncAdapters.Add(orderDetailsSyncAdapter);

As you can see, I added the adapters generated to the SyncServerProvider. Now the only thing left is to define a command to get the new anchor, this command is provider wide thus is exposed from the provider directly:

//
// 6. Setup provider wide commands
// SelectNewAnchorCommand: Returns the new high watermark for current sync, this value is
// stored at the client and used the low watermark in the next sync
//

// select new anchor command
SqlCommand anchorCmd = new SqlCommand();
anchorCmd.CommandType = CommandType.Text;
anchorCmd.CommandText = "SELECT @" + SyncSession.SyncNewReceivedAnchor + " @@DBTS";    
anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;           
serverSyncProvider.SelectNewAnchorCommand = anchorCmd;

That is it. Time to sync...
 

Install OfflineAppDemo Application Steps:

• Fire SQL server and load demo.sql file
• Execute the script until the "test sample" marker
• Load VS solution (OfflineAppDemo-Builder Project)
• Build the project
• You are ready to go

Version 1.4 [C#]: Download Now