Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

How to: Filter Rows and Columns

This topic describes how to filter rows and columns in tables that are synchronized. The examples in this topic focus on the following Sync Framework types:

For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Client and Server Synchronization Tasks.

Frequently, the client database requires only a subset of the data that is available at the server. Sync Framework lets you specify which rows and columns a client requires, whether you construct synchronization commands manually or by using the SqlSyncAdapterBuilder. By filtering data, you can accomplish the following:

  • Reduce the amount of data sent over the network.

  • Reduce the amount of storage space that is required at the client.

  • Provide custom data partitions based on individual client requirements.

  • Avoid or reduce conflicts if clients are updating data, because different data partitions can be sent to different clients. (No two clients will be updating the same data values.)

Filters can be based on a single table, or they can reference multiple tables by using JOIN clauses or multiple SELECT statements. Basing filters on multiple tables enables a user to download a partition of data, such as a salesperson that requires only the data for her customers and all the customers' orders. Filtering across multiple tables provides flexibility. However, you should keep filters as simple as possible and test performance as the number of tables increases. You should also index the columns on which filters are based.

Important noteImportant

Do not rely on filtering for security. The ability to filter data from the server based on a client or user ID is not a security feature. In other words, this approach cannot be used to prevent one client from reading data that belongs to another client. This type of filtering is useful only for partitioning data and reducing the amount of data that is brought down to the client database.

Sync Framework does not provide automatic partition management. This has the following consequences:

  • If you update a row and change the value of a column that was used in filtering, the row is not automatically deleted from those clients whose partition included that row. Consider an application that downloads customer data based on postal code to a salesperson. If a customer moves its offices into a new postal code area, data for that customer is not removed from the salesperson who originally had it. If that functionality is required, you could develop a system that enables Sync Framework to download that update as a delete.

  • There is no mechanism to prevent an application from inserting data at the client that is outside of that client's partition. You could add constraints at the client to disallow out-of-partition inserts and updates.

Filters Based on Non-Key Columns

In SQL Server change tracking and in some custom tracking systems, only the primary key is retained for deleted rows. If a filter is based only on the primary key, the query that you specify for the SelectIncrementalDeletesCommand property can identify the correct subset of rows and download them to the client. If the filter is based on columns outside the primary key, the query fails because it references columns that no longer exist for deleted rows. To address this issue, consider using one of the following approaches:

  • Include all filtering columns in the primary key. Put the additional columns at the end of the key so that you do not affect the selectivity of the key.

  • Only filter inserts and updates. Superfluous deletes will be downloaded to the client, but they will be ignored.

  • Perform logical deletes on the server. Instead of deleting the row, use an ON DELETE trigger to update a column that flags the row as deleted or archived. The change is then sent to the client as an update.

  • Overload the context column in SQL Server change tracking (SYS_CHANGE_CONTEXT) with additional values that can be used to filter data. This is probably the best option in terms of performance, but it might be the most complex because you have to parse this column.

The example code in this topic shows you how to filter data for the Customer, OrderHeader, and OrderDetail tables from the Sync Framework sample database. The Customer table is filtered so that only rows that have a value of Brenda Diaz for the SalesPerson column are downloaded. The filter is then extended to the other two tables. The example shows how to filter data by using the SqlSyncAdapterBuilder and by creating synchronization commands manually. For an overview of synchronization commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.

Using the SqlSyncAdapterBuilder

This section describes the API that is used in filtering if you create commands by using SqlSyncAdapterBuilder. This section provides code examples that point out the key parts of the API, and then provides a complete code example.

Key Parts of the API

The following code example creates a filter parameter that is used in the filter clause for all three tables.

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

The following code example specifies which columns to download for the Customer table.

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)

The following code example specifies which rows to download for the Customer table. You can hardcode a value for SalesPerson. However, it is more common to use a parameter that has a value that can change, as shown in the example. The parameter from the first code example is used.

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

The following code example extends the filter from the Customer table to the OrderHeader table. In this case, a SELECT statement is used in the filter clause. For the manual commands, a JOIN clause is used because it provides more control over how to specify commands.

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

The following code example specifies a value for the @SalesPerson parameter in a class that derives from SyncAgent. In an application, this value might come from a login ID or other user input.

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

Complete Code Example

The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics. When you run the sample, pay attention to the information returned by the SyncStatistics: a subset of rows is downloaded in both the initial and later synchronizations.

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

Using Manual Commands

This section describes the API that is used in filtering if you create the commands manually. This section provides code examples that point out the key parts of the API, and then provides a complete code example.

Key Parts of the API

The following code example specifies which inserted columns and rows to download for the Customer table. You can hardcode a value for SalesPerson. However, it is more common to use a parameter that has a value that can change, as shown in the example. The example passes the filter parameter together with the other parameters that are required to download incremental inserts.

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

The following code example extends the filter from the Customer table to the OrderHeader table. In this case, a JOIN clause is used to define the relationship between the two tables.

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

The following code example specifies a value for the @SalesPerson parameter in a class that derives from SyncAgent. In an application, this value might come from a login ID or other user input.

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

Complete Code Example

The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics. When you run the sample, pay attention to the information returned by the SyncStatistics: a subset of rows is downloaded in both the initial and later synchronizations.

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.