How to: Exchange Bidirectional Incremental Data Changes Between a Client and Server
This topic shows you how to exchange data changes by performing bidirectional synchronization between a server database and a SQL Server Compact client database. The examples in this topic focus on the following Sync Framework types and events:
For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Client and Server Synchronization Tasks.
The example code in this topic shows how to configure bidirectional synchronization for the Customer table from the Sync Framework sample database. For an overview of the properties that are used to specify synchronization direction, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.
Bidirectional synchronization requires that incremental data changes be tracked in and applied to the server and client databases. Sync Services handles the client database. However, you must configure the server database and specify the commands that select changes from and apply changes to the server database. The example code includes those commands. For information about how to configure the server database, see Tracking Changes in the Server Database.
Key Parts of the API
This section provides code examples that point out the key parts of the API that are used in bidirectional synchronization. The following code example specifies synchronization direction and how the table should be created on the client.
The following code example specifies a command to retrieve a new anchor value from the server. In this case, a timestamp value is retrieved and stored in the client database. During each synchronization, the new anchor value and the last anchor value from the previous synchronization are used: the set of changes between these upper and lower bounds is synchronized. You will see anchor parameters used in the commands that select changes from the server database.
The following code example specifies a command to select incremental updates from the server to apply to the client. This is a SELECT statement that includes the columns to be synchronized. There is a WHERE clause that uses tracking columns and the anchor values that the SelectNewAnchorCommand retrieves. The WHERE clause also includes UpdateId <> @sync\_client\_id. This parameter prevents uploaded changes from being immediately downloaded back to the client during the same synchronization. If the application requires changes to be echoed back to the client, omit this statement. For information about how to filter data that is downloaded, see How to: Filter Rows and Columns.
The following code example specifies a command to apply updates from the client to the server. This is an UPDATE statement that includes the columns that are to be updated. There is a WHERE clause that again uses anchor values and the client ID. The WHERE clause also includes @sync\_force\_write = 1. This parameter forces conflicting updates to be applied. For more information about @sync\_force\_write and other session variables, see How to: Use Session Variables.
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. Also be aware of how SyncStatistics is used to display information about the synchronization session, and how the CreatingSchema and SchemaCreated events are used to add a default for the CustomerId column. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.