I am trying to use Microsoft Sync Framework for syncing 2 tables. I am using SyncOrchestrator to synchronize the two tables.
The problem is Employees has a field cate_id that has the id of the category(this is a FK to categories) and Employees2 has a field called Cat and this is storing the name of the category in the field directly so(is not a fk), if you see one table has id the other the name directly,
How could I sync those tables? any advice?
This is my code
static void Main(string[] args)
{
//setup the connections
var serverConn = new SqlConnection(@"Data Source=WIN-R9D162FO6E3\HCNSQL07;User ID=mauricio;Password=Maitolin26; Initial Catalog=test;");
var clientConn = new SqlConnection(@"Data Source=WIN-R9D162FO6E3\ESP;User ID=sa;Password=Maitolin26#; Initial Catalog=MedicalDirector;");
//setup scope name
const string scopeName = "DifferentSchemaScope";
//IEnumerable<string> tablesThatChanged
//IEnumerable<string> tablesThatChanged = Enumerable.Empty<string>();
IEnumerable<string> tablesThatChanged = new string[] { "BCPeDoctors" };
//reprovision
//ReProvision(serverConn, scopeName, tablesThatChanged);
//provision server
var serverProvision = new SqlSyncScopeProvisioning(serverConn);
if (!serverProvision.ScopeExists(scopeName))
{
var serverScopeDesc = new DbSyncScopeDescription(scopeName);
// add the server table
var serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.BCPeDoctor", serverConn);
serverTableDesc.GlobalName = "BCPeDoctors";
// removing columns the source doesnt have
serverScopeDesc.Tables["BCPeDoctors"].Columns.Remove(serverScopeDesc.Tables["BCPeDoctors"].Columns["SuburbID"]);
serverScopeDesc.Tables["BCPeDoctors"].Columns.Remove(serverScopeDesc.Tables["BCPeDoctors"].Columns["Location"]);
serverScopeDesc.Tables["BCPeDoctors"].Columns.Remove(serverScopeDesc.Tables["BCPeDoctors"].Columns["SpecialtyID"]);
serverScopeDesc.Tables["BCPeDoctors"].Columns.Remove(serverScopeDesc.Tables["BCPeDoctors"].Columns["Adjusted"]);
serverScopeDesc.Tables["BCPeDoctors"].Columns.Remove(serverScopeDesc.Tables["BCPeDoctors"].Columns["Deleted"]);
serverScopeDesc.Tables["BCPeDoctors"].Columns.Remove(serverScopeDesc.Tables["BCPeDoctors"].Columns["InActive"]);
serverScopeDesc.Tables["BCPeDoctors"].Columns.Remove(serverScopeDesc.Tables["BCPeDoctors"].Columns["IsPreferred"]);
// our server and client tables has different names, so let’s setup a common name
serverScopeDesc.Tables["BCPeDoctors"].Columns["DoctorID"].IsPrimaryKey = true;
serverScopeDesc.Tables.Add(serverTableDesc);
serverProvision.PopulateFromScopeDescription(serverScopeDesc);
//apply the scope definition
serverProvision.Apply();
}
//provision client
var clientProvision = new SqlSyncScopeProvisioning(clientConn);
if (!clientProvision.ScopeExists(scopeName))
{
var clientScopeDesc = new DbSyncScopeDescription(scopeName);
// add the client table
var clientTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("CM_ADDRESS_BOOK", clientConn);
clientTableDesc.GlobalName = "CM_ADDRESS_BOOK2";
clientScopeDesc.Tables.Add(clientTableDesc);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["ADDRESS_BOOK_ID"].IsPrimaryKey = true;
// remove the columns the Source table doesnt have it
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["ADDRESS_BOOK_NO"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["STREET_LINE_3"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["CITY"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["POSTCODE"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["P_STREET_LINE_1"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["P_STREET_LINE_2"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["P_STREET_LINE_3"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["P_CITY"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["P_POSTCODE"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["PHONE_WORK"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["PAGER"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["PROVIDER_NO"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["CATEGORY"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["ABN"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["PUBLIC_KEY"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["USER_NAME"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["URL"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["COMPANY"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["STD_FORM"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["NO_GAP"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["TX"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["STAMP_CREATED_DATETIME"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["STAMP_USER_ID"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["STAMP_ACTION_CODE"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["STAMP_DATETIME"]);
clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns.Remove(clientScopeDesc.Tables["CM_ADDRESS_BOOK"].Columns["STAMP_DATETIME"]);
clientProvision.PopulateFromScopeDescription(clientScopeDesc);
//apply the scope definition
clientProvision.Apply();
}
// create the sync orchestrator
var syncOrchestrator = new SyncOrchestrator();
//setup providers
var localProvider = new SqlSyncProvider(scopeName, clientConn);
var remoteProvider = new SqlSyncProvider(scopeName, serverConn);
// lets intercept the changes so we can rename the columns
remoteProvider.ChangesSelected += new EventHandler<DbChangesSelectedEventArgs>(remoteProvider_ChangesSelected);
syncOrchestrator.LocalProvider = localProvider;
syncOrchestrator.RemoteProvider = remoteProvider;
// set the direction of sync session Download
syncOrchestrator.Direction = SyncDirectionOrder.Download;
// execute the synchronization process
var syncStats = syncOrchestrator.Synchronize();
// print statistics
Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
Console.WriteLine(String.Empty);
Console.ReadKey();
}
you can provision both tables as it is with their own structures. Then on either ChangesSelected event on the source provider or the ApplyingChanges event on the destination provider, intercept the change dataset to do a lookup and substitute the corresponding value.
(e.g., alter dataset to add the name column, lookup name using the id, assign name the name column, then remove the id column when you're done.)
if synching many rows, this can slow it down.
Sync Fx syncs by table and dont care about the FKs. Data transformations is not its core use case either.