how to synchronize from server to client.

See more:

C#

SQL

database

synchronize

I am working on project where i need to use database synchronization for offline apps. Up to some extent i am successful, i am done with synchronization for client to server using code below -

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;

Now the problem is how to synchronize from server to client. I think changing clientSyncProvider will help but there is nothing like selectServerIdCommand. So don't know how to proceed. I tried lots of searching but not able to fine appropriate ans.