HOW TO：使用 SQL Server 變更追蹤

發行項
10/17/2009

本主題提供了 SQL Server 變更追蹤的概觀，並描述可在 SQL Server 資料庫與 SQL Server Compact 資料庫之間執行雙向同步處理的主控台應用程式。如果伺服器是執行 SQL Server 2008，建議您使用 SQL Server 變更追蹤功能。如果伺服器執行不同的資料庫，請參閱 HOW TO：使用自訂變更追蹤系統。

SQL Server 變更追蹤的概觀

在此文件的許多範例中，變更追蹤的處理方式都是使用加入基底資料表的一組資料行和觸發程序 (Trigger) 以及追蹤刪除作業的其他資料表。如需詳細資訊，請參閱追蹤伺服器資料庫中的變更。這種追蹤方式對於 SQL Server 2008 資料庫以外的資料庫很有用，但是有下列幾個缺點：

伺服器資料庫中必須有結構描述變更。這可能會影響其他應用程式，也可能完全做不到。
對資料列所做的每一項變更都會引發觸發程序，這可能會影響到效能。
維護正確資料列版本和刪除的邏輯可能會變得很複雜。
如果伺服器資料庫有長時間執行的交易 (除非交易正確進行處理)，可能會在同步處理期間造成資料變更遺失，而可能會導致資料不一致。

SQL Server 變更追蹤處理了這些問題，並提供一個直接方式來追蹤變更。在資料表上啟用變更追蹤時，SQL Server Database Engine 會維護對資料表所做變更的相關資訊。然後應用程式可以使用變更追蹤函式來判斷哪些資料列已經變更，以及取得有關變更的資訊。SQL Server 變更追蹤的主要優點如下：

對於使用 Sync Framework 的離線同步處理案例，您不必建立觸發程序、時間戳記資料行或是其他資料行或其他資料表。
會在認可時 (而不是發生 DML 作業時) 追蹤變更。
函數會傳回資料表的累加變更和版本資訊。這些函數提供了可靠且容易使用的結果，即使有重疊且未認可的交易時亦然。
效能負擔會最小。
可以自動清除變更追蹤資料。

本主題的其餘部分會示範如何在 Sync Framework 應用程式中使用 SQL Server 變更追蹤。如需變更追蹤的詳細資訊，請參閱《SQL Server 2008 線上叢書》。

搭配 Sync Framework 離線資料庫提供者使用 SQL Server 變更追蹤

本主題的這一節將描述如何啟用變更追蹤，以及使用變更追蹤查詢來判斷哪些資料變更要下載到用戶端。本節中資訊描述如何使用以手動方式建立的命令，從伺服器選取變更。如需有關如何使用同步處理配接器產生器建立命令的詳細資訊，請參閱使用者入門：用戶端與伺服器同步處理。

啟用 SQL Server 變更追蹤

在伺服器資料庫上啟用變更追蹤，然後針對需要追蹤的每一個資料表啟用變更追蹤。下列程式碼範例會示範其中一個 Sync Framework 範例資料庫內之 Sales.Customer 資料表的結構描述，以及針對該資料表啟用變更追蹤所需的程式碼。每個資料表都必須有一個主索引鍵。主索引鍵在所有節點之間都必須是唯一的，而且不能重複使用：如果刪除某個資料列，該資料列的主索引鍵不可以用於另一個資料列。一般來說，識別資料行並不是分散式環境的適當選擇。如需主索引鍵的詳細資訊，請參閱為分散式環境選取適當的主索引鍵。

以下透過執行下列程式碼所指定的變更追蹤選項包括：要保留追蹤中繼資料多久，以及是否要自動清除該中繼資料。如需有關追蹤選項的詳細資訊，請參閱《SQL Server 2008 線上叢書》中的＜變更追蹤＞、＜ALTER DATABASE＞和＜ALTER TABLE＞主題。

CREATE TABLE SyncSamplesDb_ChangeTracking.Sales.Customer(
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWID(), 
    CustomerName nvarchar(100) NOT NULL,
    SalesPerson nvarchar(100) NOT NULL,
    CustomerType nvarchar(100) NOT NULL)

ALTER DATABASE SyncSamplesDb_ChangeTracking SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE SyncSamplesDb_ChangeTracking
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

ALTER TABLE SyncSamplesDb_ChangeTracking.Sales.Customer
ENABLE CHANGE_TRACKING

注意

我們強烈建議您在查詢變更資訊時使用快照集交易，這樣可確保變更資訊的一致性，並避免發生與背景清除工作有關的競爭情形。如需有關快照集隔離的詳細資訊，請參閱《SQL Server 2008 線上叢書》中的＜Database Engine 中的隔離等級＞。

判斷要將哪些資料變更下載到用戶端

在啟用變更追蹤之後，Sync Framework 應用程式會使用變更追蹤函式和「錨定」(Anchor) 來判斷要下載哪些插入、更新和刪除項目。錨定是一個時間點，用以定義一組要進行同步的變更。請考量下列查詢：

您為 SelectIncrementalInsertsCommand 屬性所指定的查詢。下列查詢會從伺服器上的 Sales.Customer 資料表選取要套用到用戶端的累加插入：
```
IF @sync_initialized = 0
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] 
  FROM Sales.Customer LEFT OUTER JOIN 
  CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
ELSE
BEGIN
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType]
  FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION 
  <= @sync_new_received_anchor)
END
```
如果這是用戶端的第一個同步處理工作階段 (@sync_initialized = 0)，會從 Sales.Customer 基底資料表直接選取結構描述和所有資料列。在稍後的同步處理期間，會執行基底資料表與其變更追蹤資料表之間的內部聯結，藉以選取新插入的資料列。變更追蹤資料表中的中繼資料是使用 CHANGETABLE() 函式加以公開。此函式會將基底資料表名稱以及上一次同步處理所儲存的變更追蹤版本當做參數。SYS_CHANGE_OPERATION 資料行會定義變更追蹤資料表之資料列內所儲存的變更類型。

