通过

如何筛选行和列

  • 项目

本主题介绍如何在要同步的表中筛选行和列。本主题中的示例着重介绍以下 Sync Framework 类型：

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

了解筛选

通常，客户端数据库只需要服务器上数据的一个子集。Sync Framework 使您能够指定客户端需要的行和列，以及是手动构造同步命令还是使用 SqlSyncAdapterBuilder 构造同步命令。通过筛选数据，可以达到以下目的：

  • 减少通过网络发送的数据量。

  • 减少在客户端上需要的存储空间。

  • 基于各个客户端需求提供自定义数据分区。

  • 避免或减少冲突（如果客户端要更新数据），因为可以向不同的客户端发送不同的数据分区。（不会出现两个客户端更新相同数据值的情况。）

筛选可以基于单个表，也可以通过使用 JOIN 子句或多条 SELECT 语句来参考多个表。通过在多个表上进行筛选，用户可以下载数据分区，例如销售人员可以只下载与其客户以及所有这些客户的订单有关的数据。在多个表中进行筛选的能力提供了很大的灵活性。但是，应该让筛选器尽可能简单，并且随着表数量的增加不断测试其性能。此外，还应对筛选所基于的列进行索引。

Note重要事项

不要依赖筛选实现安全性。基于客户端或用户 ID 从服务器筛选数据的能力并不是一种安全功能。换句话说，这种方法不能用来防止客户端读取属于其他客户端的数据。这种类型的筛选仅适用于数据分区和减少下载到客户端数据库的数据量。

Sync Framework 没有提供自动化的分区管理功能。这会造成以下后果：

  • 如果更新了某行，并且变更了用于筛选的列的值，该行不会自动从其分区包括该行的客户端上删除。假定某个应用程序根据邮编将客户数据下载给某位销售人员。如果客户将其办公室搬迁到一个新的邮编区域，那么该客户的数据不会从原来拥有它的销售人员的数据库中删除。如果需要该功能，可以开发一个机制，使得 Sync Framework 能够在下载该更新的同时执行删除操作。

  • 没有任何机制防止应用程序在客户端上插入客户端分区之外的数据。可以在客户端上添加约束来禁止分区之外的插入和更新。

基于非键列的筛选器

在 SQL Server 变更跟踪和某些自定义跟踪系统中，只为已删除的行保留主键。如果某一筛选器仅基于该主键，则您为 SelectIncrementalDeletesCommand 属性指定的查询可以标识行的正确子集并将它们下载到客户端。如果筛选器基于主键外的列，则查询将失败，因为它引用对于已删除的行不再存在的列。为了解决此问题，请考虑采用以下方法之一：

  • 在主键中包含所有筛选列。将附加列置于该键的末尾，以便不会影响该键的选择性。

  • 只筛选插入和更新。多余的删除将下载到客户端，但将忽略它们。

  • 在服务器上执行逻辑删除。使用 ON DELETE 触发器更新将该行标记为已删除或已存档的列，而非删除该行。然后将变更作为更新发送到客户端。

  • 使用可用于筛选数据的附加值重载 SQL Server 变更跟踪中的上下文列 (SYS_CHANGE_CONTEXT)。就性能而言这可能是最佳选择，但可能复杂程度最高，因为必须对该列进行分析。

示例

本主题中的示例代码演示如何筛选 Sync Framework 示例数据库中的 Customer、OrderHeader 和 OrderDetail 表的数据。Customer 表经过筛选，以便只下载 SalesPerson 列值为 Brenda Diaz 的行。然后，筛选会延伸到其他两个表。示例演示如何通过使用 SqlSyncAdapterBuilder 和手动创建同步命令来筛选数据。有关同步命令的概述，请参见如何指定快照同步、下载同步、上载同步和双向同步。

使用 SqlSyncAdapterBuilder

本节介绍在使用 SqlSyncAdapterBuilder 创建命令时用于筛选的 API。本节提供的代码示例指出了 API 的要点，然后提供了一个完整的代码示例。

API 的要点

下面的代码示例创建一个在针对所有三个表的筛选子句中使用的筛选参数。

SqlParameter filterParameter = new SqlParameter("@SalesPerson", SqlDbType.NVarChar);

Dim filterParameter As New SqlParameter("@SalesPerson", SqlDbType.NVarChar)

下面的代码示例指定 Customer 表中要下载的列。

string[] customerDataColumns = new string[3];
customerDataColumns[0] = "CustomerId";
customerDataColumns[1] = "CustomerName";
customerDataColumns[2] = "CustomerType";
customerBuilder.DataColumns.AddRange(customerDataColumns);
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns);

Dim customerDataColumns(2) As String
customerDataColumns(0) = "CustomerId"
customerDataColumns(1) = "CustomerName"
customerDataColumns(2) = "CustomerType"
customerBuilder.DataColumns.AddRange(customerDataColumns)
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns)

下面的代码示例指定 Customer 表中要下载的行。可以对 SalesPerson 的值进行硬编码。但是，更常见的是使用一个可以变更值的参数，如示例中所示。使用来自第一个代码示例的参数。

string customerFilterClause = "SalesPerson=@SalesPerson";
customerBuilder.FilterClause = customerFilterClause;
customerBuilder.FilterParameters.Add(filterParameter);
customerBuilder.TombstoneFilterClause = customerFilterClause;
customerBuilder.TombstoneFilterParameters.Add(filterParameter);

