This is part 2 in the Sync Framework 2.0 series and deals with building an occasionally connected application using Local Database Caching. Part 1 in the series dealt with installing the Sync Framework and using the File Sync Provider.
My original goal with this post was to see if I could use the Sync Framework to build a C# version of SQL Replication to keep two SQL Server databases in sync. However – I found a limited amount of information available in the public domain, so I have decided to take an initial step into SQL Synchronisation by building an “occasionally connected application” using Local Database Caching. The basic idea is to build something like the following:
Users of the application can work in local mode where all their changes are persisted to a local database, and at intervals they can sync their changes back to the main corporate database. The best initial source of information I found for this type of application was on MDSN called “Occasionally Connected Applications” – an excellent series of articles that discuss various aspects of this topic. The manner you approach this type of application depends upon what technologies you are going to use to build it – for my example – I’m going to use SQL Server 2008 R2, Visual Studio 2010 and of course – Sync Framework 2.0.
When it comes to tracking database changes, there are 2 general approaches that can be taken.
- Add columns and triggers to the tables being monitored.
- Use SQL Server Change Tracking
In general – I find Database Administrators quite reluctant to add columns to tables for this kind of work. Generally – the application is in production when someone decides that they would like to be able to add a disconnected approach, and it is often considered to risky to add these additional tracking columns and triggers. Other people don’t like mixing the data we need to store for an entity with metadata we use to embellish an entity. For this reason – I’m going to focus on using SQL Server Change Tracking and further details on change tracking can be found on MSDN.
First step is to choose the data that your application needs to work with. I wanted an example that was as simple as possible so I chose the pubs database. pubs isn’t installed by default on SQL Server 2008, so I had to download the database from Microsoft. After attaching to SQL Server 2008 – I came across my first stumbling block – using the Local Database Caching wizard (more on this later) didn’t detect it as a database which I could use SQL Server Change Tracking on as “Use SQL Server change tracking” was disabled.
I struggled to find useful information on the internet about this using my favourite search engine (perhaps I was using poor search criteria?), but in the end I made a lucky guess – perhaps it didn’t like my database as it wasn’t really a SQL Server 2008 database? It was really a SQL Server 2000 database attached to SQL Server 2008. I quickly created a new database and used the Data Import Wizard to copy the “authors” table from pubs to my new database and while this was a step forwards as “Use SQL Server change tracking was now enabled”, my next stumbling block was that it wouldn’t let me add a new Cached Table - the Add button was disabled!
More lost time using my favourite search engine and another lucky guess solved my problem – perhaps it was to do with the way my authors table was created. The Data Import Wizard created my table as well as copying the contents, and it decided not to create a primary key. Sync Framework likes primary keys so a quick “Add Primary Key” operation later – and the Local Database Caching wizard was a lot happier.
To recap – when you are choosing your data source which you wish to use for an occasionally connection application and you want to use SQL Server Change Tracking – make sure its SQL Server 2008 and you have a primary key on any table that you want to synchronise.
Now we have our data that we want to synchronise – let’s start building our application. I’m going to use a simple WinForms Desktop application. Tweak the default form so we have a tab control with 2 tabs – one to display the contents on the server and one to display the contents on our client and some buttons to perform useful operations. I’ve built the following:
Right click the project and choose “Add New Item” and on the Data section – choose “Local Database Caching”. This will bring up the “Configure Data Synchronisation” wizard where you specify the data that you wish to use. Specify the Server connection for the source of the data and the Client connection should default to a new Compact database. Choose the table which you wish to cache by clicking the Add button. The dialog should look something like the following:
Next step in the wizard is to choose the database model, and I went with the Dataset approach. This is good for getting a prototype up and running, but if you are building something for a production environment – you might want to consider using an Entity Data Model.
You end up with a typed dataset for the local version of the authors table. Visual Studio will have added references to Microsoft.Synchronisation, Microsoft.Synchronisation.Data, Microsoft.Synchronisation.Server and Microsoft.Synchronisation.ServerCe but it managed to completely screw up the references on my machine. I’m working on a 64 bit machine and it created a strange hybrid of 32 and 64 bit references. I manually removed and re-added all 64 bit references on my machine and then changed the target platform to be “Any CPU” (It had defaulted to x86).
Next step is populate the client DataGridView and this requires minimal coding. I have 2 form level variables as follows:
1: #region Variables
2:
3: private AuthorsDataSet authorsClientDataSet = new AuthorsDataSet();
4: private AuthorsDataSetTableAdapters.AuthorsTableAdapter authorsClientAdapter =
5: new AuthorsDataSetTableAdapters.AuthorsTableAdapter();
6:
7: #endregion
In the event handler for the click event for the populate button on the client tab – I have the following code:
1: /// <summary>
2: /// Populate the client data grid view control
3: /// </summary>
4: /// <param name="sender"></param>
5: /// <param name="e"></param>
6: private void buttonClientAuthorsPopulate_Click(object sender, EventArgs e)
7: {
8: try
9: {
10: authorsClientAdapter.Fill(authorsClientDataSet.Authors);
11:
12: this.dataGridViewClientAuthors.DataSource = authorsClientDataSet.Authors;
13: }
14: catch (Exception Ex)
15: {
16: MessageBox.Show("Failed to populate the client - perhaps the client compact " +
17: "database hasn't been created yet!" + Environment.NewLine + Ex.Message);
18: }
19: }
To make our prototype easy to test – it’s useful to be able to change and save the data using the application (rather than testing by changing the data directly in the database), so I implemented the save functionality for the client as follows:
1: /// <summary>
2: /// Save any changes on the client
3: /// </summary>
4: /// <param name="sender"></param>
5: /// <param name="e"></param>
6: private void buttonClientSave_Click(object sender, EventArgs e)
7: {
8: try
9: {
10: this.Validate();
11: this.dataGridViewClientAuthors.EndEdit();
12: this.authorsClientAdapter.Update(this.authorsClientDataSet);
13:
14: PopulateClient();
15: MessageBox.Show("Saved");
16: }
17: catch(Exception ex)
18: {
19: MessageBox.Show("Error " + ex.Message);
20: }
21: }
In order to make the server tab work, I decided to manually add a typed dataset for the server version of the authors table. There were a number of ways I could have made the server tab work, but using a typed dataset meant that the code looked very similar to the client version.
You should now have working versions of the server and client tabs which allow you to view, change and save values. Next step is to implement the Synchronisation functionality – we want the user to be able to perform synchronisations between their local database and the central server database. Initial implementation for performing the synchronisation is very straightforward:
1: /// <summary>
2: /// Perform a database change sync between our client and the server.
3: /// </summary>
4: /// <param name="sender"></param>
5: /// <param name="e"></param>
6: private void buttonSync_Click(object sender, EventArgs e)
7: {
8: AuthorCacheSyncAgent syncAgent = new AuthorCacheSyncAgent();
9: Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
10:
11: PopulateServer();
12: PopulateClient();
13:
14: StringBuilder message = new StringBuilder();
15: message.Append("Changes downloaded: ");
16: message.Append(syncStats.TotalChangesDownloaded.ToString());
17: message.AppendLine();
18: message.Append("Changes uploaded: ");
19: message.Append(syncStats.TotalChangesUploaded.ToString());
20:
21: MessageBox.Show(message.ToString());
22: }
When I tried to run my application for the first time – it failed with the following error: “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.” More frantic searching but at least I found other people having the same problem. The easiest way to get rid of this exception is to set the “Copy to Output Directory” for your local compact database to “Do not copy”.
You should now have a working “occasionally connected application” which can synchronise changes from the server to your client. However – what if you want to be able to synchronise both ways? This is actually easy to achieve – you just need to implement the OnInitialized() method in the cache file that the wizard generated.
1: /// <summary>
2: /// Code generated Sync Agent which we can extend
3: /// </summary>
4: public partial class AuthorCacheSyncAgent
5: {
6: /// <summary>
7: /// Initialise the sync agent to perform a bidirectional sync.
8: /// </summary>
9: partial void OnInitialized()
10: {
11: Authors.SyncDirection = SyncDirection.Bidirectional;
12: }
13: }
This allows you to perform a 2 way sync. However – what happens if you change a record on both the server and the client and perform a sync. The behaviour you should find is that the server always wins and the client change is thrown away. What happens if this isn’t the behaviour you require? You can add custom conflict handling by extending the client and server sync provider that the wizard generated. Delve into the generated code to find the names of the client and server providers, and create a partial class for the client provider as follows:
1: /// <summary>
2: /// Extends the code generated client sync provider for authors.
3: /// </summary>
4: public partial class AuthorCacheClientSyncProvider
5: {
6: /// <summary>
7: /// Allows the main form to configure the event handler for the
8: /// ApplyChangeFailed event
9: /// </summary>
10: public void AddHandlers()
11: {
12: this.ApplyChangeFailed +=
13: new System.EventHandler<ApplyChangeFailedEventArgs>
14: (AuthorCacheClientSyncProvider_ApplyChangeFailed);
15: }
16:
17: /// <summary>
18: /// Allow the client to win a conflict by choosing to continue.
19: /// </summary>
20: /// <param name="sender"></param>
21: /// <param name="e"></param>
22: private void AuthorCacheClientSyncProvider_ApplyChangeFailed(object sender,
23: Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs e)
24: {
25:
26: if (e.Conflict.ConflictType ==
27: Microsoft.Synchronization.Data.ConflictType.ClientUpdateServerUpdate)
28: {
29: e.Action = Microsoft.Synchronization.Data.ApplyAction.Continue;
30: }
31: }
32: }
Similarly – create a partial class for the server provider as follows:
1: /// <summary>
2: /// Extend the server sync provider for authors to allow a client to win a conflict.
3: /// </summary>
4: public partial class AuthorCacheServerSyncProvider
5: {
6: /// <summary>
7: /// Hooks up the ApplyChangeFailed event handler.
8: /// </summary>
9: partial void OnInitialized()
10: {
11: this.ApplyChangeFailed +=
12: new System.EventHandler<ApplyChangeFailedEventArgs>
13: (AuthorCacheServerSyncProvider_ApplyChangeFailed);
14: }
15:
16: /// <summary>
17: /// When a conflict occurs, force write the client change.
18: /// </summary>
19: /// <param name="sender"></param>
20: /// <param name="e"></param>
21: private void AuthorCacheServerSyncProvider_ApplyChangeFailed(object sender,
22: Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs e)
23: {
24: if (e.Conflict.ConflictType ==
25: Microsoft.Synchronization.Data.ConflictType.ClientUpdateServerUpdate)
26: {
27: string message = "A client/server conflict was detected at the server.";
28: MessageBox.Show(message);
29: e.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite;
30: }
31: }
32: }
Finally you need to tweak the implementation to perform the synchronisation as follows:
1: /// <summary>
2: /// Perform a database change sync between our client and the server.
3: /// </summary>
4: /// <param name="sender"></param>
5: /// <param name="e"></param>
6: private void buttonSync_Click(object sender, EventArgs e)
7: {
8: AuthorCacheSyncAgent syncAgent = new AuthorCacheSyncAgent();
9: AuthorCacheClientSyncProvider clientSyncProvider =
10: (AuthorCacheClientSyncProvider)syncAgent.LocalProvider;
11: clientSyncProvider.AddHandlers();
12:
13: Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
14:
15: PopulateServer();
16: PopulateClient();
17:
18: StringBuilder message = new StringBuilder();
19: message.Append("Changes downloaded: ");
20: message.Append(syncStats.TotalChangesDownloaded.ToString());
21: message.AppendLine();
22: message.Append("Changes uploaded: ");
23: message.Append(syncStats.TotalChangesUploaded.ToString());
24:
25: MessageBox.Show(message.ToString());
26: }
The latest implementation now means that the client always wins and the server change is throw away.. In reality you might want to prompt the user or log the details and allow manual intervention, but at least we can control how we manage the conflict.
This prototype application can be downloaded from here. You will need to create a SQL Server 2008 database with an Authors table containing the standard authors information. I used a SQL ConnectionString for a user called sqlsync but you can edit these in the app.config.
Hopefully you will agree with me that using the Local Database Cache means that we don’t have to write much code in order to produce a working occasionally connected application. However – the main problems are limited example in the public domain and some puzzling times when things don’t work quite as expected!
Technorati Tags:
Sync Framework