Microsoft Sync Framework: one field fk the other description

• Question

• 

  

  0

  Sign in to vote

  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