Dim customerFilterClause As String = "SalesPerson=@SalesPerson"
With customerBuilder
    .FilterClause = customerFilterClause
    .FilterParameters.Add(filterParameter)
    .TombstoneFilterClause = customerFilterClause
    .TombstoneFilterParameters.Add(filterParameter)
End With

下面的代码示例将筛选器从 Customer 表延伸到 OrderHeader 表。在本例中，在筛选子句中使用了一个 SELECT 语句。对于手动命令，使用了一个 JOIN 子句，因为此子句对于如何指定命令提供了更大的控制能力。

string orderHeaderFilterClause =
    "CustomerId IN (SELECT CustomerId FROM Sales.Customer " +
                        "WHERE SalesPerson=@SalesPerson)";
orderHeaderBuilder.FilterClause = orderHeaderFilterClause;   
orderHeaderBuilder.FilterParameters.Add(filterParameter);
orderHeaderBuilder.TombstoneFilterClause = orderHeaderFilterClause;
orderHeaderBuilder.TombstoneFilterParameters.Add(filterParameter);

Dim orderHeaderFilterClause As String = _
    "CustomerId IN (SELECT CustomerId FROM Sales.Customer " _
        & "WHERE SalesPerson=@SalesPerson)"
With orderHeaderBuilder
    .FilterClause = orderHeaderFilterClause
    .FilterParameters.Add(filterParameter)
    .TombstoneFilterClause = orderHeaderFilterClause
    .TombstoneFilterParameters.Add(filterParameter)
End With

以下代码示例在从 SyncAgent 派生的类中指定 @SalesPerson 参数的值。在应用程序中，此值可能来自登录 ID 或其他用户输入。

this.Configuration.SyncParameters.Add(
    new SyncParameter("@SalesPerson", "Brenda Diaz"));

Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))

完整的代码示例

