Take Data Offline Using Microsoft Synchronization Services for ADO.NET

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

using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServerCe;
using Microsoft.Synchronization.Data.Server;
using System.Data.SqlServerCe;


namespace SyncApplication
{
    public partial class SyncForm : Form
    {
        ProgressForm _progressForm;        

        public SyncForm()
        {
            InitializeComponent();            
            dbPathTextBox.Text = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\clientdb.sdf";


            if (null == textServerMachine.Text || 0 == textServerMachine.Text.Length)
            {
                textServerMachine.Text = Environment.MachineName;
            }

            if (null == textClientMachine.Text || 0 == textClientMachine.Text.Length)
            {
                textClientMachine.Text = Environment.MachineName;
            }

            _progressForm = null;
        }        


        //
        // Synchronize Call
        //
        private void buttonSynchronize_Click(object sender, EventArgs e)
        {

            try
            {                
               
                // 
                // 1. Create instance of the sync components (client, agent, server)
                // This demo illustrates direct connection to server database. In this scenario, 
                // sync components - client provider, sync agent and server provider - reside at
                // the client side. On the server, each table might need to be extended with sync
                // related columns to store metadata. This demo adds three more columns to the
                // orders and order_details tables for bidirectional sync. The changes are illustrated
                // in demo.sql file.
                //
                //
                DbServerSyncProvider serverSyncProvider = new DbServerSyncProvider();

                SyncAgent syncAgent = new SyncAgent();               
                syncAgent.RemoteProvider = serverSyncProvider;


                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                //
                // 2. Prepare server db connection and attach it to the sync agent
                //
                builder["Data Source"] = textServerMachine.Text;
                builder["integrated Security"] = true;
                builder["Initial Catalog"] = "pub";
                SqlConnection serverConnection = new SqlConnection(builder.ConnectionString);

                serverSyncProvider.Connection = serverConnection;

             
                //
                // 3. Prepare client db connection and attach it to the sync provider
                //                                
                string connString = "Data Source=" + dbPathTextBox.Text;

                if (false == File.Exists(dbPathTextBox.Text))
                {                 
                    SqlCeEngine clientEngine = new SqlCeEngine(connString);
                    clientEngine.CreateDatabase();
                    clientEngine.Dispose();
                }                
                SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(connString);
                syncAgent.LocalProvider = clientSyncProvider;

                
                //
                // 4. Create SyncTables and SyncGroups
                // To sync a table, a SyncTable object needs to be created and setup with desired properties:
                // TableCreationOption tells the agent how to initialize the new table in the local database
                // SyncDirection is how changes from with respect to client {Download, Upload, Bidirectional or Snapshot}
                //
                //
                SyncTable tableOrders = new SyncTable("orders");
                tableOrders.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
                tableOrders.SyncDirection = SyncDirection.Bidirectional;

                SyncTable tableOrderDetails = new SyncTable("order_details");
                tableOrderDetails.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
                tableOrderDetails.SyncDirection = SyncDirection.Bidirectional;

                //
                // Sync changes for both tables as one bunch, using SyncGroup object
                // This is important if the tables has PK-FK relationship, grouping will ensure that 
                // and FK change won't be applied before its PK is applied
                //
                //
                SyncGroup orderGroup = new SyncGroup("AllChanges");
                tableOrders.SyncGroup = orderGroup;
                tableOrderDetails.SyncGroup = orderGroup;

                syncAgent.Configuration.SyncTables.Add(tableOrders);
                syncAgent.Configuration.SyncTables.Add(tableOrderDetails);



                //
                // 5. Create sync adapter for each sync table and attach it to the server provider
                // Following DataAdapter style in ADO.NET, SyncAdapte is the equivelent for
                // Sync. SyncAdapterBuilder is a helper class to simplify the process of 
                // creating sync commands.                 
                //
                //

                SqlSyncAdapterBuilder ordersBuilder = new SqlSyncAdapterBuilder();                
                ordersBuilder.Connection = serverConnection;
                ordersBuilder.SyncDirection = SyncDirection.Bidirectional;

                // base table
                ordersBuilder.TableName = "orders";
                ordersBuilder.DataColumns.Add("order_id");
                ordersBuilder.DataColumns.Add("order_date");

                // tombstone table
                ordersBuilder.TombstoneTableName = "orders_tombstone";
                ordersBuilder.TombstoneDataColumns.Add("order_id");
                ordersBuilder.TombstoneDataColumns.Add("order_date");
                
                // tracking\sync columns
                ordersBuilder.CreationTrackingColumn = @"create_timestamp";
                ordersBuilder.UpdateTrackingColumn = @"update_timestamp";
                ordersBuilder.DeletionTrackingColumn = @"update_timestamp";
                ordersBuilder.UpdateOriginatorIdColumn = @"update_originator_id";
                                               
                SyncAdapter ordersSyncAdapter = ordersBuilder.ToSyncAdapter();
                ((SqlParameter)ordersSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_last_received_anchor"]).DbType = DbType.Binary;
                ((SqlParameter)ordersSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_new_received_anchor"]).DbType = DbType.Binary;
                serverSyncProvider.SyncAdapters.Add(ordersSyncAdapter);


                SqlSyncAdapterBuilder orderDetailsBuilder = new SqlSyncAdapterBuilder();
                orderDetailsBuilder.SyncDirection = SyncDirection.Bidirectional;
                orderDetailsBuilder.Connection = serverConnection;

                // base table
                orderDetailsBuilder.TableName = "order_details";
                orderDetailsBuilder.DataColumns.Add("order_id");
                orderDetailsBuilder.DataColumns.Add("order_details_id");
                orderDetailsBuilder.DataColumns.Add("product");
                orderDetailsBuilder.DataColumns.Add("quantity");

                // tombstone table
                orderDetailsBuilder.TombstoneTableName = "order_details_tombstone";
                orderDetailsBuilder.TombstoneDataColumns.Add("order_id");
                orderDetailsBuilder.TombstoneDataColumns.Add("order_details_id");
                orderDetailsBuilder.TombstoneDataColumns.Add("product");
                orderDetailsBuilder.TombstoneDataColumns.Add("quantity");

                // tracking\sync columns
                orderDetailsBuilder.CreationTrackingColumn = @"create_timestamp";
                orderDetailsBuilder.UpdateTrackingColumn = @"update_timestamp";
                orderDetailsBuilder.DeletionTrackingColumn = @"update_timestamp";
                orderDetailsBuilder.UpdateOriginatorIdColumn = @"update_originator_id";
                

                SyncAdapter orderDetailsSyncAdapter = orderDetailsBuilder.ToSyncAdapter();
                ((SqlParameter)orderDetailsSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_last_received_anchor"]).DbType = DbType.Binary;
                ((SqlParameter)orderDetailsSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_new_received_anchor"]).DbType = DbType.Binary;
                serverSyncProvider.SyncAdapters.Add(orderDetailsSyncAdapter);


                //
                // 6. Setup provider wide commands
                // There are two commands on the provider itself and not on a table sync adapter:
                // SelectNewAnchorCommand: Returns the new high watermark for current sync, this value is
                // stored at the client and used the low watermark in the next sync
                // SelectClientIdCommand: Finds out the client ID on the server, this command helps
                // avoid downloading changes that the client had made before and applied to the server
                //
                //

                // select new anchor command
                SqlCommand anchorCmd = new SqlCommand();
                anchorCmd.CommandType = CommandType.Text;
                anchorCmd.CommandText = "Select @" + SyncSession.SyncNewReceivedAnchor + " = @@DBTS";  // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"
                anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;

                serverSyncProvider.SelectNewAnchorCommand = anchorCmd;

                // client ID command (give the client id of 1)
                // in remote server scenario (middle tear), this command will reference a local client table for the ID               
                SqlCommand clientIdCmd = new SqlCommand();
                clientIdCmd.CommandType = CommandType.Text;
                clientIdCmd.CommandText = "SELECT @" + SyncSession.SyncOriginatorId + " = 1";
                clientIdCmd.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;

                serverSyncProvider.SelectClientIdCommand = clientIdCmd;
                
                
                //
                // 7. Kickoff sync process                
                //

                // Setup the progress form and sync progress event handler                
                _progressForm = new ProgressForm();
                _progressForm.Show();

                clientSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(ShowProgress);
                clientSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(ShowFailures);
                serverSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(ShowProgress);                
                SyncStatistics syncStats = syncAgent.Synchronize();

                // Update the UI
                _progressForm.EnableClose();
                _progressForm = null;
                buttonRefreshOrders_Click(null, null);
                buttonRefreshOrderDetails_Click(null, null);           
            }

            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
                if (_progressForm != null)
                {
                    _progressForm.EnableClose();
                    _progressForm = null;
                }
            }
        }

        #region UI Code

        private void buttonExit_Click(object sender, EventArgs e)
        {
            Application.Exit();            
        }

        public void ShowProgress(object syncAgent, SyncProgressEventArgs args)
        {
            if (null != _progressForm)
            {
                _progressForm.Report(args);
            }
        }

        public void ShowFailures(object syncAgent, ApplyChangeFailedEventArgs args)
        {
            args.Action = ApplyAction.Continue;
        }

        protected static void RemoveServerTrackingColumns(DataTable dataTable)
        {
            if (dataTable.Columns.Contains("update_timestamp"))
            {
                dataTable.Columns.Remove("update_timestamp");
            }

            if (dataTable.Columns.Contains("create_timestamp"))
            {
                dataTable.Columns.Remove("create_timestamp");
            }

            if (dataTable.Columns.Contains("update_originator_id"))
            {
                dataTable.Columns.Remove("update_originator_id");
            }

        }

        protected static void RemoveClientTrackingColumns(DataTable dataTable)
        {
            if (dataTable.Columns.Contains("__sysInsertTxBsn"))
            {
                dataTable.Columns.Remove("__sysInsertTxBsn");
            }

            if (dataTable.Columns.Contains("__sysChangeTxBsn"))
            {
                dataTable.Columns.Remove("__sysChangeTxBsn");
            }
        }

        private void buttonRefreshOrders_Click(object sender, EventArgs e)
        {
            try
            {                
                if (radioSubOrders.Checked)
                {
                    CheckClientDb();

                    string connString = "Data Source=" + dbPathTextBox.Text;
                    string commandString = "Select * from orders";

                    SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(commandString, connString);

                    DataTable dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);

                    RemoveClientTrackingColumns(dataTable);
                    dataGridOrders.DataSource = dataTable;

                    buttonApplyOrdersInserts.Enabled = true;
                    buttonApplyOrdersUpdates.Enabled = true;
                    buttonApplyOrdersDeletes.Enabled = true;
                }
                else
                {
                    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                    builder["Data Source"] = textServerMachine.Text;
                    builder["integrated Security"] = true;
                    builder["Initial Catalog"] = "pub";

                    string commandString = "Select * from orders";

                    SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, builder.ConnectionString);
                    DataTable dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);
                    RemoveServerTrackingColumns(dataTable);

                    dataGridOrders.DataSource = dataTable;
                }                

                Application.DoEvents();
            }           
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        private void buttonRefreshOrderDetails_Click(object sender, EventArgs e)
        {
            try
            {                
                if (radioSubOrderDetails.Checked)
                {
                    CheckClientDb();

                    string connString = "Data Source=" + dbPathTextBox.Text;                                       
                    string commandString = "Select * from order_details";

                    SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(commandString, connString);

                    DataTable dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);
                    
                    RemoveClientTrackingColumns(dataTable);
                    dataGridOrderDetails.DataSource = dataTable;

                    buttonApplyOrderDetailsInserts.Enabled = true;
                    buttonApplyOrderDetailsUpdates.Enabled = true;
                    buttonApplyOrderDetailsDeletes.Enabled = true;
                }
                else
                {
                    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                    builder["Data Source"] = textServerMachine.Text;
                    builder["integrated Security"] = true;
                    builder["Initial Catalog"] = "pub";

                    string commandString = "Select * from order_details";

                    SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, builder.ConnectionString);
                    DataTable dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);

                    RemoveServerTrackingColumns(dataTable);
                    dataGridOrderDetails.DataSource = dataTable;
                }