注意

查詢也應該檢查是否已從追蹤資料表清除任何必要的變更。例如，請參閱本主題下文中的「指定命令，從伺服器選取要套用至用戶端的累加插入」。
您為 SelectNewAnchorCommand 屬性所指定的查詢。這個查詢會擷取一個時間點值。以下查詢會使用 change_tracking_current_version() 函式，從伺服器擷取新的錨定值。此內建 SQL Server 函式會傳回與由變更追蹤所追蹤之上次已認可交易相關聯的版本整數。
```
SELECT @sync_new_received_anchor = change_tracking_current_version()
```
此整數值儲存在用戶端資料庫中，而由同步處理變更的命令加以使用。在每次同步處理工作階段期間，都會使用新的錨定值和來自上次同步處理工作階段的最後錨定值：這代表同步處理這些上限與下限之間的變更集。

在某些情況下，應用程式在每個用戶端只需要資料子集。您可以在 WHERE 子句中包含其他條件來篩選資料。如需詳細資訊，請參閱 HOW TO：篩選資料列和資料行。＜根據非索引鍵資料行篩選＞一節包含有關使用 SQL Server 變更追蹤篩選的重要資訊。

在同步處理程序期間執行的查詢

在 Sales.Customer 資料表第一次進行同步處理時，會發生下列程序：

新的錨定命令會執行。此命令會傳回整數值，例如 372。這個值會儲存在用戶端資料庫。資料表從未進行過同步處理，因此不會有來自先前同步處理的錨定值儲存在用戶端資料庫中。在此情況下，Sync Framework 會使用 0 的值。Sync Framework 執行的查詢如下所示：

exec sp_executesql N'IF @sync_initialized = 0 SELECT 
Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], 
[CustomerType] FROM Sales.Customer LEFT OUTER JOIN 
CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT 
ON CT.[CustomerId] = Sales.Customer.[CustomerId] ELSE  BEGIN SELECT 
Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], 
[CustomerType] FROM Sales.Customer JOIN CHANGETABLE(CHANGES 
Sales.Customer, @sync_last_received_anchor) CT ON CT.[CustomerId] = 
Sales.Customer.[CustomerId] WHERE (CT.SYS_CHANGE_OPERATION = ''I'' 
AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor) 
END', N'@sync_initialized int, @sync_last_received_anchor bigint, 
@sync_new_received_anchor bigint', @sync_initialized=0, 
@sync_last_received_anchor=0, @sync_new_received_anchor=372

在第二次同步處理工作階段期間，系統會執行新的錨定命令。自從上次工作階段後，系統已插入資料列。因此，此命令會傳回 375 的值。資料表從前進行過同步處理。因此，Sync Framework 可以擷取來自先前同步處理而儲存在用戶端資料庫中的 372 錨定值。執行的查詢如下所示。此查詢只從資料表下載在兩個錨定值之間插入的資料列。

exec sp_executesql N'IF @sync_initialized = 0 SELECT 
Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], 
[CustomerType] FROM Sales.Customer LEFT OUTER JOIN 
CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT 
ON CT.[CustomerId] = Sales.Customer.[CustomerId] ELSE  BEGIN SELECT 
Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], 
[CustomerType] FROM Sales.Customer JOIN CHANGETABLE(CHANGES 
Sales.Customer, @sync_last_received_anchor) CT ON CT.[CustomerId] = 
Sales.Customer.[CustomerId] WHERE (CT.SYS_CHANGE_OPERATION = ''I'' 
AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor) 
END', N'@sync_initialized int, @sync_last_received_anchor bigint, 
@sync_new_received_anchor bigint', @sync_initialized=1, 
@sync_last_received_anchor=372, @sync_new_received_anchor=375

如需更新和刪除命令的範例，請參閱本主題稍後的完整程式碼範例。

識別哪個用戶端進行了資料變更

要識別哪個用戶端進行了資料變更，有兩個主要的原因：

為了在僅限上傳和雙向的同步處理中支援衝突偵測與解析。

如果能夠變更某個特定資料行的是伺服器和用戶端，或者超過一個用戶端能夠對資料行進行變更，您可能會想要識別出這個變更究竟是在哪裡進行的。此資訊讓您能夠編寫程式碼，例如在某個變更和另一個變更之間設出優先順序。如果沒有這項資訊，對資料行所做的最後變更就會存留下來。
為了防止變更在雙向同步處理中回應至用戶端。

Sync Framework 會先將變更上傳至伺服器，然後下載變更至用戶端。如果您沒有追蹤進行變更的用戶端的識別 (Identity)，該變更會上傳到伺服器，然後在同一個同步處理工作階段中下載回用戶端。在某些案例中，需要有這樣的變更回應，但在其他案例中可能就不需要。

變更追蹤提供機制，可以在變更資料列時，儲存應用程式資料與變更資訊。此應用程式資料可用來識別正在進行變更的用戶端。識別進行變更的用戶端之後，即可在您查詢變更時傳回。

SYS_CHANGE_CONTEXT 資料行可用來搭配 ClientId 屬性使用，以判斷哪個用戶端進行了哪些插入、更新或刪除。在任何資料表第一次使用除了快照集同步處理之外的方法進行同步時，Sync Framework 就會在用戶端上儲存 GUID 值，以識別該用戶端。此識別碼會傳遞至 DbServerSyncProvider，讓每個 SyncAdapter 物件的命令都可以使用。此識別碼值可透過 ClientId 屬性、@sync_client_id 和 @sync_client_id_binary 工作階段變數取用。請考量下列 Transact-SQL 查詢：