下面的完整代码示例包括了上面介绍的代码示例以及用于执行同步的其他代码。示例需要 Utility 类，可通过用于数据库提供程序帮助主题的 Utility 类获得该类。在运行示例时，请注意由 SyncStatistics 返回的信息：在初始同步和后续同步中均下载了行的子集。

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

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

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

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

            //Initial synchronization. Instantiate the SyncAgent
            //and call Synchronize.
            SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
            SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "initial");

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

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

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

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

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

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

            //Create two SyncGroups so that changes to OrderHeader
            //and OrderDetail are made in one transaction. Depending on
            //application requirements, you might include Customer
            //in the same group.
            SyncGroup customerSyncGroup = new SyncGroup("Customer");
            SyncGroup orderSyncGroup = new SyncGroup("Order");

            //Add each table: specify a synchronization direction of
            //DownloadOnly.
            SyncTable customerSyncTable = new SyncTable("Customer");
            customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            customerSyncTable.SyncGroup = customerSyncGroup;
            this.Configuration.SyncTables.Add(customerSyncTable);
            
            SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
            orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            orderHeaderSyncTable.SyncGroup = orderSyncGroup;
            this.Configuration.SyncTables.Add(orderHeaderSyncTable);           

            SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
            orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            orderDetailSyncTable.SyncGroup = orderSyncGroup;
            this.Configuration.SyncTables.Add(orderDetailSyncTable);

            //Specify a value for the @SalesPerson parameter that is added
            //in the server synchronization provider. This value would
            //typically be provided by a user in the application, but we
            //have hardcoded it here for convenience.
            this.Configuration.SyncParameters.Add(
                new SyncParameter("@SalesPerson", "Brenda Diaz"));
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Server.DbServerSyncProvider.
    public class SampleServerSyncProvider : DbServerSyncProvider
    {
        public SampleServerSyncProvider()
        {
            //Create a connection to the sample server database.
            Utility util = new Utility();
            SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
            this.Connection = serverConn;

            //Create a command to retrieve a new anchor value from
            //the server. In this case, we use a timestamp value
            //that is retrieved and stored in the client database.
            //During each synchronization, the new anchor value and
            //the last anchor value from the previous synchronization
            //are used: the set of changes between these upper and
            //lower bounds is synchronized.
            //
            //SyncSession.SyncNewReceivedAnchor is a string constant; 
            //you could also use @sync_new_received_anchor directly in 
            //your queries.
            SqlCommand selectNewAnchorCommand = new SqlCommand();
            string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
            selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
            selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
            selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
            selectNewAnchorCommand.Connection = serverConn;
            this.SelectNewAnchorCommand = selectNewAnchorCommand;

            //Create a filter parameter that will be used in the filter clause for
            //all three tables.
            SqlParameter filterParameter = new SqlParameter("@SalesPerson", SqlDbType.NVarChar);

            //Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
            //  * Specify the base table and tombstone table names.
            //  * Specify the columns that are used to track when
            //    changes are made.
            //  * Specify download-only synchronization.
            //  * Specify if you want only certain columns at the client.
            //  * Specify filter clauses for the base tables and tombstone
            //    tables.
            //  * Call ToSyncAdapter to create the SyncAdapter.
            //  * Specify a name for the SyncAdapter that matches the
            //    the name that is specified for the corresponding SyncTable.
            //    Do not include the schema names (Sales in this case).

            //Customer table.
            SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);

            customerBuilder.TableName = "Sales.Customer";
            customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
            customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
            customerBuilder.CreationTrackingColumn = "InsertTimestamp";
            customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
            customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
            
            //Specify the columns that you want at the client. If you
            //want all columns, this code is not required. In this
            //case, we filter out SalesPerson.
            string[] customerDataColumns = new string[3];
            customerDataColumns[0] = "CustomerId";
            customerDataColumns[1] = "CustomerName";
            customerDataColumns[2] = "CustomerType";
            customerBuilder.DataColumns.AddRange(customerDataColumns);
            customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns);

            //Specify a filter clause, which is an SQL WHERE clause
            //without the WHERE keyword. Use the parameter that is 
            //created above. The value for the parameter is specified 
            //in the SyncAgent Configuration object.
            string customerFilterClause = "SalesPerson=@SalesPerson";
            customerBuilder.FilterClause = customerFilterClause;
            customerBuilder.FilterParameters.Add(filterParameter);
            customerBuilder.TombstoneFilterClause = customerFilterClause;
            customerBuilder.TombstoneFilterParameters.Add(filterParameter);
 
            SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
            customerSyncAdapter.TableName = "Customer";
            this.SyncAdapters.Add(customerSyncAdapter);


            //OrderHeader table.
            SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);

            orderHeaderBuilder.TableName = "Sales.OrderHeader";
            orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
            orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly;
            orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
            orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
            orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";
            //Filter properties: extend the filter to the OrderHeader table.
            string orderHeaderFilterClause =
                "CustomerId IN (SELECT CustomerId FROM Sales.Customer " +
                                    "WHERE SalesPerson=@SalesPerson)";
            orderHeaderBuilder.FilterClause = orderHeaderFilterClause;   
            orderHeaderBuilder.FilterParameters.Add(filterParameter);
            orderHeaderBuilder.TombstoneFilterClause = orderHeaderFilterClause;
            orderHeaderBuilder.TombstoneFilterParameters.Add(filterParameter);

            SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
            orderHeaderSyncAdapter.TableName = "OrderHeader";
            this.SyncAdapters.Add(orderHeaderSyncAdapter);


            //OrderDetail table.
            SqlSyncAdapterBuilder orderDetailBuilder = new SqlSyncAdapterBuilder(serverConn);

            orderDetailBuilder.TableName = "Sales.OrderDetail";
            orderDetailBuilder.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone";
            orderDetailBuilder.SyncDirection = SyncDirection.DownloadOnly;
            orderDetailBuilder.CreationTrackingColumn = "InsertTimestamp";
            orderDetailBuilder.UpdateTrackingColumn = "UpdateTimestamp";
            orderDetailBuilder.DeletionTrackingColumn = "DeleteTimestamp";
            //Filter properties: extend the filter to the OrderDetail table.
            string orderDetailFilterClause =
                "OrderId IN (SELECT OrderId FROM Sales.OrderHeader " +
                                "WHERE CustomerId IN " +
                                    "(SELECT CustomerId FROM Sales.Customer " +
                                        "WHERE SalesPerson=@SalesPerson))";
            orderDetailBuilder.FilterClause = orderDetailFilterClause;      
            orderDetailBuilder.FilterParameters.Add(filterParameter);
            orderDetailBuilder.TombstoneFilterClause = orderDetailFilterClause;
            orderDetailBuilder.TombstoneFilterParameters.Add(filterParameter);

            SyncAdapter orderDetailSyncAdapter = orderDetailBuilder.ToSyncAdapter();
            orderDetailSyncAdapter.TableName = "OrderDetail";
            this.SyncAdapters.Add(orderDetailSyncAdapter);            
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
    //You can just instantiate the provider directly and associate it
    //with the SyncAgent, but you could use this class to handle client 
    //provider events and other client-side processing.
    public class SampleClientSyncProvider : SqlCeClientSyncProvider
    {

        public SampleClientSyncProvider()
        {
            //Specify a connection string for the sample client database.
            Utility util = new Utility();
            this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
         }
    }

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

            Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
            Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);            
            Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
            Console.WriteLine(String.Empty);
        }
    }
}

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

Class Program

    Shared Sub Main(ByVal args() As String)

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

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

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

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

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

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

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

    End Sub 'Main
End Class 'Program

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

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

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

        'Create two SyncGroups so that changes to OrderHeader
        'and OrderDetail are made in one transaction. Depending on
        'application requirements, you might include Customer
        'in the same group.
        Dim customerSyncGroup As New SyncGroup("Customer")
        Dim orderSyncGroup As New SyncGroup("Order")

        'Add each table: specify a synchronization direction of
        'DownloadOnly.
        Dim customerSyncTable As New SyncTable("Customer")
        customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
        customerSyncTable.SyncGroup = customerSyncGroup
        Me.Configuration.SyncTables.Add(customerSyncTable)

        Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
        orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
        orderHeaderSyncTable.SyncGroup = orderSyncGroup
        Me.Configuration.SyncTables.Add(orderHeaderSyncTable)

        Dim orderDetailSyncTable As New SyncTable("OrderDetail")
        orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly
        orderDetailSyncTable.SyncGroup = orderSyncGroup
        Me.Configuration.SyncTables.Add(orderDetailSyncTable)

        'Specify a value for the @SalesPerson parameter that is added
        'in the server synchronization provider. This value would
        'typically be provided by a user in the application, but we
        'have hardcoded it here for convenience.
        Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))

    End Sub 'New
