如何使用协作同步的会话变量

项目
10/19/2009

备注

文档本节中的主题同步其他与 ADO.NET 兼容的数据库旨在说明如何使用 Sync Framework 来同步非 SQL Server 的数据库。在此版本中，SQL Server 用于代码示例中，但通过对特定于 SQL Server 的对象（如 SqlConnection）以及所示的 SQL 查询进行一些修改，这些代码就可用于其他与 ADO.NET 兼容的数据库。有关 SQL Server 同步的信息，请参见如何配置和执行协作同步 (SQL Server)。

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

有关如何运行示例代码的信息，请参见同步其他与 ADO.NET 兼容的数据库中的“帮助主题中的示例应用程序”。

了解会话变量

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

例如，下面的 UPDATE 语句通过使用 @sync_min_timestamp、@sync_force_write 和 @sync_row_count 会话变量将变更应用到 Customer 表。

UPDATE c
SET c.CustomerName = @CustomerName, c.SalesPerson = @SalesPerson, c.CustomerType = @CustomerType      
FROM Sales.Customer c JOIN Sync.Customer_Tracking t ON c.CustomerId = t.CustomerId
WHERE ((t.local_update_peer_timestamp <= @sync_min_timestamp) OR @sync_force_write = 1)
    AND t.CustomerId = @CustomerId  
SET @sync_row_count = @@rowcount

在同步期间 @sync_min_timestamp 和 @sync_force_write 的值由 Sync Framework 设置，@sync_row_count 的值则由 SQL Server @@rowcount 函数设置。在上面的查询中，可以直接使用变量名，也可以使用 SyncSession 对象中提供的字符串常量。下表列出了所有可用的会话变量以及它们的用法。

会话变量	用法
sync_check_concurrency	由 Sync Framework 用于更新和删除元数据的命令。Sync Framework 使用此值确定如果元数据引用的行已发生变更是否可以执行元数据变更。如果不能执行元数据变更，则会引发 ApplyMetadataFailed 事件。
sync_create_peer_key、sync_create_peer_timestamp、sync_row_is_tombstone、sync_row_timestamp、sync_update_peer_key、sync_update_peer_timestamp、sync_scope_cleanup_timestamp、sync_scope_local_id、sync_shared_scope_name 和 sync_table_name	由选择变更并将变更应用到变更跟踪列的命令使用。有关这些列的更多信息，请参见如何为协作同步设置服务器数据库（非 SQL Server）中的“创建用于每个表元数据的跟踪表”。
sync_force_write	与 RetryWithForceWrite 的 ApplyAction 一起使用，以强制应用由于冲突或错误而未能应用的变更。有关更多信息，请参见如何处理协作同步中的数据冲突和错误 (SQL Server)。
sync_initialize	返回一个值，指示当前同步会话是初始会话（值为 1）还是后续会话（值为 0）。
sync_metadata_only	由 Sync Framework 用于为 SelectIncrementalChangesCommand 和 SelectRowCommand 属性指定的命令。Sync Framework 为此变量指定值 1 时，它仅选择元数据而不选择相关的数据变更。在 Sync Framework 不需要数据变更的情况下，如使用 SelectRowCommand 选择发生冲突的行时，使用此会话变量最合适。
sync_min_timestamp 和sync_new_timestamp	用于定义在会话期间要同步的变更集。在当前同步会话期间，为 SelectNewTimestampCommand 属性指定的命令提供一个新的 timestamp 值。将最小值之后和新值之前所做的变更进行同步。然后，新值将存储起来并用作下一次同步会话的最小值。
sync_row_count	返回服务器上受上一次操作影响的行数。在 SQL Server 数据库中，@@ROWCOUNT 提供此变量的值。如本主题的代码示例中所示，存储过程应包含一个输出参数以设置 sync_row_count 的值。行数为 0 指示操作失败，这通常是由于冲突或错误引起的。有关更多信息，请参见如何处理协作同步中的数据冲突和错误 (SQL Server)。
sync_scope_cleanup_knowledge、sync_scope_id、sync_scope_knowledge、sync_scope_name 和 sync_scope_timestamp	由选择变更并将变更应用到存储同步知识的表的命令使用。有关此表的示例，请参见如何为协作同步设置服务器数据库（非 SQL Server）中的“创建用于每个作用域元数据的跟踪表”。
sync_session_id	返回标识当前同步会话的 GUID 值。
sync_stage_name	返回对应于 DbSyncStage 枚举中的某个值的值。

