通过

如何使用会话变量

  • 项目

本主题演示如何使用 Sync Framework 中的会话变量。本主题中的示例着重介绍以下 Sync Framework 类型：

有关如何运行示例代码的信息，请参见对常见客户端与服务器同步任务进行编程中的“帮助主题中的示例应用程序”。

了解会话变量

Sync Framework 提供了一组会话变量，在同步期间可通过它们将值传递给 SyncAdapter 和 DbServerSyncProvider 命令。这些变量的指定方式与 ADO.NET 命令中查询或存储过程的其他参数相似。在同步会话期间，当每个 ADO.NET 命令对象被 DbServerSyncProvider 调用时，提供程序遍历同步参数集合 (SyncParameters) 以确定它是否可以将每个参数与基于名称的 ADO.NET 命令参数进行匹配。如果存在与内置会话变量的匹配，或者与您已定义的自定义参数的匹配，则在提供程序调用该命令前，变量将由 Sync Framework 填充。

例如，下面的查询通过使用 sync_last_received_anchor、sync_new_received_anchor 和 sync_client_id 会话变量，从 Customer 表中选择变更。

"SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @sync_last_received_anchor AND
InsertTimestamp <= @sync_new_received_anchor AND InsertId <>
@sync_client_id"

这些变量的值由 Sync Framework 在同步期间提供。在上面的查询中，可以直接使用变量名，也可以使用 SyncSession 对象中提供的常量。

"SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @" +
SyncSession.SyncLastReceivedAnchor + 
" AND InsertTimestamp <= @" +
SyncSession.SyncNewReceivedAnchor +
" AND InsertId <> @"
SyncSession.SyncClientId

下表列出了所有可用的会话变量以及它们的用法。

会话变量 用法

sync_client_id、sync_client_id_hash、sync_client_id_binary 和 sync_originator_id

用于标识当前正在同步的客户端。ID 通常用于冲突检测，并防止在双向同步过程中将变更发送回客户端。有关更多信息，请参见如何在客户端和服务器之间交换双向增量数据变更。

默认情况下，Sync Framework 用 GUID 标识每个客户端，而 GUID 由 sync_client_id 返回。此外，还可以创建 ID 的散列值并在查询中使用 sync_client_id_hash。sync_client_id_binary 在您通过使用 SQL Server 变更跟踪来跟踪变更时很有用。可以将 GUID 从 sync_client_id 映射到某一整数并使用 sync_originator_id。有关更多信息，请参见本主题后面的“示例”部分。

sync_last_received_anchor 和sync_new_received_anchor

用于定义在会话期间要同步的变更集。在当前同步期间，为 SelectNewAnchorCommand 属性指定的命令提供一个新的定位点值。将对在上次收到的定位点值之后和新收到的定位点值之前所做的变更进行同步。然后，存储新收到的定位点并在下一次同步时将其用作上一次收到的定位点值。有关更多信息，请参见跟踪服务器数据库中的变更中的“确定要下载到客户端的数据变更”。

sync_force_write

与 RetryWithForceWrite 的 ApplyAction 一起使用，强制应用由于冲突或错误而未能应用的变更。有关更多信息，请参见如何处理数据冲突和错误。

sync_row_count

返回服务器上受上一次操作影响的行数。在 SQL Server 数据库中，@@ROWCOUNT 提供此变量的值。行数为 0 指示操作失败，这通常是由于冲突或错误引起的。有关更多信息，请参见如何处理数据冲突和错误。

sync_initialized

返回一个值，指示当前同步是初始同步（值为 0）还是后续同步（值为 1）。

sync_table_name 和sync_group_name

当必须在查询中指定表名或组名时使用。

sync_batch_count、sync_batch_size 和 sync_max_received_anchor

当进行批量变更时使用。有关更多信息，请参见如何指定变更的顺序和批大小。

sync_session_id

返回标识当前同步会话的 GUID 值。

示例

下面的代码示例演示在同步 Sync Framework 示例数据库中的 Vendor 表时如何使用会话变量。

API 的要点