End Class 'SampleSyncAgent 

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

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

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

        'Create a filter parameter that will be used in the filter clause for
        'all three tables.
        Dim filterParameter As New SqlParameter("@SalesPerson", SqlDbType.NVarChar)

        'Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
        '  * Specify the base table and tombstone table names.
        '  * Specify the columns that are used to track when
        '    changes are made.
        '  * Specify download-only synchronization.
        '  * Specify if you want only certain columns at the client.
        '  * Specify filter clauses for the base tables and tombstone
        '    tables.
        '  * Call ToSyncAdapter to create the SyncAdapter.
        '  * Specify a name for the SyncAdapter that matches the
        '    the name that is specified for the corresponding SyncTable.
        '    Do not include the schema names (Sales in this case).

        'Customer table.
        Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
        With customerBuilder
            .TableName = "Sales.Customer"
            .TombstoneTableName = customerBuilder.TableName + "_Tombstone"
            .SyncDirection = SyncDirection.DownloadOnly
            .CreationTrackingColumn = "InsertTimestamp"
            .UpdateTrackingColumn = "UpdateTimestamp"
            .DeletionTrackingColumn = "DeleteTimestamp"
        End With

        'Specify the columns that you want at the client. If you
        'want all columns, this code is not required. In this
        'case, we filter out SalesPerson.
        Dim customerDataColumns(2) As String
        customerDataColumns(0) = "CustomerId"
        customerDataColumns(1) = "CustomerName"
        customerDataColumns(2) = "CustomerType"
        customerBuilder.DataColumns.AddRange(customerDataColumns)
        customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns)

        'Specify a filter clause, which is an SQL WHERE clause
        'without the WHERE keyword. Use the parameter that is 
        'created above. The value for the parameter is specified 
        'in the SyncAgent Configuration object.
        Dim customerFilterClause As String = "SalesPerson=@SalesPerson"
        With customerBuilder
            .FilterClause = customerFilterClause
            .FilterParameters.Add(filterParameter)
            .TombstoneFilterClause = customerFilterClause
            .TombstoneFilterParameters.Add(filterParameter)
        End With

        Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
        customerSyncAdapter.TableName = "Customer"
        Me.SyncAdapters.Add(customerSyncAdapter)


        'OrderHeader table.
        Dim orderHeaderBuilder As New SqlSyncAdapterBuilder(serverConn)
        With orderHeaderBuilder
            .TableName = "Sales.OrderHeader"
            .TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone"
            .SyncDirection = SyncDirection.DownloadOnly
            .CreationTrackingColumn = "InsertTimestamp"
            .UpdateTrackingColumn = "UpdateTimestamp"
            .DeletionTrackingColumn = "DeleteTimestamp"
        End With

        'Filter properties: extend the filter to the OrderHeader table.
        Dim orderHeaderFilterClause As String = _
            "CustomerId IN (SELECT CustomerId FROM Sales.Customer " _
                & "WHERE SalesPerson=@SalesPerson)"
        With orderHeaderBuilder
            .FilterClause = orderHeaderFilterClause
            .FilterParameters.Add(filterParameter)
            .TombstoneFilterClause = orderHeaderFilterClause
            .TombstoneFilterParameters.Add(filterParameter)
        End With

        Dim orderHeaderSyncAdapter As SyncAdapter = orderHeaderBuilder.ToSyncAdapter()
        orderHeaderSyncAdapter.TableName = "OrderHeader"
        Me.SyncAdapters.Add(orderHeaderSyncAdapter)


        'OrderDetail table.
        Dim orderDetailBuilder As New SqlSyncAdapterBuilder(serverConn)
        With orderDetailBuilder
            .TableName = "Sales.OrderDetail"
            .TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone"
            .SyncDirection = SyncDirection.DownloadOnly
            .CreationTrackingColumn = "InsertTimestamp"
            .UpdateTrackingColumn = "UpdateTimestamp"
            .DeletionTrackingColumn = "DeleteTimestamp"

            'Filter properties: extend the filter to the OrderDetail table.
            Dim orderDetailFilterClause As String = _
                "OrderId IN (SELECT OrderId FROM Sales.OrderHeader " _
                    & "WHERE CustomerId IN " _
                        & "(SELECT CustomerId FROM Sales.Customer " _
                            & "WHERE SalesPerson=@SalesPerson))"
            .FilterClause = orderDetailFilterClause
            .FilterParameters.Add(filterParameter)
            .TombstoneFilterClause = orderDetailFilterClause
            .TombstoneFilterParameters.Add(filterParameter)
        End With
        Dim orderDetailSyncAdapter As SyncAdapter = orderDetailBuilder.ToSyncAdapter()
        orderDetailSyncAdapter.TableName = "OrderDetail"
        Me.SyncAdapters.Add(orderDetailSyncAdapter)

    End Sub 'New
End Class 'SampleServerSyncProvider

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


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

    End Sub 'New
End Class 'SampleClientSyncProvider

'Handle the statistics returned by the SyncAgent.

Public Class SampleStats

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

        Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
        Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
        Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
        Console.WriteLine(String.Empty)

    End Sub 'DisplayStats
End Class 'SampleStats

使用手动命令

本节介绍手动创建命令时在筛选中使用的 API。本节提供的代码示例指出了 API 的要点，然后提供了一个完整的代码示例。

API 的要点

下面的代码示例指定 Customer 表中要下载的已插入列和行。可以对 SalesPerson 的值进行硬编码。但是，更常见的是使用一个可以变更值的参数，如示例中所示。示例将筛选参数与下载增量插入所需的其他参数一起传递。

SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
    "SELECT CustomerId, CustomerName, CustomerType " +
    "FROM Sales.Customer " +
    "WHERE SalesPerson = @SalesPerson " +
    "AND (InsertTimestamp > @sync_last_received_anchor " +
    "AND InsertTimestamp <= @sync_new_received_anchor " +
    "AND InsertId <> @sync_client_id)";
customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;

Dim customerIncrInserts As New SqlCommand()
With customerIncrInserts
    .CommandText = _
        "SELECT CustomerId, CustomerName, CustomerType " _
      & "FROM Sales.Customer " _
      & "WHERE SalesPerson = @SalesPerson " _
      & "AND (InsertTimestamp > @sync_last_received_anchor " _
      & "AND InsertTimestamp <= @sync_new_received_anchor " _
      & "AND InsertId <> @sync_client_id)"
    .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
    .Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts

下面的代码示例将筛选器从 Customer 表延伸到 OrderHeader 表。在本例中，使用了一个 JOIN 子句来定义两个表之间的关系。

SqlCommand orderHeaderIncrInserts = new SqlCommand();
orderHeaderIncrInserts.CommandText =
    "SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
    "FROM Sales.OrderHeader oh " +
    "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
    "WHERE c.SalesPerson = @SalesPerson " +
    "AND (oh.InsertTimestamp > @sync_last_received_anchor " +
    "AND oh.InsertTimestamp <= @sync_new_received_anchor " +
    "AND oh.InsertId <> @sync_client_id)";
orderHeaderIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrInserts.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts;

Dim orderHeaderIncrInserts As New SqlCommand()
With orderHeaderIncrInserts
    .CommandText = _
        "SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
      & "FROM Sales.OrderHeader oh " _
      & "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
      & "WHERE c.SalesPerson = @SalesPerson " _
      & "AND (oh.InsertTimestamp > @sync_last_received_anchor " _
      & "AND oh.InsertTimestamp <= @sync_new_received_anchor " _
      & "AND oh.InsertId <> @sync_client_id)"
    .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
    .Connection = serverConn
End With
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts

以下代码示例在从 SyncAgent 派生的类中指定 @SalesPerson 参数的值。在应用程序中，此值可能来自登录 ID 或其他用户输入。

this.Configuration.SyncParameters.Add(
    new SyncParameter("@SalesPerson", "Brenda Diaz"));

this.Configuration.SyncParameters.Add(
    new SyncParameter("@SalesPerson", "Brenda Diaz"));

完整的代码示例

