SQL 2008 & Sync Framework Over WCF : With Simplicity

I am working on a project at the moment that needs to keep a local store of data in order to give it to ability to function when the WCF service that feeds is offline. Once the service is up and running again, the data needs to be synchronized back to the server and any data on the server needs to be synchronized down to the client. This is becoming an increasingly demanded functionality as we see new projects. Now out of the box the Sync Framework will work nicely if you got direct access to SQL. But as the requirement states we have a WCF service sitting between the client and the DB.

In this post I am hoping to show a step by step approach to create a bidirectional synchronization line between a products table on my client and one on my SQL 2008 server that site behind a WCF service. Directly from MSDN this image shows what we try to achieve http://msdn.microsoft.com/en-us/library/bb902831.aspx.

Note: This is not an entry post of the sync framework. It’s just an explanation how to use sync framework, WCF and SQL 2008. Source code available at the end of the post.

Install the Sync Framework

Please download and install the sync framework found here.

My DB

Nothing fancy to see here, just your good old product table with a couple of rows.

There is a key thing to mention here before I continue. If you are using an SQL Server older than 2008 you will need to be manual change tracking as explained in the link to the MSDN article above. Otherwise SQL 2008 has got built in change tracking features which provide sufficient change tracking information to our scenario. One option in your database you have to make sure is on, is that tracking of changes is enabled. Right Click your database and click properties.

I created a data dude project in my solution for simplicity of distributing the demo’s source code.

 

My Sync Service – Part 1

The first thing I need to do here is to create a WCF project and add the following dlls as references. This will be my sync service.

1. Microsoft.Synchronization.dll
2. Microsoft.Synchronization.Data.dll
3. Microsoft.Synchronization.Data.Server.dll

I then create the following service definition for my Sync Service. These methods (service operations) and their signatures are what my client is going to be expecting when attempting a sync back to the server.

using System.Collections.ObjectModel;

using System.Data;

using System.Net.Security;

using System.ServiceModel;

using Microsoft.Synchronization.Data;



namespace WcfSyncService

{

  [ServiceContract(Name = "SyncService", SessionMode = SessionMode.NotAllowed,

    ProtectionLevel = ProtectionLevel.EncryptAndSign)]

  public interface ISyncService

  {

    [OperationContract]

    SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession);



    [OperationContract]

    SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession);



    [OperationContract]

    SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession);



    [OperationContract]

    SyncServerInfo GetServerInfo(SyncSession syncSession);

  }

}

My Sync Client – Part 1

Now I come up with a WPF client that displays a list of items from the local cache at any given time. The idea will be to synchronise the local cache with my server’s database through the WCF service we defined earlier.

So I start off with a simple WPF App.

I now need to add my local cache database.

I now get presented with the following wizard. I choose the server’s connection string and a new client database option is given to me. I then change the project location of the server to my previously created WCF service project.

From there I choose the products table to create a local dataset. The steps are outlined as follows.

 

Select the field I want to include in my typed data table.

Viola! Not only did the client create the appropriate cache on the client but it also created the necessary sync providers and adapters in the service project as well.

The thing to note here is that my client is still not configured to sync with the server. For that I will need to do some minor code changes.

My Sync Service – Part 2

Jumping back to the server I need to now implement my service definition. I do that by passing all of the operation on to the Sync provider that has now been created for me by the wizard.

using System.Collections.ObjectModel;

using System.Data;

using Microsoft.Synchronization.Data;



namespace WcfSyncService

{

  public class SyncService : ISyncService

  {

    private readonly ProductCacheServerSyncProvider syncProvider;



    /// <summary>

    /// Initializes a new instance of the <see cref="SyncService"/> class.

    /// </summary>

    public SyncService()

    {

      syncProvider = new ProductCacheServerSyncProvider();

    }



    public SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)

    {

      return syncProvider.ApplyChanges(groupMetadata, dataSet, syncSession);

    }



    public SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

    {

      return syncProvider.GetChanges(groupMetadata, syncSession);

    }



    public SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession)

    {

      return syncProvider.GetSchema(tableNames, syncSession);

    }



    public SyncServerInfo GetServerInfo(SyncSession syncSession)

    {

      return syncProvider.GetServerInfo(syncSession);

    }

  }

}

