Share via

Generate sync framework stored procedures using t4 templates

  • Article

In my previous post I gave a small heads up regarding Microsoft’s Sync Framework and one of the issues which I faced while using SQLCE as a client. To get the latest version of Sync Framework which at this point of time is the v2.0 CTP1 visit this link.

If you’ve read up on the Sync Framework then you should know that it can target a variety of stores. One of the server side stores that can be used is SQL Server 2008. SQL Server 2008 provides change tracking features that enable you to easily sync your data without losing changes.

However to use the powerful (read: troublesome) change tracking features you’d require a bit of ADO.NET code to perform some of the following tasks:

  1. Set change tracking to ON in the database, set the CHANGE_RETENTION (number of days changes are held) and whether to auto cleanup the changes.
  2. Enable change tracking for each table that will be participating in synchronization.
  3. Select any incremental inserts, updates or deletes that are happening on the server.
  4. Apply any incremental inserts, updates and deletes that are propagated from client to server.
  5. Get conflicting updates and deletes when changes are made to both client and server.
  6. Stored procedures to get a database-wide timestamp/version indicating when the last change was committed. (CHANGE_TRACKING_CURRENT_VERSION).

This bunch of ADO.NET code can be put into stored procedures and executed on the ‘08 Server. There is one small problem though: Points 3,4 and 5 above have to be repeated for each of the tables participating in synchronization. Well, not quite every point listed above for every table but almost... Let me explain below:

Upload Only Scenario

For a table on the server to work successfully with the upload only scenario requires that it has to have the following stored procedures:

  • SP to select incremental inserts – basically used to retrieve the schema of the table and send it to the client.
  • SP to apply inserts on the server.
  • SP to apply updates on the server.
  • SP to apply deletes on the server.

Download Only Scenario

In the download scenario a table has to be able to send back incremental inserts, updates and deletes that happened on the server back to the client. This means:

  • SP to select incremental inserts.
  • SP to select incremental updates.
  • SP to select incremental deletes.

Bidirectional Scenario

In the bidirectional scenario a table has to fulfill both scenarios above as well as be able to detect conflicts when rows are simultaneously updated or deleted on server and client.

  • SP to select incremental inserts.
  • SP to select incremental updates.
  • SP to select incremental deletes.
  • SP to apply inserts on the server.
  • SP to apply updates on the server.
  • SP to apply deletes on the server.
  • SP to get update conflict from the server.
  • SP to get delete conflict from the server.

In the case of your application having a handful of tables (hardly likely), you can manually go ahead and write these stored procedures. However you’d soon realize the repetitiveness in such a mundane task. Enter T4.

T4 is an acronym standing for text templating transformation toolkit. It’s basically a code generator hidden inside Visual Studio that you can use to wreak havoc :). The basic concept is that you create a file like  [AnyName].tt (notice the extension tt) and then run the transformation and it turns into [AnyName].cs or whatever extension you want it to have. You can interweave code and logic inside the .tt file and the result will be code generated in your output file. It’s a slightly tricky concept to get your head around at the first go, but persevere and you’ll find you can do amazing things with T4.

For a (yawn) intro to T4 visit the msdn site but to actually get productive with T4 and to see some sample code in action visit this post on Oleg Sych’s blog. Also go through the various articles on his blog, he’s done some pretty cool stuff with T4.

Although Visual Studio provides support for T4 they don’t make it really obvious, there’s no file-> new item template or Intellisense that will help you code on T4. You can download either Clarius T4 editor (look for the free edition) or Tangible Modelling Tools plus T4 editor.

Clarius does not have intellisense when you write code inside code blocks, this is present inside the Tangible Modelling tools download. If you’re ok with getting a UML modelling tool along with the T4 editor go for Tangible. (Note: Only some of the .NET class libraries are supported in the free edition of Tangible).

Coming back to this post, I’ve written a T4 template that contains the templates for all the stored procedures mentioned above. To use it perform the following steps:

  1. Download the attached ConsolidatedSyncScript.zip file.
  2. Extract the ConsolidatedSyncScript.tt file and add it to a new project in Visual Studio.
  3. Double click the ConsolidatedSyncScript.tt file and click Ctrl + S. If you get any warning saying that the generator could harm your computer (yeah right) just click Ok (you’re cool with it right).
  4. Once you’ve done Ctrl + S you should see the ConsolidatedSyncScript.sql file as a sub file to the .tt file.
  5. Enjoy!!!

The script that I’ve provided here basically creates the structure of the stored procs for a sample database and sample tables which have to participate in synchroniztion. To actually work with your own database you’ll have to modify some code in the .tt file to generate the stored procedures for all your tables. If you’re upto it go ahead, if not, mail me sometime and I can provide a utility that you point to a database and it will generate all the stored procs for you.

I love T4!!!

 

ConsolidatedSyncScript.zip

Comments