本节提供的代码示例指出了涉及会话变量的 API 的要点。下面的代码示例为 SelectNewAnchorCommand 属性指定一个查询，该查询为 sync_new_received_anchor 变量设置值。此值由从服务器数据库中选择所做变更的同步命令使用。

SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = 
    "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
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 + " = min_active_rowversion() - 1"
    .Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
    .Parameters(newAnchorVariable).Direction = ParameterDirection.Output
    .Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand

下面的代码示例指定一条从服务器上选择要应用于客户端的增量更新的命令。该命令包括定位点变量和 sync_originator_id 变量。sync_originator_id 变量的值由为 SelectClientIdCommand 属性指定的查询提供。在本节内容的末尾将介绍该查询和属性。

SqlCommand vendorIncrUpdates = new SqlCommand();
vendorIncrUpdates.CommandText =
    "SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
    "FROM Sales.Vendor " +
    "WHERE (UpdateTimestamp > @sync_last_received_anchor " +
    "AND UpdateTimestamp <= @sync_new_received_anchor " +
    "AND UpdateId <> @sync_originator_id " +
    "AND NOT (InsertTimestamp > @sync_last_received_anchor " +
    "AND InsertId <> @sync_originator_id))";
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrUpdates.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates;

Dim vendorIncrUpdates As New SqlCommand()
With vendorIncrUpdates
    .CommandText = _
        "SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
      & "FROM Sales.Vendor " _
      & "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
      & "AND UpdateTimestamp <= @sync_new_received_anchor " _
      & "AND UpdateId <> @sync_originator_id " _
      & "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
      & "AND InsertId <> @sync_originator_id))"
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
    .Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates

下面的代码示例指定一条将来自客户端的更新应用于服务器的命令。除了定位点和 ID 变量之外，此命令还包括 sync_force_write 和 sync_row_count 变量。

SqlCommand vendorUpdates = new SqlCommand();
vendorUpdates.CommandText =
    "UPDATE Sales.Vendor SET " + 
    "VendorName = @VendorName, CreditRating = @CreditRating, " + 
    "PreferredVendor = @PreferredVendor, " + 
    "UpdateId = @sync_originator_id " +           
    "WHERE (VendorId = @VendorId) " + 
    "AND (@sync_force_write = 1 " + 
    "OR (UpdateTimestamp <= @sync_last_received_anchor " +
    "OR UpdateId = @sync_originator_id)) " + 
    "SET @sync_row_count = @@rowcount";
