如何使用 SQL Server 变更跟踪
本主题概述了 SQL Server 变更跟踪，并介绍在 SQL Server 数据库和 SQL Server Compact 数据库之间执行双向同步的控制台应用程序。如果服务器正在运行 SQL Server 2008，则建议您使用 SQL Server 变更跟踪。如果服务器运行的是其他数据库，请参见如何使用自定义变更跟踪系统。
SQL Server 变更跟踪概述
本文档中的许多示例都使用一组添加到基表中的列和触发器来处理变更跟踪，使用其他表来跟踪删除操作。有关更多信息，请参见跟踪服务器数据库中的变更。这种类型的跟踪对于非 SQL Server 2008 数据库非常有用。但是，它还是具有以下缺点：
在服务器数据库中要求架构变更。架构变更可能会影响其他应用程序，或者可能根本无法实现。
对一行中执行的每个变更都会激发触发器。这会影响性能。
用于维护正确的行版本和删除的逻辑将变得复杂。
如果某一服务器数据库具有长时间运行的事务，则除非这些事务得到正确处理，否则，在同步期间可能会失去数据变更。这可能导致数据不一致。
SQL Server 变更跟踪可解决这些问题，并提供一种简明的方法来跟踪变更。当对某一表启用了变更跟踪时，SQL Server 数据库引擎 将维护有关对表所做变更的信息。然后，应用程序使用相应变更跟踪函数来确定哪些行发生了变更并获取有关这些变更的信息。SQL Server 变更跟踪的主要优点如下：
对于使用 Sync Framework 的脱机同步方案，您不必创建触发器、时间戳列、其他附加列或附加表。
在提交时跟踪变更，而不是在发生 DML 操作时跟踪。
函数将返回对表所做的增量变更和版本信息。即使存在重叠和未提交的事务，这些函数也能提供可靠且易于使用的结果。
对性能的影响非常小。
可以自动清除变更跟踪数据。
本主题的其余部分将演示如何在 Sync Framework 应用程序中使用 SQL Server 变更跟踪。有关变更跟踪的更多信息，请参见 SQL Server 2008 联机丛书。
将 SQL Server 变更跟踪与 Sync Framework 脱机数据库提供程序一起使用
本节介绍如何启用变更跟踪，以及如何使用变更跟踪查询来确定要下载到客户端的数据变更。本节中的信息介绍如何使用手动创建的命令选择来自服务器的变更。有关如何使用同步适配器生成器为您创建命令的信息，请参见入门：客户端与服务器同步。
启用 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 联机丛书中的“数据库引擎中的隔离级别”。
确定要下载到客户端的数据变更
启用变更跟踪后，Sync Framework 应用程序使用变更跟踪函数和“定位点”**来确定要下载的插入、更新和删除。定位点仅仅是用来定义一组要同步的变更的一个时间点。请考虑以下查询：
为 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 子句中包括附加的条件，以便筛选数据。有关更多信息，请参见如何筛选行和列。“基于非键列的筛选器”一节中包括与使用 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 首先将变更上载到服务器，然后将变更下载到客户端。如果不跟踪执行变更的客户端的标识，该变更将上载到服务器，然后在同一个同步会话中下载回到该客户端。某些情况下，回送变更是必需的，但是在另一些情况下并非如此。
变更跟踪提供一个机制，可以将应用程序数据与变更行时的变更信息一起存储。这些应用程序数据可用于标识正在进行变更的客户端。然后，在您查询变更时可返回进行了变更的客户端的这一标识。
SYS_CHANGE_CONTEXT 列可与 ClientId 属性一起使用，以确定每个插入、更新或删除操作是由哪个客户端执行的。在任何表使用除快照同步以外的方法进行第一次同步时，Sync Framework 会在客户端上存储一个 GUID 值以标识该客户端。然后将此 ID 传递给 DbServerSyncProvider，以便能够由每个 SyncAdapter 对象的命令使用。可以通过 ClientId 属性以及 @sync_client_id 和 @sync_client_id_binary 会话变量获得该 ID 值。请考虑以下 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 值来标识客户端。有关更多信息，请参见如何使用会话变量。
若要跟踪哪一客户端进行了在服务器上应用的数据变更，请使用 WITH CHANGE_TRACKING_CONTEXT 子句。在执行 INSERT、UPDATE 或 DELETE 语句前，将 CHANGE_TRACKING_CONTEXT 设置为 @sync_client_id 或 @sync_client_id_binary 会话变量的值。此信息存储在变更跟踪表中，以便应用程序可以跟踪变更所处的上下文。对于 Sync Framework 来说，这通常是客户端 ID。但是，您可以存储适合 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 示例数据库。有关更多信息，请参见用于数据库提供程序帮助主题的安装脚本。
如果您曾经阅读过用于客户端与服务器同步的体系结构和类主题，应该已经对该应用程序中使用的主要类有所了解。该应用程序由以下类组成：
SampleSyncAgent. 此类派生自 SyncAgent。SampleServerSyncProvider. 此类派生自 DbServerSyncProvider 并包含 SyncAdapter 和一组查询变更跟踪表的命令。SampleClientSyncProvider. 此类派生自 SqlCeClientSyncProvider 并包含 SyncTable。SampleStats. 此类使用 SyncAgent 返回的统计信息。Program. 此类设置同步并调用Utility类的方法。Utility. 此类负责处理所有不与同步直接相关的功能，例如保存连接字符串信息以及更改服务器和客户端数据库等。有关更多信息，请参见 用于数据库提供程序帮助主题的 Utility 类。
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，则表示发生了错误或冲突。有关更多信息，请参见如何处理数据冲突和错误。
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
有关如何处理数据冲突的更多信息，请参见如何处理数据冲突和错误。
完整的代码示例
下面的完整代码示例包括了上面介绍的代码示例以及用于执行同步的其他代码。
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