下面的完整代码示例包括了上面介绍的代码示例以及用于执行同步的其他代码。示例需要 Utility 类，可通过用于数据库提供程序帮助主题的 Utility 类获得该类。在运行示例时，请注意由 SyncStatistics 返回的信息：在初始同步和后续同步中均下载了行的子集。

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

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

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

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

            //Initial synchronization. Instantiate the SyncAgent
            //and call Synchronize.
            SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
            SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "initial");

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

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

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

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

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

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

            //Create two SyncGroups, so that changes to OrderHeader
            //and OrderDetail are made in one transaction. Depending on
            //application requirements, you might include Customer
            //in the same group.
            SyncGroup customerSyncGroup = new SyncGroup("Customer");
            SyncGroup orderSyncGroup = new SyncGroup("Order");

            //Add each table: specify a synchronization direction of
            //DownloadOnly.
            SyncTable customerSyncTable = new SyncTable("Customer");
            customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            customerSyncTable.SyncGroup = customerSyncGroup;
            this.Configuration.SyncTables.Add(customerSyncTable);
            
            SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
            orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            orderHeaderSyncTable.SyncGroup = orderSyncGroup;
            this.Configuration.SyncTables.Add(orderHeaderSyncTable);
            
            SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
            orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            orderDetailSyncTable.SyncGroup = orderSyncGroup;
            this.Configuration.SyncTables.Add(orderDetailSyncTable);

            //Specify a value for the @SalesPerson parameter that is added
            //in the server synchronization provider. This value would
            //typically be provided by a user in the application, but we
            //have hardcoded it here for convenience.
            this.Configuration.SyncParameters.Add(
                new SyncParameter("@SalesPerson", "Brenda Diaz"));
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Server.DbServerSyncProvider
    public class SampleServerSyncProvider : DbServerSyncProvider
    {
        public SampleServerSyncProvider()
        {
            //Create a connection to the sample server database.
            Utility util = new Utility();
            SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
            this.Connection = serverConn;

            //Create a command to retrieve a new anchor value from
            //the server. In this case, we use a timestamp value
            //that is retrieved and stored in the client database.
            //During each synchronization, the new anchor value and
            //the last anchor value from the previous synchronization
            //are used: the set of changes between these upper and
            //lower bounds is synchronized.
            //
            //SyncSession.SyncNewReceivedAnchor is a string constant; 
            //you could also use @sync_new_received_anchor directly in 
            //your queries.
            SqlCommand selectNewAnchorCommand = new SqlCommand();
            string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
            selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
            selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
            selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
            selectNewAnchorCommand.Connection = serverConn;
            this.SelectNewAnchorCommand = selectNewAnchorCommand;

            //Create a SyncAdapter for each table, and then define
            //the commands to synchronize changes:
            //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
            //  and SelectIncrementalDeletesCommand are used to select changes
            //  from the server that the client provider then applies to the client.
            //* Specify if you want only certain columns at the client by 
            //  using the SELECT statement in the command.
            //* Filter rows by using the WHERE clause in the command. 
            //  In this case, we filter out SalesPerson.

            //
            //Customer table
            //

            //Create the SyncAdapter
            SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");

            //Select inserts from the server
            SqlCommand customerIncrInserts = new SqlCommand();
            customerIncrInserts.CommandText =
                "SELECT CustomerId, CustomerName, CustomerType " +
                "FROM Sales.Customer " +
                "WHERE SalesPerson = @SalesPerson " +
                "AND (InsertTimestamp > @sync_last_received_anchor " +
                "AND InsertTimestamp <= @sync_new_received_anchor " +
                "AND InsertId <> @sync_client_id)";
            customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            customerIncrInserts.Connection = serverConn;
            customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;

            //Select updates from the server
            SqlCommand customerIncrUpdates = new SqlCommand();
            customerIncrUpdates.CommandText =
                "SELECT CustomerId, CustomerName, CustomerType " +
                "FROM Sales.Customer " +
                "WHERE SalesPerson = @SalesPerson " +
                "AND (UpdateTimestamp > @sync_last_received_anchor " +
                "AND UpdateTimestamp <= @sync_new_received_anchor " +
                "AND UpdateId <> @sync_client_id " +
                "AND NOT (InsertTimestamp > @sync_last_received_anchor " +
                "AND InsertId <> @sync_client_id))";
            customerIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); 
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            customerIncrUpdates.Connection = serverConn;
            customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;

            //Select deletes from the server
            SqlCommand customerIncrDeletes = new SqlCommand();
            customerIncrDeletes.CommandText =
                "SELECT CustomerId, CustomerName, CustomerType " +
                "FROM Sales.Customer_Tombstone " +
                "WHERE SalesPerson = @SalesPerson " +
                "AND (@sync_initialized = 1 " +
                "AND DeleteTimestamp > @sync_last_received_anchor " +
                "AND DeleteTimestamp <= @sync_new_received_anchor " +
                "AND DeleteId <> @sync_client_id)";
            customerIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); 
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            customerIncrDeletes.Connection = serverConn;
            customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes;

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

            
            //
            //OrderHeader table
            //

            //Create the SyncAdapter
            SyncAdapter orderHeaderSyncAdapter = new SyncAdapter("OrderHeader");

            //Select inserts from the server
            SqlCommand orderHeaderIncrInserts = new SqlCommand();
            orderHeaderIncrInserts.CommandText =
                "SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
                "FROM Sales.OrderHeader oh " +
                "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
                "WHERE c.SalesPerson = @SalesPerson " +
                "AND (oh.InsertTimestamp > @sync_last_received_anchor " +
                "AND oh.InsertTimestamp <= @sync_new_received_anchor " +
                "AND oh.InsertId <> @sync_client_id)";
            orderHeaderIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            orderHeaderIncrInserts.Connection = serverConn;
            orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts;

            //Select updates from the server
            SqlCommand orderHeaderIncrUpdates = new SqlCommand();
            orderHeaderIncrUpdates.CommandText =
                "SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
                "FROM Sales.OrderHeader oh " +
                "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
                "WHERE c.SalesPerson = @SalesPerson " +
                "AND (oh.UpdateTimestamp > @sync_last_received_anchor " +
                "AND oh.UpdateTimestamp <= @sync_new_received_anchor " +
                "AND oh.UpdateId <> @sync_client_id " +
                "AND NOT (oh.InsertTimestamp > @sync_last_received_anchor " +
                "AND oh.InsertId <> @sync_client_id))";
            orderHeaderIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            orderHeaderIncrUpdates.Connection = serverConn;
            orderHeaderSyncAdapter.SelectIncrementalUpdatesCommand = orderHeaderIncrUpdates;

            //Select deletes from the server
            SqlCommand orderHeaderIncrDeletes = new SqlCommand();
            orderHeaderIncrDeletes.CommandText =
                "SELECT oht.OrderId, oht.CustomerId, oht.OrderDate, oht.OrderStatus " +
                "FROM Sales.OrderHeader_Tombstone oht " +
                "JOIN Sales.Customer c ON oht.CustomerId = c.CustomerId " +
                "WHERE c.SalesPerson = @SalesPerson " +
                "AND (@sync_initialized = 1 " +
                "AND oht.DeleteTimestamp > @sync_last_received_anchor " +
                "AND oht.DeleteTimestamp <= @sync_new_received_anchor " +
                "AND oht.DeleteId <> @sync_client_id)";
            orderHeaderIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
            orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            orderHeaderIncrDeletes.Connection = serverConn;
            orderHeaderSyncAdapter.SelectIncrementalDeletesCommand = orderHeaderIncrDeletes;

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

            
            //
            //OrderDetail table
            //

            //Create the SyncAdapter
            SyncAdapter orderDetailSyncAdapter = new SyncAdapter("OrderDetail");

            //Select inserts from the server
            SqlCommand orderDetailIncrInserts = new SqlCommand();
            orderDetailIncrInserts.CommandText =
                "SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " +
                "FROM Sales.OrderDetail od " +
                "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " +
                "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
                "WHERE SalesPerson = @SalesPerson " +
                "AND (od.InsertTimestamp > @sync_last_received_anchor " +
                "AND od.InsertTimestamp <= @sync_new_received_anchor " +
                "AND od.InsertId <> @sync_client_id)";
            orderDetailIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            orderDetailIncrInserts.Connection = serverConn;
            orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts;

            //Select updates from the server
            SqlCommand orderDetailIncrUpdates = new SqlCommand();
            orderDetailIncrUpdates.CommandText =
                "SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " +
                "FROM Sales.OrderDetail od " +
                "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " +
                "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
                "WHERE SalesPerson = @SalesPerson " +
                "AND (od.UpdateTimestamp > @sync_last_received_anchor " +
                "AND od.UpdateTimestamp <= @sync_new_received_anchor " +
                "AND od.UpdateId <> @sync_client_id " +
                "AND NOT (od.InsertTimestamp > @sync_last_received_anchor " +
                "AND od.InsertId <> @sync_client_id))";
            orderDetailIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            orderDetailIncrUpdates.Connection = serverConn;
            orderDetailSyncAdapter.SelectIncrementalUpdatesCommand = orderDetailIncrUpdates;

            //Select deletes from the server
            SqlCommand orderDetailIncrDeletes = new SqlCommand();
            orderDetailIncrDeletes.CommandText =
                "SELECT odt.OrderDetailId, odt.OrderId, odt.Product, odt.Quantity " +
                "FROM Sales.OrderDetail_Tombstone odt " +
                "JOIN Sales.OrderHeader oh ON odt.OrderId = oh.OrderId " +
                "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
                "WHERE SalesPerson = @SalesPerson " +
                "AND (@sync_initialized = 1 " +
                "AND odt.DeleteTimestamp > @sync_last_received_anchor " +
                "AND odt.DeleteTimestamp <= @sync_new_received_anchor " +
                "AND odt.DeleteId <> @sync_client_id)";
            orderDetailIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
            orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            orderDetailIncrDeletes.Connection = serverConn;
            orderDetailSyncAdapter.SelectIncrementalDeletesCommand = orderDetailIncrDeletes;

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

    //Create a class that is derived from 
    //Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
    //You can just instantiate the provider directly and associate it
    //with the SyncAgent, but you could use this class to handle client 
    //provider events and other client-side processing.
    public class SampleClientSyncProvider : SqlCeClientSyncProvider
    {

        public SampleClientSyncProvider()
        {
            //Specify a connection string for the sample client database.
            Utility util = new Utility();
            this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
        }
    }

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

            Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
            Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
            Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
            Console.WriteLine(String.Empty);
        }
    }
}

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

