Wednesday, April 24, 2013

Stop Syncing Record Deletes with Sync Framework

I had a requirement where I wanted to sync records from source to destination, in the destination there will be some updates and then I wanted to sync those records back from destination to source. Of course this was a very common requirement and Sync Framework definitely supports these kind of situations and I was using the SyncDirectionOrder.UploadAndDownload in my SyncOrchestrator. I was monitoring my source database and my sync was perfectly working and I was happy until I saw some record deletes in my source database which I cannot accept. I examined the case and actually there is nothing wrong with the sync. What happened was records in the destination database got deleted accidently and those changes were just synced back to source.

Now I needed to stop syncing record deletes and this is only from destination to source, that is if a source record gets deleted, it should be deleted in the destination, but if a destination record gets deleted, it should not be deleted in the source. June Tabadero has wrote a nice post about MANIPULATING THE CHANGE DATASET IN SYNC FX and by manipulating the change dataset, I did able to accomplish what I wanted. In addition to things mentioned in June Tabadero's post, thought to add some new things. 

Before starting manipulating the change dataset, I think it’s best to understand the event order of SqlSyncProviders. I have two SqlSyncProviders which are localProvider and remoteProvider. localProvider is for the source and remoteProvider is for the destination.
image
Event Order
So here since we are doing a UploadAndDownload, all these events will be fired in the above order, first Upload events and then the Download events. But for my requirement (stop destination deletes cascaded to source), I am mainly interested in two events. That’s remoteProvider_ChangesSelected and localProvider_ApplyingChanges. To access deleted rows on the destination and to manipulate the change dataset, I can use above mentioned two events which will get fired when remote provider has completed selecting changes and when local provider is trying to apply those changes.

remoteProvider.ChangesSelected
void remoteProvider_ChangesSelected(object sender, DbChangesSelectedEventArgs e)
{
    for (int i = 0; i < e.Context.DataSet.Tables.Count; i++)
    {
        var dataTable = e.Context.DataSet.Tables[i];
        for (int j = 0; j < dataTable.Rows.Count; j++)
        {
            var row = dataTable.Rows[j];
            if (row.RowState == DataRowState.Deleted)
            {
                dataTable.Rows.Remove(row);
                j--;
            }
        }
    }
}
localProvider.ApplyingChanges
void localProvider_ApplyingChanges(object sender, DbApplyingChangesEventArgs e)
{
    for (int i = 0; i < e.Context.DataSet.Tables.Count; i++)
    {
        var dataTable = e.Context.DataSet.Tables[i];
        for (int j = 0; j < dataTable.Rows.Count; j++)
        {
            var row = dataTable.Rows[j];
            if (row.RowState == DataRowState.Deleted)
            {
                dataTable.Rows.Remove(row);
                j--;
            }
        }
    }
}
In these two events, the method body is the same. Here I am iterating through all the tables in the change dataset. If you want to stop syncing deletes only for a specific table, you can do that too. I can use any event from above two events to remove the rows in change dataset which are marked as deleted. Please note that when deleting a row which is marked as deleted in the change dataset I am decrementing the value of “j” by 1. That is because for an example if a row is deleted from the 0th position of the data table, I have to move back to new row which is at 0th position now and check whether that row is marked as deleted.

I have created a sample and I am uploading it to my skydrive with database backup files. If you test this out, you can see that when the destination records gets deleted it will not be reflected in the source. But here there is a another problem, that is when the destination records gets deleted, they will not get synced back from the source. That is because source has no idea that destination records are deleted. For that you will have to do a dummy update on source records, so they will get synced back to destination.


Hope this helps.

Happy Coding.

Regards,
Jaliya

1 comment:

  1. Hi jaliya,

    I have achieved same with disabling delete trigger during deleteing records from specific tables, so basically while performing

    delete Operation on that table, tracking tables dont get updated as triggers are disabled.

    So in this case sync will not delete record from server.

    I am not sure for the possible side effects of this approach, do you find anything wrong with it ?

    ReplyDelete