public SampleSyncAgent(string localProviderConnString, string remoteProviderConnString) { //Instantiate the sample provider that allows us to create a provider //for both of the peers that are being synchronized. SampleSyncProvider sampleSyncProvider = new SampleSyncProvider(); //Instantiate a DbSyncProvider for the local peer and the remote peer. //For example, if this code is running at peer1 and is //synchronizing with peer2, peer1 would be the local provider //and peer2 the remote provider. //<snippetOCSv2_CS_Basic_Peer_DbSyncProvider> DbSyncProvider localProvider = new DbSyncProvider(); DbSyncProvider remoteProvider = new DbSyncProvider(); //Create a provider by using the SetupSyncProvider on the sample class. sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider); localProvider.SyncProviderPosition = SyncProviderPosition.Local; sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider); remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote; //</snippetOCSv2_CS_Basic_Peer_DbSyncProvider> //Specify the local and remote providers that should be synchronized, //and the direction and order of changes. In this case, changes are first //uploaded from remote to local and then downloaded in the other direction. this.LocalProvider = localProvider; this.RemoteProvider = remoteProvider; this.Direction = SyncDirectionOrder.UploadAndDownload; }
// // Synchronize Call // private void buttonSynchronize_Click(object sender, EventArgs e) { try { // // 1. Create instance of the sync components (peer, agent, peer) // This demo illustrates direct connection to server database. In this scenario, // all sync components reside in the sample process. // DbSyncProvider localProvider = new DbSyncProvider(); DbSyncProvider remoteProvider = new DbSyncProvider(); localProvider = SetupSyncProvider(GetFromPeerConnectionString(), localProvider); localProvider.SyncProviderPosition = SyncProviderPosition.Local; localProvider.ChangesSelected += new EventHandler <DbChangesSelectedEventArgs>(ChangesSelected); remoteProvider = SetupSyncProvider(GetToPeerConnectionString(), remoteProvider); remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote; remoteProvider.ChangesApplied += new EventHandler <DbChangesAppliedEventArgs>(ChangesApplied); SyncOrchestrator syncOrchestrator = new SyncOrchestrator(); // sync direction : local -> remote syncOrchestrator.LocalProvider = localProvider; syncOrchestrator.RemoteProvider = remoteProvider; syncOrchestrator.Direction = SyncDirectionOrder.Upload; _progressForm = new ProgressForm(); _progressForm.Show(); SyncOperationStatistics syncStats = syncOrchestrator.Synchronize(); _progressForm.ShowStatistics(syncStats); // Update the UI _progressForm.EnableClose(); _progressForm = null; } catch (DbOutdatedSyncException ex) { MessageBox.Show(ex.Message); MessageBox.Show("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() + " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString()); } catch (Exception ex) { MessageBox.Show(ex.Message); if (_progressForm != null) { _progressForm.EnableClose(); _progressForm = null; } } }
public static DbSyncProvider CreateSyncProvider(int peerID) { DbSyncProvider syncProvider = new DbSyncProvider(); syncProvider.ScopeName = "CardsScope"; syncProvider.Connection = new SqlConnection(DBHelper.GetPeerConncetionStringByPeerID(peerID)); syncProvider.SyncAdapters.Add(SyncAdapterHelper.CreateSyncAdpters()); syncProvider.SelectNewTimestampCommand = SyncProviderCommandHelper.GetSelectNewTimestampCommand(); syncProvider.SelectScopeInfoCommand = SyncProviderCommandHelper.GetSelectScopeInfoCommand(); syncProvider.UpdateScopeInfoCommand = SyncProviderCommandHelper.GetUpdateScopeInfoCommand(); return(syncProvider); }
//For Compact databases that are not initialized with a snapshot, //get the schema and initial data from a server database. //<snippetOCSv2_CS_Basic_PeerWithCe_CheckIfProviderNeedsSchema> private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema) { //If one of the providers is a SqlCeSyncProvider and it needs //to be initialized, retrieve the schema from the other provider //if that provider is a DbSyncProvider; otherwise configure a //DbSyncProvider, connect to the server, and retrieve the schema. if (providerToCheck != null) { SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(); SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection; string scopeName = providerToCheck.ScopeName; if (!ceConfig.ScopeExists(scopeName, ceConn)) { DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription(); ceConfig.ScopeDescription = scopeDesc; ceConfig.Apply(ceConn); } } }
public void Synchronization(int localPeerID, int remotePeerID) { try { localSyncProvider = SyncProviderHelper.CreateSyncProvider(localPeerID); localSyncProvider.SyncProviderPosition = SyncProviderPosition.Local; remoteSyncProvider = SyncProviderHelper.CreateSyncProvider(remotePeerID); remoteSyncProvider.SyncProviderPosition = SyncProviderPosition.Remote; collaborationSyncAgent = new SyncOrchestrator(); collaborationSyncAgent.LocalProvider = localSyncProvider; collaborationSyncAgent.RemoteProvider = remoteSyncProvider; collaborationSyncAgent.Direction = SyncDirectionOrder.UploadAndDownload; syncStatistics = collaborationSyncAgent.Synchronize(); } catch (Exception exc) { Console.WriteLine(exc.ToString()); } }
private void buttonCleanupOrderDetailsMetadata_Click(object sender, EventArgs e) { try { DbSyncProvider provider = new DbSyncProvider(); provider = SetupSyncProvider(GetConnectionString(), provider); if (provider.CleanupMetadata() == false) { MessageBox.Show("Metadata cleanup failed, most likely due to concurrency issues. Please retry"); } } catch (Exception ex) { MessageBox.Show(ex.Message); if (_progressForm != null) { _progressForm.EnableClose(); _progressForm = null; } } }
public SampleSyncAgent(string localProviderConnString, string remoteProviderConnString) { //Instantiate the sample provider that allows us to create a provider //for both of the peers that are being synchronized. SampleSyncProvider sampleSyncProvider = new SampleSyncProvider(); //Instantiate a DbSyncProvider for the local peer and the remote peer. //For example, if this code is running at peer1 and is //synchronizing with peer2, peer1 would be the local provider //and peer2 the remote provider. DbSyncProvider localProvider = new DbSyncProvider(); DbSyncProvider remoteProvider = new DbSyncProvider(); //Create a provider by using the SetupSyncProvider on the sample class. sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider); localProvider.SyncProviderPosition = SyncProviderPosition.Local; sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider); remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote; //Specify event handlers for the ApplyChangeFailed event for each provider. //The handlers are used to resolve conflicting rows and log error information. //<snippetOCSv2_CS_Peer_Conflicts_DbApplyChangeFailedEventHandler> localProvider.ApplyChangeFailed += new EventHandler <DbApplyChangeFailedEventArgs>(dbProvider_ApplyChangeFailed); remoteProvider.ApplyChangeFailed += new EventHandler <DbApplyChangeFailedEventArgs>(dbProvider_ApplyChangeFailed); //</snippetOCSv2_CS_Peer_Conflicts_DbApplyChangeFailedEventHandler> //Specify the local and remote providers that should be synchronized, //and the direction and order of changes. In this case, changes are first //uploaded from remote to local and then downloaded in the other direction. this.LocalProvider = localProvider; this.RemoteProvider = remoteProvider; this.Direction = SyncDirectionOrder.UploadAndDownload; _localProviderDatabase = localProvider.Connection.Database.ToString(); _remoteProviderDatabase = remoteProvider.Connection.Database.ToString(); }
private void buttonCleanupOrderMetadata_Click(object sender, EventArgs e) { try { DbSyncProvider provider = new DbSyncProvider(); provider = SetupSyncProvider(GetConnectionString(), provider); if (provider.CleanupMetadata() == false) { MessageBox.Show("Metadata cleanup failed. Please retry"); } } catch (Exception ex) { MessageBox.Show(ex.Message); if (_progressForm != null) { _progressForm.EnableClose(); _progressForm = null; } } }
DbSyncProvider SetupSyncProvider(string connectionString, DbSyncProvider peerProvider) { const int TombstoneAgingInHours = 100; SqlConnection connection = new SqlConnection(connectionString); peerProvider.Connection = connection; // // 1. Create sync adapter for each sync table and attach it to the provider // Following DataAdapter style in ADO.NET, DbSyncAdapter is the equivelent for sync. // The code below shows how to create DbSyncAdapter objects for orders // and order_details tables using stored procedures stored on the database. // peerProvider.ScopeName = "Sales"; // orders table DbSyncAdapter adaptorOrders = new DbSyncAdapter("orders"); adaptorOrders.RowIdColumns.Add("order_id"); // select incremental changes command SqlCommand chgsOrdersCmd = new SqlCommand(); chgsOrdersCmd.CommandType = CommandType.StoredProcedure; chgsOrdersCmd.CommandText = "sp_orders_selectchanges"; chgsOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int); chgsOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); adaptorOrders.SelectIncrementalChangesCommand = chgsOrdersCmd; // insert row command SqlCommand insOrdersCmd = new SqlCommand(); insOrdersCmd.CommandType = CommandType.StoredProcedure; insOrdersCmd.CommandText = "sp_orders_applyinsert"; insOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); insOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime); insOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.InsertCommand = insOrdersCmd; // update row command SqlCommand updOrdersCmd = new SqlCommand(); updOrdersCmd.CommandType = CommandType.StoredProcedure; updOrdersCmd.CommandText = "sp_orders_applyupdate"; updOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); updOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime); updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.UpdateCommand = updOrdersCmd; // delete row command SqlCommand delOrdersCmd = new SqlCommand(); delOrdersCmd.CommandType = CommandType.StoredProcedure; delOrdersCmd.CommandText = "sp_orders_applydelete"; delOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); delOrdersCmd.Parameters.Add("@sync_force_write", SqlDbType.Int); delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.DeleteCommand = delOrdersCmd; // get row command SqlCommand selRowOrdersCmd = new SqlCommand(); selRowOrdersCmd.CommandType = CommandType.StoredProcedure; selRowOrdersCmd.CommandText = "sp_orders_selectrow"; selRowOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); adaptorOrders.SelectRowCommand = selRowOrdersCmd; // insert row metadata command SqlCommand insMetadataOrdersCmd = new SqlCommand(); insMetadataOrdersCmd.CommandType = CommandType.StoredProcedure; insMetadataOrdersCmd.CommandText = "sp_orders_insertmetadata"; insMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.InsertMetadataCommand = insMetadataOrdersCmd; // update row metadata command SqlCommand updMetadataOrdersCmd = new SqlCommand(); updMetadataOrdersCmd.CommandType = CommandType.StoredProcedure; updMetadataOrdersCmd.CommandText = "sp_orders_updatemetadata"; updMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.UpdateMetadataCommand = updMetadataOrdersCmd; // delete row metadata command SqlCommand delMetadataOrdersCmd = new SqlCommand(); delMetadataOrdersCmd.CommandType = CommandType.StoredProcedure; delMetadataOrdersCmd.CommandText = "sp_orders_deletemetadata"; delMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.DeleteMetadataCommand = delMetadataOrdersCmd; // get tombstones for cleanup SqlCommand selTombstonesOrdersCmd = new SqlCommand(); selTombstonesOrdersCmd.CommandType = CommandType.StoredProcedure; selTombstonesOrdersCmd.CommandText = "sp_orders_selecttombstones"; selTombstonesOrdersCmd.Parameters.Add("@tombstone_aging_in_hours", SqlDbType.Int).Value = TombstoneAgingInHours; adaptorOrders.SelectMetadataForCleanupCommand = selTombstonesOrdersCmd; peerProvider.SyncAdapters.Add(adaptorOrders); // order_details table DbSyncAdapter adaptorOrderDetails = new DbSyncAdapter("order_details"); adaptorOrderDetails.RowIdColumns.Add("order_id"); // select incremental inserts command SqlCommand chgsOrderDetailsCmd = new SqlCommand(); chgsOrderDetailsCmd.CommandType = CommandType.StoredProcedure; chgsOrderDetailsCmd.CommandText = "sp_order_details_selectchanges"; chgsOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int); chgsOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); adaptorOrderDetails.SelectIncrementalChangesCommand = chgsOrderDetailsCmd; // insert row command SqlCommand insOrderDetailsCmd = new SqlCommand(); insOrderDetailsCmd.CommandType = CommandType.StoredProcedure; insOrderDetailsCmd.CommandText = "sp_order_details_applyinsert"; insOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); insOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int); insOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100); insOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int); insOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.InsertCommand = insOrderDetailsCmd; // update row command SqlCommand updOrderDetailsCmd = new SqlCommand(); updOrderDetailsCmd.CommandType = CommandType.StoredProcedure; updOrderDetailsCmd.CommandText = "sp_order_details_applyupdate"; updOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); updOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int); updOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100); updOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int); updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.UpdateCommand = updOrderDetailsCmd; // delete row command SqlCommand delOrderDetailsCmd = new SqlCommand(); delOrderDetailsCmd.CommandType = CommandType.StoredProcedure; delOrderDetailsCmd.CommandText = "sp_order_details_applydelete"; delOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); delOrderDetailsCmd.Parameters.Add("@sync_force_write", SqlDbType.Int); delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.DeleteCommand = delOrderDetailsCmd; // get row command SqlCommand selRowOrderDetailsCmd = new SqlCommand(); selRowOrderDetailsCmd.CommandType = CommandType.StoredProcedure; selRowOrderDetailsCmd.CommandText = "sp_order_details_selectrow"; selRowOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); adaptorOrderDetails.SelectRowCommand = selRowOrderDetailsCmd; // insert row metadata command SqlCommand insMetadataOrderDetailsCmd = new SqlCommand(); insMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure; insMetadataOrderDetailsCmd.CommandText = "sp_order_details_insertmetadata"; insMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.InsertMetadataCommand = insMetadataOrderDetailsCmd; // update row metadata command SqlCommand updMetadataOrderDetailsCmd = new SqlCommand(); updMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure; updMetadataOrderDetailsCmd.CommandText = "sp_order_details_updatemetadata"; updMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.UpdateMetadataCommand = updMetadataOrderDetailsCmd; // delete row metadata command SqlCommand delMetadataOrderDetailsCmd = new SqlCommand(); delMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure; delMetadataOrderDetailsCmd.CommandText = "sp_order_details_deletemetadata"; delMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.DeleteMetadataCommand = delMetadataOrderDetailsCmd; // get tombstones for cleanup SqlCommand selTombstonesOrderDetailsCmd = new SqlCommand(); selTombstonesOrderDetailsCmd.CommandType = CommandType.StoredProcedure; selTombstonesOrderDetailsCmd.CommandText = "sp_order_details_selecttombstones"; selTombstonesOrderDetailsCmd.Parameters.Add("@tombstone_aging_in_hours", SqlDbType.Int).Value = TombstoneAgingInHours; adaptorOrderDetails.SelectMetadataForCleanupCommand = selTombstonesOrderDetailsCmd; peerProvider.SyncAdapters.Add(adaptorOrderDetails); // // 2. Setup provider wide commands // There are few commands on the provider itself and not on a table sync adapter: // SelectNewTimestampCommand: Returns the new high watermark for current sync // SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge and scope version (timestamp) // UpdateScopeInfoCommand: Sets the new values for sync knowledge and cleanup knowledge // SqlCommand anchorCmd = new SqlCommand(); anchorCmd.CommandType = CommandType.Text; anchorCmd.CommandText = "select @" + DbSyncSession.SyncNewTimestamp + " = @@DBTS"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1" anchorCmd.Parameters.Add("@" + DbSyncSession.SyncNewTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output; peerProvider.SelectNewTimestampCommand = anchorCmd; // // Select local replica info // SqlCommand selReplicaInfoCmd = new SqlCommand(); selReplicaInfoCmd.CommandType = CommandType.Text; selReplicaInfoCmd.CommandText = "select " + "@" + DbSyncSession.SyncScopeId + " = scope_Id, " + "@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " + "@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " + "@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " + "from scope_info " + "where scope_name = @" + DbSyncSession.SyncScopeName; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output; peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd; // // Update local replica info // SqlCommand updReplicaInfoCmd = new SqlCommand(); updReplicaInfoCmd.CommandType = CommandType.Text; updReplicaInfoCmd.CommandText = "update scope_info set " + "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " + "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " + "where scope_name = @" + DbSyncSession.SyncScopeName + " ;" + "set @" + DbSyncSession.SyncRowCount + " = @@rowcount"; updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd; // // 3. Track sync process // peerProvider.SyncProgress += new EventHandler <DbSyncProgressEventArgs>(ShowProgress); peerProvider.ApplyChangeFailed += new EventHandler <DbApplyChangeFailedEventArgs>(ShowFailures); peerProvider.SyncPeerOutdated += new EventHandler <DbOutdatedEventArgs>(ShowOutdated); return(peerProvider); }
//</snippetOCSv2_CS_Peer_Cleanup_ConfigureCeSyncProvider> //<snippetOCSv2_CS_Peer_Cleanup_ConfigureDbSyncProvider> public DbSyncProvider ConfigureDbSyncProvider(string peerConnString, int metadataAgingInDays) { DbSyncProvider sampleDbProvider = new DbSyncProvider(); //<snippetOCSv2_CS_Peer_Cleanup_Scope> SqlConnection peerConnection = new SqlConnection(peerConnString); sampleDbProvider.Connection = peerConnection; sampleDbProvider.ScopeName = "Sales"; //</snippetOCSv2_CS_Peer_Cleanup_Scope> //Create a DbSyncAdapter object for the Customer table and associate it //with the DbSyncProvider. Following the DataAdapter style in ADO.NET, //DbSyncAdapter is the equivalent for synchronization. The commands that //are specified for the DbSyncAdapter object call stored procedures //that are created in each peer database. //<snippetOCSv2_CS_Peer_Cleanup_AdapterCustomer> DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer"); //Specify the primary key, which Sync Services uses //to identify each row during synchronization. adapterCustomer.RowIdColumns.Add("CustomerId"); //</snippetOCSv2_CS_Peer_Cleanup_AdapterCustomer> //Specify the command to select incremental changes. //In this command and other commands, session variables are //used to pass information at runtime. DbSyncSession.SyncMetadataOnly //and SyncMinTimestamp are two of the string constants that //the DbSyncSession class exposes. You could also include //@sync_metadata_only and @sync_min_timestamp directly in your //queries: //* sync_metadata_only is used by Sync Services as an optimization // in some queries. //* The value of the sync_min_timestamp session variable is compared to // values in the sync_row_timestamp column in the tracking table to // determine which rows to select. //<snippetOCSv2_CS_Peer_Cleanup_SelectIncrementalChangesCommand> SqlCommand chgsCustomerCmd = new SqlCommand(); chgsCustomerCmd.CommandType = CommandType.StoredProcedure; chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges"; chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int); chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int); chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int); adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_SelectIncrementalChangesCommand> //Specify the command to insert rows. //The sync_row_count session variable is used in this command //and other commands to return a count of the rows affected by an operation. //A count of 0 indicates that an operation failed. //<snippetOCSv2_CS_Peer_Cleanup_InsertCommand> SqlCommand insCustomerCmd = new SqlCommand(); insCustomerCmd.CommandType = CommandType.StoredProcedure; insCustomerCmd.CommandText = "Sync.sp_Customer_ApplyInsert"; insCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); insCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar); insCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); insCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar); insCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.InsertCommand = insCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_InsertCommand> //Specify the command to update rows. //The value of the sync_min_timestamp session variable is compared to //values in the sync_row_timestamp column in the tracking table to //determine which rows to update. //<snippetOCSv2_CS_Peer_Cleanup_UpdateCommand> SqlCommand updCustomerCmd = new SqlCommand(); updCustomerCmd.CommandType = CommandType.StoredProcedure; updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate"; updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar); updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar); updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int); adapterCustomer.UpdateCommand = updCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_UpdateCommand> //Specify the command to delete rows. //The value of the sync_min_timestamp session variable is compared to //values in the sync_row_timestamp column in the tracking table to //determine which rows to delete. //<snippetOCSv2_CS_Peer_Cleanup_DeleteCommand> SqlCommand delCustomerCmd = new SqlCommand(); delCustomerCmd.CommandType = CommandType.StoredProcedure; delCustomerCmd.CommandText = "Sync.sp_Customer_ApplyDelete"; delCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int); adapterCustomer.DeleteCommand = delCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_DeleteCommand> //Specify the command to select any conflicting rows. //<snippetOCSv2_CS_Peer_Cleanup_SelectRowCommand> SqlCommand selRowCustomerCmd = new SqlCommand(); selRowCustomerCmd.CommandType = CommandType.StoredProcedure; selRowCustomerCmd.CommandText = "Sync.sp_Customer_SelectRow"; selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int); adapterCustomer.SelectRowCommand = selRowCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_SelectRowCommand> //Specify the command to insert metadata rows. //The session variables in this command relate to columns in //the tracking table. //<snippetOCSv2_CS_Peer_Cleanup_InsertMetadataCommand> SqlCommand insMetadataCustomerCmd = new SqlCommand(); insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure; insMetadataCustomerCmd.CommandText = "Sync.sp_Customer_InsertMetadata"; insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_InsertMetadataCommand> //Specify the command to update metadata rows. //<snippetOCSv2_CS_Peer_Cleanup_UpdateMetadataCommand> SqlCommand updMetadataCustomerCmd = new SqlCommand(); updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure; updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata"; updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_UpdateMetadataCommand> //Specify the command to delete metadata rows. //<snippetOCSv2_CS_Peer_Cleanup_DeleteMetadataCommand> SqlCommand delMetadataCustomerCmd = new SqlCommand(); delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure; delMetadataCustomerCmd.CommandText = "Sync.sp_Customer_DeleteMetadata"; delMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_DeleteMetadataCommand> //Specify the command to select metadata rows for cleanup. //<snippetOCSv2_CS_Peer_Cleanup_SelectMetadataForCleanupCommand> SqlCommand selMetadataCustomerCmd = new SqlCommand(); selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure; selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata"; selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays; selMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int); adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd; //</snippetOCSv2_CS_Peer_Cleanup_SelectMetadataForCleanupCommand> //Add the adapter to the provider. sampleDbProvider.SyncAdapters.Add(adapterCustomer); // Configure commands that relate to the provider itself rather // than the DbSyncAdapter object for each table: // * SelectNewTimestampCommand: Returns the new high watermark for // the current synchronization session. // * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge, // and a scope version (timestamp). // * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge. // * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table // or tracking table, to determine whether for each table the destination already // has all of the changes from the source. If a destination table has all the changes, // SelectIncrementalChangesCommand is not called for that table. // * SelectOverlappingScopesCommand: returns the scope name and table name for all tables // in the specified scope that are also included in other scopes. // * UpdateScopeCleanupTimestampCommand: updates the scope_cleanup_timestamp column for a // particular scope in the scope_info table, to mark the point up to which cleanup // has been performed for the scope. //Select a new timestamp. //During each synchronization, the new value and //the last value from the previous synchronization //are used: the set of changes between these upper and //lower bounds is synchronized. //<snippetOCSv2_CS_Peer_Cleanup_NewAnchorCommand> SqlCommand selectNewTimestampCommand = new SqlCommand(); string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp; selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"; selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp); selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output; sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand; //</snippetOCSv2_CS_Peer_Cleanup_NewAnchorCommand> //Specify the command to select local replica metadata. //<snippetOCSv2_CS_Peer_Cleanup_SelectScopeInfoCommand> SqlCommand selReplicaInfoCmd = new SqlCommand(); selReplicaInfoCmd.CommandType = CommandType.Text; selReplicaInfoCmd.CommandText = "SELECT " + "scope_id, " + "scope_local_id, " + "scope_sync_knowledge, " + "scope_tombstone_cleanup_knowledge, " + "scope_timestamp " + "FROM Sync.ScopeInfo " + "WHERE scope_name = @" + DbSyncSession.SyncScopeName; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd; //</snippetOCSv2_CS_Peer_Cleanup_SelectScopeInfoCommand> //Specify the command to update local replica metadata. //<snippetOCSv2_CS_Peer_Cleanup_UpdateScopeInfoCommand> SqlCommand updReplicaInfoCmd = new SqlCommand(); updReplicaInfoCmd.CommandType = CommandType.Text; updReplicaInfoCmd.CommandText = "UPDATE Sync.ScopeInfo SET " + "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " + "scope_id = @" + DbSyncSession.SyncScopeId + ", " + "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " + "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " + " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " + "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"; updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd; //</snippetOCSv2_CS_Peer_Cleanup_UpdateScopeInfoCommand> //Return the maximum timestamp from the Customer_Tracking table. //If more tables are synchronized, the query should UNION //all of the results. The table name is not schema-qualified //in this case because the name was not schema qualified in the //DbSyncAdapter constructor. //<snippetOCSv2_CS_Peer_Cleanup_SelectTableMaxTimestampsCommand> SqlCommand selTableMaxTsCmd = new SqlCommand(); selTableMaxTsCmd.CommandType = CommandType.Text; selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " + "MAX(local_update_peer_timestamp) AS max_timestamp " + "FROM Sync.Customer_Tracking"; sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd; //</snippetOCSv2_CS_Peer_Cleanup_SelectTableMaxTimestampsCommand> //Specify the command to select table and scope names for //any tables that are in more than one scope. //<snippetOCSv2_CS_Peer_Cleanup_SelectOverlappingScopesCommand> SqlCommand overlappingScopesCmd = new SqlCommand(); overlappingScopesCmd.CommandType = CommandType.StoredProcedure; overlappingScopesCmd.CommandText = "Sync.sp_SelectSharedScopes"; overlappingScopesCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd; //</snippetOCSv2_CS_Peer_Cleanup_SelectOverlappingScopesCommand> //Specify the command that updates the scope information table //to indicate to which point metadata has been cleaned up for a scope. //<snippetOCSv2_CS_Peer_Cleanup_UpdateScopeCleanupTimestampCommand> SqlCommand updScopeCleanupInfoCmd = new SqlCommand(); updScopeCleanupInfoCmd.CommandType = CommandType.Text; updScopeCleanupInfoCmd.CommandText = "UPDATE scope_info set " + " scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp + " WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp < @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" + " SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"; updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt); updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd; //</snippetOCSv2_CS_Peer_Cleanup_UpdateScopeCleanupTimestampCommand> return(sampleDbProvider); }
public DbSyncProvider SetupSyncProvider(string scopeName, string peerConnString) { const int MetadataAgingInHours = 100; DbSyncProvider peerProvider = new DbSyncProvider(); SqlConnection peerConnection = new SqlConnection(peerConnString); peerProvider.Connection = peerConnection; peerProvider.ScopeName = scopeName; DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer"); adapterCustomer.RowIdColumns.Add("CustomerId"); SqlCommand chgsCustomerCmd = new SqlCommand(); chgsCustomerCmd.CommandType = CommandType.StoredProcedure; chgsCustomerCmd.CommandText = "Sales.sp_Customer_SelectChanges"; chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int); chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int); adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd; SqlCommand insCustomerCmd = new SqlCommand(); insCustomerCmd.CommandType = CommandType.StoredProcedure; insCustomerCmd.CommandText = "Sales.sp_Customer_ApplyInsert"; insCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); insCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar); insCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); insCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar); insCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.InsertCommand = insCustomerCmd; SqlCommand updCustomerCmd = new SqlCommand(); updCustomerCmd.CommandType = CommandType.StoredProcedure; updCustomerCmd.CommandText = "Sales.sp_Customer_ApplyUpdate"; updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar); updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar); updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int); adapterCustomer.UpdateCommand = updCustomerCmd; SqlCommand delCustomerCmd = new SqlCommand(); delCustomerCmd.CommandType = CommandType.StoredProcedure; delCustomerCmd.CommandText = "Sales.sp_Customer_ApplyDelete"; delCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.DeleteCommand = delCustomerCmd; SqlCommand selRowCustomerCmd = new SqlCommand(); selRowCustomerCmd.CommandType = CommandType.StoredProcedure; selRowCustomerCmd.CommandText = "Sales.sp_Customer_SelectRow"; selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); adapterCustomer.SelectRowCommand = selRowCustomerCmd; SqlCommand insMetadataCustomerCmd = new SqlCommand(); insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure; insMetadataCustomerCmd.CommandText = "Sales.sp_Customer_InsertMetadata"; insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int); insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd; SqlCommand updMetadataCustomerCmd = new SqlCommand(); updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure; updMetadataCustomerCmd.CommandText = "Sales.sp_Customer_UpdateMetadata"; updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd; SqlCommand delMetadataCustomerCmd = new SqlCommand(); delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure; delMetadataCustomerCmd.CommandText = "Sales.sp_Customer_DeleteMetadata"; delMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd; SqlCommand selMetadataCustomerCmd = new SqlCommand(); selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure; selMetadataCustomerCmd.CommandText = "Sales.sp_Customer_SelectMetadata"; selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours; adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd; peerProvider.SyncAdapters.Add(adapterCustomer); SqlCommand selectNewTimestampCommand = new SqlCommand(); string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp; selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"; selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp); selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output; peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand; SqlCommand selReplicaInfoCmd = new SqlCommand(); selReplicaInfoCmd.CommandType = CommandType.Text; selReplicaInfoCmd.CommandText = "SELECT " + "@" + DbSyncSession.SyncScopeId + " = scope_id, " + "@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " + "@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " + "@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " + "FROM Sales.ScopeInfo " + "WHERE scope_name = @" + DbSyncSession.SyncScopeName; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output; peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd; SqlCommand updReplicaInfoCmd = new SqlCommand(); updReplicaInfoCmd.CommandType = CommandType.Text; updReplicaInfoCmd.CommandText = "UPDATE Sales.ScopeInfo SET " + "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " + "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " + "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " + " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 or scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " + "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"; updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd; return(peerProvider); }
static void Main(string[] args) { //The Utility class handles all functionality that is not //directly related to synchronization, such as holding peerConnection //string information and making changes to the server database. Utility util = new Utility(); util.RecreateCePeerDatabase(util.Ce1ConnString); //<snippetOCSv2_CS_Peer_Cleanup_Synchronize> //The SampleStats class handles information from the SyncStatistics //object that the Synchronize method returns. SampleStats sampleStats = new SampleStats(); SampleSyncProvider sampleSyncProvider = new SampleSyncProvider(); try { //Initial synchronization. Instantiate the SyncOrchestrator //and call Synchronize. sampleSyncProvider = new SampleSyncProvider(); SyncOrchestrator sampleSyncAgent; SyncOperationStatistics syncStatistics; //The integer passed to ConfigureDbSyncProvider is how old that metadata //can be (in days) before it is deleted when CleanupMetadata() is called. //The integer value is only relevant if CleanupMetadata() is called, as //demonstrated later in this application. sampleSyncAgent = new SampleSyncAgent( sampleSyncProvider.ConfigureDbSyncProvider(util.Peer1ConnString, 7), sampleSyncProvider.ConfigureDbSyncProvider(util.Peer3ConnString, 7)); syncStatistics = sampleSyncAgent.Synchronize(); sampleStats.DisplayStats(syncStatistics, "initial"); sampleSyncAgent = new SampleSyncAgent( sampleSyncProvider.ConfigureDbSyncProvider(util.Peer3ConnString, 7), sampleSyncProvider.ConfigureCeSyncProvider(util.Ce1ConnString)); syncStatistics = sampleSyncAgent.Synchronize(); sampleStats.DisplayStats(syncStatistics, "initial"); } catch (DbOutdatedSyncException ex) { Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() + " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } //</snippetOCSv2_CS_Peer_Cleanup_Synchronize> //Update a row on peer 1. util.MakeDataChangesOnPeer(util.Peer1ConnString, "Customer"); //Instantiate a provider, connect to peer 1, and delete tombstone metadata that //is older than 7 days. //<snippetOCSv2_CS_Peer_Cleanup_CleanupMetadata> sampleSyncProvider = new SampleSyncProvider(); DbSyncProvider provider1 = sampleSyncProvider.ConfigureDbSyncProvider(util.Peer1ConnString, 7); if (provider1.CleanupMetadata() == true) { Console.WriteLine(String.Empty); Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database."); Console.WriteLine("Metadata more than 7 days old was deleted."); } else { Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues."); } //</snippetOCSv2_CS_Peer_Cleanup_CleanupMetadata> //Synchronize. try { SyncOrchestrator sampleSyncAgent; SyncOperationStatistics syncStatistics; sampleSyncAgent = new SampleSyncAgent( sampleSyncProvider.ConfigureDbSyncProvider(util.Peer1ConnString, 7), sampleSyncProvider.ConfigureDbSyncProvider(util.Peer3ConnString, 7)); syncStatistics = sampleSyncAgent.Synchronize(); sampleStats.DisplayStats(syncStatistics, "subsequent"); } catch (DbOutdatedSyncException ex) { Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() + " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } //Delete a row on peer 3. util.MakeDataChangesOnPeer(util.Peer3ConnString, "Customer"); //Instantiate a provider, connect to peer 3, and delete all tombstone metadata. sampleSyncProvider = new SampleSyncProvider(); DbSyncProvider provider3 = sampleSyncProvider.ConfigureDbSyncProvider(util.Peer3ConnString, -1); if (provider3.CleanupMetadata() == true) { Console.WriteLine(String.Empty); Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer3 database."); Console.WriteLine("All metadata was deleted."); } else { Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues."); } //Synchronize. try { SyncOrchestrator sampleSyncAgent; SyncOperationStatistics syncStatistics; sampleSyncAgent = new SampleSyncAgent( sampleSyncProvider.ConfigureDbSyncProvider(util.Peer1ConnString, 7), sampleSyncProvider.ConfigureDbSyncProvider(util.Peer3ConnString, 7)); syncStatistics = sampleSyncAgent.Synchronize(); sampleStats.DisplayStats(syncStatistics, "subsequent"); } catch (DbOutdatedSyncException ex) { Console.WriteLine(String.Empty); Console.WriteLine("Synchronization failed due to outdated synchronization knowledge,"); Console.WriteLine("which is expected in this sample application."); Console.WriteLine("Drop and recreate the sample databases."); Console.WriteLine(String.Empty); Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() + " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString()); Console.WriteLine(String.Empty); } catch (Exception ex) { Console.WriteLine(ex.Message); } //Return peer data back to its original state. util.CleanUpPeer(util.Peer1ConnString); util.CleanUpPeer(util.Peer3ConnString); //Exit. Console.Write("\nPress Enter to close the window."); Console.ReadLine(); }
DbSyncProvider SetupSyncProvider(string connectionString, DbSyncProvider peerProvider) { const int TombstoneAgingInHours = 100; SqlConnection connection = new SqlConnection(connectionString); peerProvider.Connection = connection; // // 1. Create sync adapter for each sync table and attach it to the provider // Following DataAdapter style in ADO.NET, DbSyncAdapter is the equivelent for sync. // The code below shows how to create DbSyncAdapter objects for orders // and order_details tables using stored procedures stored on the database. // peerProvider.ScopeName = "Sales"; // orders table DbSyncAdapter adaptorOrders = new DbSyncAdapter("orders"); adaptorOrders.RowIdColumns.Add("order_id"); // select incremental changes command SqlCommand chgsOrdersCmd = new SqlCommand(); chgsOrdersCmd.CommandType = CommandType.StoredProcedure; chgsOrdersCmd.CommandText = "sp_orders_selectchanges"; chgsOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int); chgsOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); adaptorOrders.SelectIncrementalChangesCommand = chgsOrdersCmd; // insert row command SqlCommand insOrdersCmd = new SqlCommand(); insOrdersCmd.CommandType = CommandType.StoredProcedure; insOrdersCmd.CommandText = "sp_orders_applyinsert"; insOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); insOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime); insOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.InsertCommand = insOrdersCmd; // update row command SqlCommand updOrdersCmd = new SqlCommand(); updOrdersCmd.CommandType = CommandType.StoredProcedure; updOrdersCmd.CommandText = "sp_orders_applyupdate"; updOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); updOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime); updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int); updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.UpdateCommand = updOrdersCmd; // delete row command SqlCommand delOrdersCmd = new SqlCommand(); delOrdersCmd.CommandType = CommandType.StoredProcedure; delOrdersCmd.CommandText = "sp_orders_applydelete"; delOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int); delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.DeleteCommand = delOrdersCmd; // get row command SqlCommand selRowOrdersCmd = new SqlCommand(); selRowOrdersCmd.CommandType = CommandType.StoredProcedure; selRowOrdersCmd.CommandText = "sp_orders_selectrow"; selRowOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); adaptorOrders.SelectRowCommand = selRowOrdersCmd; // insert row metadata command SqlCommand insMetadataOrdersCmd = new SqlCommand(); insMetadataOrdersCmd.CommandType = CommandType.StoredProcedure; insMetadataOrdersCmd.CommandText = "sp_orders_insertmetadata"; insMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int); insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.InsertMetadataCommand = insMetadataOrdersCmd; // update row metadata command SqlCommand updMetadataOrdersCmd = new SqlCommand(); updMetadataOrdersCmd.CommandType = CommandType.StoredProcedure; updMetadataOrdersCmd.CommandText = "sp_orders_updatemetadata"; updMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.UpdateMetadataCommand = updMetadataOrdersCmd; // delete row metadata command SqlCommand delMetadataOrdersCmd = new SqlCommand(); delMetadataOrdersCmd.CommandType = CommandType.StoredProcedure; delMetadataOrdersCmd.CommandText = "sp_orders_deletemetadata"; delMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int); delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrders.DeleteMetadataCommand = delMetadataOrdersCmd; // get tombstones for cleanup SqlCommand selTombstonesOrdersCmd = new SqlCommand(); selTombstonesOrdersCmd.CommandType = CommandType.StoredProcedure; selTombstonesOrdersCmd.CommandText = "sp_orders_selecttombstones"; selTombstonesOrdersCmd.Parameters.Add("@tombstone_aging_in_hours", SqlDbType.Int).Value = TombstoneAgingInHours; adaptorOrders.SelectMetadataForCleanupCommand = selTombstonesOrdersCmd; peerProvider.SyncAdapters.Add(adaptorOrders); // order_details table DbSyncAdapter adaptorOrderDetails = new DbSyncAdapter("order_details"); adaptorOrderDetails.RowIdColumns.Add("order_id"); // select incremental inserts command SqlCommand chgsOrderDetailsCmd = new SqlCommand(); chgsOrderDetailsCmd.CommandType = CommandType.StoredProcedure; chgsOrderDetailsCmd.CommandText = "sp_order_details_selectchanges"; chgsOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int); chgsOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); adaptorOrderDetails.SelectIncrementalChangesCommand = chgsOrderDetailsCmd; // insert row command SqlCommand insOrderDetailsCmd = new SqlCommand(); insOrderDetailsCmd.CommandType = CommandType.StoredProcedure; insOrderDetailsCmd.CommandText = "sp_order_details_applyinsert"; insOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); insOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int); insOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100); insOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int); insOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.InsertCommand = insOrderDetailsCmd; // update row command SqlCommand updOrderDetailsCmd = new SqlCommand(); updOrderDetailsCmd.CommandType = CommandType.StoredProcedure; updOrderDetailsCmd.CommandText = "sp_order_details_applyupdate"; updOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); updOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int); updOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100); updOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int); updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int); updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.UpdateCommand = updOrderDetailsCmd; // delete row command SqlCommand delOrderDetailsCmd = new SqlCommand(); delOrderDetailsCmd.CommandType = CommandType.StoredProcedure; delOrderDetailsCmd.CommandText = "sp_order_details_applydelete"; delOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt); delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int); delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.DeleteCommand = delOrderDetailsCmd; // get row command SqlCommand selRowOrderDetailsCmd = new SqlCommand(); selRowOrderDetailsCmd.CommandType = CommandType.StoredProcedure; selRowOrderDetailsCmd.CommandText = "sp_order_details_selectrow"; selRowOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); adaptorOrderDetails.SelectRowCommand = selRowOrderDetailsCmd; // insert row metadata command SqlCommand insMetadataOrderDetailsCmd = new SqlCommand(); insMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure; insMetadataOrderDetailsCmd.CommandText = "sp_order_details_insertmetadata"; insMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int); insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.InsertMetadataCommand = insMetadataOrderDetailsCmd; // update row metadata command SqlCommand updMetadataOrderDetailsCmd = new SqlCommand(); updMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure; updMetadataOrderDetailsCmd.CommandText = "sp_order_details_updatemetadata"; updMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.UpdateMetadataCommand = updMetadataOrderDetailsCmd; // delete row metadata command SqlCommand delMetadataOrderDetailsCmd = new SqlCommand(); delMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure; delMetadataOrderDetailsCmd.CommandText = "sp_order_details_deletemetadata"; delMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int); delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int); delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt); delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; adaptorOrderDetails.DeleteMetadataCommand = delMetadataOrderDetailsCmd; // get tombstones for cleanup SqlCommand selTombstonesOrderDetailsCmd = new SqlCommand(); selTombstonesOrderDetailsCmd.CommandType = CommandType.StoredProcedure; selTombstonesOrderDetailsCmd.CommandText = "sp_order_details_selecttombstones"; selTombstonesOrderDetailsCmd.Parameters.Add("@tombstone_aging_in_hours", SqlDbType.Int).Value = TombstoneAgingInHours; adaptorOrderDetails.SelectMetadataForCleanupCommand = selTombstonesOrderDetailsCmd; peerProvider.SyncAdapters.Add(adaptorOrderDetails); // // 2. Setup provider wide commands // There are few commands on the provider itself and not on a table sync adapter: // SelectNewTimestampCommand: Returns the new high watermark for current sync // SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge and scope version (timestamp) // UpdateScopeInfoCommand: Sets the new values for sync knowledge and cleanup knowledge // SqlCommand anchorCmd = new SqlCommand(); anchorCmd.CommandType = CommandType.Text; anchorCmd.CommandText = "select @" + DbSyncSession.SyncNewTimestamp + " = @@DBTS"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1" anchorCmd.Parameters.Add("@" + DbSyncSession.SyncNewTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output; peerProvider.SelectNewTimestampCommand = anchorCmd; // // Select local replica info // SqlCommand selReplicaInfoCmd = new SqlCommand(); selReplicaInfoCmd.CommandType = CommandType.Text; selReplicaInfoCmd.CommandText = "select " + "@" + DbSyncSession.SyncScopeId + " = scope_Id, " + "@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " + "@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " + "@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " + "from scope_info " + "where scope_name = @" + DbSyncSession.SyncScopeName; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output; selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output; peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd; // // Update local replica info // SqlCommand updReplicaInfoCmd = new SqlCommand(); updReplicaInfoCmd.CommandType = CommandType.Text; updReplicaInfoCmd.CommandText = "update scope_info set " + "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " + "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " + "where scope_name = @" + DbSyncSession.SyncScopeName + " ;" + "set @" + DbSyncSession.SyncRowCount + " = @@rowcount"; updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100); updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd; // // 3. Track sync process // peerProvider.SyncProgress += new EventHandler<DbSyncProgressEventArgs>(ShowProgress); peerProvider.ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(ShowFailures); return peerProvider; }
// // Synchronize Call // private void buttonSynchronize_Click(object sender, EventArgs e) { try { // // 1. Create instance of the sync components (peer, agent, peer) // This demo illustrates direct connection to server database. In this scenario, // all sync components reside in the sample process. // DbSyncProvider localProvider = new DbSyncProvider(); DbSyncProvider remoteProvider = new DbSyncProvider(); localProvider = SetupSyncProvider(GetFromPeerConnectionString(), localProvider); localProvider.SyncProviderPosition = SyncProviderPosition.Local; remoteProvider = SetupSyncProvider(GetToPeerConnectionString(), remoteProvider); remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote; SyncOrchestrator syncOrchestrator = new SyncOrchestrator(); // syncdirection: local->remote syncOrchestrator.LocalProvider = localProvider; syncOrchestrator.RemoteProvider = remoteProvider; syncOrchestrator.Direction = SyncDirectionOrder.Upload; syncOrchestrator.SessionProgress += new EventHandler<SyncStagedProgressEventArgs>(ProgressChanged); _progressForm = new ProgressForm(); _progressForm.Show(); SyncOperationStatistics syncStats = syncOrchestrator.Synchronize(); _progressForm.ShowStatistics(syncStats); // Update the UI _progressForm.EnableClose(); _progressForm = null; } catch (Exception ex) { MessageBox.Show(ex.Message); if (_progressForm != null) { _progressForm.EnableClose(); _progressForm = null; } } }
private DbSyncProvider remoteSyncProvider = null; public void BeginSession() { remoteSyncProvider = SyncProviderHelper.CreateSyncProvider(2); }
//[OperationContract(IsInitiating = true, IsTerminating = false)] public void BeginSession() { remoteSyncProvider = SyncProviderHelper.CreateSyncProvider(3);//@:Peer2 }
public void EndSession() { remoteSyncProvider = null; }