Walkthrough: Extending the Local Database Cache to Support Bidirectional Synchronization

Article
12/03/2008

In Visual Studio 2008, the Local Database Cache configures a SQL Server Compact 3.5 database and a set of partial classes that enable Microsoft Synchronization Services for ADO.NET. 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 Synchronization 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.

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 3.5 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:

partial void OnInitialized()
{
    this.Customers.SyncDirection = 
    Microsoft.Synchronization.Data.SyncDirection.Bidirectional;
}

Private Sub OnInitialized()
    Me.Customers.SyncDirection = 
    Microsoft.Synchronization.Data.SyncDirection.Bidirectional
End Sub

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:

MessageBox.Show("Changes downloaded: " +
    syncStats.TotalChangesDownloaded.ToString() + 
    Environment.NewLine +
    "Changes uploaded: " + 
    syncStats.TotalChangesUploaded.ToString());

MessageBox.Show("Changes downloaded: " & _
    syncStats.TotalChangesDownloaded.ToString & _
    Environment.NewLine & _
    "Changes uploaded: " & _
    syncStats.TotalChangesUploaded.ToString)

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 Synchronization Services, a row is in conflict when it has been changed at both the client and server between synchronizations. Synchronization Services 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."

Note

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:

public partial class NorthwindCacheServerSyncProvider
{

    partial void OnInitialized()
    {
        this.ApplyChangeFailed +=
            new System.EventHandler<Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs>
            (NorthwindCacheServerSyncProvider_ApplyChangeFailed);
    }

    private void NorthwindCacheServerSyncProvider_ApplyChangeFailed(object sender,
        Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs e)
    {

    if (e.Conflict.ConflictType ==
        Microsoft.Synchronization.Data.ConflictType.ClientUpdateServerUpdate)
        {

        //Resolve a client-update/server-update conflict by force writing
        //the client change to the server database.
        System.Windows.Forms.MessageBox.Show("A client update / server update conflict " +
                                                "was detected at the server.");
        e.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite;

        }

    }
}

public partial class NorthwindCacheClientSyncProvider
{

    public void AddHandlers()
    {
        this.ApplyChangeFailed +=
            new System.EventHandler<Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs>
            (NorthwindCacheClientSyncProvider_ApplyChangeFailed);
    }

    private void NorthwindCacheClientSyncProvider_ApplyChangeFailed(object sender,
        Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs e)
    {

        if (e.Conflict.ConflictType ==
            Microsoft.Synchronization.Data.ConflictType.ClientUpdateServerUpdate)
        {

            //Resolve a client-update/server-update conflict by keeping the 
            //client change.
            e.Action = Microsoft.Synchronization.Data.ApplyAction.Continue;

        }

    }
}

In Form1.cs, edit the code in the SynchronizeButton_Click event handler so that it calls the AddHandler method that you added to NorthwindCache.cs in the previous step:

NorthwindCacheSyncAgent syncAgent = new NorthwindCacheSyncAgent();

NorthwindCacheClientSyncProvider clientSyncProvider =
    (NorthwindCacheClientSyncProvider)syncAgent.LocalProvider;
clientSyncProvider.AddHandlers();

Microsoft.Synchronization.Data.SyncStatistics syncStats = 
    syncAgent.Synchronize();

For Visual Basic, in NorthwindCache.vb add the following code after the End Class statement for the NorthwindCacheSyncAgent class.

Partial Public Class NorthwindCacheServerSyncProvider

    Private Sub NorthwindCacheServerSyncProvider_ApplyChangeFailed( _
        ByVal sender As Object, ByVal e As  _
        Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) _
        Handles Me.ApplyChangeFailed

        If e.Conflict.ConflictType = _
            Microsoft.Synchronization.Data.ConflictType.ClientUpdateServerUpdate Then

            'Resolve a client-update/server-update conflict by force writing
            'the client change to the server database.
            MessageBox.Show("A client update / server update conflict was detected at the server.")
            e.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite

        End If

    End Sub

End Class

Partial Public Class NorthwindCacheClientSyncProvider

    Private Sub NorthwindCacheClientSyncProvider_ApplyChangeFailed( _
        ByVal sender As Object, ByVal e As  _
        Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) _
        Handles Me.ApplyChangeFailed

        If e.Conflict.ConflictType = _
            Microsoft.Synchronization.Data.ConflictType.ClientUpdateServerUpdate Then

            'Resolve a client-update/server-update conflict by keeping the 
            'client change.
            e.Action = Microsoft.Synchronization.Data.ApplyAction.Continue

        End If

    End Sub

End 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.

Note

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. We have redefined these commands 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 Synchronization Services, 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.

public partial class CustomersSyncAdapter
{