                Application.DoEvents();
            }
            
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }
        

        private void radioSubOrders_CheckedChanged(object sender, EventArgs e)
        {
            buttonApplyOrdersInserts.Enabled = true;
            buttonApplyOrdersUpdates.Enabled = true;
            buttonApplyOrdersDeletes.Enabled = true;
            buttonRefreshOrders_Click(sender, e);
        }

        private void radioPubOrders_CheckedChanged(object sender, EventArgs e)
        {
            buttonApplyOrdersInserts.Enabled = false;
            buttonApplyOrdersUpdates.Enabled = false;
            buttonApplyOrdersDeletes.Enabled = false;
            
        }

        private void radioSubOrderDetails_CheckedChanged(object sender, EventArgs e)
        {
            buttonApplyOrderDetailsInserts.Enabled = true;
            buttonApplyOrderDetailsUpdates.Enabled = true;
            buttonApplyOrderDetailsDeletes.Enabled = true;
            buttonRefreshOrderDetails_Click(sender, e);
        }

        private void radioPubOrderDetails_CheckedChanged(object sender, EventArgs e)
        {
            buttonApplyOrderDetailsInserts.Enabled = false;
            buttonApplyOrderDetailsUpdates.Enabled = false;
            buttonApplyOrderDetailsDeletes.Enabled = false;
            
        }

