none
the database records doesn't sync RRS feed

  • Question

  • 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

Answers

  • have you checked if there conflicts or errors? When you do the sync, you can subscribe to the ApplyChangeFailed event and see if there's a conflict or error.
    • Marked as answer by ken yup Wednesday, December 9, 2015 2:32 PM
    Wednesday, December 9, 2015 3:31 AM
    Moderator

All replies

  • have you checked if there conflicts or errors? When you do the sync, you can subscribe to the ApplyChangeFailed event and see if there's a conflict or error.
    • Marked as answer by ken yup Wednesday, December 9, 2015 2:32 PM
    Wednesday, December 9, 2015 3:31 AM
    Moderator
  • THANK YOU,I have Googled ,the same question was posting on Stackoverflow.com ,you had answered it,thanks anyway.

    the solution is a little bit weird,I had add the ApplyChangeFailed event in the code like the below

                    ((SqlSyncProvider)syncOrchestrator.RemoteProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(RemoteProvider_ApplyChangeFailed);

          SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

    then I had added the event

        private void RemoteProvider_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
            {

               
                    e.Action = ApplyAction.Continue;
                
            }

     but nothing has fired until I viewed the Service1.asmx in the browser,It's back to normal and works.I think maybe it's VS problem.       

    regards

    Ken






    • Edited by ken yup Wednesday, December 9, 2015 2:37 PM
    Wednesday, December 9, 2015 2:32 PM