Manipulating the change dataset in Sync Fx


One of the most common scenario asked in the Sync Fx forum is on manipulating the changes selected during synchronization before they get applied.

For example, some sync scenarios requires the client to synchronize and download rows from the server, update the rows in the client, upload the changes back to the server and then delete them from the client without having those deletes cascaded to the server when uploading changes during synchronization.

Another scenario is where based on specific conditions,  the sync application either applies the changes as it is or modify the row in transit during the sync like encrypting them or even changing how the row gets applied like applying an update as a delete instead.

Before showing some sample codes on how to go about these types of change manipulation, let’s briefly talked about how Sync Fx actually tracks or transmits these changes.

Sync Fx loads the changes to be synchronized in a dataset. To differentiate Inserts, Updates and Deletes, Sync Fx sets the RowState of each row to either Added, Modified or Deleted:

DataRowState.Added for Inserts
DataRowState.Modified for Updates
DataRowState.Deleted for Deletes

Please note that the RowState gets reset whenever AcceptChanges is called on the Dataset.

For example, if you decide to change a value of a particular column in the row marked by Sync Fx  for Update (DataRowState.Modified)  before it gets applied and you call AcceptChanges, the row’s DataRowState would actually be changed to DataRowState.Unchanged which would prevent Sync Fx from applying the change since it has already lost track of whether the update is an Insert or an Update.

To manipulate the changes before they get applied, we must intercept the change dataset and make our changes before allowing the dataset to flow thru and let Sync Fx do the change application.

So let’s look at a scenario where we want to apply a server Update as a Delete instead:

Code Snippet
  1. void LocalDataCache1ServerSyncProvider_ChangesSelected(object sender, Microsoft.Synchronization.Data.ChangesSelectedEventArgs e)
  2. {
  3.   //let's check if we're synching the table we're interested
  4.   if (e.Context.DataSet.Tables.Contains("WorkOrders"))
  5.   {
  6.    var dataTable = e.Context.DataSet.Tables["WorkOrders"];
  7.    for (int j = 0; j < dataTable.Rows.Count; j++)
  8.    {
  9.     DataRow row = dataTable.Rows[j];
  10.  
  11.     // we're only interested in updates
  12.     if (row.RowState == DataRowState.Modified)
  13.     {
  14.      // check if the status is Completed
  15.      if (row["Status"].ToString() == "Completed")
  16.      {
  17.       // let's delete the row so it gets applied as a delete instead of applying it as an update
  18.       dataTable.Rows[j].Delete();
  19.      }
  20.     }
  21.    }
  22.   }
  23. }

In this example, we subscribed to the server provider’s ChangesSelected event. We then check if it’s the table we’re interested in. If it’s the table we want to manipulate the change application, we then loop thru each data row checking if it’s a row that’s marked as an Update. Assuming it’s an update, we check  one of the  row’s specific column value and if it’s a match, we delete the row from the dataset effectively changing it’s DataRowState to Deleted. Sync Fx then applies this row as a Delete instead of an Update originally.

Now on to another scenario where we want to prevent the source’s Deletes from being applied to the destination:

Code Snippet
  1. void LocalDataCache1ServerSyncProvider_ChangesSelected(object sender, Microsoft.Synchronization.Data.ChangesSelectedEventArgs e)
  2. {
  3.   //let's check if we're synching the table we're interested
  4.   if (e.Context.DataSet.Tables.Contains("WorkOrders"))
  5.   {
  6.    var dataTable = e.Context.DataSet.Tables["WorkOrders"];
  7.    for (int j = 0; j < dataTable.Rows.Count; j++)
  8.    {
  9.     DataRow row = dataTable.Rows[j];
  10.  
  11.     // we're only interested in updates
  12.     if (row.RowState == DataRowState.Deleted)
  13.     {
  14.           // let's delete the row so it doesn't get sent
  15.              dataTable.Rows.Remove(dataTable.Rows[j]);
  16.      }
  17.    }
  18.   }
  19.  
  20. }

In this example, we check if the row is tagged as a Delete, and then we remove it from the dataset. Please note that we used Remove instead of Delete.

Here’s another example where we change the actual column value in the row:

