Remove From My Forums

wanting to synchronize three tables in the same sqlserver db catalog

Question
0

Sign in to vote

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