locked
Sync bi-directional Table with Deleted rows RRS feed

  • Question

  • Hello,

    I am using the offline scenario in MSF v2.0 to synchronize a SQL Server 2008 (server) database with a SQL Server 2008 Express (client) database via WCF.

    I have a multiple tables that are configured for bi-directional synchronization.  There are Foreign key constrains linking these tables.  For example, Customer table (parent) and Project (child) table.

    I am getting the following exception when I attempt to synchronize when there are deleted rows in both the Customer and Project tables
    The DELETE statement conflicted with the REFERENCE constraint.

    This is happening because I have configured the SyncAgent to process the Customer table before the Project table.  The Customer table was added to the SyncAgent before the Project table so the Customer records would be inserted/updated before the Project records.

    My Question:

    How can you configure a SyncAgent to perform Inserts, Updates and Deletes in the correct order?

    From what I can see, I would need to create multiple SyncAgents, one to first perform the Deletes, then a second to perform the Inserts/Updates.
    However, how can the server sync provider be configured to only return Deleted records for the first SyncAgent, and to return Inserted/Updated records for the second SyncAgent?

    Thanks in advance,
    Garry
    • Moved by Max Wang_1983 Tuesday, April 19, 2011 11:05 PM Forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, December 18, 2009 4:17 AM

Answers

  • Garry,

    The way to control the order in which changes are applied is through the order of the adapters.  Parent tables should come before child tables.  For inserts and updates, changes are applied in forward adapter order.  For deletes, changes are applied in reverse adapter order.  Hope this helps-

    Phil
    Friday, December 18, 2009 6:15 PM
  • I beleive that I found my problem.

    I was passing in Null as the SyncGroup for these two tables.  This was creating two separate groups.  Therefore, it was processing the "Client Group" first and then the "Project Group".

    I changed the SyncAgent so that the Client and Project tables are in the same group and it appears to be working.

    Thanks again for your help.
    Garry
    • Marked as answer by Garry English Saturday, December 19, 2009 1:55 AM
    Saturday, December 19, 2009 1:55 AM

All replies

  • Garry,

    The way to control the order in which changes are applied is through the order of the adapters.  Parent tables should come before child tables.  For inserts and updates, changes are applied in forward adapter order.  For deletes, changes are applied in reverse adapter order.  Hope this helps-

    Phil
    Friday, December 18, 2009 6:15 PM
  • Thanks Phil for you response.

    I am aware that the tables are processed in the other in which they are added to the SyncAdpters collection, however, it isn't reversing the order for deleted rows.

    Here is what I have.

    On the client side, I have a custom SyncProvider that inherits from the "SqlExpressClientSyncProvider" class.  In the constructor of this provider, I have the following.

    // Initialize the Client table SyncAdapter.
    SyncAdapter clientSyncAdapter = new SyncAdapter( Db.Tables.Client );
    this.SyncAdapters.Add( clientSyncAdapter );
    clientSyncAdapter.SelectIncrementalInsertsCommand = this.GetStoredProcCommand( Db.Sprocs.ClientSelectIncrementalInserts, database, connection );
    clientSyncAdapter.SelectIncrementalUpdatesCommand = this.GetStoredProcCommand( Db.Sprocs.ClientSelectIncrementalUpdates, database, connection );
    clientSyncAdapter.SelectIncrementalDeletesCommand = this.GetStoredProcCommand( Db.Sprocs.ClientSelectIncrementalDeletes, database, connection );
    clientSyncAdapter.InsertCommand = this.GetStoredProcCommand( Db.Sprocs.ClientInsert, database, connection );
    clientSyncAdapter.UpdateCommand = this.GetStoredProcCommand( Db.Sprocs.ClientUpdate, database, connection );
    clientSyncAdapter.DeleteCommand = this.GetStoredProcCommand( Db.Sprocs.ClientDelete, database, connection );
    clientSyncAdapter.SelectConflictUpdatedRowsCommand = this.GetStoredProcCommand( Db.Sprocs.ClientSelectConflictUpdatedRows, database, connection );
    clientSyncAdapter.SelectConflictDeletedRowsCommand = this.GetStoredProcCommand( Db.Sprocs.ClientSelectConflictDeletedRows, database, connection );
    
    // Initialize the Project table SyncAdapter.
    SyncAdapter projectSyncAdapter = new SyncAdapter( Db.Tables.Project );
    this.SyncAdapters.Add( projectSyncAdapter );
    projectSyncAdapter.SelectIncrementalInsertsCommand = this.GetStoredProcCommand( Db.Sprocs.ProjectSelectIncrementalInserts, database, connection );
    projectSyncAdapter.SelectIncrementalUpdatesCommand = this.GetStoredProcCommand( Db.Sprocs.ProjectSelectIncrementalUpdates, database, connection );
    projectSyncAdapter.SelectIncrementalDeletesCommand = this.GetStoredProcCommand( Db.Sprocs.ProjectSelectIncrementalDeletes, database, connection );
    projectSyncAdapter.InsertCommand = this.GetStoredProcCommand( Db.Sprocs.ProjectInsert, database, connection );
    projectSyncAdapter.UpdateCommand = this.GetStoredProcCommand( Db.Sprocs.ProjectUpdate, database, connection );
    projectSyncAdapter.DeleteCommand = this.GetStoredProcCommand( Db.Sprocs.ProjectDelete, database, connection );
    projectSyncAdapter.SelectConflictUpdatedRowsCommand = this.GetStoredProcCommand( Db.Sprocs.ProjectSelectConflictUpdatedRows, database, connection );
    projectSyncAdapter.SelectConflictDeletedRowsCommand = this.GetStoredProcCommand( Db.Sprocs.ProjectSelectConflictDeletedRows, database, connection );
    

    GetStoredProcCommand - is a simple method that returns a DbCommand object that has been initialized for the specified stored procedure.

    The "Client" table is the parent table and the "Project" table is the child table. (i.e. the Project table has a ClientId FK column)

    On the server side, I have a custom SyncProvider that inherits from the "DbServerSyncProvider" class.  The constructor for this provider has the same initialization code that is listed above.

    I have a custom "SyncAgent" class that contains the following code in the constructor:
    // Add the SyncTables that will be included in the synchronization process.
    this.AddSyncTable( Db.Tables.Client, TableCreationOption.UseExistingTableOrFail, SyncDirection.Bidirectional, null );
    this.AddSyncTable( Db.Tables.Project, TableCreationOption.UseExistingTableOrFail, SyncDirection.Bidirectional, null );
    I then call the SyncAgent.Synchronize() method to being the synchronization process.  I have debugged the synchronization process and the Client's deleted rows are processed before the Project's deleted rows.

    Can you please tell what is missing?

    Thanks for your help.
    Garry
    Saturday, December 19, 2009 1:47 AM
  • I beleive that I found my problem.

    I was passing in Null as the SyncGroup for these two tables.  This was creating two separate groups.  Therefore, it was processing the "Client Group" first and then the "Project Group".

    I changed the SyncAgent so that the Client and Project tables are in the same group and it appears to be working.

    Thanks again for your help.
    Garry
    • Marked as answer by Garry English Saturday, December 19, 2009 1:55 AM
    Saturday, December 19, 2009 1:55 AM