locked
How to sync related tables? RRS feed

  • Question

  • Hi,

     

    We have a Sync Framework based project created by VS 2010 Local Database Cache Wizard. Local DB is SQL Server Compact 3.5 SP2, remote is SQL Server Express 2008. Sync Framework version is 1.0 SP1.


    We have 11 tables involved in bi-directional sync. Some tables have foreign keys pointing to the other tables. Clearly when we insert data we first need to update tables which don't refer anything and then tables which refer just added rows. When we delete data we need to delete from referring tables first and then from the "simple" tables.


    Two questions:

    1. How do we control the order in which tables are synchronied? (Reminder - Local Database Cache Wizard generated project)

    2. How we deal with the problem that for insertion and deletion this order should be different?


    I'm interested in a simplest (least custom code) solution.

     


    Thank you! Konstantin
    Thursday, July 29, 2010 2:57 AM

All replies

  • In some other post I saw an advice "enable trace for the sync". How exactly do I do that?

    Now to change the tables sync order I need to change the order they are added to SyncAgent.Configuration.SyncTables, right? This code is in the .Designer.cs file and I hesitate to touch it, because it is Wizard generated file and my changes will be lost if I rerun the Wizard. What is the right way to code that?


    Thank you! Konstantin
    Thursday, July 29, 2010 6:51 PM
  • BTW to my surprise tables are updated NOT in the order specified by the client's SyncAgent.Configuration.SyncTables collection! It seems that they go in the order specified by the server side DbServerSyncProvider.InitializeSyncAdapters method. Am I supposed to replace wizard generated InitializeSyncAdapters with the one which adds server side sync adapters for each table to SyncAdapters collection in the right order?
    Thank you! Konstantin
    Thursday, July 29, 2010 9:40 PM
  • Hi, you can check this link http://msdn.microsoft.com/en-us/library/dd918848.aspx

    It's about Collaborative Synchronization. That's the scenario you're using as I understand it. You could have coded this manually and it would be much easier to control it. I am a MSF newbie but I managed to synchronize three different SQL Server databases (not the CE versions, but all three are true SQL Server DBs) using the example provided with the link I just gave you.

    As far as the order of synchronization is concerned, here's the part of the code that you have to modify and it's pretty straightforward:

    syncOrchestrator = new
    
    
     SampleSyncOrchestrator(
    new SqlSyncProvider("filtered_customer" , clientSqlConn, null , "Sync" ),
    new SqlSyncProvider("filtered_customer" , serverConn, null , "Sync" )
    );

    This is a chunk of code that you can find in the example. Here we're creating a new SyncOrchestrator with two parameters:

    1. the first parameter is the SqlSyncProvider for the client database
    2. the second parameter is the SqlSyncProvider for the server database

    The important thing to notice here are the strings passed in as the first parameter to SqlSyncProvider - those are the table names which you are synchronizing!

    So, in your code for example, if you want to synchronize tables named "PK_Table" and "FK_Table", where you want to synchronize first the tables "PK_Table" and then "FK_Table", you would write your code like this:

     

    // First the tables with primary keys
    
    
    syncOrchestrator = new
    
     SampleSyncOrchestrator(
        new
    
     SqlSyncProvider("PK_Table"
    
    , clientSqlConn, null
    
    , "Sync"
    
    ),
        new
    
     SqlSyncProvider("PK_Table"
    
    , serverConn, null
    
    , "Sync"
    
    )
        );
    syncOrchestrator.Synchronize(); // execute synchronization
    
    
    
    
    // then the tables with foreign keys
    
    
    syncOrchestrator = new
    
     SampleSyncOrchestrator(
        new
    
     SqlSyncProvider("FK_Table"
    
    , clientSqlConn, null
    
    , "Sync"
    
    ),
        new
    
     SqlSyncProvider("FK_Table"
    
    , serverConn, null
    
    , "Sync"
    
    )
        );
    syncOrchestrator.Synchronize(); // execute synchronization
    
    
    
    Hope this helps you and that I didn't miss something! :) If there's anything else you need to know just ask.

     

    Friday, July 30, 2010 4:23 PM
  • This shows one of the possible way to synchronize tables in the order we need, but it does not resolve the problem that for insertions and deletions the order of table synchronization should be different. When we insert data we want to sync "simple" tables first and the tables with foreign keys second. When the data are deleted we need to start with the tables with foreign keys. What if some data were added, some are deleted and we need to sync two databases? What do we do about the table order? Looks like for any order insertions or deletions would fail.
    Thank you! Konstantin
    Tuesday, August 3, 2010 8:03 PM