示例

下面的代码示例说明在同步 Sync Framework 示例对等数据库中的 Customer 表时如何使用会话变量。

API 的要点

本节提供的代码示例指出了涉及会话变量的 API 的一些要点。显示的几个命令调用包含在示例对等数据库中的存储过程。有关更多信息，请参见如何为协作同步设置服务器数据库（非 SQL Server）中的“创建用于选择和更新数据和元数据的存储过程”。

下面的代码示例指定了针对 SelectNewTimestampCommand 属性的一个查询。此属性设置 sync_new_timestamp 变量的值。此值由从服务器数据库中选择所做变更的同步命令使用。

SqlCommand selectNewTimestampCommand = new SqlCommand();
string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;

sampleProvider.SelectNewTimestampCommand = selectNewTimestampCommand;

Dim newTimestampVariable As String = "@" + DbSyncSession.SyncNewTimestamp

Dim selectNewTimestampCommand As New SqlCommand()

With selectNewTimestampCommand
    .CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"
    .Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
    .Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With

sampleProvider.SelectNewTimestampCommand = selectNewTimestampCommand

MIN_ACTIVE_ROWVERSION 函数返回当前数据库中最小的活动 timestamp（也称为 rowversion）值。尚未提交的事务中使用的 timestamp 值处于活动状态。如果数据库中没有活动的值，MIN_ACTIVE_ROWVERSION 将返回与 @@DBTS + 1 相同的值。MIN_ACTIVE_ROWVERSION 对于诸如使用 timestamp 值将多组变更分组在一起的数据同步等情况非常有用。如果应用程序在其定位点命令中使用的是 @@DBTS 而不是 MIN_ACTIVE_ROWVERSION，则可能会丢失那些在进行同步时处于活动状态的变更。

下面的代码示例为 SelectIncrementalChangesCommand 属性指定了一个存储过程。此过程在同步会话期间选择某一对等方的插入、更新和删除操作以应用到另一对等方。sync_min_timestamp 变量指定要同步的变更集中包含的最小 timestamp。将此变量的值与跟踪表的 sync_row_timestamp 列中的值进行比较，以确定要选择哪些行。sync_initialize 变量用于指定同步会话是否是两个对等方间的第一个会话。如果是，则 sp_Customer_SelectChanges 存储过程可以包含仅在此第一个会话期间执行的逻辑。

SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);

adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;

Dim chgsCustomerCmd As New SqlCommand()

With chgsCustomerCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sync.sp_Customer_SelectChanges"
    .Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int)
End With

adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd

下面的代码示例指定一条将从某一对等方选择的更新操作应用到另一对等方的命令。sync_row_count 变量使得 Sync Framework 可以确定更新操作是成功还是失败。如果更新失败，可以将 sync_force_write 变量设置为 1。这允许 sp_Customer_ApplyUpdate 存储过程中的逻辑不同。有关更多信息，请参见如何处理协作同步中的数据冲突和错误 (SQL Server)。在将更新操作应用到某一对等方之前，使用 sync_min_timestamp 变量来检查自上一同步会话以来该对等方上的行是否已更新。

SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate";
updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);

adapterCustomer.UpdateCommand = updCustomerCmd;

Dim updCustomerCmd As New SqlCommand()

With updCustomerCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sync.sp_Customer_ApplyUpdate"
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
    .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
    .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
    .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
    .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
    .Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With

adapterCustomer.UpdateCommand = updCustomerCmd

下面的代码示例指定将更新操作应用到 Customer_Tracking 表和 ScopeInfo 表的命令。大多数会话变量对应于跟踪表中的列。在命令中使用 sync_check_concurrency 变量来确定如果元数据引用的行已发生变更是否可以执行元数据变更。

SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;

Dim updMetadataCustomerCmd As New SqlCommand()

With updMetadataCustomerCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sync.sp_Customer_UpdateMetadata"
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
    .Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With

adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd

SqlCommand updReplicaInfoCmd = new SqlCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "UPDATE  Sync.ScopeInfo SET " +
                                "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
                                "scope_id = @" + DbSyncSession.SyncScopeId + ", " +
                                "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
                                "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
                                " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
                                "set @" + DbSyncSession.SyncRowCount + " = @@rowcount";
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

sampleProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;