IF @sync_initialized = 0
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] 
  FROM Sales.Customer LEFT OUTER JOIN 
  CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)
ELSE
BEGIN
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType]
  FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION 
<= @sync_new_received_anchor 
  AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));

此查詢類似於先前追蹤伺服器上進行之插入的查詢。每一個 WHERE 子句中外加的這個陳述式可確保所下載的唯一插入並不是由目前同步處理之用戶端所進行的插入，Sync Framework 也讓應用程式能夠使用伺服器端的整數來識別用戶端，而不是使用 GUID 值。如需詳細資訊，請參閱 HOW TO：使用工作階段變數。

若要追蹤套用到伺服器端的資料變更是由哪個用戶端進行變更，請使用 WITH CHANGE_TRACKING_CONTEXT 子句。執行 INSERT、UPDATE 或 DELETE 陳述式之前，請將 CHANGE_TRACKING_CONTEXT 設定為 @sync_client_id 或 @sync_client_id_binary 工作階段變數的值。此資訊會儲存在變更追蹤資料表內，好讓應用程式可以追蹤進行變更的內容。如果是 Sync Framework，這通常是用戶端識別碼。但是，您可以儲存任何可納入 varbinary(128) 資料行的值。

WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson,
 CustomerType)
VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType)
SET @sync_row_count = @@rowcount

了解及執行範例應用程式

本主題這一節包含設定及執行同步處理所需的應用程式程式碼。您可以透過閱讀範例程式碼的方式學習。不過，直接執行這些程式碼並看它運作的方式，是更有教育性的。在您執行程式碼之前，請先確定您已經安裝了下列程式：

Sync Framework

此應用程式需要參考 Microsoft.Synchronization.Data.dll、Microsoft.Synchronization.dll、Microsoft.Synchronization.Data.Server.dll 和 Microsoft.Synchronization.Data.SqlServerCe.dll。
SQL Server 2008

此範例程式碼會在連接字串中使用 localhost。若要使用遠端伺服程式，請將 localhost 變更為適當的伺服器名稱。
Sync Framework 範例資料庫。如需詳細資訊，請參閱資料庫提供者的安裝指令碼 HOW-TO 主題。

如果您已經讀過用戶端與伺服器同步處理的架構與類別主題，對用於應用程式中的主要類別應該已經有些了解。應用程式是由以下類別組成：

SampleSyncAgent. 此類別衍生自 SyncAgent。
SampleServerSyncProvider. 此類別衍生自 DbServerSyncProvider，且包含 SyncAdapter 及查詢變更追蹤資料表的一組命令。
SampleClientSyncProvider. 此類別衍生自 SqlCeClientSyncProvider，並包含 SyncTable。
SampleStats. 此類別使用由 SyncAgent 傳回的統計資料。
Program. 此類別會設定同步處理，並從 Utility 類別中呼叫方法。
Utility. 此類別會處理所有與同步處理沒有直接關聯的功能，例如保留連接字串資訊，以及對伺服器和用戶端資料庫進行變更。如需詳細資訊，請參閱資料庫提供者公用程式類別的 HOW-TO 主題。

API 的主要部分

在您查看完整的範例程式碼之前，建議您先檢閱下列程式碼範例。這些範例會為您說明用於此應用程式中的一些 API 主要部分。顯示的所有範例程式碼都包含在 SampleServerSyncProvider 類別中。除了本節中所顯示的命令以外，完整的程式碼範例也包含可將插入套用到伺服器的命令，以及可選取及套用刪除的命令。

第一個範例直接套用至 DbServerSyncProvider 屬性 SelectNewAnchorCommand。其他範例則套用至 Sales.Customer 資料表的 SyncAdapter 物件。

從伺服器擷取新的錨定值

下列程式碼範例會指定要從伺服器擷取新錨定值的命令。SyncSession 類別包含幾個可用於同步處理命令的字串常數，SyncNewReceivedAnchor 就是其中之一。您也可以直接在您的查詢中使用常值 @sync_new_received_anchor。

SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
    "SELECT " + newAnchorVariable + " = change_tracking_current_version()";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;

Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
    .CommandText = _
        "SELECT " + newAnchorVariable + " = change_tracking_current_version()"
    .Parameters.Add(newAnchorVariable, SqlDbType.BigInt)
    .Parameters(newAnchorVariable).Direction = ParameterDirection.Output
    .Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand

指定命令，從伺服器選取套用至用戶端的累加插入

下列程式碼範例會指定一個命令，以便從伺服器選取要套用至用戶端的累加插入。所有累加變更的查詢都會檢查是否已從追蹤資料表清除任何必要的變更。這項檢查會從下列子句開始，如果已清除變更就會引發錯誤：

IF CHANGE_TRACKING_MIN_VALID_VERSION (object_id (@sync_table_name)) > @sync_last_received_anchor

SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
    "IF @sync_initialized = 0 " +
        "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " +
        "FROM Sales.Customer LEFT OUTER JOIN " +
        "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
        "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
        "WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) " +
    "ELSE  " +
    "BEGIN " +
        "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " +
        "FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
        "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
        "WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION " +
        "<= @sync_new_received_anchor " +
        "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " +
        "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " +
        "> @sync_last_received_anchor " +
        "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
        "To recover from this error, the client must reinitialize its local database and try again' " +
        ",16,3,@sync_table_name)  " +
    "END";
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;

