Microsoft Sync Framework is transport agnostic platform for automatic data synchronization across different types of datasources. It can be used for occasionally connected applications (smart clients) and p2p supplications. Previously we had to either use SQL server merge replication or write own synchronization framework. And now we have a solution from Microsoft that looks very promising. There is a good documentation for this on MSDN, if you need more details or code samples you will find them there.
While playing with Visual Studio 2008, I accidentally found new item type called "Local Database Cache". I turned out that this is really Microsoft Sync Framework integration with Visual Studio.
Note: I've tried this with SQL Server 2005. It may work different with SQL Server 2008 because there are new features for tracking data changes.
When you add this item to your solution, "Configure Data Synchronization" window is opened. There are plenty of settings like server database location, table selection, which column to use to compare etc…
Finally, when you finish configuration, Visual Studio does the following for you:
- Adds references to necessary assemblies
- Adds configuration strings for Server DB and for Client DB (SQLCE3.5)
- Adds SQLScripts and SQLUndo scripts to the solution. There is one script of each type per table. First creates new columns to track updates and inserts (you can select to use existing), triggers to fill update columns, "TombStone" tables (used to track deleted records). Undo scripts can be used to delete all above mentioned.
- Executes SQL scripts on your SQL Server.
- Adds client database
- Thoughtfully creates Dataset for you.
- Adds "sync" item to the solution. You can open sync configuration by clicking on this item. It also contains autogenerated code.
- Syncronizes databases for the first time.
After that you should be able to use the following code to synchronize your databases:
// Call SyncAgent.Synchronize() to initiate the synchronization process.
// Synchronization only updates the local database, not your project's data source.
LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
// TODO: Reload your project data source from the local database
// (for example, call the TableAdapter.Fill method).
Pretty simple, isn't it?
I've decided to check how it works. To do that I've modified column in one of the tables on the server, and executed the code to sync databases. After that I opened table in local database from Visual Studio. Unfortunately, my change was not reflected in the local table. Or fortunately, because it forced me to take a closer look on how it works. Running SQL Profiler and debugging the code revealed nothing. Then I decided to check my last suspicion and output local table contents into datagridview. That shown correct results! Syncronization worked just fine! The reason was the caching in visual studio table viewer (I pressed button with red exclamation sight, I swear).