Code Snippet
  1. void LocalDataCache1ServerSyncProvider_ChangesSelected(object sender, Microsoft.Synchronization.Data.ChangesSelectedEventArgs e)
  2. {
  3.   //let's check if we're synching the table we're interested
  4.   if (e.Context.DataSet.Tables.Contains("WorkOrders"))
  5.   {
  6.    var dataTable = e.Context.DataSet.Tables["WorkOrders"];
  7.    for (int j = 0; j < dataTable.Rows.Count; j++)
  8.    {
  9.     DataRow row = dataTable.Rows[j];
  10.  
  11.     // we're only interested in updates
  12.     if (row.RowState == DataRowState.Modified)
  13.     {
  14.      // check if the status is Completed
  15.      if (row["Status"].ToString() == "Completed")
  16.      {
  17.        //let's change the row value
  18.          row["Status"] = "For Archiving";
  19.      }
  20.     }
  21.    }
  22.   }
  23.  
  24. }

Note that in all examples, we didn’t invoke AcceptChanges as it would effectively wipe out the row states that Sync Fx uses to differentiate Inserts, Updates and Deletes.

Intercepting and manipulating the change dataset can be done either on the local or the remote provider.

Also note that we are doing the manipulation before the changes get’s applied, in this case using the ChangesSelected event. You may also use the ApplyingChanges event to intercept the change dataset.

Hope this helps.

Cheers!

About these ads

