Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

How to: Create Views of Data on a Client

This topic shows you how to use Sync Framework to combine two or more server tables into a single table in the client database. The examples in this topic focus on the following Sync Framework types and events:

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

The following code examples show how to synchronize the Customer and CustomerContact tables in the Sync Framework sample database. During the initial synchronization, data from the two tables is selected by using the incremental inserts command, downloaded to the client, and then inserted into the CustomerInfo table. During the subsequent synchronization, qualifying changes are downloaded to the client.

Key Parts of the API

This section provides code examples that point out the key parts of the API to use when you are combining tables. The following code examples specify SyncTable and SyncAdapter objects that both use the name CustomerInfo. Specifying the name in both places enables the SyncAgent and providers to translate selects from the Customer and CustomerContact tables into inserts for the CustomerInfo table.

SyncTable customerInfoSyncTable = new SyncTable("CustomerInfo");
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerInfoSyncTable);
SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo");
Dim customerInfoSyncTable As New SyncTable("CustomerInfo")
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerInfoSyncTable)
Dim customerInfoSyncAdapter As New SyncAdapter("CustomerInfo")

The following code example includes the query for the SelectIncrementalInsertsCommand property. The query uses a join to ensure that only customers who have contact information are downloaded to the client during the initial synchronization. For subsequent synchronizations, inserts to the CustomerContact table are downloaded; inserts to the Customer table are downloaded only if a row for the customer is also inserted into the CustomerContact table. Notice that the select list does not include all columns. As with all the queries that are used in synchronization, the logic of each query depends on the requirements of the application. For example, you could download all customers, even if they do not have contact information.

SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand();
customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text;
customerInfoIncrementalInsertsCommand.CommandText =
    "SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
    "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
    "c.CustomerId = cc.CustomerId " +
    "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " +
    "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " +
    "(cc.InsertTimestamp > @sync_last_received_anchor " +
    "AND cc.InsertTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand;
Dim customerInfoIncrementalInsertsCommand As New SqlCommand()
With customerInfoIncrementalInsertsCommand
    .CommandType = CommandType.Text
    .CommandText = _
        "SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
      & "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
      & "c.CustomerId = cc.CustomerId " _
      & "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " _
      & "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " _
      & "(cc.InsertTimestamp > @sync_last_received_anchor " _
      & "AND cc.InsertTimestamp <= @sync_new_received_anchor))"
    .Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
    .Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
    .Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand

The following code example includes the query for the SelectIncrementalDeletesCommand property. The query uses two SELECT statements and a UNION to select deletes for the Customer and CustomerContact tables. The first SELECT statement joins with the CustomerContact table to retrieve the PhoneType column. This column is part of the composite key for the CustomerInfo table. The logic is as follows:

  • If a customer was deleted, all the rows for that customer are deleted at the client.

  • If contact information was deleted, only that row is deleted at the client.

  • If a customer row and contact information for that customer were both deleted, at least one extra delete is downloaded. This will not cause any errors when changes are applied at the client database.

SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand();
customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text;
customerInfoIncrementalDeletesCommand.CommandText =
    "SELECT c.CustomerId, cc.PhoneType " +
    "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " +
    "c.CustomerId = cc.CustomerId " +
    "WHERE (@sync_initialized = 1 " +
    "AND (DeleteTimestamp > @sync_last_received_anchor " +
    "AND DeleteTimestamp <= @sync_new_received_anchor)) " +
    "UNION " +
    "SELECT CustomerId, PhoneType " +
    "FROM Sales.CustomerContact_Tombstone " +
    "WHERE (@sync_initialized = 1 " +
    "AND (DeleteTimestamp > @sync_last_received_anchor " +
    "AND DeleteTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand;
Dim customerInfoIncrementalDeletesCommand As New SqlCommand()
With customerInfoIncrementalDeletesCommand
    .CommandType = CommandType.Text
    .CommandText = _
        "SELECT c.CustomerId, cc.PhoneType " _
      & "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " _
      & "c.CustomerId = cc.CustomerId " _
      & "WHERE (@sync_initialized = 1 " _
      & "AND (DeleteTimestamp > @sync_last_received_anchor " _
      & "AND DeleteTimestamp <= @sync_new_received_anchor)) " _
      & "UNION " _
      & "SELECT CustomerId, PhoneType " _
      & "FROM Sales.CustomerContact_Tombstone " _
      & "WHERE (@sync_initialized = 1 " _
      & "AND (DeleteTimestamp > @sync_last_received_anchor " _
      & "AND DeleteTimestamp <= @sync_new_received_anchor))"
    .Parameters.Add("@sync_initialized", SqlDbType.Bit)
    .Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
    .Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
    .Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand

The following code example creates a composite primary key for the CustomerInfo table. This key matches the key for the CustomerContact table. Sync Framework can infer the schema from the tables at the server, but the key must be specified in this case. You can also manually create the schema, as described in How to: Initialize the Client Database and Work with Table Schema.