Dim customerIncrInserts As New SqlCommand()
With customerIncrInserts
    .CommandText = _
        "IF @sync_initialized = 0 " _
            & "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " _
            & "FROM Sales.Customer LEFT OUTER JOIN " _
            & "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
            & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
            & "WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) " _
        & "ELSE  " _
        & "BEGIN " _
            & "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " _
            & "FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
            & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
            & "WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION " _
            & "<= @sync_new_received_anchor " _
            & "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " _
            & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " _
            & "> @sync_last_received_anchor " _
            & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
            & "To recover from this error, the client must reinitialize its local database and try again' " _
            & ",16,3,@sync_table_name)  " _
        & "END"
    .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int)
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
    .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
    .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt)
    .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
    .Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts

指定命令，從伺服器選取套用至用戶端的累加更新

下列程式碼範例會指定一個命令，以便從伺服器選取要套用至用戶端的累加更新。

SqlCommand customerIncrUpdates = new SqlCommand();
customerIncrUpdates.CommandText =
    "IF @sync_initialized > 0  " +
    "BEGIN " +
        "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " +
        "FROM Sales.Customer JOIN " +
        "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
        "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
        "WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION " +
        "<= @sync_new_received_anchor " +
        "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " +
        "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " +
        "> @sync_last_received_anchor " +
        "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
        "To recover from this error, the client must reinitialize its local database and try again'" +
        ",16,3,@sync_table_name)  " +
    "END";
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
customerIncrUpdates.Connection = serverConn;
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;

Dim customerIncrUpdates As New SqlCommand()
With customerIncrUpdates
    .CommandText = _
          "IF @sync_initialized > 0  " _
        & "BEGIN " _
            & "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " _
            & "FROM Sales.Customer JOIN " _
            & "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
            & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
            & "WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION " _
            & "<= @sync_new_received_anchor " _
            & "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " _
            & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " _
            & "> @sync_last_received_anchor " _
            & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
            & "To recover from this error, the client must reinitialize its local database and try again'" _
            & ",16,3,@sync_table_name)  " _
        & "END"
    .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int)
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
    .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt)
    .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
    .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
    .Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates

指定命令，將累加更新從戶端套用至伺服器

在下列程式碼範例中，UPDATE 陳述式會更新基底資料表，並傳回受影響的資料列計數。如果資料列計數是 0，表示發生了錯誤或衝突。如需詳細資訊，請參閱 HOW TO：處理資料衝突和錯誤。

SqlCommand customerUpdates = new SqlCommand();
customerUpdates.CommandText =
    ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " +
    "UPDATE Sales.Customer " +
    "SET [CustomerName] = @CustomerName, [SalesPerson] = @SalesPerson, [CustomerType] = @CustomerType " +
    "FROM Sales.Customer  " +
    "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " +
    "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
    "WHERE (@sync_force_write = 1 " +
    "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " +
    "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " +
    "SET @sync_row_count = @@rowcount; " +
    "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " +
        "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
        "To recover from this error, the client must reinitialize its local database and try again'" +
        ",16,3,@sync_table_name)";
customerUpdates.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); 
customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar);            
customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
customerUpdates.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output;
customerUpdates.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
customerUpdates.Connection = serverConn;
customerSyncAdapter.UpdateCommand = customerUpdates;

Dim customerUpdates As New SqlCommand()
With customerUpdates
    .CommandText = _
          ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " _
        & "UPDATE Sales.Customer " _
        & "SET [CustomerName] = @CustomerName, [SalesPerson] = @SalesPerson, [CustomerType] = @CustomerType " _
        & "FROM Sales.Customer  " _
        & "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " _
        & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
        & "WHERE (@sync_force_write = 1 " _
        & "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " _
        & "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " _
        & "SET @sync_row_count = @@rowcount; " _
        & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " _
            & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
            & "To recover from this error, the client must reinitialize its local database and try again'" _
            & ",16,3,@sync_table_name)"
    .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
    .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
    .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
    .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
    .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
    .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
    .Parameters("@" + SyncSession.SyncRowCount).Direction = ParameterDirection.Output
    .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
    .Connection = serverConn
End With
customerSyncAdapter.UpdateCommand = customerUpdates

選取衝突資料列

如果基本資料表中仍然有衝突的資料列，以下命令會從伺服器資料庫選取該資料列。

SqlCommand customerUpdateConflicts = new SqlCommand();
customerUpdateConflicts.CommandText =
    "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType], " +
    "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " +
    "FROM Sales.Customer JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " +
    "ON CT.[CustomerId] = Sales.Customer.[CustomerId]";
customerUpdateConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerUpdateConflicts.Connection = serverConn;
customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts;

Dim customerUpdateConflicts As New SqlCommand()
With customerUpdateConflicts
    .CommandText = _
          "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType], " _
        & "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " _
        & "FROM Sales.Customer JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " _
        & "ON CT.[CustomerId] = Sales.Customer.[CustomerId]"
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
    .Connection = serverConn
End With
customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts

如果已經從基本資料表刪除衝突的資料列，以下命令會從伺服器資料庫選取該資料列。

SqlCommand customerDeleteConflicts = new SqlCommand();
customerDeleteConflicts.CommandText =
    "SELECT CT.[CustomerId], " +
    "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " +
    "FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
    "WHERE (CT.[CustomerId] = @CustomerId AND CT.SYS_CHANGE_OPERATION = 'D')";
customerDeleteConflicts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
customerDeleteConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerDeleteConflicts.Connection = serverConn;
customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts;

