HOW TO：使用共同作業同步處理的工作階段變數
注意
文件中同步處理其他 ADO.NET 相容的資料庫這一節的主題是用來示範如何使用 Sync Framework 來同步處理 SQL Server 以外的資料庫。在這一版中，SQL Server 會在程式碼範例中使用，但是程式碼可用於其他 ADO.NET 相容的資料庫，只需要針對 SQL Server 特有的物件 (例如 SqlConnection) 以及顯示的 SQL 查詢進行某些修改。如需 SQL Server 同步處理的詳細資訊，請參閱HOW TO：設定及執行共同作業同步處理 (SQL Server)。
本主題示範如何在 Sync Framework 中使用工作階段變數。本主題的範例將重點放在下列 Sync Framework 型別與屬性：
如需如何執行範例程式碼的詳細資訊，請參閱同步處理其他 ADO.NET 相容的資料庫中的＜HOW-TO 主題中的範例應用程式＞。
了解工作階段變數
Sync Framework 提供一組工作階段變數，讓您能在同步處理期間，將值傳遞至 DbSyncAdapter 和 DbSyncProvider 命令。指定這些變數與其他參數的指定方式一樣，都是在 ADO.NET 命令中指定到查詢或預存程序中。在同步處理工作階段期間，當 DbSyncProvider 叫用每一個 ADO.NET 命令物件時，提供者會逐一檢查同步處理參數集合 (SyncParameters)，以判斷它是否可以根據名稱將每一個參數比對到 ADO.NET 命令參數。如果可比對到內建工作階段變數或是您已定義的自訂參數，Sync Framework 會先擴展該變數，然後提供者才會呼叫命令。
例如，下列 UPDATE 陳述式會使用 @sync_min_timestamp、@sync_force_write 和 @sync_row_count 工作階段變數，套用變更至 Customer 資料表。
UPDATE c
SET c.CustomerName = @CustomerName, c.SalesPerson = @SalesPerson, c.CustomerType = @CustomerType
FROM Sales.Customer c JOIN Sync.Customer_Tracking t ON c.CustomerId = t.CustomerId
WHERE ((t.local_update_peer_timestamp <= @sync_min_timestamp) OR @sync_force_write = 1)
AND t.CustomerId = @CustomerId
SET @sync_row_count = @@rowcount
@sync_min_timestamp 和 @sync_force_write 的值是在同步處理期間由 Sync Framework 設定，而 @sync_row_count 的值則是由 SQL Server @@rowcount 函式設定。您可以像在先前的查詢中一樣直接使用這些變數的名稱，或是使用 SyncSession 物件中可用的字串常數。下表列出所有可用的工作階段變數及其使用方式。
| 工作階段變數 | 用法 |
|---|---|
sync_check_concurrency |
由 Sync Framework 用於更新及刪除中繼資料的命令，Sync Framework 使用此值，以決定如果中繼資料所參考之資料列已變更時可否變更中繼資料。如果無法變更中繼資料，就會引發 ApplyMetadataFailed 事件。 |
sync_create_peer_key、sync_create_peer_timestamp、sync_row_is_tombstone、sync_row_timestamp、sync_update_peer_key、sync_update_peer_timestamp、sync_scope_cleanup_timestamp、 sync_scope_local_id、 sync_shared_scope_name 和 sync_table_name |
由選取及套用變更至變更追蹤資料行的命令使用。如需這些資料行的詳細資訊，請參閱 HOW TO：佈建伺服器資料庫來進行共同作業同步處理 (非 SQL Server)中的＜針對每個資料表的中繼資料建立追蹤資料表＞。 |
sync_force_write |
配合 RetryWithForceWrite 的 ApplyAction 使用，以強制套用因衝突或錯誤而失敗的變更。如需詳細資訊，請參閱 HOW TO：處理共同作業同步處理的資料衝突和錯誤 (SQL Server)。 |
sync_initialize |
傳回目前的同步處理工作階段是初始工作階段 (值為 1)，或是後續的工作階段 (值為 0)。 |
sync_metadata_only |
由 Sync Framework 用於為 SelectIncrementalChangesCommand 和 SelectRowCommand 屬性指定的命令。當 Sync Framework 指定此變數值為 1 時，只會選取中繼資料，而不會選取相關聯的資料變更。這是 Sync Framework 不需要資料變更 (如選取與 SelectRowCommand 相衝突的資料列) 時的最佳化做法。 |
sync_min_timestamp 與sync_new_timestamp |
用以定義在工作階段期間要同步 (Synchronize) 的變更集合。在目前的同步處理工作階段期間，為 SelectNewTimestampCommand 屬性指定的命令會提供新的 timestamp 值。在最小值之後與新值之前的變更會進行同步處理。然後會儲存新值，並用來做為下一個同步處理工作階段的起點。 |
sync_row_count |
傳回伺服器端最後作業所影響的資料列數目。在 SQL Server 資料庫中，@@ROWCOUNT 會提供此變數的值。如本主題中的程式碼範例中所示，預存程序應該包含設定 sync_row_count 之值的輸出參數。 資料列計數為 0，表示某項作業失敗，通常是由於衝突或錯誤。如需詳細資訊，請參閱 HOW TO：處理共同作業同步處理的資料衝突和錯誤 (SQL Server)。 |
sync_scope_cleanup_knowledge、sync_scope_id、sync_scope_knowledge、sync_scope_name 和 sync_scope_timestamp |
由命令所使用，該命令會選取及套用變更至儲存同步處理知識的資料表。如需此資料表的範例，請參閱 HOW TO：佈建伺服器資料庫來進行共同作業同步處理 (非 SQL Server)中的＜針對每個範圍的中繼資料建立追蹤資料表＞。 |
sync_session_id |
傳回識別目前的同步處理工作階段的 GUID 值。 |
sync_stage_name |
傳回對應於 DbSyncStage 列舉其中一個值的值。 |
範例
下列程式碼範例顯示如何在同步處理 Sync Framework 對等範例資料庫的 Customer 資料表時，使用工作階段變數。
API 的主要部分
本節提供的程式碼範例將指出牽涉到工作階段變數的一些 API 主要部分。如下所示的一些命令會呼叫包含於範例對等資料庫中的預存程序。如需詳細資訊，請參閱 HOW TO：佈建伺服器資料庫來進行共同作業同步處理 (非 SQL Server)中的＜建立預存程序來選取及更新資料和中繼資料＞。
下列程式碼範例指定 SelectNewTimestampCommand 屬性的查詢。此程序會設定 sync_new_timestamp 變數的值。從伺服器資料庫選取變更的同步處理命令會使用此值。
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;
sampleProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
Dim newTimestampVariable As String = "@" + DbSyncSession.SyncNewTimestamp
Dim selectNewTimestampCommand As New SqlCommand()
With selectNewTimestampCommand
.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleProvider.SelectNewTimestampCommand = selectNewTimestampCommand
MIN_ACTIVE_ROWVERSION 函式會傳回目前資料庫中最低的使用中 timestamp (也稱為 rowversion) 值。timestamp 值使用在尚未認可的交易中時，其值即為使用中。如果在資料庫中沒有使用中的值，MIN_ACTIVE_ROWVERSION 會傳回與 @@DBTS + 1 相同的值。例如，對於使用 timestamp 值為一個群組集的變更一起進行資料同步處理的情節來說，MIN_ACTIVE_ROWVERSION 很有用。如果應用程式在其錨定命令中使用 @@DBTS 而非 MIN_ACTIVE_ROWVERSION，有可能會遺漏在同步處理發生時使用中的變更。
下列程式碼範例會為 SelectIncrementalChangesCommand 屬性指定預存程序。此程序會在同步處理工作階段期間，從對等選取插入、更新和刪除套用至第二個對等。sync_min_timestamp 變數會指定包含於要進行同步處理之變更集中的最小 timestamp。此變數的值會與追蹤資料表中 sync_row_timestamp 資料行內的值進行比較，以決定所要選取的資料列。sync_initialize 變數是用來指定同步處理工作階段是否為兩個對等之間的第一個工作階段。如果是兩個對等之間的第一個工作階段，sp_Customer_SelectChanges 預存程序可以包含只在此第一個工作階段期間排除的邏輯。
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;
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
下列程式碼範例會指定命令，在一個對等上套用從另一個對等選取的更新。sync_row_count 變數可以讓 Sync Framework 判斷更新是成功還是失敗。如果更新失敗，sync_force_write 變數可以設定為 1。這會啟用 sp_Customer_ApplyUpdate 預存程序中不同的邏輯。如需詳細資訊，請參閱 HOW TO：處理共同作業同步處理的資料衝突和錯誤 (SQL Server)。在對等套用更新之前，sync_min_timestamp 變數是用來檢查從上一個同步處理工作階段以來，是否已有資料列進行更新。
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;
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
下列程式碼範例會指定命令，套用更新至 Customer_Tracking 資料表和 ScopeInfo 資料表。大部分工作階段變數都對應到追蹤資料表中的資料行。sync_check_concurrency 變數是用於命令中，以決定當中繼資料所參考之資料列已變更時可否變更中繼資料。
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;
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
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;
sampleProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
Dim updReplicaInfoCmd As New SqlCommand()
With updReplicaInfoCmd
.CommandType = CommandType.Text
.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"
.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
sampleProvider.UpdateScopeInfoCommand = updReplicaInfoCmd
完整的程式碼範例
下列的完整程式碼範例包含本主題中先前所描述的程式碼範例，以及其他的程式碼，可用來執行同步處理。此範例需要 Utility 類別，詳情請參閱資料庫提供者公用程式類別的 HOW-TO 主題。
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize. Note that data is not synchronized during the
//session between peer 1 and peer 3, because all rows have already
//been delivered to peer 3 during its synchronization session with peer 2.
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync2, Utility.ConnStr_DbSync3);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync3);
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);
}
//Make changes in each peer database.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer");
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync3, "Customer");
try
{
//Subsequent synchronization. Changes are now synchronized between all
//peers.
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync2, Utility.ConnStr_DbSync3);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync3);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Sessions in which no new changes have been made.
//In this case, the call to SelectTableMaxTimestampsCommand indicates
//that no data changes are available to synchronize, so
//SelectIncrementalChangesCommand is not called.
sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2);
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);
}
//Return peer data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1);
Utility.CleanUpNode(Utility.ConnStr_DbSync2);
Utility.CleanUpNode(Utility.ConnStr_DbSync3);
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncOrchestrator.
public class SampleSyncAgent : SyncOrchestrator
{
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 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;
}
}
public class SampleSyncProvider
{
public DbSyncProvider SetupSyncProvider(string peerConnString, DbSyncProvider sampleProvider)
{
SqlConnection peerConnection = new SqlConnection(peerConnString);
sampleProvider.Connection = peerConnection;
sampleProvider.ScopeName = "Sales";
//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.
DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer");
//Specify the primary key, which Sync Framework uses
//to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId");
//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 Framework 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.
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;
//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.
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;
//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.
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;
//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.
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;
//Specify the command to select any conflicting rows.
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;
//Specify the command to insert metadata rows.
//The session variables in this command relate to columns in
//the tracking table.
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;
//Specify the command to update metadata rows.
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;
//Specify the command to delete metadata rows.
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;
//Add the adapter to the provider.
sampleProvider.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.
// There are additional commands related to metadata cleanup that are not
// included in this application.
//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.
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;
sampleProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
//Specify the command to select local replica metadata.
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);
sampleProvider.SelectScopeInfoCommand = selReplicaInfoCmd;
//Specify the command to update local replica metadata.
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;
sampleProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
//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.
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";
sampleProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
return sampleProvider;
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
Console.WriteLine(String.Empty);
}
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Class Program
Shared Sub Main(ByVal args() As String)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize. Note that data is not synchronized during the
'session between peer 1 and peer 3, because all rows have already
'been delivered to peer 3 during its synchronization session with peer 2.
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync2, Utility.ConnStr_DbSync3)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync3)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Catch ex As DbOutdatedSyncException
Console.WriteLine("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
& " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Make changes in each peer database.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer")
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync3, "Customer")
Try
'Subsequent synchronization. Changes are now synchronized between all
'peers.
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync2, Utility.ConnStr_DbSync3)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync3)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Sessions in which no new changes have been made.
'In this case, the call to SelectTableMaxTimestampsCommand indicates
'that no data changes are available to synchronize, so
'SelectIncrementalChangesCommand is not called.
sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
Catch ex As DbOutdatedSyncException
Console.WriteLine("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
& " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Return peer data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1)
Utility.CleanUpNode(Utility.ConnStr_DbSync2)
Utility.CleanUpNode(Utility.ConnStr_DbSync3)
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncOrchestrator.
Public Class SampleSyncAgent
Inherits SyncOrchestrator
Public Sub New(ByVal localProviderConnString As String, ByVal remoteProviderConnString As String)
'Instantiate the sample provider that allows us to create a provider
'for both of the peers that are being synchronized.
Dim sampleSyncProvider As 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.
Dim localProvider As New DbSyncProvider()
Dim remoteProvider As 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 the local and remote providers that should be synchronized,
'and the direction and order of changes. In this case, changes are first
'uploaded from local to remote and then downloaded in the other direction.
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
End Sub 'New
End Class 'SampleSyncAgent
Public Class SampleSyncProvider
Public Function SetupSyncProvider(ByVal peerConnString As String, ByVal sampleProvider As DbSyncProvider) As DbSyncProvider
Dim peerConnection As New SqlConnection(peerConnString)
sampleProvider.Connection = peerConnection
sampleProvider.ScopeName = "Sales"
'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.
Dim adapterCustomer As New DbSyncAdapter("Customer")
'Specify the primary key, which Sync Framework uses
'to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId")
'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 Framework 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.
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
'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.
Dim insCustomerCmd As New SqlCommand()
With insCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyInsert"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.InsertCommand = insCustomerCmd
'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.
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
'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.
Dim delCustomerCmd As New SqlCommand()
With delCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyDelete"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.DeleteCommand = delCustomerCmd
'Specify the command to select any conflicting rows.
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
End With
adapterCustomer.SelectRowCommand = selRowCustomerCmd
'Specify the command to insert metadata rows.
'The session variables in this command relate to columns in
'the tracking table. These are the same columns
'that were specified as DbSyncAdapter properties at the beginning
'of this code example.
Dim insMetadataCustomerCmd As New SqlCommand()
With insMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_InsertMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd
'Specify the command to update metadata rows.
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
'Specify the command to delete metadata rows.
Dim delMetadataCustomerCmd As New SqlCommand()
With delMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_DeleteMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd
sampleProvider.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.
' There are additional commands related to metadata cleanup that are not
' included in this application.
'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.
Dim newTimestampVariable As String = "@" + DbSyncSession.SyncNewTimestamp
Dim selectNewTimestampCommand As New SqlCommand()
With selectNewTimestampCommand
.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleProvider.SelectNewTimestampCommand = selectNewTimestampCommand
'Specify the command to select local replica metadata.
Dim selReplicaInfoCmd As New SqlCommand()
With selReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "SELECT " _
& "scope_id, " _
& "scope_local_id, " _
& "scope_sync_knowledge, " _
& "scope_tombstone_cleanup_knowledge, " _
& "scope_timestamp " _
& "FROM Sync.ScopeInfo " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName
.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
End With
sampleProvider.SelectScopeInfoCommand = selReplicaInfoCmd
'Specify the command to update local replica metadata.
Dim updReplicaInfoCmd As New SqlCommand()
With updReplicaInfoCmd
.CommandType = CommandType.Text
.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"
.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
sampleProvider.UpdateScopeInfoCommand = updReplicaInfoCmd
'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.
Dim selTableMaxTsCmd As 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"
sampleProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd
Return sampleProvider
End Function 'SetupSyncProvider
End Class 'SampleSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats