Walkthrough: Extending the Local Database Cache to Support Bidirectional Synchronization

Article
02/04/2013

You can add a Local Database Cache item to your project to configure a local SQL Server Compact 3.5 database cache and generate 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 partial classes, see How to: Split a Class into Partial Classes (Class Designer).

By default, the Configure Data Synchronization dialog box enables you to configure Synchronization Services for download 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 Walkthrough: Creating an Occasionally Connected Application. After you complete that walkthrough, you have a project that contains a Local Database Cache item and a Windows Forms 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.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

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 enables you to set 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 click View Code. The first time that you do this, Visual Studio creates a NorthwindCache 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 code so that the NorthwindCacheSyncAgent.OnInitialized() method looks like the following code:

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, modify 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)

Testing the Application

Now the application is configured to perform both uploads and downloads during synchronization.

To test the application

Press F5.
In the form, update a record, and then click the Save button (the disk icon 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 echoed back 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.

Now you will change the same record on both the client and the server to force a conflict (a concurrency violation) during synchronization.

To test the application and force a conflict

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 on the form, but change it 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 you made on the server has overwritten the update on the client. For information about how to change this conflict resolution behavior, see the next section of this topic, "Adding Code to Handle Synchronization Conflicts".

Adding Code to Handle Synchronization Conflicts

In Synchronization Services, a row is in conflict if it has been changed on both the client and server between synchronizations. Synchronization Services provides a set of features that can be used to detect and resolve conflicts. In this section, you will add basic handling for conflicts in which the same row is updated on 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 detecting and resolving conflicts, see How to: Handle Data Conflicts and Errors.

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.

Add code to handle the server ApplyChangeFailed event and the client ApplyChangeFailed event. These events are raised when a row cannot be applied because of a conflict or an error. The methods that handle these events 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."

The steps you perform to add conflict handling differ, depending on whether you are using C# or Visual Basic.

To add conflict handling

If you are using 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, modify 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();

If you are using 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 change it to a different value, and then 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 you made on the client has overwritten the update on 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 guarantee convergence of data on 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 2008 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.

The following 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. In the following code, these commands are redefined in the OnInitialized method of the CustomersSyncAdapter class. The DeleteCommand() property is not redefined because it does not affect the CreationDate or LastEditDate column.

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 on the server. In SQL Server databases, @@rowcount provides the value for this variable.
@sync\_force\_write: Used to force application of 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 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.

public partial class CustomersSyncAdapter
{

    partial void OnInitialized()
    {

    // Redefine the insert command 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 update command 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 insert command 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 update command 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

Testing the Application

To synchronize and view a tracking column update

Press F5.
On 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 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 on the client. The update process is as follows:
1. Synchronization Services determines that a row has changed on 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 echoed back to the client, but the commands that select changes from the server do include the tracking columns. Therefore, the change that was made on the client is overwritten by the value from the server.

Next Steps

In this walkthrough, you have configured bidirectional synchronization with basic conflict handling, and you 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 needs of your applications. For more information, see How to Program Common Client and Server Synchronization Tasks.

Walkthrough: Extending the Local Database Cache to Support Bidirectional Synchronization

Prerequisites

To open the OCSWalkthrough solution

Setting Synchronization Direction

To set synchronization direction

Testing the Application

To test the application

To test the application and force a conflict

Adding Code to Handle Synchronization Conflicts

To add conflict handling

To synchronize and view conflict resolution

Removing Server Tracking Columns from Synchronization Commands

To remove tracking columns from synchronization commands

Testing the Application

To synchronize and view a tracking column update

Next Steps

See Also

Concepts

Other Resources

Additional resources