Как использовать переменные сеанса для синхронизации совместной работы (не SQL Server)

В данном разделе демонстрируется использование переменных сеанса в Sync Framework. В примерах, приведенных в этом разделе, обсуждаются следующие типы данных и свойства платформ Sync Framework:

• DbSyncProvider

• DbSyncAdapter

• DbSyncSession

• SyncParameters

Сведения о запуске образца кода см. в подразделе «Образцы приложений в разделах инструкций» раздела Синхронизация других баз данных, совместимых с ADO.NET.

Основные сведения о переменных сеанса

Sync Framework предоставляет набор переменных сеанса, с помощью которых в процессе синхронизации можно передавать значения командам DbSyncAdapter и DbSyncProvider. Эти переменные указываются в командах ADO.NET аналогично другим параметрам запросов или хранимых процедур. Во время сеанса синхронизации, когда каждый объект команды ADO.NET вызывается кодом DbSyncProvider, поставщик просматривает коллекцию параметров синхронизации (SyncParameters) для выяснения, можно ли сопоставить конкретный параметр с командой параметра ADO.NET исходя из имени. Если есть совпадение с сеансовой переменной либо с пользовательским параметром, такая переменная заполняется кодом Sync Framework до того, как поставщик вызовет команду.

Например, в приведенной ниже инструкции UPDATE изменения к таблице Customer применяются с помощью переменных сеанса @sync\_min\_timestamp, @sync\_force\_write и @sync\_row\_count.

 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. В приведенной ниже таблице перечислены все доступные переменные сеанса и способы их использования.

Пример

В приведенном ниже примере кода показано, как использовать переменные сеанса при синхронизации таблицы Customer в одноранговой учебной базе данных Sync Framework.

Ключевые элементы 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 используются для группирования наборов изменений. Если в командах привязки приложения вместо функции MIN_ACTIVE_ROWVERSION используется функция @@DBTS, это может привести к пропуску изменений, активных на момент запуска синхронизации.

В приведенном ниже примере кода для свойства 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 System.Data.SqlServerCe;
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 System.Data.SqlServerCe
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