Class Program

    Shared Sub Main(ByVal args() As String)

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

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

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

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

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

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

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

    End Sub 'Main
End Class 'Program

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

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

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

        'Create two SyncGroups so that changes to OrderHeader
        'and OrderDetail are made in one transaction. Depending on
        'application requirements, you might include Customer
        'in the same group.
        Dim customerSyncGroup As New SyncGroup("Customer")
        Dim orderSyncGroup As New SyncGroup("Order")

        'Add each table: specify a synchronization direction of
        'DownloadOnly.
        Dim customerSyncTable As New SyncTable("Customer")
        customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
        customerSyncTable.SyncGroup = customerSyncGroup
        Me.Configuration.SyncTables.Add(customerSyncTable)

        Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
        orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
        orderHeaderSyncTable.SyncGroup = orderSyncGroup
        Me.Configuration.SyncTables.Add(orderHeaderSyncTable)

        Dim orderDetailSyncTable As New SyncTable("OrderDetail")
        orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly
        orderDetailSyncTable.SyncGroup = orderSyncGroup
        Me.Configuration.SyncTables.Add(orderDetailSyncTable)

        'Specify a value for the @SalesPerson parameter that is added
        'in the server synchronization provider. This value would
        'typically be provided by a user in the application, but we
        'have hardcoded it here for convenience.
        Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))

    End Sub 'New
End Class 'SampleSyncAgent 

'Create a class that is derived from 
'Microsoft.Synchronization.Server.DbServerSyncProvider.

