locked
Changes to the DBSyncContext Dataset object prior to applying the changes RRS feed

  • Question

  • I am working on a POC where I am supposed to do the sync between onpremise database and SQL Azure database.

    My On-Premise database is on SQL Server 2005. I am using Sync Framework v2.1. For me simple synchronization is working fine... However , the challenge is , prior to pushing the data to SQL Azure I need to encrypt the data using custom encryption logic . I cannot use the inbuilt SQL Server Encryption as need to encrypt data using encryption component customized as per the requirement and decrypt it when downloading the data back from SQL Azure to SQL Server 2005.

    I have kept the SyncDirectionOrder as UploadandDownload.

    I have added the eventhandler of ApplyingChanges for the localprovider and remoteprovider where I can access the Dataset property of the DBSyncContext. However when I am making changes to the dataset , and accepting the changes, no changes are applied to the context.

    The SyncStatistics shows 0 changes applied.

    Can anyone help me on this ? Am I missing something ?

    Also is there any way around to get my requirement fulfilled ?

     

     

     

    Sunday, January 30, 2011 3:24 AM

Answers

  • I have tired the below code in the ApplyingChanges() event with both Sql and SqlAzure and I can see the changes made to the dataset was applied the DB.

    private

     

     

    void MakeDataChange(DbApplyingChangesEventArgs e, int number)

    {

     

     

    if (e.Context.DataSet != null && e.Context.DataSet.Tables.Count > 0)

    {

     

     

    foreach (DataRow dr in e.Context.DataSet.Tables["TableName"].Rows)

    {

    dr[

     

    "ColumnName"] = number;

    }

    }

    }


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yunwen Bai Friday, February 25, 2011 1:21 AM
    Friday, February 25, 2011 1:05 AM

