Bidirectional synchronization with ADO.NET Sync Services and SQL Server 2005

So as you may have come to realize: Visual Studio 2008’s Data Synchronization designer only sets up your code for DownloadOnly Synchronization. You may have tried implementing the OnInitialized partial method to configure your SyncTable’s SyncDirection as Bidirectional. But then you may discover that synchronization fails on data with a master-detail relationship. This failure is due to the incorrect sequence of adding SyncTables to the SyncTable collection of the SyncAgent.Configuration property. So how do you control the order in which your SyncTables are added to the SyncAgent.Configuration.SyncTables collection? Well, with the code generated by Visual Studio 2008 I don’t think there is an easy or effective way. And you obviously don’t want to have to write all the generated code manually just to get control over the sync process. So the good news is, you don’t have to.

 My solution:

I have created T4 templates that automatically generate the code you need for bidirectional synchronization. “T4 is a Microsoft Language for writing Code Generators which is similar to ASP.NET and is used by Microsoft internally to build software and new features for .NET 4.0″. T4 template execution is built into Visual Studio 2008 so you won’t need to download any plug-in. The templates are not going to magically know in which sequence to add the SyncTables to the SyncAgent.Configuration.SyncTables collection, but it at least gives you the ability to change that sequence yourself. The sequence should be (1st – master table , 2nd details table).

T4 templates automatically works in Visual Studio 2008 as it is also used to generate code such as the dbml files used by LINQ. However, to get intellisense for T4 templates you’ll need to download  T4 Tangible Modelling Tools. You may want to customize the templates to conform to the namespaces you use in your project and to use the connectionstrings for your databases.

You’ll find the solution here.

Here is a quick rundown of the code being generated for you:

 
2 Projects

• GlobalCode (I moved all C# code to this library to make debugging easier)
• SyncServCodeGen (These are the T4 templates which generates the code)

01_GeneratedSQLToConfigureServerDB.tt

This template generates the SQL statements you use to configure your server database for synchronization (adding tombstone tables and LastEditDate + CreationDate columns). The code generated by the templates knows not to sync the LastEditDate en CreationDate columns to the client, as this will cause unnecessary conflicts with data on the server.

Customization: Change the values for serverName, databaseName to point to your database.

Important: Please backup your server database before running these scripts. I have not tested these scripts thoroughly.

02_GenerateCRUDProcs.tt

This template generates the stored procedures for your SQL Server 2005 server database to speed up data synchronization access to the database.

Customization: Change the values for serverName, databaseName to point to your database.

03_GenerateSyncAdapterHelper.tt

This template generates a class containing all the SyncAdapters for all the tables that are going to take part in synchronization.

Customization: Change the values for serverName, databaseName to point to your database.

04_GenerateServerSyncProvider.tt

This template generates the ServerSyncProvider class where all the SyncAdapters are added to.

Customization: Change the values for serverName, databaseName to point to your database.

05_GenerateWCFServiceInterface.tt

This template generates the interface (Service Contract) used for the WCF web service to sync data to and from the server.

06_GenerateWCFService.tt

This template generates the WCF web service that you can expose through your web server (IIS).

07_GenerateSyncTableDeclarations.tt

This template generates all the SyncTables for all the tables that are going to take part in synchronization. These classes are used when the tables on the client are created.

Customization: Change the values for serverName, databaseName to point to your database.

08_GenerateClientSyncProvider.tt

This template generates the ClientSyncProvider class which only determines the connectionstring on the client.

09_GenerateSyncAgent.tt

This is the most important template. This is also the one that you want to customize for you exact needs.  I use one SyncGroup for all tables. The SyncGroup represents a transaction boundary for synchronization. All my tables relate to one another directly or indirectly so one SyncGroup makes sense in my case (and most cases). You can change the sequence in which the SyncTables are added to the SyncAgent.Configuration.SyncTables collection in the InitializeSyncTables() function.

Note: I used the name “WCFSyncServiceProxy” for the web reference name. If you name the web reference something else, just update this template accordingly.