string[] customerInfoPrimaryKey = new string[2];
customerInfoPrimaryKey[0] = "CustomerId";
customerInfoPrimaryKey[1] = "PhoneType";
e.Schema.Tables["CustomerInfo"].PrimaryKey = customerInfoPrimaryKey;
Dim customerInfoPrimaryKey(1) As String
customerInfoPrimaryKey(0) = "CustomerId"
customerInfoPrimaryKey(1) = "PhoneType"
e.Schema.Tables("CustomerInfo").PrimaryKey = customerInfoPrimaryKey

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. Notice that the insert into the Customer table in the Utility class is not downloaded on the subsequent synchronization because there is no corresponding row in the CustomerContact table.

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");
            Utility.MakeDataChangesOnServer("CustomerContact");

            //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();

            //Add the Customer table: specify a synchronization direction of
            //DownloadOnly, and that an existing table should be dropped.
            SyncTable customerInfoSyncTable = new SyncTable("CustomerInfo");
            customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            this.Configuration.SyncTables.Add(customerInfoSyncTable);

        }
    }

    //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 the CustomerInfo table. The CustomerInfo 
            //table on the client is a combination of the Customer and CustomerContact
            //tables on the server. This table is download-only, as specified in 
            //SampleSyncAgent.
            SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo");

            //Specify synchronization commands. The CustomerInfo table 
            //is download-only, so we do not define commands to apply changes to 
            //the server. Each command joins the base tables or tombstone tables
            //to select the appropriate incremental changes. For this application,
            //the logic is as follows:
            //* Select all inserts for customers that have contact information.
            //  This results in more than one row for a customer if that customer 
            //  has more than one phone number.
            //* Select all updates for customer and contact information that has 
            //  already been downloaded.
            //* Select all deletes for customer and contact information that has 
            //  already been downloaded. If a customer has been deleted, delete
            //  all of the rows for that customer. If a phone number has been
            //  deleted, delete only that row.

            //Select inserts.
            SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand();
            customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text;
            customerInfoIncrementalInsertsCommand.CommandText =
                "SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
                "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
                "c.CustomerId = cc.CustomerId " +
                "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " +
                "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " +
                "(cc.InsertTimestamp > @sync_last_received_anchor " +
                "AND cc.InsertTimestamp <= @sync_new_received_anchor))";
            customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
            customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
            customerInfoIncrementalInsertsCommand.Connection = serverConn;
            customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand;

            //Select updates.
            SqlCommand customerInfoIncrementalUpdatesCommand = new SqlCommand();
            customerInfoIncrementalUpdatesCommand.CommandType = CommandType.Text;
            customerInfoIncrementalUpdatesCommand.CommandText =
                "SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
                "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
                "c.CustomerId = cc.CustomerId " +
                "WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " +
                "AND c.UpdateTimestamp <= @sync_new_received_anchor " +
                "AND c.InsertTimestamp <= @sync_last_received_anchor) " +
                "OR (cc.UpdateTimestamp > @sync_last_received_anchor " +
                "AND cc.UpdateTimestamp <= @sync_new_received_anchor " +
                "AND cc.InsertTimestamp <= @sync_last_received_anchor))";
            customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
            customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
            customerInfoIncrementalUpdatesCommand.Connection = serverConn;
            customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand;

            //Select deletes.
            SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand();
            customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text;
            customerInfoIncrementalDeletesCommand.CommandText =
                "SELECT c.CustomerId, cc.PhoneType " +
                "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " +
                "c.CustomerId = cc.CustomerId " +
                "WHERE (@sync_initialized = 1 " +
                "AND (DeleteTimestamp > @sync_last_received_anchor " +
                "AND DeleteTimestamp <= @sync_new_received_anchor)) " +
                "UNION " +
                "SELECT CustomerId, PhoneType " +
                "FROM Sales.CustomerContact_Tombstone " +
                "WHERE (@sync_initialized = 1 " +
                "AND (DeleteTimestamp > @sync_last_received_anchor " +
                "AND DeleteTimestamp <= @sync_new_received_anchor))";
            customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit);
            customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
            customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
            customerInfoIncrementalDeletesCommand.Connection = serverConn;
            customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand;

            //Add the SyncAdapter to the provider.
            this.SyncAdapters.Add(customerInfoSyncAdapter);

        }
    }

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

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

            //Handle the two schema-related events.
            this.CreatingSchema += new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
            this.SchemaCreated += new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
        }

        private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
        {
            Console.Write("Creating schema for " + e.Table.TableName + " | ");

            //Create a compostite primary key for the CustomerInfo table.
            string[] customerInfoPrimaryKey = new string[2];
            customerInfoPrimaryKey[0] = "CustomerId";
            customerInfoPrimaryKey[1] = "PhoneType";
            e.Schema.Tables["CustomerInfo"].PrimaryKey = customerInfoPrimaryKey;
        }

        private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
        {
            Console.WriteLine("Schema created for " + e.Table.TableName);
        }
    }

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

            Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
            Console.WriteLine("Total Changes 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")
        Utility.MakeDataChangesOnServer("CustomerContact")

        '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()

        'Add the Customer table: specify a synchronization direction of
        'DownloadOnly, and that an existing table should be dropped.
        Dim customerInfoSyncTable As New SyncTable("CustomerInfo")
        customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly
        Me.Configuration.SyncTables.Add(customerInfoSyncTable)

    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 the CustomerInfo table. The CustomerInfo 
        'table on the client is a combination of the Customer and CustomerContact
        'tables on the server. This table is download-only, as specified in 
        'SampleSyncAgent.
        Dim customerInfoSyncAdapter As New SyncAdapter("CustomerInfo")

        'Specify synchronization commands. The CustomerInfo table 
        'is download-only, so we do not define commands to apply changes to 
        'the server. Each command joins the base tables or tombstone tables
        'to select the appropriate incremental changes. For this application,
        'the logic is as follows:
        '* Select all inserts for customers that have contact information.
        '  This results in more than one row for a customer if that customer 
        '  has more than one phone number.
        '* Select all updates for customer and contact information that has 
        '  already been downloaded.
        '* Select all deletes for customer and contact information that has 
        '  already been downloaded. If a customer has been deleted, delete
        '  all of the rows for that customer. If a phone number has been
        '  deleted, delete only that row.
        'Select inserts.
        Dim customerInfoIncrementalInsertsCommand As New SqlCommand()
        With customerInfoIncrementalInsertsCommand
            .CommandType = CommandType.Text
            .CommandText = _
                "SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
              & "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
              & "c.CustomerId = cc.CustomerId " _
              & "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " _
              & "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " _
              & "(cc.InsertTimestamp > @sync_last_received_anchor " _
              & "AND cc.InsertTimestamp <= @sync_new_received_anchor))"
            .Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
            .Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
            .Connection = serverConn
        End With
        customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand

        'Select updates.
        Dim customerInfoIncrementalUpdatesCommand As New SqlCommand()
        With customerInfoIncrementalUpdatesCommand
            .CommandType = CommandType.Text
            .CommandText = _
                "SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
              & "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
              & "c.CustomerId = cc.CustomerId " _
              & "WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " _
              & "AND c.UpdateTimestamp <= @sync_new_received_anchor " _
              & "AND c.InsertTimestamp <= @sync_last_received_anchor) " _
              & "OR (cc.UpdateTimestamp > @sync_last_received_anchor " _
              & "AND cc.UpdateTimestamp <= @sync_new_received_anchor " _
              & "AND cc.InsertTimestamp <= @sync_last_received_anchor))"
            .Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
            .Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
            .Connection = serverConn
        End With
        customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand

        'Select deletes.
        Dim customerInfoIncrementalDeletesCommand As New SqlCommand()
        With customerInfoIncrementalDeletesCommand
            .CommandType = CommandType.Text
            .CommandText = _
                "SELECT c.CustomerId, cc.PhoneType " _
              & "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " _
              & "c.CustomerId = cc.CustomerId " _
              & "WHERE (@sync_initialized = 1 " _
              & "AND (DeleteTimestamp > @sync_last_received_anchor " _
              & "AND DeleteTimestamp <= @sync_new_received_anchor)) " _
              & "UNION " _
              & "SELECT CustomerId, PhoneType " _
              & "FROM Sales.CustomerContact_Tombstone " _
              & "WHERE (@sync_initialized = 1 " _
              & "AND (DeleteTimestamp > @sync_last_received_anchor " _
              & "AND DeleteTimestamp <= @sync_new_received_anchor))"
            .Parameters.Add("@sync_initialized", SqlDbType.Bit)
            .Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
            .Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
            .Connection = serverConn
        End With
        customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand

        'Add the SyncAdapter to the provider.
        Me.SyncAdapters.Add(customerInfoSyncAdapter)

    End Sub 'New 
End Class 'SampleServerSyncProvider

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


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

        'Handle the two schema-related events.
        AddHandler Me.CreatingSchema, AddressOf SampleClientSyncProvider_CreatingSchema
        AddHandler Me.SchemaCreated, AddressOf SampleClientSyncProvider_SchemaCreated

    End Sub 'New


    Private Sub SampleClientSyncProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)

        Console.Write("Creating schema for " + e.Table.TableName + " | ")

        'Create a compostite primary key for the CustomerInfo table.
        Dim customerInfoPrimaryKey(1) As String
        customerInfoPrimaryKey(0) = "CustomerId"
        customerInfoPrimaryKey(1) = "PhoneType"
        e.Schema.Tables("CustomerInfo").PrimaryKey = customerInfoPrimaryKey

    End Sub 'SampleClientSyncProvider_CreatingSchema


    Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)

        Console.WriteLine("Schema created for " + e.Table.TableName)

    End Sub 'SampleClientSyncProvider_SchemaCreated
End Class 'SampleClientSyncProvider

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

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

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

    End Sub 'DisplayStats
End Class 'SampleStats
Show:
© 2014 Microsoft. All rights reserved.