Dim updReplicaInfoCmd As New SqlCommand()

With updReplicaInfoCmd
    .CommandType = CommandType.Text
    .CommandText = "UPDATE  Sync.ScopeInfo SET " _
                 & "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
                 & "scope_id = @" + DbSyncSession.SyncScopeId + ", " _
                 & "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " _
                 & "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " _
                 & " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " _
                 & "set @" + DbSyncSession.SyncRowCount + " = @@rowcount"
    .Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000)
    .Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000)
    .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
    .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier)
    .Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With

sampleProvider.UpdateScopeInfoCommand = updReplicaInfoCmd

完整的代码示例

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

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

namespace Microsoft.Samples.Synchronization
{
    class Program
    {
        static void Main(string[] args)
        {

            //The SampleStats class handles information from the SyncStatistics
            //object that the Synchronize method returns.
            SampleStats sampleStats = new SampleStats();

            try
            {
                //Initial synchronization. Instantiate the SyncOrchestrator
                //and call Synchronize. Note that data is not synchronized during the
                //session between peer 1 and peer 3, because all rows have already
                //been delivered to peer 3 during its synchronization session with peer 2.
                SyncOrchestrator sampleSyncAgent;
                SyncOperationStatistics syncStatistics;

                sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "initial");

                sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync2, Utility.ConnStr_DbSync3);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "initial");

                sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync3);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "initial");
            }


            catch (DbOutdatedSyncException ex)
            {
                Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
                                  " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            //Make changes in each peer database.
            Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
            Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer");
            Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync3, "Customer");

            try
            {
                //Subsequent synchronization. Changes are now synchronized between all
                //peers.
                SyncOrchestrator sampleSyncAgent;
                SyncOperationStatistics syncStatistics;

                sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");

                sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync2, Utility.ConnStr_DbSync3);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");

                sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync3);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");

                //Sessions in which no new changes have been made.
                //In this case, the call to SelectTableMaxTimestampsCommand indicates 
                //that no data changes are available to synchronize, so
                //SelectIncrementalChangesCommand is not called.
                sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");

                sampleSyncAgent = new SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");
            }


            catch (DbOutdatedSyncException ex)
            {
                Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
                                  " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            //Return peer data back to its original state.
            Utility.CleanUpNode(Utility.ConnStr_DbSync1);
            Utility.CleanUpNode(Utility.ConnStr_DbSync2);
            Utility.CleanUpNode(Utility.ConnStr_DbSync3);

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

        //Create a class that is derived from 
        //Microsoft.Synchronization.SyncOrchestrator.
        public class SampleSyncAgent : SyncOrchestrator
        {
            public SampleSyncAgent(string localProviderConnString, string remoteProviderConnString)
            {

                //Instantiate the sample provider that allows us to create a provider
                //for both of the peers that are being synchronized.
                SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();

                //Instantiate a DbSyncProvider for the local peer and the remote peer.
                //For example, if this code is running at peer1 and is
                //synchronizing with peer2, peer1 would be the local provider
                //and peer2 the remote provider.
                DbSyncProvider localProvider = new DbSyncProvider();
                DbSyncProvider remoteProvider = new DbSyncProvider();

                //Create a provider by using the SetupSyncProvider on the sample class.             
                sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider);
                localProvider.SyncProviderPosition = SyncProviderPosition.Local;
                
                sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider);
                remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote;

                //Specify the local and remote providers that should be synchronized,
                //and the direction and order of changes. In this case, changes are first
                //uploaded from remote to local and then downloaded in the other direction.
                this.LocalProvider = localProvider;
                this.RemoteProvider = remoteProvider;
                this.Direction = SyncDirectionOrder.UploadAndDownload;
            }
        }


        public class SampleSyncProvider
        {
            public DbSyncProvider SetupSyncProvider(string peerConnString, DbSyncProvider sampleProvider)
            {

                SqlConnection peerConnection = new SqlConnection(peerConnString);
                sampleProvider.Connection = peerConnection;
                sampleProvider.ScopeName = "Sales";

                //Create a DbSyncAdapter object for the Customer table and associate it 
                //with the DbSyncProvider. Following the DataAdapter style in ADO.NET, 
                //DbSyncAdapter is the equivalent for synchronization. The commands that 
                //are specified for the DbSyncAdapter object call stored procedures
                //that are created in each peer database.
                DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer");


                //Specify the primary key, which Sync Framework uses
                //to identify each row during synchronization.
                adapterCustomer.RowIdColumns.Add("CustomerId");


                //Specify the command to select incremental changes.
                //In this command and other commands, session variables are
                //used to pass information at runtime. DbSyncSession.SyncMetadataOnly 
                //and SyncMinTimestamp are two of the string constants that
                //the DbSyncSession class exposes. You could also include 
                //@sync_metadata_only and @sync_min_timestamp directly in your 
                //queries:
                //*  sync_metadata_only is used by Sync Framework as an optimization
                //   in some queries.
                //* The value of the sync_min_timestamp session variable is compared to
                //   values in the sync_row_timestamp column in the tracking table to 
                //   determine which rows to select.
                SqlCommand chgsCustomerCmd = new SqlCommand();
                chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
                chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges";
                chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
                chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);

                adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;

                //Specify the command to insert rows.
                //The sync_row_count session variable is used in this command 
                //and other commands to return a count of the rows affected by an operation. 
                //A count of 0 indicates that an operation failed.
                SqlCommand insCustomerCmd = new SqlCommand();
                insCustomerCmd.CommandType = CommandType.StoredProcedure;
                insCustomerCmd.CommandText = "Sync.sp_Customer_ApplyInsert";
                insCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                insCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                insCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
                insCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
                insCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                adapterCustomer.InsertCommand = insCustomerCmd;


                //Specify the command to update rows.
                //The value of the sync_min_timestamp session variable is compared to
                //values in the sync_row_timestamp column in the tracking table to 
                //determine which rows to update.
                SqlCommand updCustomerCmd = new SqlCommand();
                updCustomerCmd.CommandType = CommandType.StoredProcedure;
                updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate";
                updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
                updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
                updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
                
                adapterCustomer.UpdateCommand = updCustomerCmd;


                //Specify the command to delete rows.
                //The value of the sync_min_timestamp session variable is compared to
                //values in the sync_row_timestamp column in the tracking table to 
                //determine which rows to delete.
                SqlCommand delCustomerCmd = new SqlCommand();
                delCustomerCmd.CommandType = CommandType.StoredProcedure;
                delCustomerCmd.CommandText = "Sync.sp_Customer_ApplyDelete";
                delCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
                
                adapterCustomer.DeleteCommand = delCustomerCmd;

                //Specify the command to select any conflicting rows.
                SqlCommand selRowCustomerCmd = new SqlCommand();
                selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
                selRowCustomerCmd.CommandText = "Sync.sp_Customer_SelectRow";
                selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);

                adapterCustomer.SelectRowCommand = selRowCustomerCmd;


                //Specify the command to insert metadata rows.
                //The session variables in this command relate to columns in
                //the tracking table.
                SqlCommand insMetadataCustomerCmd = new SqlCommand();
                insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
                insMetadataCustomerCmd.CommandText = "Sync.sp_Customer_InsertMetadata";
                insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);          
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd;


                //Specify the command to update metadata rows.
                SqlCommand updMetadataCustomerCmd = new SqlCommand();
                updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
                updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata";
                updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;

                //Specify the command to delete metadata rows.
                SqlCommand delMetadataCustomerCmd = new SqlCommand();
                delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
                delMetadataCustomerCmd.CommandText = "Sync.sp_Customer_DeleteMetadata";
                delMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd;


                //Add the adapter to the provider.
                sampleProvider.SyncAdapters.Add(adapterCustomer);


                // Configure commands that relate to the provider itself rather 
                // than the DbSyncAdapter object for each table:
                // * SelectNewTimestampCommand: Returns the new high watermark for 
                //   the current synchronization session.
                // * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge, 
                //   and a scope version (timestamp).
                // * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.            
                // * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table 
                //   or tracking table, to determine whether for each table the destination already 
                //   has all of the changes from the source. If a destination table has all the changes,
                //   SelectIncrementalChangesCommand is not called for that table.
                // There are additional commands related to metadata cleanup that are not 
                // included in this application.


                //Select a new timestamp.
                //During each synchronization, the new value and
                //the last value from the previous synchronization
                //are used: the set of changes between these upper and
                //lower bounds is synchronized.
                SqlCommand selectNewTimestampCommand = new SqlCommand();
                string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
                selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
                selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
                selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
                
                sampleProvider.SelectNewTimestampCommand = selectNewTimestampCommand;

                //Specify the command to select local replica metadata.
                SqlCommand selReplicaInfoCmd = new SqlCommand();
                selReplicaInfoCmd.CommandType = CommandType.Text;
                selReplicaInfoCmd.CommandText = "SELECT " +
                                                "scope_id, " +
                                                "scope_local_id, " +
                                                "scope_sync_knowledge, " +
                                                "scope_tombstone_cleanup_knowledge, " +
                                                "scope_timestamp " +
                                                "FROM Sync.ScopeInfo " +
                                                "WHERE scope_name = @" + DbSyncSession.SyncScopeName;
                selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
                
                sampleProvider.SelectScopeInfoCommand = selReplicaInfoCmd;


                //Specify the command to update local replica metadata. 
                SqlCommand updReplicaInfoCmd = new SqlCommand();
                updReplicaInfoCmd.CommandType = CommandType.Text;
                updReplicaInfoCmd.CommandText = "UPDATE  Sync.ScopeInfo SET " +
                                                "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
                                                "scope_id = @" + DbSyncSession.SyncScopeId + ", " +
                                                "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
                                                "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
                                                " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
                                                "set @" + DbSyncSession.SyncRowCount + " = @@rowcount";
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                
                sampleProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;


                //Return the maximum timestamp from the Customer_Tracking table.
                //If more tables are synchronized, the query should UNION
                //all of the results. The table name is not schema-qualified
                //in this case because the name was not schema qualified in the
                //DbSyncAdapter constructor.
                SqlCommand selTableMaxTsCmd = new SqlCommand();
                selTableMaxTsCmd.CommandType = CommandType.Text;
                selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
                                               "MAX(local_update_peer_timestamp) AS max_timestamp " +
                                               "FROM Sync.Customer_Tracking";
                sampleProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;

                return sampleProvider;
            }
        }

        //Handle the statistics that are returned by the SyncAgent.
        public class SampleStats
        {
            public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
            {
                Console.WriteLine(String.Empty);
                if (syncType == "initial")
                {
                    Console.WriteLine("****** Initial Synchronization ******");
                }
                else if (syncType == "subsequent")
                {
                    Console.WriteLine("***** Subsequent Synchronization ****");
                }

                Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
                Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
                Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
                Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
                Console.WriteLine(String.Empty);
            }
        }
    }
}

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

