c# - Microsoft Sync Framework: one field fk the other description -
i trying use microsoft sync framework syncing 2 tables. using syncorchestrator synchronize 2 tables.
the problem employees has field cate_id has id of category(this fk categories) , employees2 has field called cat , storing name of category in field directly so(is not fk), if see 1 table has id other name directly,
how sync tables? advice?
this code
static void main(string[] args) { //setup 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 server table var servertabledesc = sqlsyncdescriptionbuilder.getdescriptionfortable("dbo.bcpedoctor", serverconn); servertabledesc.globalname = "bcpedoctors"; // removing columns 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 , client tables has different names, let’s setup common name serverscopedesc.tables["bcpedoctors"].columns["doctorid"].isprimarykey = true; serverscopedesc.tables.add(servertabledesc); serverprovision.populatefromscopedescription(serverscopedesc); //apply scope definition serverprovision.apply(); } //provision client var clientprovision = new sqlsyncscopeprovisioning(clientconn); if (!clientprovision.scopeexists(scopename)) { var clientscopedesc = new dbsyncscopedescription(scopename); // add 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 columns source table doesnt have 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 scope definition clientprovision.apply(); } // create sync orchestrator var syncorchestrator = new syncorchestrator(); //setup providers var localprovider = new sqlsyncprovider(scopename, clientconn); var remoteprovider = new sqlsyncprovider(scopename, serverconn); // lets intercept changes can rename columns remoteprovider.changesselected += new eventhandler<dbchangesselectedeventargs>(remoteprovider_changesselected); syncorchestrator.localprovider = localprovider; syncorchestrator.remoteprovider = remoteprovider; // set direction of sync session download syncorchestrator.direction = syncdirectionorder.download; // execute 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 own structures. on either changesselected event on source provider or applyingchanges event on destination provider, intercept change dataset lookup , substitute corresponding value.
(e.g., alter dataset add name column, lookup name using id, assign name name column, remove id column when you're done.)
if synching many rows, can slow down.
sync fx syncs table , dont care fks. data transformations not core use case either.
Comments
Post a Comment