Death By Patterns

SQL Express Change Tracking Framework – Part 2

In Part 1 we took a look at what SQL Change Tracking was and had a brief glance at how we can take a look at the underlying table to see what data has changed.

This time we’re going to do something a little more practical and use SQL change tracking to actually synchronise data between a client and a server by throwing in some Sync Services for ADO.Net.  We’re going to synchronise the data to a SQL CE database to get the concepts and after that move it over to a SQL Express database.

The Database

Meet Joe.  Joe owns a deli and has a side interest in programming.  Joe’s gone and made an application which keeps track of all his recipes and all the orders he gets.

Each recipe has various ingredients, and may even require other recipes to be finished first (the self-join i need).  Each recipe has various ingredients with different quantities.  Each order is comprised of various quantities of recipes and the order has to be filled at a certain date.

Joe’s Deli app works fine, but over the past few months Joe has decided to increase his sales staff and he’s hired a couple of mobile sales reps.  These sales reps need to go to the clients and push through orders remotely… keeping in mind that the list of available recipes may change.  These sales reps will synchronise with the central DB whenever they are able to.

Assuming that Joe’s Deli app will work perfectly on mobile devices and the only change that he has to do is allow his sales teams to connect and resynchronize data with the central database then it’s simple:  Throw some SQL Change Tracking and ADO.Net Sync Services at the problem.

Daniel Moth has an excellent screencast on using Change Tracking with ADO.Net Sync Services which you can view here.  The screencast only shows the sync happening within one project so I’m going to show you how to sync between multiple projects (client server style).

1. Create two projects

The first of which will act as the server which the clients will synch with, and the second will act as the tool which runs on the clients and causes the sync with the server app.

The server will host the data over a WCF endpoint which the clients will connect and authenticate to and use that connection to sync data across.  Each client will run a copy of the Client Sync App.

So again, your first project would be a WCF Service Library so you can go ahead and start one of those up.  After you’ve created it delete all the files in the project except the App.Config file.

Then add another project to the list, this one is going to be used by your client app to sync the data.  One thing you’ll notice is that for some reason only certain project types allow you to add a Local Database Cache to them, and since we don’t know what type of device or application is going to be initiating the sync I’m going to make the second project a Class Library so that it can be used in most projects.

After the Class Library is created delete the Class1.cs file that’s added for you.  OK, so we’ve got our 2 projects.  Now add a Local Database Cache to the Client Library.

A wizard will appear to help you configure the change tracking.  The only step here that’s different to Daniel’s screencast is that we’re splitting the implementation into two projects.  So go ahead and expand the advanced section and tell the wizard which project is the server project (the WCF project) and which is the client project (the class library).

Do the rest like Daniels video (add the tables, click OK, do your usual dataset stuff) and then go and open the <DataCacheName>.Server.SyncContract.cs file.  Right at the top you’ll see a commented out section which has a <service> element and a <behaviour> section.  Copy the <service> element and paste it over the existing <service> element in the Server project’s App.Config.  Do the same with the <behaviour> element.

If you’ve done this correctly your Server project should appear in the “Add Service Reference” dialog for the client library after clicking the “Discover” button.

So go ahead and add the service reference.  All we have to do now is expose a Synchronise method from the Class Library.

All I did was add a class file (ClientSyncManager.cs) which exposes the following static method:

public static SyncStatistics Synchronise()
{
    using (JoesDeliCacheSyncAgent syncAgent = new JoesDeliCacheSyncAgent())
    {
        using (ServerSyncProviderProxy serverProxy =
            new ServerSyncProviderProxy(new JoesDeliCacheSyncContractClient()))
        {
            syncAgent.RemoteProvider = serverProxy;
            return syncAgent.Synchronize();
        }
    }
}

This allows us to make changes to the datawarehouse and a simple call to ClientSyncManager.Synchronise() will sync the data down to the client.

What about uploading changes?  Also simple, but we just have to tell the client that it must upload the changes too. To do that, open up the Client’s generated SyncAgent and add the following for each of the tables that need to upload changes too:

public partial class JoesDeliCacheSyncAgent
{
    partial void OnInitialized()
    {
        BaseIngredients.SyncDirection = SyncDirection.Bidirectional;
    }
}

Next Time:

Next time we’ll add some additional functionality such as:

• Filtering the data to be synched so that each agent only has a subset of the data
• Progress reporting (so we know how far our sync is, progress bars are pretty)
• Allowing the clients to delete on their local DB without the delete affecting the data warehouse
• Conflict Handling (both the server and the client have new versions of the same row)

Download the Sample Code