Answered by:
wanting to synchronize three tables in the same sqlserver db catalog

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 • ASPInsiderSunday, 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 • ASPInsiderMonday, November 14, 2011 6:07 AM