Dim customerDeleteConflicts As New SqlCommand()
With customerDeleteConflicts
    .CommandText = _
          "SELECT CT.[CustomerId], " _
        & "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " _
        & "FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
        & "WHERE (CT.[CustomerId] = @CustomerId AND CT.SYS_CHANGE_OPERATION = 'D')"
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
    .Connection = serverConn
End With
customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts

如需有關如何處理資料衝突的詳細資訊，請參閱HOW TO：處理資料衝突和錯誤。

完整的程式碼範例

下列的完整程式碼範例包含先前所述的程式碼範例，以及其他的程式碼，可用來執行同步處理。

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;

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();

            //Request a password for the client database, and delete
            //and re-create the database. The client synchronization
            //provider also enables you to create the client database 
            //if it does not exist.
            Utility.SetPassword_SqlCeClientSync();
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);

            //Specify which server and database to connect to.
            Utility.SetServerAndDb_DbServerSync("localhost", "SyncSamplesDb_ChangeTracking");
            
            //Initial synchronization. Instantiate the SyncAgent
            //and call Synchronize.
            SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
            SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "initial");

            //Make changes on the server and client.
            Utility.MakeDataChangesOnServer("Customer");
            Utility.MakeDataChangesOnClient("Customer");          

            //Subsequent synchronization.
            syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "subsequent");

            //Make conflicting changes on the server and client.
            Utility.MakeConflictingChangesOnClientAndServer();

            //Subsequent synchronization.
            syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "subsequent");

            //Return server data back to its original state.
            Utility.CleanUpServer();

            //Exit.
            Console.Write("\nPress Enter to close the window.");
            Console.ReadLine();
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.SyncAgent.
    public class SampleSyncAgent : SyncAgent
    {
        public SampleSyncAgent()
        {            
            //Instantiate a client synchronization provider and specify it
            //as the local provider for this synchronization agent.
            this.LocalProvider = new SampleClientSyncProvider();

            //Instantiate a server synchronization provider and specify it
            //as the remote provider for this synchronization agent.
            this.RemoteProvider = new SampleServerSyncProvider();

            //Add the Customer table: specify a synchronization direction of
            //Bidirectional, and that an existing table should be dropped.
            SyncTable customerSyncTable = new SyncTable("Customer");
            customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            customerSyncTable.SyncDirection = SyncDirection.Bidirectional;
            this.Configuration.SyncTables.Add(customerSyncTable);
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Server.DbServerSyncProvider.
    public class SampleServerSyncProvider : DbServerSyncProvider
    {
        public SampleServerSyncProvider()
        {
            //Create a connection to the sample server database.
            Utility util = new Utility();
            SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
            this.Connection = serverConn;
          
            //Create a command to retrieve a new anchor value from
            //the server. In this case, we use a BigInt value
            //from the change tracking table.
            //During each synchronization, the new anchor value and
            //the last anchor value from the previous synchronization
            //are used: the set of changes between these upper and
            //lower bounds is synchronized.
            //
            //SyncSession.SyncNewReceivedAnchor is a string constant; 
            //you could also use @sync_new_received_anchor directly in 
            //your queries.
            SqlCommand selectNewAnchorCommand = new SqlCommand();
            string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
            selectNewAnchorCommand.CommandText =
                "SELECT " + newAnchorVariable + " = change_tracking_current_version()";
            selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
            selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
            selectNewAnchorCommand.Connection = serverConn;
            this.SelectNewAnchorCommand = selectNewAnchorCommand;
            
            //Create a SyncAdapter for the Customer table, and then define
            //the commands to synchronize changes:
            //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
            //  and SelectIncrementalDeletesCommand are used to select changes
            //  from the server that the client provider then applies to the client.
            //* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
            //  to the server the changes that the client provider has selected
            //  from the client.
            //* SelectConflictUpdatedRowsCommand SelectConflictDeletedRowsCommand
            //  are used to detect if there are conflicts on the server during
            //  synchronization.
            //The commands reference the change tracking table that is configured
            //for the Customer table.

            //Create the SyncAdapter.
            SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");            
            
            //Select inserts from the server.
            SqlCommand customerIncrInserts = new SqlCommand();
            customerIncrInserts.CommandText =
                "IF @sync_initialized = 0 " +
                    "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " +
                    "FROM Sales.Customer LEFT OUTER JOIN " +
                    "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
                    "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
                    "WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) " +
                "ELSE  " +
                "BEGIN " +
                    "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " +
                    "FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
                    "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
                    "WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION " +
                    "<= @sync_new_received_anchor " +
                    "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " +
                    "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " +
                    "> @sync_last_received_anchor " +
                    "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
                    "To recover from this error, the client must reinitialize its local database and try again' " +
                    ",16,3,@sync_table_name)  " +
                "END";
            customerIncrInserts.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int);
            customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary);
            customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt);
            customerIncrInserts.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
            customerIncrInserts.Connection = serverConn;
            customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;

            //Apply inserts to the server.
            SqlCommand customerInserts = new SqlCommand();
            customerInserts.CommandText =
                ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " +
                "INSERT INTO Sales.Customer ([CustomerId], [CustomerName], [SalesPerson], [CustomerType]) " +
                "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType) " +
                "SET @sync_row_count = @@rowcount; " +
                "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " +
                    "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
                    "To recover from this error, the client must reinitialize its local database and try again'" +
                    ",16,3,@sync_table_name)";
            customerInserts.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary);
            customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
            customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
            customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
            customerInserts.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output;
            customerInserts.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
            customerInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            customerInserts.Connection = serverConn;
            customerSyncAdapter.InsertCommand = customerInserts;
                                    
            //Select updates from the server.
            SqlCommand customerIncrUpdates = new SqlCommand();
            customerIncrUpdates.CommandText =
                "IF @sync_initialized > 0  " +
                "BEGIN " +
                    "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " +
                    "FROM Sales.Customer JOIN " +
                    "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
                    "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
                    "WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION " +
                    "<= @sync_new_received_anchor " +
                    "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " +
                    "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " +
                    "> @sync_last_received_anchor " +
                    "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
                    "To recover from this error, the client must reinitialize its local database and try again'" +
                    ",16,3,@sync_table_name)  " +
                "END";
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int);
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt);
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary);
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
            customerIncrUpdates.Connection = serverConn;
            customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;
            
            //Apply updates to the server.
            SqlCommand customerUpdates = new SqlCommand();
            customerUpdates.CommandText =
                ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " +
                "UPDATE Sales.Customer " +
                "SET [CustomerName] = @CustomerName, [SalesPerson] = @SalesPerson, [CustomerType] = @CustomerType " +
                "FROM Sales.Customer  " +
                "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " +
                "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
                "WHERE (@sync_force_write = 1 " +
                "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " +
                "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " +
                "SET @sync_row_count = @@rowcount; " +
                "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " +
                    "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
                    "To recover from this error, the client must reinitialize its local database and try again'" +
                    ",16,3,@sync_table_name)";
            customerUpdates.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); 
            customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
            customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar);            
            customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
            customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
            customerUpdates.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output;
            customerUpdates.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
            customerUpdates.Connection = serverConn;
            customerSyncAdapter.UpdateCommand = customerUpdates;

            //Select deletes from the server.
            SqlCommand customerIncrDeletes = new SqlCommand();
            customerIncrDeletes.CommandText =
                "IF @sync_initialized > 0  " +
                "BEGIN " +
                    "SELECT CT.[CustomerId] FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
                    "WHERE (CT.SYS_CHANGE_OPERATION = 'D' AND CT.SYS_CHANGE_VERSION " +
                    "<= @sync_new_received_anchor " +
                    "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " +
                    "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " +
                    "> @sync_last_received_anchor " +
                    "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
                    "To recover from this error, the client must reinitialize its local database and try again'" +
                    ",16,3,@sync_table_name)  " +
                "END";
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int);
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt);
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary);
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
            customerIncrDeletes.Connection = serverConn;
            customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes;

            //Apply deletes to the server.            
            SqlCommand customerDeletes = new SqlCommand();
            customerDeletes.CommandText =
                ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " +
                "DELETE Sales.Customer FROM Sales.Customer " +
                "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " +
                "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " +
                "WHERE (@sync_force_write = 1 " +
                "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " +
                "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " +
                "SET @sync_row_count = @@rowcount; " +
                "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " +
                    "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " +
                    "To recover from this error, the client must reinitialize its local database and try again'" +
                    ",16,3,@sync_table_name)";
            customerDeletes.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary);
            customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
            customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);           
            customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
            customerDeletes.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output;
            customerDeletes.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar);
            customerDeletes.Connection = serverConn;
            customerSyncAdapter.DeleteCommand = customerDeletes;
           
            //This command is used if @sync_row_count returns
            //0 when changes are applied to the server.
            SqlCommand customerUpdateConflicts = new SqlCommand();
            customerUpdateConflicts.CommandText =
                "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType], " +
                "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " +
                "FROM Sales.Customer JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " +
                "ON CT.[CustomerId] = Sales.Customer.[CustomerId]";
            customerUpdateConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            customerUpdateConflicts.Connection = serverConn;
            customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts;

            //This command is used if the server provider cannot find
            //a row in the base table.
            SqlCommand customerDeleteConflicts = new SqlCommand();
            customerDeleteConflicts.CommandText =
                "SELECT CT.[CustomerId], " +
                "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " +
                "FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " +
                "WHERE (CT.[CustomerId] = @CustomerId AND CT.SYS_CHANGE_OPERATION = 'D')";
            customerDeleteConflicts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            customerDeleteConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            customerDeleteConflicts.Connection = serverConn;
            customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts;


            //Add the SyncAdapter to the server synchronization provider.
            this.SyncAdapters.Add(customerSyncAdapter);

        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
    //You can just instantiate the provider directly and associate it
    //with the SyncAgent, but here we use this class to handle client 
    //provider events.
    public class SampleClientSyncProvider : SqlCeClientSyncProvider
    {
            
        public SampleClientSyncProvider()
        {
            //Specify a connection string for the sample client database.
            Utility util = new Utility();
            this.ConnectionString = Utility.ConnStr_SqlCeClientSync;

            //We use the CreatingSchema event to change the schema
            //by using the API. We use the SchemaCreated event to 
            //change the schema by using SQL.
            this.CreatingSchema +=new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
            this.SchemaCreated +=new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
        }

        private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
        {
            //Set the RowGuid property because it is not copied
            //to the client by default. This is also a good time
            //to specify literal defaults with .Columns[ColName].DefaultValue;
            //but we will specify defaults like NEWID() by calling
            //ALTER TABLE after the table is created.
            Console.Write("Creating schema for " + e.Table.TableName + " | ");                        
            e.Schema.Tables["Customer"].Columns["CustomerId"].RowGuid = true;
        }

        private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
        {        
            //Call ALTER TABLE on the client. This must be done
            //over the same connection and within the same
            //transaction that Sync Framework uses
            //to create the schema on the client.
            Utility util = new Utility();
            Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName);
            Console.WriteLine("Schema created for " + e.Table.TableName);
        }
    }

    //Handle the statistics that are returned by the SyncAgent.
    public class SampleStats
    {
        public void DisplayStats(SyncStatistics 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.TotalChangesUploaded);
            Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
            Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
            Console.WriteLine(String.Empty);
        }
    }
}

Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe


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()

        'Request a password for the client database, and delete
        'and re-create the database. The client synchronization
        'provider also enables you to create the client database 
        'if it does not exist.
        Utility.SetPassword_SqlCeClientSync()
        Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, True)

        'Specify which server and database to connect to.
        Utility.SetServerAndDb_DbServerSync("localhost", "SyncSamplesDb_ChangeTracking")

        'Initial synchronization. Instantiate the SyncAgent
        'and call Synchronize.
        Dim sampleSyncAgent As New SampleSyncAgent()
        Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
        sampleStats.DisplayStats(syncStatistics, "initial")

        'Make changes on the server and client.
        Utility.MakeDataChangesOnServer("Customer")
        Utility.MakeDataChangesOnClient("Customer")

        'Subsequent synchronization.
        syncStatistics = sampleSyncAgent.Synchronize()
        sampleStats.DisplayStats(syncStatistics, "subsequent")

        'Make conflicting changes on the server and client.
        Utility.MakeConflictingChangesOnClientAndServer()

        'Subsequent synchronization.
        syncStatistics = sampleSyncAgent.Synchronize()
        sampleStats.DisplayStats(syncStatistics, "subsequent")

        'Return server data back to its original state.
        Utility.CleanUpServer()

        '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.SyncAgent.