vendorUpdates.Parameters.Add("@VendorName", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorUpdates.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorUpdates.Connection = serverConn;
vendorSyncAdapter.UpdateCommand = vendorUpdates;

Dim vendorUpdates As New SqlCommand()
With vendorUpdates
    .CommandText = _
        "UPDATE Sales.Vendor SET " _
      & "VendorName = @VendorName, CreditRating = @CreditRating, " _
      & "PreferredVendor = @PreferredVendor, " _
      & "UpdateId = @sync_originator_id " _
      & "WHERE (VendorId = @VendorId) " _
      & "AND (@sync_force_write = 1 " _
      & "OR (UpdateTimestamp <= @sync_last_received_anchor " _
      & "OR UpdateId = @sync_originator_id)) " _
      & "SET @sync_row_count = @@rowcount"
    .Parameters.Add("@VendorName", SqlDbType.NVarChar)
    .Parameters.Add("@CreditRating", SqlDbType.NVarChar)
    .Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
    .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
    .Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
    .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
    .Connection = serverConn
End With
vendorSyncAdapter.UpdateCommand = vendorUpdates

下面的代码示例指定一条从服务器上选择要应用于客户端的增量删除的命令。除了定位点和 ID 参数之外，此命令还包括 sync_initialized 变量。在这种情况下，只有当执行后续同步时，才从逻辑删除表中选择行。在初始同步期间与逻辑删除表中的行无关。

SqlCommand vendorIncrDeletes = new SqlCommand();
vendorIncrDeletes.CommandText =
    "SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
    "FROM Sales.Vendor_Tombstone " +
    "WHERE (@sync_initialized = 1 " +
    "AND DeleteTimestamp > @sync_last_received_anchor " +
    "AND DeleteTimestamp <= @sync_new_received_anchor " +
    "AND DeleteId <> @sync_originator_id)";
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrDeletes.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes;

Dim vendorIncrDeletes As New SqlCommand()
With vendorIncrDeletes
    .CommandText = _
        "SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
      & "FROM Sales.Vendor_Tombstone " _
      & "WHERE (@sync_initialized = 1 " _
      & "AND DeleteTimestamp > @sync_last_received_anchor " _
      & "AND DeleteTimestamp <= @sync_new_received_anchor " _
      & "AND DeleteId <> @sync_originator_id)"
    .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
    .Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes

下面的代码示例指定一条将客户端 ID 映射到发起方 ID 的命令。这不是必需的，但是对于使用整数而不是 Sync Framework 使用的 GUID 来表示客户端十分有用。该存储过程在下一个代码示例中介绍。

SqlCommand selectClientIdCommand = new SqlCommand();
selectClientIdCommand.CommandType = CommandType.StoredProcedure;
selectClientIdCommand.CommandText = "usp_GetOriginatorId";
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;
selectClientIdCommand.Connection = serverConn;
this.SelectClientIdCommand = selectClientIdCommand;

Dim selectClientIdCommand As New SqlCommand()
With selectClientIdCommand
    .CommandType = CommandType.StoredProcedure
    .CommandText = "usp_GetOriginatorId"
    .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
    .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output
    .Connection = serverConn
End With
Me.SelectClientIdCommand = selectClientIdCommand

下面的代码示例创建一个映射表和一个存储过程，以从表中读取数据并填充表。该存储过程使用客户端 ID（一个 GUID）作为输入，并返回一个发起方 ID（一个整数）。为服务器插入一个映射行，并在同步新客户端时添加其他行。在客户端进行了一次同步之后，映射表便会包含与该客户端对应的一个条目。由于 SyncAdapter 命令使用发起方 ID，因此 Vendor 表中的跟踪列的类型为 int 而不是 uniqueidentifier。

CREATE TABLE IdMapping(
    ClientId uniqueidentifier NOT NULL PRIMARY KEY, 
    OriginatorId int NOT NULL)
GO

--Insert a mapping for the server.
INSERT INTO IdMapping VALUES ('00000000-0000-0000-0000-000000000000', 0)
GO

CREATE PROCEDURE usp_GetOriginatorId
    @sync_client_id uniqueidentifier,
    @sync_originator_id int out

AS
     SELECT @sync_originator_id = OriginatorId FROM IdMapping WHERE ClientId = @sync_client_id 

     IF ( @sync_originator_id IS NULL )
     BEGIN
          SELECT @sync_originator_id = MAX(OriginatorId) + 1 FROM IdMapping
          INSERT INTO IdMapping VALUES (@sync_client_id, @sync_originator_id)
     END
GO

完整的代码示例

下面的完整代码示例包括了上面介绍的代码示例以及用于执行同步的其他代码。示例需要 Utility 类，可通过用于数据库提供程序帮助主题的 Utility 类获得该类。

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);
            
            //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("Vendor");
            Utility.MakeDataChangesOnClient("Vendor");

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

            //Return the 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 Vendor table: specify a synchronization direction of
            //Bidirectional, and that an existing table should be dropped.
            SyncTable vendorSyncTable = new SyncTable("Vendor");
            vendorSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            vendorSyncTable.SyncDirection = SyncDirection.Bidirectional;
            this.Configuration.SyncTables.Add(vendorSyncTable);
        }
    }

    //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 timestamp value
            //that is retrieved and stored in the client database.
            //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 + " = min_active_rowversion() - 1";
            selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
            selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
            selectNewAnchorCommand.Connection = serverConn;
            this.SelectNewAnchorCommand = selectNewAnchorCommand;
            
            //Create a command that enables you to pass in a 
            //client ID (a GUID) and get back the orginator ID (an integer) 
            //that is defined in a mapping table on the server.
            SqlCommand selectClientIdCommand = new SqlCommand();
            selectClientIdCommand.CommandType = CommandType.StoredProcedure;
            selectClientIdCommand.CommandText = "usp_GetOriginatorId";
            selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;
            selectClientIdCommand.Connection = serverConn;
            this.SelectClientIdCommand = selectClientIdCommand;

            //Create a SyncAdapter for the Vendor 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.

            //Create the SyncAdapter
            SyncAdapter vendorSyncAdapter = new SyncAdapter("Vendor");            
            
            //Select inserts from the server.
            //This command includes three session variables:
            //@sync_last_received_anchor, @sync_new_received_anchor,
            //and @sync_originator_id. The anchor variables are used with
            //SelectNewAnchorCommand to determine the set of changes to 
            //synchronize. In other example code, the commands use 
            //@sync_client_id instead of @sync_originator_id. In this case, 
            //@sync_originator_id is used because the SelectClientIdCommand 
            //is specified.
            SqlCommand vendorIncrInserts = new SqlCommand();
            vendorIncrInserts.CommandText =  
                "SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
                "FROM Sales.Vendor " +
                "WHERE (InsertTimestamp > @sync_last_received_anchor " +
                "AND InsertTimestamp <= @sync_new_received_anchor " +
                "AND InsertId <> @sync_originator_id)";
            vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
            vendorIncrInserts.Connection = serverConn;
            vendorSyncAdapter.SelectIncrementalInsertsCommand = vendorIncrInserts;

            //Apply inserts to the server.
            //This command includes @sync_row_count, which returns
            //a count of how many rows were affected by the
            //last database operation. In SQL Server, the variable
            //is assigned the value of @@rowcount. The count is used
            //to determine whether an operation was successful or
            //was unsuccessful due to a conflict or an error.
            SqlCommand vendorInserts = new SqlCommand();
            vendorInserts.CommandText =
                "INSERT INTO Sales.Vendor (VendorId, VendorName, CreditRating, PreferredVendor, InsertId, UpdateId) " +
                "VALUES (@VendorId, @VendorName, @CreditRating, @PreferredVendor, @sync_originator_id, @sync_originator_id) " +
                "SET @sync_row_count = @@rowcount";
            vendorInserts.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
            vendorInserts.Parameters.Add("@VendorName", SqlDbType.NVarChar);
            vendorInserts.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
            vendorInserts.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
            vendorInserts.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
            vendorInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
            vendorInserts.Connection = serverConn;
            vendorSyncAdapter.InsertCommand = vendorInserts;
                                    
            
            //Select updates from the server
            SqlCommand vendorIncrUpdates = new SqlCommand();
            vendorIncrUpdates.CommandText =
                "SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
                "FROM Sales.Vendor " +
                "WHERE (UpdateTimestamp > @sync_last_received_anchor " +
                "AND UpdateTimestamp <= @sync_new_received_anchor " +
                "AND UpdateId <> @sync_originator_id " +
                "AND NOT (InsertTimestamp > @sync_last_received_anchor " +
                "AND InsertId <> @sync_originator_id))";
            vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
            vendorIncrUpdates.Connection = serverConn;
            vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates;
            
            //Apply updates to the server.
            //This command includes @sync_force_write, which can
            //be used to apply changes in case of a conflict.
            SqlCommand vendorUpdates = new SqlCommand();
            vendorUpdates.CommandText =
                "UPDATE Sales.Vendor SET " + 
                "VendorName = @VendorName, CreditRating = @CreditRating, " + 
                "PreferredVendor = @PreferredVendor, " + 
                "UpdateId = @sync_originator_id " +           
                "WHERE (VendorId = @VendorId) " + 
                "AND (@sync_force_write = 1 " + 
                "OR (UpdateTimestamp <= @sync_last_received_anchor " +
                "OR UpdateId = @sync_originator_id)) " + 
                "SET @sync_row_count = @@rowcount";
            vendorUpdates.Parameters.Add("@VendorName", SqlDbType.NVarChar);
            vendorUpdates.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
            vendorUpdates.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
            vendorUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
            vendorUpdates.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
            vendorUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
            vendorUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            vendorUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
            vendorUpdates.Connection = serverConn;
            vendorSyncAdapter.UpdateCommand = vendorUpdates;


            //Select deletes from the server.
            //This command includes @sync_initialized, which is
            //used to determine whether a client has been 
            //initialized already. If this variable returns 0,
            //this is the first synchronization for this client ID
            //or originator ID.
            SqlCommand vendorIncrDeletes = new SqlCommand();
            vendorIncrDeletes.CommandText =
                "SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
                "FROM Sales.Vendor_Tombstone " +
                "WHERE (@sync_initialized = 1 " +
                "AND DeleteTimestamp > @sync_last_received_anchor " +
                "AND DeleteTimestamp <= @sync_new_received_anchor " +
                "AND DeleteId <> @sync_originator_id)";
            vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
            vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
            vendorIncrDeletes.Connection = serverConn;
            vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes;

            //Apply deletes to the server.            
            SqlCommand vendorDeletes = new SqlCommand();
            vendorDeletes.CommandText =
                "DELETE FROM Sales.Vendor " +
                "WHERE (VendorId = @VendorId) " + 
                "AND (@sync_force_write = 1 " + 
                "OR (UpdateTimestamp <= @sync_last_received_anchor " + 
                "OR UpdateId = @sync_originator_id)) " + 
                "SET @sync_row_count = @@rowcount " + 
                "IF (@sync_row_count > 0)  BEGIN " + 
                "UPDATE Sales.Vendor_Tombstone " + 
                "SET DeleteId = @sync_originator_id " +
                "WHERE (VendorId = @VendorId) " + 
                "END";
            vendorDeletes.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);       
            vendorDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
            vendorDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            vendorDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
            vendorDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);           
            vendorDeletes.Connection = serverConn;
            vendorSyncAdapter.DeleteCommand = vendorDeletes;     


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

        }
    }

    //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["Vendor"].Columns["VendorId"].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)

        '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("Vendor")
        Utility.MakeDataChangesOnClient("Vendor")

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

        'Return the 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 Vendor table: specify a synchronization direction of
        'Bidirectional, and that an existing table should be dropped.
        Dim vendorSyncTable As New SyncTable("Vendor")
        vendorSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        vendorSyncTable.SyncDirection = SyncDirection.Bidirectional
        Me.Configuration.SyncTables.Add(vendorSyncTable)

    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 timestamp value
        'that is retrieved and stored in the client database.
        '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 + " = min_active_rowversion() - 1"
            .Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
            .Parameters(newAnchorVariable).Direction = ParameterDirection.Output
            .Connection = serverConn
        End With
        Me.SelectNewAnchorCommand = selectNewAnchorCommand

        'Create a command that enables you to pass in a 
        'client ID (a GUID) and get back the orginator ID (an integer) 
        'that is defined in a mapping table on the server.
        Dim selectClientIdCommand As New SqlCommand()
        With selectClientIdCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "usp_GetOriginatorId"
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output
            .Connection = serverConn
        End With
        Me.SelectClientIdCommand = selectClientIdCommand

        'Create a SyncAdapter for the Vendor 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.

        'Create the SyncAdapter
        Dim vendorSyncAdapter As New SyncAdapter("Vendor")

        'Select inserts from the server.
        'This command includes three session variables:
        '@sync_last_received_anchor, @sync_new_received_anchor,
        'and @sync_originator_id. The anchor variables are used with
        'SelectNewAnchorCommand to determine the set of changes to 
        'synchronize. In other example code, the commands use 
        '@sync_client_id instead of @sync_originator_id. In this case, 
        '@sync_originator_id is used because the SelectClientIdCommand 
        'is specified.
        Dim vendorIncrInserts As New SqlCommand()
        With vendorIncrInserts
            .CommandText = _
                "SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
              & "FROM Sales.Vendor " _
              & "WHERE (InsertTimestamp > @sync_last_received_anchor " _
              & "AND InsertTimestamp <= @sync_new_received_anchor " _
              & "AND InsertId <> @sync_originator_id)"
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
            .Connection = serverConn
        End With
        vendorSyncAdapter.SelectIncrementalInsertsCommand = vendorIncrInserts

        'Apply inserts to the server.
        'This command includes @sync_row_count, which returns
        'a count of how many rows were affected by the
        'last database operation. In SQL Server, the variable
        'is assigned the value of @@rowcount. The count is used
        'to determine whether an operation was successful or
        'was unsuccessful due to a conflict or an error.
        Dim vendorInserts As New SqlCommand()
        With vendorInserts
            .CommandText = _
                "INSERT INTO Sales.Vendor (VendorId, VendorName, CreditRating, PreferredVendor, InsertId, UpdateId) " _
              & "VALUES (@VendorId, @VendorName, @CreditRating, @PreferredVendor, @sync_originator_id, @sync_originator_id) " _
              & "SET @sync_row_count = @@rowcount"
            .Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@VendorName", SqlDbType.NVarChar)
            .Parameters.Add("@CreditRating", SqlDbType.NVarChar)
            .Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Connection = serverConn
        End With
        vendorSyncAdapter.InsertCommand = vendorInserts

        'Select updates from the server
        Dim vendorIncrUpdates As New SqlCommand()
        With vendorIncrUpdates
            .CommandText = _
                "SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
              & "FROM Sales.Vendor " _
              & "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
              & "AND UpdateTimestamp <= @sync_new_received_anchor " _
              & "AND UpdateId <> @sync_originator_id " _
              & "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
              & "AND InsertId <> @sync_originator_id))"
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
            .Connection = serverConn
        End With
        vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates

        'Apply updates to the server.
        'This command includes @sync_force_write, which can
        'be used to apply changes in case of a conflict.
        Dim vendorUpdates As New SqlCommand()
        With vendorUpdates
            .CommandText = _
                "UPDATE Sales.Vendor SET " _
              & "VendorName = @VendorName, CreditRating = @CreditRating, " _
              & "PreferredVendor = @PreferredVendor, " _
              & "UpdateId = @sync_originator_id " _
              & "WHERE (VendorId = @VendorId) " _
              & "AND (@sync_force_write = 1 " _
              & "OR (UpdateTimestamp <= @sync_last_received_anchor " _
              & "OR UpdateId = @sync_originator_id)) " _
              & "SET @sync_row_count = @@rowcount"
            .Parameters.Add("@VendorName", SqlDbType.NVarChar)
            .Parameters.Add("@CreditRating", SqlDbType.NVarChar)
            .Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
            .Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Connection = serverConn
        End With
        vendorSyncAdapter.UpdateCommand = vendorUpdates

        'Select deletes from the server.
        'This command includes @sync_initialized, which is
        'used to determine whether a client has been 
        'initialized already. If this variable returns 0,
        'this is the first synchronization for this client ID
        'or originator ID.
        Dim vendorIncrDeletes As New SqlCommand()
        With vendorIncrDeletes
            .CommandText = _
                "SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
              & "FROM Sales.Vendor_Tombstone " _
              & "WHERE (@sync_initialized = 1 " _
              & "AND DeleteTimestamp > @sync_last_received_anchor " _
              & "AND DeleteTimestamp <= @sync_new_received_anchor " _
              & "AND DeleteId <> @sync_originator_id)"
            .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
            .Connection = serverConn
        End With
        vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes

        'Apply deletes to the server.            
        Dim vendorDeletes As New SqlCommand()
        With vendorDeletes
            .CommandText = _
                "DELETE FROM Sales.Vendor " _
              & "WHERE (VendorId = @VendorId) " _
              & "AND (@sync_force_write = 1 " _
              & "OR (UpdateTimestamp <= @sync_last_received_anchor " _
              & "OR UpdateId = @sync_originator_id)) " _
              & "SET @sync_row_count = @@rowcount " _
              & "IF (@sync_row_count > 0)  BEGIN " _
              & "UPDATE Sales.Vendor_Tombstone " _
              & "SET DeleteId = @sync_originator_id " _
              & "WHERE (VendorId = @VendorId) " _
              & "END"
            .Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Connection = serverConn
        End With
        vendorSyncAdapter.DeleteCommand = vendorDeletes


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

    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("Vendor").Columns("VendorId").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

请参阅

概念

对常见客户端与服务器同步任务进行编程