My Sync service is now ready to roll and I load in the wcf test client just to make sure everything is alright.

OK so the mental note here is the service does load and it does run but the parameters are not supported by the WCF test client.

My Sync Client – Part 2

Ok now my WCF service is ready. The next step is to configure the client to use the WCF service for the synchronization. I now add a service reference to my service from the client.

One more thing to note here. The wizard will always create SyncTables with a default SyncDirection of DownloadOnly. But in this scenario we need the table updates to be Bidirectional.  The trick is to right click the ProductCache and click on View Code. A partial class is created and you need to modify it as follows.

using Microsoft.Synchronization.Data;



namespace WcfSyncClient {

  

  

  public partial class ProductCacheSyncAgent 
{

    

    partial void OnInitialized()

    {

      this.Product.SyncDirection = SyncDirection.Bidirectional;

    }

  }

}

Ok so now all my infrastructure is ready so time for a semi pretty UI for the display of data and operations.

It’s pretty simple what happens behind these buttons. The main thing to note is that I am instantiating the agent using my WCF client proxy. This basically tells the sync agent to use my WCF service for any synchronisations.

using System;

using System.Windows;

using Microsoft.Synchronization.Data;

using WcfSyncClient.SyncDBDataSetTableAdapters;

using WcfSyncClient.SyncServiceProxy;



namespace WcfSyncClient

{

  /// <summary>

  /// Interaction logic for Shell.xaml

  /// </summary>

  public partial class Shell : Window

  {

    public Shell()

    {

      InitializeComponent();

    }



    private void btnRefresh_Click(object sender, RoutedEventArgs e)

    {

      var ds = new SyncDBDataSet();

      var adapter = new ProductTableAdapter();

      adapter.Fill(ds.Product);

      listBox.DataContext = ds;

    }



    private void btnSynchronise_Click(object sender, RoutedEventArgs e)

    {

      var syncAgent = new ProductCacheSyncAgent(new SyncServiceClient());

      SyncStatistics syncStats = syncAgent.Synchronize();

    }



    private void btnAddRow_Click(object sender, RoutedEventArgs e)

    {

      var ds = (SyncDBDataSet) listBox.DataContext;

      var dr = ds.Product.NewRow() as SyncDBDataSet.ProductRow;

      if (dr != null)

      {

        dr.Name = "New Product " + (new Random()).Next();

        dr.ProductId = Guid.NewGuid();

        dr.Price = 800;

        ds.Product.AddProductRow(dr);



        var adapter = new ProductTableAdapter();

        adapter.Update(ds);

      }

    }

  }

}

I can see by running the application the SQL DB is also getting that changes as I add new records on the client and vice versa.

One Problem You Might Run Into

One exception that you might get during debugging your application on 64-bit machines is:

Unable to load DLL 'sqlceme35.dll': The specified module could not be found.

This is related to the fact that the original release SqlCe was not supported on 64-bit machines. You have two options:

1. Download Microsoft SQL Server Compact 3.5 Service Pack 1 and Synchronization Services for ADO.NET version 1.0 Service Pack 1 for Windows Desktop
2. Compile your project for x86 CPU configuration.

Clearly the first option is the recommended one.

Source Code

Conclusion

The sync framework is a nice tool and toy that simplifies a lot of the hassles we had to go through in the past for offline data caching. Bundling it with WCF just gives it a fantastic edge. It should not be missed in your future projects.

Comments

[hattar]

Great article, I get the following error whenever I try to synchronize. "The specified change tracking operation is not supported. To carry out this operation on the table, disable the change tracking on the table, and enable the change tracking." I tried disabling/re-enabling tracking on the SQL server but that didn't seem to help. Anyone else seeing this?

[body detox]

oject at the moment that needs to keep a local store of data in order to give it to ability to function when the WCF service that feeds is offline. Once the service is up and running again, the data needs to be synchronized back to the server and any data on the server needs to

[Paul]

Nice work. Any ideas if this will play well with an SQL Azure DB? I know the Sync Framework has some examples of this but your's is so much more straight forward IMO.

[Jeff Pigott]

Has anyone ran into issues if you need to add more tables or new columns to something that is being synced into production?

Can this be changed in the current wizard or does it have to be ripped and replaced?