    partial void OnInitialized()
    {

    //Redefine the insertCommand so that it does not insert values 
    //into the CreationDate and LastEditDate columns.
    System.Data.SqlClient.SqlCommand insertCommand = new System.Data.SqlClient.SqlCommand();

    insertCommand.CommandText = "INSERT INTO dbo.Customers ([CustomerID], [CompanyName], " +
        "[ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], " +
        "[Country], [Phone], [Fax] )" +
        "VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, " +
        "@Region, @PostalCode, @Country, @Phone, @Fax) SET @sync_row_count = @@rowcount";
    insertCommand.CommandType = System.Data.CommandType.Text;
    insertCommand.Parameters.Add("@CustomerID", System.Data.SqlDbType.NChar);
    insertCommand.Parameters.Add("@CompanyName", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@ContactName", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@ContactTitle", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@Address", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@City", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@Region", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@PostalCode", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@Country", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@Phone", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@Fax", System.Data.SqlDbType.NVarChar);
    insertCommand.Parameters.Add("@sync_row_count", System.Data.SqlDbType.Int);
    insertCommand.Parameters["@sync_row_count"].Direction = 
        System.Data.ParameterDirection.Output;

    this.InsertCommand = insertCommand;


    //Redefine the updateCommand so that it does not update values 
    //in the CreationDate and LastEditDate columns.
    System.Data.SqlClient.SqlCommand updateCommand = new System.Data.SqlClient.SqlCommand();

    updateCommand.CommandText = "UPDATE dbo.Customers SET [CompanyName] = @CompanyName, [ContactName] " +
        "= @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] " +
        "= @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, " +
        "[Phone] = @Phone, [Fax] = @Fax " +
        "WHERE ([CustomerID] = @CustomerID) AND (@sync_force_write = 1 " +
        "OR ([LastEditDate] <= @sync_last_received_anchor)) SET @sync_row_count = @@rowcount";
    updateCommand.CommandType = System.Data.CommandType.Text;
    updateCommand.Parameters.Add("@CompanyName", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@ContactName", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@ContactTitle", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@Address", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@City", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@Region", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@PostalCode", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@Country", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@Phone", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@Fax", System.Data.SqlDbType.NVarChar);
    updateCommand.Parameters.Add("@CustomerID", System.Data.SqlDbType.NChar);
    updateCommand.Parameters.Add("@sync_force_write", System.Data.SqlDbType.Bit);
    updateCommand.Parameters.Add("@sync_last_received_anchor", System.Data.SqlDbType.DateTime);
    updateCommand.Parameters.Add("@sync_row_count", System.Data.SqlDbType.Int);
    updateCommand.Parameters["@sync_row_count"].Direction = 
        System.Data.ParameterDirection.Output;

    this.UpdateCommand = updateCommand;

    }
}

Partial Public Class CustomersSyncAdapter
    Private Sub OnInitialized()

        'Redefine the insertCommand so that it does not insert values 
        'into the CreationDate and LastEditDate columns.
        Dim insertCommand As New System.Data.SqlClient.SqlCommand
        With insertCommand
            .CommandText = "INSERT INTO dbo.Customers ([CustomerID], [CompanyName], " & _
                "[ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], " & _
                "[Country], [Phone], [Fax] )" & _
                "VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, " & _
                "@Region, @PostalCode, @Country, @Phone, @Fax) SET @sync_row_count = @@rowcount"
            .CommandType = System.Data.CommandType.Text
            .Parameters.Add("@CustomerID", System.Data.SqlDbType.NChar)
            .Parameters.Add("@CompanyName", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@ContactName", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@ContactTitle", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Address", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@City", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Region", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@PostalCode", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Country", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Phone", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Fax", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@sync_row_count", System.Data.SqlDbType.Int)
            .Parameters("@sync_row_count").Direction = ParameterDirection.Output
        End With

        Me.InsertCommand = insertCommand

        'Redefine the updateCommand so that it does not update values 
        'in the CreationDate and LastEditDate columns.
        Dim updateCommand As New System.Data.SqlClient.SqlCommand
        With updateCommand
            .CommandText = "UPDATE dbo.Customers SET [CompanyName] = @CompanyName, [ContactName] " & _
                "= @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] " & _
                "= @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, " & _
                "[Phone] = @Phone, [Fax] = @Fax " & _
                "WHERE ([CustomerID] = @CustomerID) AND (@sync_force_write = 1 " & _
                "OR ([LastEditDate] <= @sync_last_received_anchor)) SET @sync_row_count = @@rowcount"
            .CommandType = System.Data.CommandType.Text
            .Parameters.Add("@CompanyName", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@ContactName", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@ContactTitle", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Address", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@City", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Region", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@PostalCode", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Country", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Phone", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@Fax", System.Data.SqlDbType.NVarChar)
            .Parameters.Add("@CustomerID", System.Data.SqlDbType.NChar)
            .Parameters.Add("@sync_force_write", System.Data.SqlDbType.Bit)
            .Parameters.Add("@sync_last_received_anchor", System.Data.SqlDbType.DateTime)
            .Parameters.Add("@sync_row_count", System.Data.SqlDbType.Int)
            .Parameters("@sync_row_count").Direction = ParameterDirection.Output
        End With

        Me.UpdateCommand = updateCommand

    End Sub

End Class

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:
1. Synchronization Services identifies that a row was changed at the client.
2. 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. Synchronization Services effectively performs a "dummy update" to the table.
3. 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 Synchronization Tasks.

Walkthrough: Extending the Local Database Cache to Support Bidirectional Synchronization

Prerequisites

Setting Synchronization Direction

Add Code to Handle Synchronization Conflicts

Removing Server Tracking Columns from Synchronization Commands

Conclusion

See Also

Concepts

Additional resources