locked
How to track column updates with SQLSyncProvider RRS feed

  • Question

  • I'm working with the Sync Framework 2.0 and experimenting with the DatabaseSync SQL and SQLCompact sample application. I want to be able to have oneof the Sql Compact clients change the value of one field and the other client change the value of a different field in the same record. Then when I sync the two clients with each other I want only the updated columns changed. 

    For example, Client A might change the name of the product in the order_details table and Client B might change the quantity. After I sync the clients peer to peer, I want the record to reflect the new product name and the new quantity. Currently, the tracking is at the row level so one client's changes are lost.

     

    Thursday, April 1, 2010 9:32 PM

Answers

  • Sync Fx doesnt support column level change tracking.

    however, you may be able to achieve what you want.

    when a row is change on the client and on the server, that will result to a conflict (ClientUpdateServerUpdate).

    You can listen to the ApplyChangesFailed event

    check for the conflict ConflictType.ClientUpdateServerUpdate

    retrieve the conflict rows for both client and server from ApplyChangeFailedEventArgs' Conflict property (e.Conflict.ServerChange and e.Conflict.ClientChange)

    then do a compare of the column changes you want to merge

    update winning row (either client or server) with the column changes you want to merge

    then resolve the conflict using the updated row with the merged column changes.

     

    hth

    Friday, April 2, 2010 3:53 AM

All replies

  • Sync Fx doesnt support column level change tracking.

    however, you may be able to achieve what you want.

    when a row is change on the client and on the server, that will result to a conflict (ClientUpdateServerUpdate).

    You can listen to the ApplyChangesFailed event

    check for the conflict ConflictType.ClientUpdateServerUpdate

    retrieve the conflict rows for both client and server from ApplyChangeFailedEventArgs' Conflict property (e.Conflict.ServerChange and e.Conflict.ClientChange)

    then do a compare of the column changes you want to merge

    update winning row (either client or server) with the column changes you want to merge

    then resolve the conflict using the updated row with the merged column changes.

     

    hth

    Friday, April 2, 2010 3:53 AM
  • That's an interesting approach, particularly since it will be a fairly infrequent event that I will have to merge changes from two different clients.  It would allow me to use the provided SqlSyncProvider and SqlCeSyncProvider rather than writing a custom provider.

    Using the exception handling approach you describe will require that I flag what column was updated in my SQL Server Compact client database. I could use that information to determine how to merge the changes when the conflict is raised. I think I can do that by adding some custom metadata, perhaps as simple as a binary string with a 1 for each changed column.

    I'll explore that possibility.

    Thanks.

     

    Friday, April 2, 2010 6:10 PM
  • here's an example code from the sample at http://msdn.microsoft.com/en-us/library/bb725997.aspx 

    //Provide a custom resolution scheme that takes each conflicting
    //column and applies the combined contents of the column to the 
    //client and server. This is not necessarily a resolution scheme 
    //that you would use in production. Instead, it is used to 
    //demonstrate the various ways you can interact with conflicting 
    //data during synchronization.
    //
    //Get the ID for the conflicting row from the client data table,
    //and add it to a list of GUIDs. We update rows at the server
    //based on this list.
    Guid customerId = (Guid)conflictingClientChange.Rows[0]["CustomerId"];
    _updateConflictGuids.Add(customerId);
    
    //Create a dictionary to hold the column ordinal and value for
    //any columns that are in confict.
    Dictionary<int, string> conflictingColumns = new Dictionary<int, string>();
    string combinedColumnValue;
    
    //Determine which columns are different at the client and server.
    //We already looped through these columns once, but we wanted to
    //keep this code separate from the display code above.
    for (int i = 0; i < clientColumnCount; i++)
    {
        if (conflictingClientChange.Rows[0][i].ToString() != conflictingServerChange.Rows[0][i].ToString())
        {
            //If we find a column that is different, combine the values from
            //the client and server, and write "| conflict |" between them.
            combinedColumnValue = conflictingClientChange.Rows[0][i] + "  | conflict |  " + 
                conflictingServerChange.Rows[0][i];
            conflictingColumns.Add(i, combinedColumnValue);
        }
    }
    
    //Loop through the rows in the Context object, which exposes
    //the set of changes that are uploaded from the client.
    //Note: In the ApplyChangeFailed event for the client provider,  
    //you have access to the set of changes that was downloaded from
    //the server.
    DataTable allClientChanges = e.Context.DataSet.Tables["Customer"];
    int allClientRowCount = allClientChanges.Rows.Count;
    int allClientColumnCount = allClientChanges.Columns.Count;
    
    for (int i = 0; i < allClientRowCount; i++)
    {
        //Find the changed row with the GUID from the Conflict object.
        if (allClientChanges.Rows[i].RowState == DataRowState.Modified &&
            (Guid)allClientChanges.Rows[i]["CustomerId"] == customerId)
        {
            //Loop through the columns and check whether the column
            //is in the conflictingColumns dictionary. If it is,
            //update the value in the allClientChanges Context object.
            for (int j = 0; j < allClientColumnCount; j++)
            {
                if (conflictingColumns.ContainsKey(j))
                {
                    allClientChanges.Rows[i][j] = conflictingColumns[j];
                }
            }
        }
    }
    
    //Apply the changed row with its combined values to the server.
    //This change will persist at the server, but it will not be 
    //downloaded with the SelectIncrementalUpdate command that we use.
    //It will not download the change because it checks for the UpdateId,
    //which is still set to the client that made the upload.
    //We use the ChangesApplied event to set the UpdateId for the
    //change to a value that represents the server. This ensures
    //that the change is applied at the client during the download
    //phase of synchronization (see SampleServerSyncProvider_ChangesApplied).
    e.Action = ApplyAction.RetryWithForceWrite;
    
    Saturday, April 3, 2010 12:02 AM