the database records doesn't sync

• Question

• 

  

  0

  Sign in to vote

  hello sir/madam

      although I'm not  familar with sync framework,but with Google,I have written the code in below,but the codes seems have problem,I tried sync two same structure & fields tables in two databases,some records doesn't sync anyway,what I have done wrong?there are two SQL SERVER 2005 in two servers

          public void Syncdb()
          {
              string localstring = @"server =.;database=DBdestination;uid=xx;pwd='xxx'";
              string remotestring = @"server =XXXXX;database=DBsource;uid=xx;pwd=''xxx";
              if (setup(remotestring, localstring)!=0)
                  return;
              if (sync(remotestring, localstring) != 0)
                  return;
            //  dropprevision(remotestring, localstring);
          }
  
          private int setup(string remotestr,string localstr)
          {
              try
              {
                  // Connection to on  SQL Server database
                  SqlConnection serverConn = new SqlConnection(remotestr);
  
                  // Connection to SQL client database
                  SqlConnection clientConn = new SqlConnection(localstr);
  
                  // Create a scope named "product" and add tables to it.
                  DbSyncScopeDescription productScope = new DbSyncScopeDescription("product_mytemp");
  
                  // Select the colums to be included in the Collection Object
                  Collection<string> includeColumns = new Collection<string>();
                  includeColumns.Add("prod_no");
                  includeColumns.Add("prod_name");
                  includeColumns.Add("prod_size");
                  includeColumns.Add("monad");
                  includeColumns.Add("box_num");
                  includeColumns.Add("dep_pos");
                  includeColumns.Add("ware_no");
                  includeColumns.Add("recipe");
                  includeColumns.Add("sell_stop");
                  includeColumns.Add("retail_price");
                  includeColumns.Add("std_price");
                  includeColumns.Add("Cuntry_Price");
                  includeColumns.Add("corp_price");
                  includeColumns.Add("buy_price");
                  includeColumns.Add("dep_no");
                  includeColumns.Add("dep_price");
                  includeColumns.Add("limit_num");
                  includeColumns.Add("up_num");
                  includeColumns.Add("per_memo");
                  includeColumns.Add("limit_price");
                  includeColumns.Add("prod_memo");
                  includeColumns.Add("last_add");
                  includeColumns.Add("std_per");
                  includeColumns.Add("prod_dx");
                  includeColumns.Add("prod_price_a");
                  includeColumns.Add("prod_price_b");
                  includeColumns.Add("prod_price_c");
                  includeColumns.Add("price_type");
                  includeColumns.Add("prod_price_d");
                  includeColumns.Add("prod_price_e");
                  includeColumns.Add("prod_made");
                  includeColumns.Add("prod_label");
                  includeColumns.Add("prod_pzwh");
                  includeColumns.Add("box_midnum");
                  includeColumns.Add("bar_code");
                  includeColumns.Add("last_sup");
                  includeColumns.Add("last_date");
                  includeColumns.Add("last_buy_num");
                  includeColumns.Add("avail_length");
                  includeColumns.Add("yhbz");
                  includeColumns.Add("ps_pc");
                  includeColumns.Add("ps_xh");
                  includeColumns.Add("kssbz");
                  includeColumns.Add("prod_gml");
                  includeColumns.Add("prod_no_tmp");
                  includeColumns.Add("prod_no_old");
                  includeColumns.Add("prod_no_new");
                  includeColumns.Add("lzbz");
                  includeColumns.Add("com_jc");
                  includeColumns.Add("prod_pre");
                  includeColumns.Add("prod_name2");
                  includeColumns.Add("inv_lx");
                  includeColumns.Add("prod_price_db");
                  includeColumns.Add("ys_bl");
                  includeColumns.Add("jsypbz");
                  includeColumns.Add("fl_price");
                  includeColumns.Add("prod_database");
                  includeColumns.Add("zypj_bz");
                  includeColumns.Add("zyps_bm");
                  includeColumns.Add("zypj_ex_bz");
                  includeColumns.Add("prod_lc");
                  includeColumns.Add("notsell_md");
                  includeColumns.Add("sell_md");
                  includeColumns.Add("prod_gsp_type");
                  includeColumns.Add("zy_box_num");
                  includeColumns.Add("box_midnum_md");
                  includeColumns.Add("prod_gsp_lx");
                  includeColumns.Add("box_midnum_md_m");
                  includeColumns.Add("prod_cctj");
                  includeColumns.Add("auto_dep_bz");
                  includeColumns.Add("gj_type");
                  includeColumns.Add("prod_lc_num");
                  includeColumns.Add("prod_yj_jx");
                  includeColumns.Add("prod_yj_lb");
                  includeColumns.Add("hm_pz_fl");
                  // Define the Products table.
                  DbSyncTableDescription productDescription =
                                                          SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Product",
                                                                                                              includeColumns, serverConn);
  
                  // Add the Table to the scope object.    
                  productScope.Tables.Add(productDescription);
  
                  // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
                  SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);
  
  
  
  
                  if (!serverProvision.ScopeExists("product_mytemp"))
                      serverProvision.Apply();
  
                  // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
                  SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);
  
  
                  if (!clientProvision.ScopeExists("product_mytemp"))
                      clientProvision.Apply();
  
             
  
                  // Create a scope named "prod_det_mytemp" and add tables to it.
                  DbSyncScopeDescription detScope = new DbSyncScopeDescription("prod_det_mytemp");
  
                  // Select the colums to be included in the Collection Object
                  Collection<string> includeColumnsdet = new Collection<string>();
                  includeColumnsdet.Add("prod_no");
                  includeColumnsdet.Add("prod_add");
                  includeColumnsdet.Add("batch_no");
                  includeColumnsdet.Add("stop");
                  includeColumnsdet.Add("avail_date");
                  includeColumnsdet.Add("stop_num");
                  includeColumnsdet.Add("bz");
                  includeColumnsdet.Add("produce_date");
  
                  DbSyncTableDescription detDescription =
                                                          SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Prod_det",
                                                                                                              includeColumnsdet, serverConn);
  
                  // Add the Table to the scope object.    
                  detScope.Tables.Add(detDescription);
  
                  // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
                  SqlSyncScopeProvisioning detserverProvision = new SqlSyncScopeProvisioning(serverConn, detScope);
  
  
  
  
                  if (!detserverProvision.ScopeExists("prod_det_mytemp"))
                      detserverProvision.Apply();
  
                  // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
                  SqlSyncScopeProvisioning detclientProvision = new SqlSyncScopeProvisioning(clientConn, detScope);
  
  
                  if (!detclientProvision.ScopeExists("prod_det_mytemp"))
                      detclientProvision.Apply();
  
          
                  // Create a scope named "prod_det_mytemp" and add tables to it.
                  DbSyncScopeDescription depScope = new DbSyncScopeDescription("prod_dep_mytemp");
  
                  // Select the colums to be included in the Collection Object
                  Collection<string> includeColumnsdep = new Collection<string>();
                  includeColumnsdep.Add("prod_no");
                  includeColumnsdep.Add("prod_add");
                  includeColumnsdep.Add("batch_no");
                  includeColumnsdep.Add("lest_num");
                  includeColumnsdep.Add("Box_Num");
                  includeColumnsdep.Add("avail_date");
  
                  DbSyncTableDescription depDescription =
                                                          SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Prod_dep",
                                                                                                              includeColumnsdep, serverConn);
  
                  // Add the Table to the scope object.    
                  depScope.Tables.Add(depDescription);
  
                  // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
                  SqlSyncScopeProvisioning depserverProvision = new SqlSyncScopeProvisioning(serverConn, depScope);
  
  
  
  
                  if (!depserverProvision.ScopeExists("prod_dep_mytemp"))
                      depserverProvision.Apply();
  
                  // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
                  SqlSyncScopeProvisioning depclientProvision = new SqlSyncScopeProvisioning(clientConn, depScope);
  
  
                  if (!depclientProvision.ScopeExists("prod_dep_mytemp"))
                      depclientProvision.Apply();
  
  
  
  
  
  
                  // Shut down database connections.
  
                  serverConn.Close();
  
                  serverConn.Dispose();
  
                  clientConn.Close();
  
                  clientConn.Dispose();
  
                  return 0;
              }
              catch(Exception ex)
              {
                  sendemail(-1, "", "", "", "", "initialing exception，"+ ex.ToString());
                  return -1;
              }
          }

   private int sync(string remotestr, string localstr)
          {
              try
              {
              
                  SqlConnection serverConn = new SqlConnection(remotestr);
  
                
                  SqlConnection clientConn = new SqlConnection(localstr);
  
                  // Perform Synchronization between SQL Server and the SQL client.
                  SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
  
                  // Create provider for SQL Server
                  SqlSyncProvider serverProvider = new SqlSyncProvider("product_mytemp", serverConn);
  
                  // Set the command timeout and maximum transaction size for the SQL Azure provider.
                  SqlSyncProvider clientProvider = new SqlSyncProvider("product_mytemp", clientConn);
  
                  // Set Local provider of SyncOrchestrator to the server provider
                  syncOrchestrator.LocalProvider = serverProvider;
  
                  // Set Remote provider of SyncOrchestrator to the client provider
                  syncOrchestrator.RemoteProvider = clientProvider;
  
                  // Set the direction of SyncOrchestrator session to Upload and Download
                  syncOrchestrator.Direction = SyncDirectionOrder.Upload;
  
                  // Create SyncOperations Statistics Object
                  SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
  
                  DateTime begintime = syncStats.SyncStartTime;
                  int totalul = syncStats.UploadChangesTotal;
                  int totaldl = syncStats.DownloadChangesTotal;
  
                  serverProvider = new SqlSyncProvider("prod_det_mytemp", serverConn);
                  clientProvider = new SqlSyncProvider("prod_det_mytemp", clientConn);
                  syncStats = syncOrchestrator.Synchronize();
                  totalul = totalul + syncStats.UploadChangesTotal;
                  totaldl = totaldl + syncStats.DownloadChangesTotal;
  
                  serverProvider = new SqlSyncProvider("prod_dep_mytemp", serverConn);
                  clientProvider = new SqlSyncProvider("prod_dep_mytemp", clientConn);
                  syncStats = syncOrchestrator.Synchronize();
                  totalul = totalul + syncStats.UploadChangesTotal;
                  totaldl = totaldl + syncStats.DownloadChangesTotal;
                  // Display the Statistics
  
                  sendemail(0, begintime.ToString(), totalul.ToString(), totaldl.ToString(), syncStats.SyncEndTime.ToString(), "");
                  // Shut down database connections.
                  serverConn.Close();
                  serverConn.Dispose();
                  clientConn.Close();
                  clientConn.Dispose();
                  return 0;
              }
              catch (Exception ex)
              {
                  sendemail(-1, "", "", "", "", "sync exception，" + ex.ToString());
                  return -1;
              }
          }
  

  any helps would be appciated

  regards

  Ken

  
  
  

  • Edited by ken yup Tuesday, December 8, 2015 3:25 PM

  Tuesday, December 8, 2015 3:08 PM