Public Class SampleSyncAgent
    Inherits SyncAgent

    Public Sub New()
        'Instantiate a client synchronization provider and specify it
        'as the local provider for this synchronization agent.
        Me.LocalProvider = New SampleClientSyncProvider()

        'Instantiate a server synchronization provider and specify it
        'as the remote provider for this synchronization agent.
        Me.RemoteProvider = New SampleServerSyncProvider()

        'Add the Customer table: specify a synchronization direction of
        'Bidirectional, and that an existing table should be dropped.
        Dim customerSyncTable As New SyncTable("Customer")
        customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        customerSyncTable.SyncDirection = SyncDirection.Bidirectional
        Me.Configuration.SyncTables.Add(customerSyncTable)

    End Sub 'New
End Class 'SampleSyncAgent

'Create a class that is derived from 
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
    Inherits DbServerSyncProvider

    Public Sub New()
        'Create a connection to the sample server database.
        Dim util As New Utility()
        Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
        Me.Connection = serverConn

        'Create a command to retrieve a new anchor value from
        'the server. In this case, we use a BigInt value
        'from the change tracking table.
        'During each synchronization, the new anchor value and
        'the last anchor value from the previous synchronization
        'are used: the set of changes between these upper and
        'lower bounds is synchronized.
        '
        'SyncSession.SyncNewReceivedAnchor is a string constant; 
        'you could also use @sync_new_received_anchor directly in 
        'your queries.
        Dim selectNewAnchorCommand As New SqlCommand()
        Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
        With selectNewAnchorCommand
            .CommandText = _
                "SELECT " + newAnchorVariable + " = change_tracking_current_version()"
            .Parameters.Add(newAnchorVariable, SqlDbType.BigInt)
            .Parameters(newAnchorVariable).Direction = ParameterDirection.Output
            .Connection = serverConn
        End With
        Me.SelectNewAnchorCommand = selectNewAnchorCommand

        'Create a SyncAdapter for the Customer table, and then define
        'the commands to synchronize changes:
        '* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
        '  and SelectIncrementalDeletesCommand are used to select changes
        '  from the server that the client provider then applies to the client.
        '* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
        '  to the server the changes that the client provider has selected
        '  from the client.
        '* SelectConflictUpdatedRowsCommand SelectConflictDeletedRowsCommand
        '  are used to detect if there are conflicts on the server during
        '  synchronization.
        'The commands reference the change tracking table that is configured
        'for the Customer table.
        'Create the SyncAdapter.
        Dim customerSyncAdapter As New SyncAdapter("Customer")

        'Select inserts from the server.
        Dim customerIncrInserts As New SqlCommand()
        With customerIncrInserts
            .CommandText = _
                "IF @sync_initialized = 0 " _
                    & "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " _
                    & "FROM Sales.Customer LEFT OUTER JOIN " _
                    & "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
                    & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
                    & "WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) " _
                & "ELSE  " _
                & "BEGIN " _
                    & "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " _
                    & "FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
                    & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
                    & "WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION " _
                    & "<= @sync_new_received_anchor " _
                    & "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " _
                    & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " _
                    & "> @sync_last_received_anchor " _
                    & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
                    & "To recover from this error, the client must reinitialize its local database and try again' " _
                    & ",16,3,@sync_table_name)  " _
                & "END"
            .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
        'Apply inserts to the server.
        Dim customerInserts As New SqlCommand()
        With customerInserts
            .CommandText = _
                  ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " _
                & "INSERT INTO Sales.Customer ([CustomerId], [CustomerName], [SalesPerson], [CustomerType]) " _
                & "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType) " _
                & "SET @sync_row_count = @@rowcount; " _
                & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " _
                    & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
                    & "To recover from this error, the client must reinitialize its local database and try again'" _
                    & ",16,3,@sync_table_name)"
            .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Parameters("@" + SyncSession.SyncRowCount).Direction = ParameterDirection.Output
            .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
            .Connection = serverConn
        End With
        customerSyncAdapter.InsertCommand = customerInserts

        'Select updates from the server.
        Dim customerIncrUpdates As New SqlCommand()
        With customerIncrUpdates
            .CommandText = _
                  "IF @sync_initialized > 0  " _
                & "BEGIN " _
                    & "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " _
                    & "FROM Sales.Customer JOIN " _
                    & "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
                    & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
                    & "WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION " _
                    & "<= @sync_new_received_anchor " _
                    & "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " _
                    & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " _
                    & "> @sync_last_received_anchor " _
                    & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
                    & "To recover from this error, the client must reinitialize its local database and try again'" _
                    & ",16,3,@sync_table_name)  " _
                & "END"
            .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
            .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates
        'Apply updates to the server.
        Dim customerUpdates As New SqlCommand()
        With customerUpdates
            .CommandText = _
                  ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " _
                & "UPDATE Sales.Customer " _
                & "SET [CustomerName] = @CustomerName, [SalesPerson] = @SalesPerson, [CustomerType] = @CustomerType " _
                & "FROM Sales.Customer  " _
                & "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " _
                & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
                & "WHERE (@sync_force_write = 1 " _
                & "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " _
                & "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " _
                & "SET @sync_row_count = @@rowcount; " _
                & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " _
                    & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
                    & "To recover from this error, the client must reinitialize its local database and try again'" _
                    & ",16,3,@sync_table_name)"
            .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
            .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Parameters("@" + SyncSession.SyncRowCount).Direction = ParameterDirection.Output
            .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
            .Connection = serverConn
        End With
        customerSyncAdapter.UpdateCommand = customerUpdates
        'Select deletes from the server.
        Dim customerIncrDeletes As New SqlCommand()
        With customerIncrDeletes
            .CommandText = _
                  "IF @sync_initialized > 0  " _
                & "BEGIN " _
                    & "SELECT CT.[CustomerId] FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
                    & "WHERE (CT.SYS_CHANGE_OPERATION = 'D' AND CT.SYS_CHANGE_VERSION " _
                    & "<= @sync_new_received_anchor " _
                    & "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " _
                    & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " _
                    & "> @sync_last_received_anchor " _
                    & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
                    & "To recover from this error, the client must reinitialize its local database and try again'" _
                    & ",16,3,@sync_table_name)  " _
                & "END"
            .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
            .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes

        'Apply deletes to the server.            
        Dim customerDeletes As New SqlCommand()
        With customerDeletes
            .CommandText = _
                  ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " _
                & "DELETE Sales.Customer FROM Sales.Customer " _
                & "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " _
                & "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " _
                & "WHERE (@sync_force_write = 1 " _
                & "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " _
                & "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " _
                & "SET @sync_row_count = @@rowcount; " _
                & "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " _
                    & "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " _
                    & "To recover from this error, the client must reinitialize its local database and try again'" _
                    & ",16,3,@sync_table_name)"
            .Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary)
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Parameters("@" + SyncSession.SyncRowCount).Direction = ParameterDirection.Output
            .Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar)
            .Connection = serverConn
        End With
        customerSyncAdapter.DeleteCommand = customerDeletes

        'This command is used if @sync_row_count returns
        '0 when changes are applied to the server.
        Dim customerUpdateConflicts As New SqlCommand()
        With customerUpdateConflicts
            .CommandText = _
                  "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType], " _
                & "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " _
                & "FROM Sales.Customer JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT  " _
                & "ON CT.[CustomerId] = Sales.Customer.[CustomerId]"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts

        'This command is used if the server provider cannot find
        'a row in the base table.
        Dim customerDeleteConflicts As New SqlCommand()
        With customerDeleteConflicts
            .CommandText = _
                  "SELECT CT.[CustomerId], " _
                & "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " _
                & "FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " _
                & "WHERE (CT.[CustomerId] = @CustomerId AND CT.SYS_CHANGE_OPERATION = 'D')"
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt)
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts

        'Add the SyncAdapter to the server synchronization provider.
        Me.SyncAdapters.Add(customerSyncAdapter)

    End Sub 'New 
End Class 'SampleServerSyncProvider

'Create a class that is derived from 
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but here we use this class to handle client 
'provider events.
Public Class SampleClientSyncProvider
    Inherits SqlCeClientSyncProvider


    Public Sub New()
        'Specify a connection string for the sample client database.
        Dim util As New Utility()
        Me.ConnectionString = Utility.ConnStr_SqlCeClientSync

        'We use the CreatingSchema event to change the schema
        'by using the API. We use the SchemaCreated event to 
        'change the schema by using SQL.
        AddHandler Me.CreatingSchema, AddressOf SampleClientSyncProvider_CreatingSchema
        AddHandler Me.SchemaCreated, AddressOf SampleClientSyncProvider_SchemaCreated
    End Sub 'New


    Private Sub SampleClientSyncProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
        'Set the RowGuid property because it is not copied
        'to the client by default. This is also a good time
        'to specify literal defaults with .Columns[ColName].DefaultValue;
        'but we will specify defaults like NEWID() by calling
        'ALTER TABLE after the table is created.
        Console.Write("Creating schema for " + e.Table.TableName + " | ")
        e.Schema.Tables("Customer").Columns("CustomerId").RowGuid = True

    End Sub 'SampleClientSyncProvider_CreatingSchema


    Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
        'Call ALTER TABLE on the client. This must be done
        'over the same connection and within the same
        'transaction that Sync Framework uses
        'to create the schema on the client.
        Dim util As New Utility()
        Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName)
        Console.WriteLine("Schema created for " + e.Table.TableName)

    End Sub 'SampleClientSyncProvider_SchemaCreated
End Class 'SampleClientSyncProvider

'Handle the statistics that are returned by the SyncAgent.

Public Class SampleStats

    Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, 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.TotalChangesUploaded)
        Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
        Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime)
        Console.WriteLine(String.Empty)

    End Sub 'DisplayStats
End Class 'SampleStats

請參閱

概念

追蹤伺服器資料庫中的變更

共用方式為