All replies

  • once you call the acceptChanges on the dataset, the changed rows will be marked as "unchanged" hence they will not be picked up at the server/client side. you won't need to call this method unless you have other reasons.

    I assumed you are using the Ntier configuration, e.g. IIS was used to host the server side providers, if so, using SSL would be a better way to handle this instead of encrypt the decrypt the data itself.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Yunwen Bai Monday, January 31, 2011 3:22 AM
    Monday, January 31, 2011 3:22 AM
  • Hi Yunwen,

    A bit correction in my original query.  After allowing the AcceptChanges method in

    void AzureSyncProvider_ApplyingChanges(object sender, DbApplyingChangesEventArgs e) event, I can see the data is synchronized to the resultant database.

    However, whatever changes were done to the dataset is not commited. I am able to get the original data before the changes were done.

    No I am not using IIS to host the server side providers. I am working on a standalone application where in the Provisioning and Sync code is written and no WCF service is involved.

    Is it appropriate way to code for my requirement ? Please guide

    Below is the code for the same :

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;

    using System.Data.SqlClient;
    using Microsoft.Synchronization.Data.SqlServer;
    using Microsoft.Synchronization.Data;
    using Microsoft.Synchronization;

    namespace WindowsFormsApplication2
    {
        public partial class Form1 : Form
        {
            public static string sqlazureConnectionString = @"Data Source=Server\SQLSERVER2K8;Initial Catalog=AdventureWorks_Azure;Persist Security Info=True;User ID=sa;Password=XXXX";
            public static string sqllocalConnectionString = @"Data Source=Server;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=XXXX";
           
            public static readonly string scopeName = "AllTableSyncGroup";
            public  DataSet Encrypted_ds;
            public  DataSet Decrypted_ds;

            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                try
                {

                    SqlConnection sqlServerConn = new SqlConnection(sqllocalConnectionString);
                    SqlConnection sqlAzureConn = new SqlConnection(sqlazureConnectionString);

                    DbSyncScopeDescription myScope = new DbSyncScopeDescription(scopeName);

                    DbSyncTableDescription CreditCard = SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CreditCard", sqlServerConn);
                    DbSyncTableDescription Product = SqlSyncDescriptionBuilder.GetDescriptionForTable("Production.Product", sqlServerConn);

                    // Add the tables from above to the scope
                    myScope.Tables.Add(CreditCard);
                    myScope.Tables.Add(Product);

                    // Setup SQL Server for sync
                    SqlSyncScopeProvisioning sqlServerProv = new SqlSyncScopeProvisioning(sqlServerConn, myScope);
                    if (!sqlServerProv.ScopeExists(scopeName))
                    {
                        // Apply the scope provisioning.
                        MessageBox.Show("Provisioning SQL Server for sync " + DateTime.Now);
                        sqlServerProv.Apply();
                        MessageBox.Show("Done Provisioning SQL Server for sync " + DateTime.Now);
                    }
                    else
                        MessageBox.Show("SQL Server Database server already provisioned for sync " + DateTime.Now);

                    // Setup SQL Azure for sync
                    SqlSyncScopeProvisioning sqlAzureProv = new SqlSyncScopeProvisioning(sqlAzureConn, myScope);
                    if (!sqlAzureProv.ScopeExists(scopeName))
                    {
                        // Apply the scope provisioning.
                        MessageBox.Show("Provisioning SQL Azure for sync " + DateTime.Now);
                        sqlAzureProv.Apply();
                        MessageBox.Show("Done Provisioning SQL Azure for sync " + DateTime.Now);
                    }
                    else
                        MessageBox.Show("SQL Azure Database server already provisioned for sync " + DateTime.Now);

                    sqlAzureConn.Close();
                    sqlServerConn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

            }

            private void button2_Click(object sender, EventArgs e)
            {
                try
                {

                    SqlConnection sqlServerConn = new SqlConnection(sqllocalConnectionString);
                    SqlConnection sqlAzureConn = new SqlConnection(sqlazureConnectionString);
                   
                    SqlSyncProvider onPremiseSyncProvider = new SqlSyncProvider(scopeName, sqlServerConn);
                    SqlSyncProvider AzureSyncProvider = new SqlSyncProvider(scopeName, sqlAzureConn);

                  
                    AzureSyncProvider.ApplyingChanges+= new EventHandler<DbApplyingChangesEventArgs>(AzureSyncProvider_ApplyingChanges);

                    SyncOrchestrator orch = new SyncOrchestrator();
                    orch.LocalProvider = onPremiseSyncProvider;
                    orch.RemoteProvider = AzureSyncProvider;
                    orch.Direction = SyncDirectionOrder.Upload;
                   
                    MessageBox.Show("ScopeName={0} ", scopeName.ToUpper());
                    MessageBox.Show("Starting Sync " + DateTime.Now);

                    ShowStatistics(orch.Synchronize());
                   
                    sqlAzureConn.Close();
                    sqlServerConn.Close();

                    BindingSource bs = new BindingSource();
                    bs.DataSource = Decrypted_ds;
                    bs.DataMember = Decrypted_ds.Tables[0].TableName;
                    dataGridView1.DataSource = bs;
                    dataGridView1.Refresh();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }

          

            void AzureSyncProvider_ApplyingChanges(object sender, DbApplyingChangesEventArgs e)
            {
                DataSet ExistingOnPremiseData_ds = e.Context.DataSet;
                MessageBox.Show(e.Context.DataSet.Tables[0].Rows.Count.ToString());
                DataSet intermediate_ds=new DataSet();

                intermediate_ds.Tables.Add(e.Context.DataSet.Tables[0].TableName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[0].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[1].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[2].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[3].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[4].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[5].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[6].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[7].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[8].ColumnName);
                intermediate_ds.Tables[0].Columns.Add(e.Context.DataSet.Tables[0].Columns[9].ColumnName);

                for (int i = 0; i < e.Context.DataSet.Tables[0].Rows.Count; i++)
                {
                    object[] objRow={e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(0),e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(1),"Decrypt"+e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(2),e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(3),e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(4),e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(5),e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(6),e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(7),e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(8),e.Context.DataSet.Tables[0].Rows[i].ItemArray.GetValue(9)};
                    intermediate_ds.Tables[0].Rows.Add(objRow);
                }
                for (int i = 1; i < e.Context.DataSet.Tables.Count; i++)
                {
                    intermediate_ds.Tables.Add(e.Context.DataSet.Tables[i].Copy());
                }
                MessageBox.Show(intermediate_ds.Tables[0].Rows.Count.ToString());

                Decrypted_ds = intermediate_ds;
                e.Context.DataSet=null;
                e.Context.DataSet = intermediate_ds.Copy();
                e.Context.DataSet.AcceptChanges();
        
           
            }

            public static void ShowStatistics(SyncOperationStatistics syncStats)
            {
                string message;

                message = "\tSync Start Time :" + syncStats.SyncStartTime.ToString();
                message = message + "\tSync End Time   :" + syncStats.SyncEndTime.ToString();
                message = message + "\tUpload Changes Applied :" + syncStats.UploadChangesApplied.ToString();
                //Console.WriteLine(message);
                message = message + "\tUpload Changes Failed  :" + syncStats.UploadChangesFailed.ToString();
                //Console.WriteLine(message);
                message = message + "\tUpload Changes Total   :" + syncStats.UploadChangesTotal.ToString();
                //Console.WriteLine(message);
                message = message + "\tDownload Changes Applied :" + syncStats.DownloadChangesApplied.ToString();
                //Console.WriteLine(message);
                message = message + "\tDownload Changes Failed  :" + syncStats.DownloadChangesFailed.ToString();
                //Console.WriteLine(message);
                message = message + "\tDownload Changes Total   :" + syncStats.DownloadChangesTotal.ToString();
                MessageBox.Show(message);
            }

            private void Form1_Load(object sender, EventArgs e)
            {
               
            }
        }
    }


     

     

     

    Monday, January 31, 2011 11:35 AM
  • this seems to be contradicting: the first one said the data is synchronized but the second one said the changes is not committed.

          void AzureSyncProvider_ApplyingChanges(object sender, DbApplyingChangesEventArgs e) event, I can see the data is synchronized to the    resultant database.

        However, whatever changes were done to the dataset is not commited. I am able to get the original data before the changes were done.

    I got your point to encrypt the data now. in the applyingChanges event, you won't need to have a temp object of the dataset, you can modif the e.Context.DataSet directly. and again, you should not call the AcceptChanges() there.

    what is the sync statistics  of the sync ? I assume they will be 0s but want to confirm here.

     do you have the applychangefailed event implemented ? is there  a failure reported ?

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 4, 2011 10:22 PM
  • As mentioned by Yunwen, dont call AcceptChanges. Sync Fx tracks Inserts, Updates and Deletes in each row's RowState. When you call AcceptChanges, rows marked as Added and Modified become Unchanged and rows mark as Deleted are removed from the dataset.
    Monday, February 7, 2011 12:42 PM
  • I had tried modifying the dataset directly as well. Whether I call AcceptChanges method, the data that has been inserted in the resultant database is as-is and is not encrypted as per the logic applied.

    But when I had not called AcceptChanges method, then the data was not synchronized at all. i.e my resutlant database did not get any rows that were changed.

    I tried using SyncProgress event. It worked good with it. But just curious shouldnt it be working with ApplyingChanges event ?

     

    Tuesday, February 15, 2011 5:08 AM
  • thanks for the info about using syncProgress event that works.

    I would like to trace down a bit about why this doesn't work if the changes are made to the ApplyingChanges event. would you share out the code you used in the applyingChanges event ( if there are differences with the code above in your previous post )?

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, February 23, 2011 7:06 AM
  • I have tired the below code in the ApplyingChanges() event with both Sql and SqlAzure and I can see the changes made to the dataset was applied the DB.

    private

     

     

    void MakeDataChange(DbApplyingChangesEventArgs e, int number)

    {

     

     

    if (e.Context.DataSet != null && e.Context.DataSet.Tables.Count > 0)

    {

     

     

    foreach (DataRow dr in e.Context.DataSet.Tables["TableName"].Rows)

    {

    dr[

     

    "ColumnName"] = number;

    }

    }

    }


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yunwen Bai Friday, February 25, 2011 1:21 AM
    Friday, February 25, 2011 1:05 AM