Public Class SampleServerSyncProvider
    Inherits DbServerSyncProvider

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

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

        'Create a SyncAdapter for each table, and then define
        'the commands to synchronize changes:
        '* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
        '  and SelectIncrementalDeletesCommand are used to select changes
        '  from the server that the client provider then applies to the client.
        '* Specify if you want only certain columns at the client by 
        '  using the SELECT statement in the command.
        '* Filter rows by using the WHERE clause in the command. 
        '  In this case, we filter out SalesPerson.
        '
        'Customer table.
        '
        'Create the SyncAdapter.
        Dim customerSyncAdapter As New SyncAdapter("Customer")

        'Select inserts from the server.
        Dim customerIncrInserts As New SqlCommand()
        With customerIncrInserts
            .CommandText = _
                "SELECT CustomerId, CustomerName, CustomerType " _
              & "FROM Sales.Customer " _
              & "WHERE SalesPerson = @SalesPerson " _
              & "AND (InsertTimestamp > @sync_last_received_anchor " _
              & "AND InsertTimestamp <= @sync_new_received_anchor " _
              & "AND InsertId <> @sync_client_id)"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts

        'Select updates from the server.
        Dim customerIncrUpdates As New SqlCommand()
        With customerIncrUpdates
            .CommandText = _
                "SELECT CustomerId, CustomerName, CustomerType " _
              & "FROM Sales.Customer " _
              & "WHERE SalesPerson = @SalesPerson " _
              & "AND (UpdateTimestamp > @sync_last_received_anchor " _
              & "AND UpdateTimestamp <= @sync_new_received_anchor " _
              & "AND UpdateId <> @sync_client_id " _
              & "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
              & "AND InsertId <> @sync_client_id))"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates

        'Select deletes from the server.
        Dim customerIncrDeletes As New SqlCommand()
        With customerIncrDeletes
            .CommandText = _
                "SELECT CustomerId, CustomerName, CustomerType " _
              & "FROM Sales.Customer_Tombstone " _
              & "WHERE SalesPerson = @SalesPerson " _
              & "AND (@sync_initialized = 1 " _
              & "AND DeleteTimestamp > @sync_last_received_anchor " _
              & "AND DeleteTimestamp <= @sync_new_received_anchor " _
              & "AND DeleteId <> @sync_client_id)"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes

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


        '
        'OrderHeader table.
        '
        'Create the SyncAdapter.
        Dim orderHeaderSyncAdapter As New SyncAdapter("OrderHeader")

        'Select inserts from the server.
        Dim orderHeaderIncrInserts As New SqlCommand()
        With orderHeaderIncrInserts
            .CommandText = _
                "SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
              & "FROM Sales.OrderHeader oh " _
              & "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
              & "WHERE c.SalesPerson = @SalesPerson " _
              & "AND (oh.InsertTimestamp > @sync_last_received_anchor " _
              & "AND oh.InsertTimestamp <= @sync_new_received_anchor " _
              & "AND oh.InsertId <> @sync_client_id)"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts

        'Select updates from the server.
        Dim orderHeaderIncrUpdates As New SqlCommand()
        With orderHeaderIncrUpdates
            .CommandText = _
                "SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
              & "FROM Sales.OrderHeader oh " _
              & "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
              & "WHERE c.SalesPerson = @SalesPerson " _
              & "AND (oh.UpdateTimestamp > @sync_last_received_anchor " _
              & "AND oh.UpdateTimestamp <= @sync_new_received_anchor " _
              & "AND oh.UpdateId <> @sync_client_id " _
              & "AND NOT (oh.InsertTimestamp > @sync_last_received_anchor " _
              & "AND oh.InsertId <> @sync_client_id))"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        orderHeaderSyncAdapter.SelectIncrementalUpdatesCommand = orderHeaderIncrUpdates

        'Select deletes from the server.
        Dim orderHeaderIncrDeletes As New SqlCommand()
        With orderHeaderIncrDeletes
            .CommandText = _
                "SELECT oht.OrderId, oht.CustomerId, oht.OrderDate, oht.OrderStatus " _
              & "FROM Sales.OrderHeader_Tombstone oht " _
              & "JOIN Sales.Customer c ON oht.CustomerId = c.CustomerId " _
              & "WHERE c.SalesPerson = @SalesPerson " _
              & "AND (@sync_initialized = 1 " _
              & "AND oht.DeleteTimestamp > @sync_last_received_anchor " _
              & "AND oht.DeleteTimestamp <= @sync_new_received_anchor " _
              & "AND oht.DeleteId <> @sync_client_id)"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        orderHeaderSyncAdapter.SelectIncrementalDeletesCommand = orderHeaderIncrDeletes

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


        '
        'OrderDetail table.
        '
        'Create the SyncAdapter.
        Dim orderDetailSyncAdapter As New SyncAdapter("OrderDetail")

        'Select inserts from the server.
        Dim orderDetailIncrInserts As New SqlCommand()
        With orderDetailIncrInserts
            .CommandText = _
                "SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " _
              & "FROM Sales.OrderDetail od " _
              & "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " _
              & "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
              & "WHERE SalesPerson = @SalesPerson " _
              & "AND (od.InsertTimestamp > @sync_last_received_anchor " _
              & "AND od.InsertTimestamp <= @sync_new_received_anchor " _
              & "AND od.InsertId <> @sync_client_id)"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts

        'Select updates from the server.
        Dim orderDetailIncrUpdates As New SqlCommand()
        With orderDetailIncrUpdates
            .CommandText = _
                "SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " _
              & "FROM Sales.OrderDetail od " _
              & "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " _
              & "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
              & "WHERE SalesPerson = @SalesPerson " _
              & "AND (od.UpdateTimestamp > @sync_last_received_anchor " _
              & "AND od.UpdateTimestamp <= @sync_new_received_anchor " _
              & "AND od.UpdateId <> @sync_client_id " _
              & "AND NOT (od.InsertTimestamp > @sync_last_received_anchor " _
              & "AND od.InsertId <> @sync_client_id))"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        orderDetailSyncAdapter.SelectIncrementalUpdatesCommand = orderDetailIncrUpdates

        'Select deletes from the server.
        Dim orderDetailIncrDeletes As New SqlCommand()
        With orderDetailIncrDeletes
            .CommandText = _
                "SELECT odt.OrderDetailId, odt.OrderId, odt.Product, odt.Quantity " _
              & "FROM Sales.OrderDetail_Tombstone odt " _
              & "JOIN Sales.OrderHeader oh ON odt.OrderId = oh.OrderId " _
              & "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
              & "WHERE SalesPerson = @SalesPerson " _
              & "AND (@sync_initialized = 1 " _
              & "AND odt.DeleteTimestamp > @sync_last_received_anchor " _
              & "AND odt.DeleteTimestamp <= @sync_new_received_anchor " _
              & "AND odt.DeleteId <> @sync_client_id)"
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        orderDetailSyncAdapter.SelectIncrementalDeletesCommand = orderDetailIncrDeletes

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

    End Sub 'New 
End Class 'SampleServerSyncProvider

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


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

    End Sub 'New
End Class 'SampleClientSyncProvider

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

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

        Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
        Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
        Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
        Console.WriteLine(String.Empty)

    End Sub 'DisplayStats
End Class 'SampleStats

请参阅

概念

对常见客户端与服务器同步任务进行编程
有助于开发应用程序的工具