Sync for ADO.NET - Client (Part 1)

Now it's time to set up our client. I'm not a big fan of wizards, and at this point in time the wizard for Sync is broken for my design so we'll code it all manually. I think this really helps with the understanding of the framework anyway.

For the Sync framework the concept is pretty simple, there's a few main roles:

• Client Sync Provider - this is what talks to our local SQL CE database
• Server Sync Provider - this talks to the server SQL database
• Sync Agent - this contains the group(s) of tables, instances of the providers, and exposes the Synchronize() method

If you're planning on using this in a distributed app where some clients don't have direct access to the SQL server you'll want to host the Server Sync Provider inside a WCF service, this provides for a secure way to talk to the database without exposing it to the Internet.

It's also worth noting that if you host using a WCF service layer, the server data store could be anything as long as it supports tracking changes using an anchor.

Sync Agent

This is where we create our groups of tables, each group will be updated at the server using a single transaction, if part of it fails the transaction fails (and will be retried on next sync).

For our purposes we'll have a single group with all our tables, we just have to make sure we add the tables in the right order so as not to break a relationship (for this demo it means TimeEntries is added last).

We can also add handlers to the StateChanged and SessionProgress events on this class if we want to show some feedback in our UI.

Client Sync Provider

To configure the local SQL CE database the out-of-the-box solution is good enough, but I like to make some minor changes like setting my primary key Guids to have RowGuid = true, as enforce foreign keys (these are dropped by Sync, probably because it's pretty easy to bring over partial results that might not include everything needed for relationships).

Again we use event handlers to add extra functionality by subscribing to CreatingSchema and SchemaCreated. There some events handy for logging too SchemaCreated, ChangesSelected, ChangesApplied, ApplyChangeFailed.

The above mentioned changes to Primary Key and Foreign Key references would be done in a CreatingSchema event handler, and any ALTER TABLE statements can be done in the SchemaCreated handler.

Server Sync Provider

We need to specify our SelectNewAnchorCommand, which can be as simple as getting the "Min_Active_RowVersion() - 1" (SQL 2005 SP2 fix for @@DBTS issue), or calling a stored proc that gets an anchor with support for batching.

For each table we want to synchronize against there's a bunch of SQL statements needed. If you want to go the easy way you can use the SqlSyncAdapterBuilder, just give it a little info about your table and it'll go off and read the schema and create the commands needed. I would suggest doing it by hand the first couple of times so you know exactly what's going on. For enterprise apps, make your DBA's happy and let them create stored procedures for the insert/update/delete pairs.

There's some events you can hook into here too, notably ChangesSelected, ChangesApplied, ApplyChangeFailed and ApplyingChanges. Mostly these would be for logging purposes, although in your ApplyingChanges handler you could modify the data about to be written.

One important note here, this class would normally exist inside a WCF layer, so we'll put it in another Class Library project for now that way it's easier to drop the reference in our client and use a wrapper around the WCF service at a later date.

What Next?

I'll go into a bit more detail showing some code for the client, perhaps even a download for the solution so far.