17 comments

  1. Good morning,

    How could we use this for avoiding that one row updates/inserts in a local database?

    And when we have ignore this row if we want to get the row?

    Thank you.

    1. can you elaborate more on your scenario? i dont get it why you would want to ignore the row if you want to get that row. if you dont want updates/deletes cascading in your local database, you would intercept the ChangesSelected event on the remote/server provider and remove it from the DataTable.

  2. Wich is the difference between using Remove or Delete ??

    1. Delete marks the row as deleted in the DataTable, Remove will remove it from the DataTable

  3. Hi JuneT … i have received a lot of help from you on the various forums – thanks!

    I think i am experiencing something related to the 2nd scenario above and i am wondering if you have too.

    I am attempting to remove records from the client without allowing the “delete” to flow to the server … so i am removing the “delete” records from the recordset in the ChangesSelected event.

    What i think is happening is that IF after removing all the “delete” records … the remaining dataset is EMPTY the .Synchronize() method does not finish (meaning a sync is not actually started) … actually exits without throwing an error.

    Have you seen this behavior? I am using SFx 2.1 over WCF with SqlCe 3.5/SQL 2008.

    Thanks.

    1. havent seen that behaviour, but that makes sense, empty dataset, nothing to sync so cancel the sync.

      try enabling Sync Fx tracing in verbose mode and see if its cancelling the sync.

  4. What if you want server-side changes to overwrite any client-side changes?

    If you set the Direction to Download then the client-side changes are never picked up. If you set the Direction to DownloadAndUpload, then your server-side originals get wiped out.

    If it do the latter plus remove the rows in one of the Change Selected or Applying events, then your server-side originals remain but the client-side changes also remain (because they’re the newest).

    I read some comments about doing a dummy update on the server for those rows and then removing them from the collection – so they’ll have a newer timestamp on the server and update the client on the next Download. But I haven’t seen anyone provide code for the sql providers to do that in the event handler in a generic way – without hardcoding update statements for every table you use (which is frankly unacceptable).

    I’d love to see a better way to handle this.

    1. am not sure I completely follow what you’re asking. what exactly is your scenario?

      if you want server side to overwrite client changes, then you should handle that in the ApplyChangesFailed event because that’s the only place that tells you both copies has been updated.

      of course when you set the Sync Direction as Download, it’s a unidirectional sync. you are getting changes from somewhere but not sending changes.

      if you instruct it to upload, of course it will overwrite the destination (unless there’s a conflict and you have to choose which wins)

      this post is about manipulating the change dataset to override the default behaviour.

      if you’re removing rows from the dataset, that is because you don’t want that change to flow thru. so if you don’t want the change to flow thru, of course nothing happens to your destination. and the row remains as it is on the source as well.

  5. Sorry to hijack your post, I’m harping on about a different scenario that Sync Framework doesn’t handle well and that is frustrating me (and others who use it): one master database, distributing to multiple client databases, and the sync is one way only; master -> client.

    It’s possible that a DBA or someone else could modify a row on the client database when they shouldn’t. The problem is what happens after that.

    If you only Download changes they won’t progress back to the master. If you do both a Download and Upload, you can intercept the ApplyChanges and discard the rows with Remove so they don’t impact the master.

    But whichever way you go you’re left with out-of-sync rows on the client, and Sync Framework doesn’t provide any way to force a re-send of just those rows from the master.

    The only way I’ve seen around it is to intercept the Upload rows in the ApplyChanges event and construct a dummy update (update field = field where primary key = row) statement to mark each row on the master database as dirty, then Remove them. Then on the Download it causes the master rows to be re-sent and overwrite the client database changes.

    But it was extremely difficult and messy, especially doing it in a generic way. I wish that Sync Framework catered for the situation more neatly.

    1. not trying to defend the framework, but I disagree. a common misconception about Sync Framework is that is should do “differential” syncs or sync what’s different between tables (much like tablediff utility). But it’s based on “incremental” syncs, what has changed since the last sync. in your scenario, the framework is doing what its been designed for. if you specify a one way sync, then it does a one way sync. It’s not its fault that someone updates a row they shouldn’t be updating. And to address your scenario, there are actually enough extension points to do exactly what you want to do. Sync Fx is an SDK. it’s not a tool like SQL Replication where you simply point and click configuration and fire off.

  6. Question: how do you know which direction the changes are destined for? I have a bidirectional sync up and working. Now I want to set it up so that all changes to come down from the server to the local cache (updates, inserts and deletes). But going the other direction – I only want to allow updates. Not deletes or inserts. The client machines will make some changes to the database that need to be synced but they will never add or delete rows in my application.

    So how can ChangesSelected know if it should remove the INSERT or DELETE?

    1. your local and remote provider have their own ChangesSelected event. in your scenario (and the same as what is shown in this post), you will listen for the local provider’s ChangesSelected event and loop thru the rows. If you look at the blog post, each row in the DataTable has a row state, the inserts has an DataRowState.Added for Inserts and DataRowState.Deleted for Deletes.

      1. That does make sense and is exactly what I tried to do. But I am not seeing that behavior. I have added a ChagesSelected handler for both client and server providers.

        In the client handler I just do things like track how many rows were added vs. how many rows were modified for each table to report statistics at the end of the sync.

        In the server handler – I remove all the DataRowState.Added and DataRowState.Deleted rows (since I don’t want rows created or removed on the client being synced to the server)

        But I noticed when creating a fresh local cache database that I was only getting HALF the rows from the server. Appears to be EVERY OTHER row coming down to the client. So I removed my handler for the server provider – and I get them all. Then I added the handler back in and set a breakpoint in the server provider handler and I see that I am getting DataRowState.Added events (which my server handler, of course, removes).

        AND THIS IS WHAT HAS ME CONFUSED

        If I delete my local cache database and sync – I would expect to get NO EVENTS AT ALL for the server provider ChangesSelected handler. Shouldn’t this only be a “one way sync” at this point? With the only events firing in the CLIENT ChangesSelected handler?

        So why am I even getting ServerProivder_ChangesSelected events when creating a new local cache database?

  7. You can remove my previous question. I just found my problem. I was confused about the meanings of “server provider” and “client provider”. I assumed “server provider” meant “provides changes to the server” when really it appears it means “provides changes from the server” So I had my code for the two different providers backwards. I swapped them around (all my client handler code added to the server and vice-versa) and things appear to be working now.

    Thank you for your time…

  8. I have my bi-directional sync working fine now. I am allowing everything to come from server to client and stripping row Inserts and Deletes going from the client to the server.

    I have one last question: how to restrict changes to just selected columns? I see the Update messages in my ChangesSelected – but is there a way to know WHICH columns have changed data?

    For safety I would like to only allow updates to ColumnA, ColumnB and Column F (for example) But remove changes from the sync to any other column going from client to server. This is protect against mass data corruption being propogated if a single user starts hacking my local cache file and then syncs to the server.

    1. change tracking is done at the row level. Sync Fx has no idea which column has changed or not.

  9. Vaggelis · · Reply

    …………….
    dataTable.Rows.Remove(dataTable.Rows[j]);
    i–;
    …………….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 231 other followers

%d bloggers like this: