locked
wanting to synchronize three tables in the same sqlserver db catalog RRS feed

  • Question

  • I've got two tables with identical columms and I want to synchronize the data. (they are all in the same catalog)

    That is:

    table_1 {columns: Id,Name,Data}

    table_2 {columns: Id,Name,Data}

    table_3 {columns: Id,Name,Data}

    I want to Keep these three tables in sync.  Are there any examples or suggestions on how to do this?

     


    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Sunday, November 13, 2011 7:52 PM

Answers

  • you can setup three scopes: eg., table1_scope,  table2_scope, table3_scope

    make sure you set the GlobalName for the table in each scope to a common name, eg.,  table1TableDesc.GlobalName="SomeCommonTableName";

     something similar to this:

    var dbConn = new SqlConnection(@"Data Source=.; Initial Catalog=MyDb; Integrated Security=True");
    
    var scopeProvisioning = new SqlSyncScopeProvisioning(dbConn);
    
    //provision table 1
    var table1ScopeDesc = new DbSyncScopeDescription("Table1_Scope");
    var table1Desc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_1", dbConn);
    table1Desc.GlobalName = "Tables123"; //set GlobalName since table names are different
    
    table1ScopeDesc.Tables.Add(table1Desc);
    scopeProvisioning.PopulateFromScopeDescription(table1ScopeDesc);
    scopeProvisioning.Apply();
    
    //provision table 2
    var table2ScopeDesc = new DbSyncScopeDescription("Table2_Scope");
    var table2Desc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_2", dbConn);
    table2Desc.GlobalName = "Tables123"; //set GlobalName since table names are different
    
    table2ScopeDesc.Tables.Add(table2Desc);
    scopeProvisioning.PopulateFromScopeDescription(table2ScopeDesc);
    scopeProvisioning.Apply();
    
    //provision table 3
    var table3ScopeDesc = new DbSyncScopeDescription("Table3_Scope");
    var table3Desc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_3", dbConn);
    table3Desc.GlobalName = "Tables123"; //set GlobalName since table names are different
    
    table3ScopeDesc.Tables.Add(table3Desc);
    scopeProvisioning.PopulateFromScopeDescription(table3ScopeDesc);
    scopeProvisioning.Apply();
    
    var syncOrchestrator = new SyncOrchestrator();
    
    //set sync direction to bidirectional
    syncOrchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;
    
    var table1Provider = new SqlSyncProvider("Table1_Scope", dbConn);
    var table2Provider = new SqlSyncProvider("Table2_Scope", dbConn);
    var table3Provider = new SqlSyncProvider("Table3_Scope", dbConn);
    
    // sync table 1 and 2
    syncOrchestrator.LocalProvider = table1Provider;
    syncOrchestrator.RemoteProvider = table2Provider;
    
    var syncStats = syncOrchestrator.Synchronize();
                
    //sync table 2 and 3
    syncOrchestrator.LocalProvider = table2Provider;
    syncOrchestrator.RemoteProvider = table3Provider;
    
    syncStats= syncOrchestrator.Synchronize();
    

    i wrote the test on another machine and just retyped everything here, so there might be some syntax errors

     

    • Marked as answer by Peter Kellner Monday, November 14, 2011 6:07 AM
    Monday, November 14, 2011 5:04 AM

All replies

  • you can setup three scopes: eg., table1_scope,  table2_scope, table3_scope

    make sure you set the GlobalName for the table in each scope to a common name, eg.,  table1TableDesc.GlobalName="SomeCommonTableName";

     something similar to this:

    var dbConn = new SqlConnection(@"Data Source=.; Initial Catalog=MyDb; Integrated Security=True");
    
    var scopeProvisioning = new SqlSyncScopeProvisioning(dbConn);
    
    //provision table 1
    var table1ScopeDesc = new DbSyncScopeDescription("Table1_Scope");
    var table1Desc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_1", dbConn);
    table1Desc.GlobalName = "Tables123"; //set GlobalName since table names are different
    
    table1ScopeDesc.Tables.Add(table1Desc);
    scopeProvisioning.PopulateFromScopeDescription(table1ScopeDesc);
    scopeProvisioning.Apply();
    
    //provision table 2
    var table2ScopeDesc = new DbSyncScopeDescription("Table2_Scope");
    var table2Desc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_2", dbConn);
    table2Desc.GlobalName = "Tables123"; //set GlobalName since table names are different
    
    table2ScopeDesc.Tables.Add(table2Desc);
    scopeProvisioning.PopulateFromScopeDescription(table2ScopeDesc);
    scopeProvisioning.Apply();
    
    //provision table 3
    var table3ScopeDesc = new DbSyncScopeDescription("Table3_Scope");
    var table3Desc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_3", dbConn);
    table3Desc.GlobalName = "Tables123"; //set GlobalName since table names are different
    
    table3ScopeDesc.Tables.Add(table3Desc);
    scopeProvisioning.PopulateFromScopeDescription(table3ScopeDesc);
    scopeProvisioning.Apply();
    
    var syncOrchestrator = new SyncOrchestrator();
    
    //set sync direction to bidirectional
    syncOrchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;
    
    var table1Provider = new SqlSyncProvider("Table1_Scope", dbConn);
    var table2Provider = new SqlSyncProvider("Table2_Scope", dbConn);
    var table3Provider = new SqlSyncProvider("Table3_Scope", dbConn);
    
    // sync table 1 and 2
    syncOrchestrator.LocalProvider = table1Provider;
    syncOrchestrator.RemoteProvider = table2Provider;
    
    var syncStats = syncOrchestrator.Synchronize();
                
    //sync table 2 and 3
    syncOrchestrator.LocalProvider = table2Provider;
    syncOrchestrator.RemoteProvider = table3Provider;
    
    syncStats= syncOrchestrator.Synchronize();
    

    i wrote the test on another machine and just retyped everything here, so there might be some syntax errors

     

    • Marked as answer by Peter Kellner Monday, November 14, 2011 6:07 AM
    Monday, November 14, 2011 5:04 AM
  • Thanks June!
    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Monday, November 14, 2011 6:07 AM