Class Program

    Shared Sub Main(ByVal args() As String)

        'The SampleStats class handles information from the SyncStatistics
        'object that the Synchronize method returns.
        Dim sampleStats As New SampleStats()

        Try
            'Initial synchronization. Instantiate the SyncOrchestrator
            'and call Synchronize. Note that data is not synchronized during the
            'session between peer 1 and peer 3, because all rows have already
            'been delivered to peer 3 during its synchronization session with peer 2.              
            Dim sampleSyncAgent As SyncOrchestrator
            Dim syncStatistics As SyncOperationStatistics

            sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "initial")

            sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync2, Utility.ConnStr_DbSync3)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "initial")

            sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync3)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "initial")


        Catch ex As DbOutdatedSyncException
            Console.WriteLine("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
                            & " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        'Make changes in each peer database.
        Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
        Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer")
        Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync3, "Customer")

        Try
            'Subsequent synchronization. Changes are now synchronized between all
            'peers.
            Dim sampleSyncAgent As SyncOrchestrator
            Dim syncStatistics As SyncOperationStatistics

            sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")

            sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync2, Utility.ConnStr_DbSync3)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")

            sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync3)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")

            'Sessions in which no new changes have been made.
            'In this case, the call to SelectTableMaxTimestampsCommand indicates 
            'that no data changes are available to synchronize, so
            'SelectIncrementalChangesCommand is not called.
            sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")

            sampleSyncAgent = New SampleSyncAgent(Utility.ConnStr_DbSync1, Utility.ConnStr_DbSync2)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")


        Catch ex As DbOutdatedSyncException
            Console.WriteLine("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
                            & " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        'Return peer data back to its original state.
        Utility.CleanUpNode(Utility.ConnStr_DbSync1)
        Utility.CleanUpNode(Utility.ConnStr_DbSync2)
        Utility.CleanUpNode(Utility.ConnStr_DbSync3)

        'Exit.
        Console.Write(vbLf + "Press Enter to close the window.")
        Console.ReadLine()

    End Sub 'Main

End Class 'Program

'Create a class that is derived from 
'Microsoft.Synchronization.SyncOrchestrator.
Public Class SampleSyncAgent
    Inherits SyncOrchestrator

    Public Sub New(ByVal localProviderConnString As String, ByVal remoteProviderConnString As String)

        'Instantiate the sample provider that allows us to create a provider
        'for both of the peers that are being synchronized.
        Dim sampleSyncProvider As New SampleSyncProvider()

        'Instantiate a DbSyncProvider for the local peer and the remote peer.
        'For example, if this code is running at peer1 and is
        'synchronizing with peer2, peer1 would be the local provider
        'and peer2 the remote provider.  
        Dim localProvider As New DbSyncProvider()
        Dim remoteProvider As New DbSyncProvider()

        'Create a provider by using the SetupSyncProvider on the sample class.
        sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider)
        localProvider.SyncProviderPosition = SyncProviderPosition.Local

        sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider)
        remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote

        'Specify the local and remote providers that should be synchronized,
        'and the direction and order of changes. In this case, changes are first
        'uploaded from local to remote and then downloaded in the other direction.
        Me.LocalProvider = localProvider
        Me.RemoteProvider = remoteProvider
        Me.Direction = SyncDirectionOrder.UploadAndDownload

    End Sub 'New
