Click here to Skip to main content
15,881,803 members
Please Sign up or sign in to vote.
1.16/5 (4 votes)
C#
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;
Posted
Updated 31-Mar-14 20:43pm
v5
Comments
ZurdoDev 31-Mar-14 8:58am    
What's your question?
Dave Kreskowiak 31-Mar-14 12:35pm    
Congratulations! Thanks for letting us know you're working on something.

Now, did you have a question in that post or did you just want to tell the world you're working on something?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900