Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Take Data Offline Using Microsoft Synchronization Services for ADO.NET

, 11 Jan 2008 CPOL
Build occasionally connected applications using Microsoft Synchronization Services for ADO.NET
takedataoffline_demo.zip
OfflineAppDemo-Builder
app
Settings.settings
bin
Release
SyncApplication.exe
SyncApplication.pdb
SyncApplication.vshost.exe
Properties
setup
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

    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rafik Robeal
Chief Technology Officer Raveable.com
United States United States
Entrepreneur, developer and then some. I started Raveable Hotel Reviews in 2008 to help travelers find the best hotel for their vacation with no history of bed bug reports (hint: don't go new york without checking bed bugs in NYC hotels page), cases of personal property theft, or food poisoning incidents.
 
Prior to Raveable.com, I was a development lead at Microsoft Corporation. I led the team developing the next generation social networking for Windows Mobile. Before that I played leading role in the design and development of Microsoft Sync Framework, Sync Services for ADO.NET, SQL Server Replication and Windows File Replication.

My Company: Raveable Hotel Reviews
My Sync Blog: The Synchronizer
My Sync WebSite: www.syncguru.com

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 11 Jan 2008
Article Copyright 2007 by Rafik Robeal
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid