locked
Sync Bi-directional re-inserts data to client RRS feed

  • Question

  • I have configured bidirectional sync table. It is a parent table. If there is a delete that happens to the parent table, it should delete the child entries also.

    one of the things that i noted is., after i delete my parent table entry and then child entries, the next subsequent sync brings the parent and child data again back to client. Is the order in which i add tables to Sync adapters important in handling cascade deletes ? In this case i have parent added first and then child.

    Any help is appreciated.

    -

    Viswanth

     

    Tuesday, September 13, 2011 4:55 AM

Answers

  • on your client, subscribe to the local provider's ChangesSelected event and inspect the the event argument's dataset if it actually select's the delete you made.

    then on your remote provider, subscribe to the AppliedChangesFailed event to see if its actually encountering an error applying the change.

    assuming its not able to apply the changes on the server, then the same row you just deleted in the client will be downloaded by your SelectIncrementalInserts above since you're actually selecting all rows matching the userId without checking their timestamps.

    when you add tables to the SyncGroup, make sure you add the parent table first before the child table.

    • Marked as answer by Viswanth Thursday, September 15, 2011 6:54 AM
    Wednesday, September 14, 2011 5:56 AM

All replies

  • which provider are you using?

    if you're doing bidirectional sync, then a delete on the client should have been applied on the server on the next sync. if it has been applied on the server as well, then there is nothing to be downloaded and re-inserted on the client.

    can you describe your dev environment and how you configured the sync?

    Tuesday, September 13, 2011 5:13 AM
  • I was able to delete it from local store which deleted the record from actual database and after the sync completes, the record comes back to the client. Meaning the delete was not applied on the server.

    I'm using DBServerSyncProvider. Below is the code snippet.

     

     //Customers
                    SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);

                    customerBuilder .TableName = "Customers";
                    customerBuilder .TombstoneTableName = customerBuilder .TableName + "_Tombstone";
                    customerBuilder .SyncDirection = SyncDirection.Bidirectional;

                    customerBuilder .CreationTrackingColumn = "InsertTimestamp";
                    customerBuilder .UpdateTrackingColumn = "UpdateTimestamp";
                    customerBuilder .DeletionTrackingColumn = "DeleteTimestamp";
                    customerBuilder .CreationOriginatorIdColumn = "InsertId";
                    customerBuilder .UpdateOriginatorIdColumn = "UpdateId";
                    customerBuilder .DeletionOriginatorIdColumn = "DeleteId";

                    SqlCommand customerIncrInserts = new SqlCommand();
                    customerIncrInserts .CommandText = "Select * from Customers C " +                                                
                                                                                "WHERE C.CustomerID IN " +
                                                                                "(Select UserID from Users U where u.username = 'XYZ')";

                    SqlCommand customerIncrUpdates = new SqlCommand();
                    customerIncrUpdates.CommandText = "Select * from Customers C " +
                                                                                 " WHERE (C.UpdateTimestamp > @" + SyncSession.SyncLastReceivedAnchor +
                                                                                " AND C.UpdateTimestamp <= @" + SyncSession.SyncNewReceivedAnchor +
                                                                                " AND NOT (C.InsertTimestamp > @" + SyncSession.SyncLastReceivedAnchor + ") " +
                                                                                " AND C.CustomerID IN " +
                                                                                "(Select UserID from Users U where u.username = 'XYZ')))";

                    customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                    customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);

                    //Incremental Deletes
                    SqlCommand customerIncrDeletes = new SqlCommand();
                    customerIncrDeletes .CommandText = "Select * from Customers C " +                                                     
                                                                                 " WHERE (@" + SyncSession.SyncInitialized + "= 1 " +
                                                                                 " AND C.DeleteTimestamp > @sync_last_received_anchor " +
                                                                                 " AND C.DeleteTimestamp <= @sync_new_received_anchor " +
                                                                                 " AND C.CustomerID IN " +
                                                                                "(Select UserID from Users U where u.username = 'XYZ'))";

                    customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
                    customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                    customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);

                    customerIncrDeletes.Connection = serverConn;

                    SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
                    customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
                    customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;
                    customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes;


                    customerSyncAdapter.TableName = "Customers";
                    this.SyncAdapters.Add(customerSyncAdapter);

     

    In the same way if i have something called CustomerDetails. Should i configure the CustomerDetails builder,adapter first and then the for the Customers ? Becos, when i delete the customer details, i want the customer also to be removed from the database.

    P.S - Please ignore the query logic and syntax, this is just a sample that i'm trying to implement.


    • Edited by Viswanth Wednesday, September 14, 2011 5:17 AM
    Wednesday, September 14, 2011 1:50 AM
  • on your client, subscribe to the local provider's ChangesSelected event and inspect the the event argument's dataset if it actually select's the delete you made.

    then on your remote provider, subscribe to the AppliedChangesFailed event to see if its actually encountering an error applying the change.

    assuming its not able to apply the changes on the server, then the same row you just deleted in the client will be downloaded by your SelectIncrementalInserts above since you're actually selecting all rows matching the userId without checking their timestamps.

    when you add tables to the SyncGroup, make sure you add the parent table first before the child table.

    • Marked as answer by Viswanth Thursday, September 15, 2011 6:54 AM
    Wednesday, September 14, 2011 5:56 AM
  • On another note, if i have some tables being populated via scripts it would not have the correct timestamps for the sync framework to download the incremental changes.

    The scenario happens when there is a legacy system which has large amounts of data and those data were migrated to the database that has download only sync tables. For some reason it doesn't download the migrated data. Here is my tracking column configuration.

    Read as [ColumnName - DataType - Allow nulls - Default value or binding]

     

    UpdateTimestamp - timestamp - True

    InsertTimestamp - binary(8) - True - Default value or binding = (@@dbts+(1))

    InsertID - UniqueIdentifier - True  -  Default value or binding = ('00000000-0000-0000-0000-000000000000')

    UpdateID - UniqueIdentifier - True - Default value or binding = ('00000000-0000-0000-0000-000000000000')

     

    Will the data get downloaded even when it doesn't pass through the sync session (via application) ?

     

     

    Thursday, September 15, 2011 7:07 AM