Following up on this post where I just got started with Sync Services, if I want to do more than just an occasional snapshot of data from server to client then I need to do a bit more work.

If I want to do incremental downloads of data from my server to my client then (depending on what I want) I need do probably;

1. Have a way of spotting what inserts have happened at the server
2. Have a way of spotting what updates have happened at the server
3. Have a way of spotting what deletes have happened at the server

Sync Services doesn't do this for you - you need to do it so I took a very simplistic approach to this and took my original table;

 

create table fruitSales
(
    id int identity not null primary key,
    fruit nchar(20) not null,
    quantity int not null,
    price money not null
)

and added a couple of columns to it so that I can detect inserts and updates;

alter table fruitSales
add
    insDate datetime not null default getutcdate(),
    modDate datetime not null default getutcdate()

with a couple of triggers to try and keep those things up to date;

create trigger fs_insert on fruitSales after insert
as
    update fruitSales
    set insDate = getutcdate()
    from fruitSales fs
    join inserted i on fs.id = i.id 

create trigger fs_update on fruitSales after update
as
    update fruitSales
    set modDate = getutcdate()
    from fruitSales fs
    join inserted i
    on fs.id = i.id 

In order to try and track deletes, I created a tombstone table as;

 

create table fruitSales_Tombstone
(
    fruitId int not null primary key,
    delDate datetime not null
)

and a trigger to populate that when records are deleted from my fruitSales table;

create trigger fs_delete on fruitSales after delete
as
    insert fruitSales_Tombstone 
    select id, getutcdate() from deleted

With that in place, a lot of the code that I need to do an incremental download of data is very similar to the previous example. We create the details of the table to sync (this time using the DownloadOnly option), the client and server providers;

 

      // The table to sync and the details of how...
      SyncTable syncTable = new SyncTable("fruitSales")
      {
        CreationOption = TableCreationOption.UseExistingTableOrFail,
        SyncDirection = SyncDirection.DownloadOnly
      };

      // The client side "provider"- SQL CE
      SqlCeClientSyncProvider clientProvider =
        new SqlCeClientSyncProvider(localDbConStr, true);

      // The server side "provider" - SQL Server
      SqlConnection sourceDbCon = new SqlConnection(sourceDbConStr);

      DbServerSyncProvider serverProvider = new DbServerSyncProvider()
      {
        Connection = sourceDbCon
      };

When it comes to creating the SyncAdapter, we need different kinds of commands. We need commands that;

1. Get any inserts since we last sync'd.
2. Get any updates since we last sync'd.
3. Get any deleted data since we last sync'd.

So we need to provide 3 commands that do this work. The "trick" is how we provide the commands with the information that they will need to identify which rows to bring back from the source data. Sync Services does this with what it calls an anchor. To my thinking, an anchor is a value that sync services will remember for us on a per-table basis in order that when we sync, we'll generate a new anchor value to represent this particular sync session and we'll use any last anchor value that was saved the last time we did a sync. It's easiest to think of these with dates as below;

Sync Session	Last Anchor	New Anchor	Rows Retrieved
21/05/07 14:30	NULL	21/05/07 14:30	All to 14.30
21/05/07 15.30	21/05/07 14:30	21/05/07 15:30	All from 14.30 to 15.30

The other "trick" about Sync Services is that it has some special variables named @sync_last_received_anchor and @sync_new_received_anchor to represent these values that you can use in your queries.

So...in order to determine updates to the table I can use;

 

      SqlCommand updatesCommand = new SqlCommand(
        "select * from fruitSales where modDate > @sync_last_received_anchor" + 
        " and modDate <= @sync_new_received_anchor",
          sourceDbCon);

      updatesCommand.Parameters.Add("@sync_new_received_anchor", 
        SqlDbType.DateTime);
      updatesCommand.Parameters.Add("@sync_last_received_anchor",
        SqlDbType.DateTime);

      updatesCommand.CommandType = CommandType.Text;

and in order to determine inserts to the table I can use;

 SqlCommand insertsCommand = new SqlCommand(
        "select * from fruitSales where insDate > @sync_last_received_anchor" +
        "  and insDate <= @sync_new_received_anchor",
        sourceDbCon);

      insertsCommand.Parameters.Add("@sync_new_received_anchor",
        SqlDbType.DateTime);
      insertsCommand.Parameters.Add("@sync_last_received_anchor",
        SqlDbType.DateTime);

      insertsCommand.CommandType = CommandType.Text;

and to determine deletes I can use;

  SqlCommand deletesCommand = new SqlCommand(
        "select fruitId id from fruitSales_Tombstone where delDate > @sync_last_received_anchor" +
        "  and delDate <= @sync_new_received_anchor",
        sourceDbCon);

      deletesCommand.Parameters.Add("@sync_new_received_anchor",
        SqlDbType.DateTime);
      deletesCommand.Parameters.Add("@sync_last_received_anchor",
        SqlDbType.DateTime);

      deletesCommand.CommandType = CommandType.Text;

Note that in that deletes query, it seems to be important that the column has the same name as it does in the fruitSales table (i.e. id).

With all that in place, I can create my adapter again and tell it about these commands I've created;

SyncAdapter adapter = new SyncAdapter("fruitSales");

adapter.SelectIncrementalUpdatesCommand = updatesCommand;
      adapter.SelectIncrementalInsertsCommand = insertsCommand;
      adapter.SelectIncrementalDeletesCommand = deletesCommand;

and finally go ahead, create the SyncAgent and get it to do the synchronisation;

 serverProvider.SyncAdapters.Add(adapter);

      serverProvider.SelectNewAnchorCommand = 
        new SqlCommand("select getdate()", sourceDbCon);

      // The agent that does the work
      SyncAgent agent = new SyncAgent()
      {
        ClientSyncProvider = clientProvider,
        ServerSyncProvider = serverProvider
      };

      agent.SyncTables.Add(syncTable);

      // Go...
      agent.Synchronize();

Neat. Now, I guess at this point I'm hoping for some kind of generation tool because I've only done one table here! (I think there is a tool in the bits but I've not looked at it yet). Also, this only caters for getting data from the server to the client. There's still the work of getting data from the client to the server and potentially dealing with conflicts and identifying which clients have made changes (this is all part of the framework).

---

Posted Mon, May 21 2007 6:56 AM by mtaulty