End Class 'SampleSyncAgent

Public Class SampleSyncProvider

    Public Function SetupSyncProvider(ByVal peerConnString As String, ByVal sampleProvider As DbSyncProvider) As DbSyncProvider

        Dim peerConnection As New SqlConnection(peerConnString)
        sampleProvider.Connection = peerConnection
        sampleProvider.ScopeName = "Sales"

        'Create a DbSyncAdapter object for the Customer table and associate it 
        'with the DbSyncProvider. Following the DataAdapter style in ADO.NET, 
        'DbSyncAdapter is the equivalent for synchronization. The commands that 
        'are specified for the DbSyncAdapter object call stored procedures
        'that are created in each peer database.
        Dim adapterCustomer As New DbSyncAdapter("Customer")

        'Specify the primary key, which Sync Framework uses
        'to identify each row during synchronization.
        adapterCustomer.RowIdColumns.Add("CustomerId")

        'Specify the command to select incremental changes.
        'In this command and other commands, session variables are
        'used to pass information at runtime. DbSyncSession.SyncMetadataOnly 
        'and SyncMinTimestamp are two of the string constants that
        'the DbSyncSession class exposes. You could also include 
        '@sync_metadata_only and @sync_min_timestamp directly in your 
        'queries:
        '*  sync_metadata_only is used by Sync Framework as an optimization
        '   in some queries.
        '* The value of the sync_min_timestamp session variable is compared to
        '   values in the sync_row_timestamp column in the tracking table to 
        '   determine which rows to select.
        Dim chgsCustomerCmd As New SqlCommand()

        With chgsCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_SelectChanges"
            .Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int)
        End With

        adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd

        'Specify the command to insert rows.
        'The sync_row_count session variable is used in this command 
        'and other commands to return a count of the rows affected by an operation. 
        'A count of 0 indicates that an operation failed.
        Dim insCustomerCmd As New SqlCommand()

        With insCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_ApplyInsert"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.InsertCommand = insCustomerCmd


        'Specify the command to update rows.
        'The value of the sync_min_timestamp session variable is compared to
        'values in the sync_row_timestamp column in the tracking table to 
        'determine which rows to update.
        Dim updCustomerCmd As New SqlCommand()

        With updCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_ApplyUpdate"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
            .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
            .Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int)
        End With

        adapterCustomer.UpdateCommand = updCustomerCmd


        'Specify the command to delete rows.
        'The value of the sync_min_timestamp session variable is compared to
        'values in the sync_row_timestamp column in the tracking table to 
        'determine which rows to delete.
        Dim delCustomerCmd As New SqlCommand()

        With delCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_ApplyDelete"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
            .Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int)
        End With

        adapterCustomer.DeleteCommand = delCustomerCmd

        'Specify the command to select any conflicting rows.
        Dim selRowCustomerCmd As New SqlCommand()

        With selRowCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_SelectRow"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
        End With

        adapterCustomer.SelectRowCommand = selRowCustomerCmd


        'Specify the command to insert metadata rows.
        'The session variables in this command relate to columns in
        'the tracking table. These are the same columns
        'that were specified as DbSyncAdapter properties at the beginning 
        'of this code example.
        Dim insMetadataCustomerCmd As New SqlCommand()

        With insMetadataCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_InsertMetadata"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd


        'Specify the command to update metadata rows.
        Dim updMetadataCustomerCmd As New SqlCommand()

        With updMetadataCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_UpdateMetadata"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd

        'Specify the command to delete metadata rows.
        Dim delMetadataCustomerCmd As New SqlCommand()

        With delMetadataCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_DeleteMetadata"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd


        sampleProvider.SyncAdapters.Add(adapterCustomer)


        ' Configure commands that relate to the provider itself rather 
        ' than the DbSyncAdapter object for each table:
        ' * SelectNewTimestampCommand: Returns the new high watermark for 
        '   the current synchronization session.
        ' * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge, 
        '   and a scope version (timestamp).
        ' * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.            
        ' * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table 
        '   or tracking table, to determine whether for each table the destination already 
        '   has all of the changes from the source. If a destination table has all the changes,
        '   SelectIncrementalChangesCommand is not called for that table.
        ' There are additional commands related to metadata cleanup that are not 
        ' included in this application.           


        'Select a new timestamp.
        'During each synchronization, the new value and
        'the last value from the previous synchronization
        'are used: the set of changes between these upper and
        'lower bounds is synchronized.
        Dim newTimestampVariable As String = "@" + DbSyncSession.SyncNewTimestamp

        Dim selectNewTimestampCommand As New SqlCommand()

        With selectNewTimestampCommand
            .CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"
            .Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
            .Parameters(newTimestampVariable).Direction = ParameterDirection.Output
        End With

        sampleProvider.SelectNewTimestampCommand = selectNewTimestampCommand

        'Specify the command to select local replica metadata. 
        Dim selReplicaInfoCmd As New SqlCommand()

        With selReplicaInfoCmd
            .CommandType = CommandType.Text
            .CommandText = "SELECT " _
                         & "scope_id, " _
                         & "scope_local_id, " _
                         & "scope_sync_knowledge, " _
                         & "scope_tombstone_cleanup_knowledge, " _
                         & "scope_timestamp " _
                         & "FROM Sync.ScopeInfo " _
                         & "WHERE scope_name = @" + DbSyncSession.SyncScopeName
            .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
        End With

        sampleProvider.SelectScopeInfoCommand = selReplicaInfoCmd


        'Specify the command to update local replica metadata. 
        Dim updReplicaInfoCmd As New SqlCommand()

        With updReplicaInfoCmd
            .CommandType = CommandType.Text
            .CommandText = "UPDATE  Sync.ScopeInfo SET " _
                         & "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
                         & "scope_id = @" + DbSyncSession.SyncScopeId + ", " _
                         & "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " _
                         & "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " _
                         & " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " _
                         & "set @" + DbSyncSession.SyncRowCount + " = @@rowcount"
            .Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000)
            .Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000)
            .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
            .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        sampleProvider.UpdateScopeInfoCommand = updReplicaInfoCmd

        'Return the maximum timestamp from the Customer_Tracking table.
        'If more tables are synchronized, the query should UNION
        'all of the results. The table name is not schema-qualified
        'in this case because the name was not schema qualified in the
        'DbSyncAdapter constructor.
        Dim selTableMaxTsCmd As New SqlCommand()
        selTableMaxTsCmd.CommandType = CommandType.Text
        selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
                                     & "MAX(local_update_peer_timestamp) AS max_timestamp " _
                                     & "FROM Sync.Customer_Tracking"
        sampleProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd

        Return sampleProvider

    End Function 'SetupSyncProvider

End Class 'SampleSyncProvider


'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats

    Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
        Console.WriteLine(String.Empty)
        If syncType = "initial" Then
            Console.WriteLine("****** Initial Synchronization ******")
        ElseIf syncType = "subsequent" Then
            Console.WriteLine("***** Subsequent Synchronization ****")
        End If

        Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
        Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
        Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
        Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
        Console.WriteLine(String.Empty)

    End Sub 'DisplayStats
End Class 'SampleStats

请参阅

概念

同步其他与 ADO.NET 兼容的数据库

通过

如何使用协作同步的会话变量

了解会话变量

示例

API 的要点

完整的代码示例

请参阅

概念

其他资源