#endregion

        #region Random Inserts, Updates and Delets to client DB

        private void CheckClientDb()
        {
            if (false == File.Exists(dbPathTextBox.Text))
            {
                throw new FileNotFoundException("Client DB not found. You need to Synchronize first before excuting random operations on the local db.");
            }
        }

        private void buttonApplyOrdersInserts_Click(object sender, EventArgs e)
        {            
            Random rand = new Random();            
            string connString = "Data Source=" + dbPathTextBox.Text;
            SqlCeConnection conn = new SqlCeConnection(connString);            
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandText = "INSERT INTO orders(order_id, order_date) values(@order_id, @order_date)";
            cmd.Parameters.AddWithValue("@order_id", rand.Next((int)(DateTime.Now.ToFileTime() % 10000)));
            cmd.Parameters.AddWithValue("@order_date", DateTime.Now);            
            cmd.Connection = conn;
            
            try
            {
                CheckClientDb();
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }

        }

        private void buttonApplyOrderDetailsInserts_Click(object sender, EventArgs e)
        {            
            Random rand = new Random();            
            string connString = "Data Source=" + dbPathTextBox.Text;
            SqlCeConnection conn = new SqlCeConnection(connString);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandText = "INSERT INTO order_details(order_id, order_details_id, product, quantity) values(@order_id, @order_details_id, @product, @quantity)";
            cmd.Parameters.AddWithValue("@order_id", rand.Next((int)(DateTime.Now.ToFileTime() % 10000)));
            cmd.Parameters.AddWithValue("@order_details_id", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));
            cmd.Parameters.AddWithValue("@product", "NEW");
            cmd.Parameters.AddWithValue("@quantity", rand.Next((int)(DateTime.Now.ToFileTime() % 1000000)));

            cmd.Connection = conn;

            try
            {
                CheckClientDb();
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        private void buttonApplyOrdersUpdates_Click(object sender, EventArgs e)
        {            
            Random rand = new Random();
            string connString = "Data Source=" + dbPathTextBox.Text;
            SqlCeConnection conn = new SqlCeConnection(connString);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandText = "UPDATE orders SET order_date = @order_date where order_id % @factor < 10";
            cmd.Parameters.AddWithValue("@factor", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));
            cmd.Parameters.AddWithValue("@order_date", DateTime.Now);
            cmd.Connection = conn;

            try
            {
                CheckClientDb();
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        private void buttonApplyOrderDetailsUpdates_Click(object sender, EventArgs e)
        {            
            Random rand = new Random();
            string connString = "Data Source=" + dbPathTextBox.Text;
            SqlCeConnection conn = new SqlCeConnection(connString);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandText = "UPDATE order_details SET quantity = @quantity, product = @product where order_id % @factor < 10";
            cmd.Parameters.AddWithValue("@factor", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));            
            cmd.Parameters.AddWithValue("@product", "UPD");
            cmd.Parameters.AddWithValue("@quantity", rand.Next((int)(DateTime.Now.ToFileTime() % 1000000)));

            cmd.Connection = conn;

            try
            {
                CheckClientDb();
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        private void buttonApplyOrdersDeletes_Click(object sender, EventArgs e)
        {            
            Random rand = new Random();
            string connString = "Data Source=" + dbPathTextBox.Text;
            SqlCeConnection conn = new SqlCeConnection(connString);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandText = "DELETE orders WHERE order_id % @factor < 5";
            cmd.Parameters.AddWithValue("@factor", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));            
            cmd.Connection = conn;

            try
            {
                CheckClientDb();
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        private void buttonApplyOrderDetailsDeletes_Click(object sender, EventArgs e)
        {            
            Random rand = new Random();
            string connString = "Data Source=" + dbPathTextBox.Text;
            SqlCeConnection conn = new SqlCeConnection(connString);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandText = "DELETE order_details WHERE order_id % @factor < 10";
            cmd.Parameters.AddWithValue("@factor", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));            
            cmd.Connection = conn;

            try
            {
                CheckClientDb();
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

    }
}