If you’ve been using Data Source Configuration Wizard since Visual Studio 2005, you’ll notice that with Visual Studio 2008 Service Pack 1, there’s a “small” change – on Choose Your Database Objects page, a new checkbox would allow you to “enable local database caching”:

clip_image001

This checkbox will be available if the current connection is to a SQL Server database. The technology behind is Microsoft Synchronization Service for ADO.NET, which lets you synchronize data among any kind of data sources that support ADO.NET. If your client application does not always have a network connection, or if the application requires good data loading performance regardless of the network condition, you should consider using this feature to create a local copy of the data on the client and have your DataSet work against this local data cache. Another benefit is scalability. You can use local database caches for read-only data or rarely-changing data to increase database scalability. For more information of the technology, please click here.

If this checkbox is checked, the next page will let you choose among all the data tables in the DataSet, which tables you’d like to cache.

clip_image002

Something worth mentioning on this page are:

  1. Not all tables can be synchronized. Tables that don’t have a primary key, for example, cannot be synchronized with the wizard.
  2. There’re two synchronization modes: Incremental mode synchronizes only changes that were made on the server since the last synchronization. Snapshot mode replaces the whole locally cached table with the current table on the server.
  3. If you’re using SQL Server 2008, another checkbox will let you take advantage of the change tracking feature. With this feature enabled, for incremental mode of synchronization, you don’t need to create timestamp column or tombstone table [BAM3] to track all the changes since the last synchronization (timestamp columns will track each data item’s insert and update time; a tombstone table will track each data item’s delete time) . SQL Server 2008 will do it for you.

After selecting these settings, the Data Sources Configuration Wizard will prompt you to generate and run a script if you have tables with incremental mode synchronization. The script will help you turn on the SQL Server 2008 change tracking feature, or if you didn’t enable this feature, the script will help you generate all the necessary columns or table to track insert, update and delete operations since last sync.

clip_image003

Now a DataSet, a sync file, and a local database cache are added into the project. The DataSet will have two connection strings, one to the remote database, another to the local database cache. The application works fine at first because the server tables and client tables have identical data. However, of course, sooner or later, the two databases will be out of sync. To synchronize the local database cache with the remote one is pretty simple: call Synchronize method of the SyncAgent. For more information, please check this walkthrough: Creating an Occasionally Connected Application by Using the Data Source Configuration Wizard.

Please pay attention if you reopen the Data Source Configuration Wizard and make updates to the synchronization configuration. It might cause the DataSet to be regenerated. If you have any customization to the DataSet, please remember to back it up beforehand.

Enjoy coding and syncing!

Cheers!