Wednesday, October 28, 2009

Using Local Database Cache for Occasionally Connected Data Synchronizing

After getting everything setup using the Visual Studio template and configuration wizards, I found that the server table was not being updated during the synchronization. Using SQL Server Profiler, I could see the SELECT queries that were interrogating the server instance to see if there were changes on the server, but I saw no indication of UPDATE or INSERT statements. I ran across a helpful blog post that indicated the default behavior of the Sync process is DownloadOnly where changes to the central database are downloaded to the local cached database. A simple change to OnInitialized event in the code-behind file of the .sync file resolved that issue easily.

   1: Private Sub OnInitialized()
   2:  ' ProductionHistoryDetail is
   3:  ' the name of the cached table
   4:  Me.ProductionHistoryDetail.SyncDirection = _
   5:     Microsoft.Synchronization.Data.SyncDirection.Bidirectional
   7: End Sub

There were two other issues to overcome:

  1. While testing what would happen if the database was taken offline between synchronization, I found that the .Net SQLClient Data Provider connection that the sync framework provider was using would block the process where the command was issued to set the database offline (ALTER DATABASE <databasename> SET OFFLINE).
  2. Once the database went offline, the application would throw an exception when it attempted to connect to the server database to synchronize with the local SQL CE 3.5 database.


  1. ?? Still investigating.
  2. Wrap the call to syncAgent.Synchronize() in a try/catch block. Surely there are methods to determine ability to connect to the db more efficiently than catching an exception.

No comments: