Walkthrough: Extending the Local Database Cache to Support Bidirectional Synchronization
In Visual Studio 2008, the Local Database Cache configures a SQL Server Compact database and a set of partial classes that enable Sync Framework. Because Visual Studio generates partial classes, you can write code to add synchronization functionality and still keep the ability to view and change settings in the Configure Data Synchronization dialog box. For more information about the Local Database Cache and partial classes, see the Visual Studio 2008 documentation.
By default, the Configure Data Synchronization dialog box enables you to configure Sync Services for download scenarios only. This means that after you configure data synchronization, calling Synchronize will only download changes from the server to the client database. One of the most common ways to extend the synchronization code is to configure bidirectional synchronization. This lets you upload changes from the client to the server. To enable bidirectional synchronization, we recommend that you extend the generated code in the following ways:
Set the synchronization direction to bidirectional.
Add code to handle synchronization conflicts.
Remove server tracking columns from synchronization commands.
Nota
Visual Studio 2008 uses Sync Framework for ADO.NET 1.0 when it generates code for the Local Database Cache.
Prerequisites
Before you start this walkthrough, you must complete the following walkthrough in the Visual Studio 2008 documentation: "Walkthrough: Creating an Occasionally Connected Application". After you complete that walkthrough, you have a project that contains a Local Database Cache and a Windows Form application that lets you download changes from the Northwind Customers table to a SQL Server Compact database. You are now ready to load this walkthrough solution and add bidirectional functionality.
To open the OCSWalkthrough solution
Open Visual Studio.
On the File menu, open an existing solution or project and locate the OCSWalkthrough solution. This is the OCSWalkthrough.sln file.
Setting Synchronization Direction
The Configure Data Synchronization dialog box sets the SyncDirection property to either DownloadOnly or Snapshot. To enable bidirectional synchronization, set the SyncDirection property to Bidirectional for each table that you want to enable for uploading of changes.
To set synchronization direction
Right-click NorthwindCache.sync, and select View Code. The first time that you do this, Visual Studio creates a NorthwindCache class file under the NorthwindCache.sync node in Solution Explorer. This file contains a NorthwindCacheSyncAgent partial class, and you can add other classes as needed.
In the NorthwindCache class file, add a line of code to the NorthwindCacheSyncAgent.OnInitialized() method:
Open Form1 in the Code Editor.
In the Form1 file, edit the line of code in the SynchronizeButton_Click event handler so that it includes upload and download statistics:
To synchronize and view statistics
Press F5.
In the form, update a record, and then click the Save button on the toolbar.
Click Synchronize Now.
A message box that contains information about synchronized records appears. The statistics show that one row was uploaded and one was downloaded, even though no changes were made on the server. The additional download occurs because changes from the client are returned to the client after they are applied at the server. For more information, see "Determining Which Client Made a Data Change" in How to: Use a Custom Change Tracking System.
Click OK to close the message box, but leave the application running.
To synchronize and view conflict resolution
In the form, update a record, and then click the Save button.
With the application still running, use Server Explorer/Database Explorer (or another database management tool) to connect to the server database.
To demonstrate the default behavior for conflict resolution, in Server Explorer/Database Explorer, update the same record that you updated in the form but to a different value, and commit the change. (Move off the modified row.)
Go back to the form, and then click Synchronize Now.
Verify the update in the application grid and server database. Notice that the update that you made at the server has overwritten the update at the client. For information about how to change this conflict resolution behavior, see the next section of this topic, "Add Code to Handle Synchronization Conflicts."
Add Code to Handle Synchronization Conflicts
In Sync Framework, a row is in conflict when it has been changed at both the client and server between synchronizations. Sync Framework provides a set of features that you can use to detect and resolve conflicts. In this walkthrough, you will add basic handling for conflicts in which the same row was updated at the client and server. Other kinds of conflicts include a row being deleted in one database and updated in another, or rows that have duplicate primary keys being inserted into both databases. For more information about how to detect and resolve conflicts, see How to: Handle Data Conflicts and Errors.
To add conflict handling
Add code to handle the server ApplyChangeFailed event and the client ApplyChangeFailed event. These events are fired when a row cannot be applied because of a conflict or an error. The methods that handle these events in the sample code check for the kind of conflict and specify that client-update/server-update conflicts should be resolved by forcing the client change to be written to the server database. The synchronization command that applies updates to the server database includes logic to recognize when a change should be forced. This command is included in the code in the next section of this topic, "Removing Server Tracking Columns from Synchronization Commands."
Nota
The sample code provides a basic example of conflict handling. The way in which you handle conflicts depends on the requirements of your application and business logic.
How you add code for C# differs from Visual Basic:
For C#, add code to NorthwindCache.cs and Form1.cs. In NorthwindCache.cs, add the following code after the end of the NorthwindCacheSyncAgent class:
In Form1.cs, edit the code in the SynchronizeButton_Click event handler so that it calls the AddHandlers method that you added to NorthwindCache.cs in the previous step:
For Visual Basic, in NorthwindCache.vb add the following code after the End Class statement for the NorthwindCacheSyncAgent class.
To synchronize and view conflict resolution
Press F5.
In the form, update a record, and then click the Save button.
In Server Explorer/Database Explorer, update the same record that you updated in the form but to a different value, and commit the change.
Go back to the form, and then click Synchronize Now.
Verify the update in the application grid and server database. Notice that the update that you made at the client has overwritten the update at the server.
Removing Server Tracking Columns from Synchronization Commands
When the Local Database Cache is created, the columns that are used to track changes in the server database are downloaded to the client. (In this walkthrough, the columns are CreationDate and LastEditDate.) To support bidirectional synchronization and to help ensure convergence of data at the client and server, remove these columns from the SQL commands that apply changes to the server database. You can also remove the columns from the commands that select changes from the server to apply to the client, but this is not required. Because of restrictions on some schema changes in the client database, the columns cannot be dropped. For more information about synchronization commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.
Nota
If you use SQL Server change tracking, tracking columns are not added to your tables. In this case, you do not have to change the commands that apply changes to the server.
To remove tracking columns from synchronization commands
Add the following code to the NorthwindCache class (NorthwindCache.vb or NorthwindCache.cs) after the End Class statement for the NorthwindCacheServerSyncProvider class. This code redefines two commands that are set as properties on the SyncAdapter object for the Customers table: the InsertCommand and UpdateCommand properties. The commands that were generated by the Configure Data Synchronization dialog box contained references to the CreationDate and LastEditDate columns. These commands were redefined in the OnInitialized method of the CustomersSyncAdapter class. The DeleteCommand property is not redefined because it does not affect the CreationDate or LastEditDate columns.
The variables in each SQL command are used to pass data and metadata between Sync Framework, the client, and the server. The following session variables are used in the commands below:
@sync\_row\_count: Returns the number of rows that were affected by the last operation at the server. In SQL Server databases, @@ROWCOUNT provides the value for this variable.
@sync\_force\_write: Used to force applying a change that failed because of a conflict or an error.
@sync\_last\_received\_anchor: Used to define the set of changes to be synchronized during a session.
For more information about session variables, see How to: Use Session Variables.
To synchronize and view a tracking column update
Press F5.
In the form, update a record by changing a value in the LastEditDate column, and then click the Save button.
Go back to the form, and then click Synchronize Now.
Verify the update in the application grid and server database. Notice that the column value from the server has overwritten the update at the client. The update process is as follows:
Sync Framework identifies that a row was changed at the client.
During synchronization the row is uploaded and applied to the table in the server database. However, the tracking columns are not included in the update statement. Sync Framework effectively performs a "dummy update" to the table.
The row is now returned to the client, but the commands that select changes from the server do include the tracking columns. Therefore, the change that was made at the client is overwritten by the value from the server.
Conclusion
In this walkthrough, you have configured bidirectional synchronization with basic conflict handling, and have addressed the potential issue of having server tracking columns in the client database. By using partial classes, you can extend the Local Database Cache code in other significant ways. For example, you could redefine the SQL commands that select changes from the server database so that data is filtered when it is downloaded to the client. We recommend that you read the how-to topics in this documentation to understand the ways in which you can add or change synchronization code to meet the requirements of your applications. For more information, see Programming Common Client and Server Synchronization Tasks.