none
Microsoft Sync Framework: one field fk the other description RRS feed

  • Question

  • 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"]); .......

    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(); }

    Tuesday, March 17, 2015 4:58 AM

All replies

  • this is normally the domain of ETL tools and not Sync Fx. but you can intercept the changes and manipulate them.

    For example, in the source you can intercept the ChangesSelected event to look up the name for the Id and send the name instead of the Id

    Monday, April 13, 2015 8:10 AM
    Moderator