クライアントとサーバー間で双方向でデータの増分変更を交換する方法

[アーティクル]
10/17/2009

このトピックでは、サーバーデータベースと SQL Server Compact クライアントデータベース間で双方向同期を実行し、データ変更を交換する方法について説明します。このトピックの例では、次に示す Sync Framework の型とイベントを中心に説明します。

DbServerSyncProvider
SyncAdapter
SqlCeClientSyncProvider
SyncDirection
SyncSession
SyncStatistics
SyncTable
SyncAgent
CreatingSchema および CreatingSchemaEventArgs
SchemaCreated および SchemaCreatedEventArgs

サンプルコードを実行する方法については、「一般的なクライアントとサーバーの同期タスクのプログラミング」の「操作方法に関するトピックのサンプルアプリケーション」を参照してください。

例

このトピックのコード例では、Sync Framework サンプルデータベースの Customer テーブルに対して双方向同期を構成する方法を示しています。同期の方向を指定する際に使用するプロパティの概要については、「スナップショット、ダウンロード、アップロード、および双方向の各同期を指定する方法」を参照してください。

双方向同期では、サーバーデータベースとクライアントデータベースでデータの増分変更を追跡して適用する必要があります。クライアントデータベースは、Sync Framework で処理されます。ただし、サーバーデータベースを構成し、変更をサーバーデータベースで選択して適用するコマンドを指定する必要があります。このコード例には、こうしたコマンドが含まれています。サーバーデータベースの構成方法の詳細については、「サーバーデータベースの変更の追跡」を参照してください。

API の主要部分

このセクションでは、双方向同期で使用される API の主要部分に注目したコード例を示します。次のコード例では、同期の方向と、クライアントにテーブルを作成する方法を指定します。

SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.Bidirectional;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);

Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.Bidirectional
customerSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)

次のコード例では、サーバーから新しいアンカー値を取得するコマンドを指定しています。この場合、タイムスタンプ値が取得され、クライアントデータベースに格納されます。同期のたびに、新しいアンカー値と、前回の同期の最後のアンカー値が使用されます。その結果、この範囲内の一連の変更が同期されます。サーバーデータベースから変更を選択するコマンドで使用されるアンカーパラメーターが表示されます。

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;

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

次のコード例では、サーバーから増分更新を選択してクライアントに適用するコマンドを指定します。これは、同期する列を含む SELECT ステートメントです。追跡中の列と、SelectNewAnchorCommand によって取得されるアンカー値を使用する WHERE 句もあります。WHERE 句には、UpdateId <> @sync_client_id も含まれています。このパラメーターにより、アップロードされた変更がその同期中にすぐにクライアントにダウンロードされることはなくなります。変更をクライアントにエコーバックする必要があるアプリケーションでは、このステートメントを省略してください。ダウンロードするデータをフィルター選択する方法の詳細については、「行および列をフィルター選択する方法」を参照してください。

SqlCommand customerIncrUpdates = new SqlCommand();
customerIncrUpdates.CommandText =
    "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
    "FROM Sales.Customer " +
    "WHERE (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("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrUpdates.Connection = serverConn;
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;

Dim customerIncrUpdates As New SqlCommand()
With customerIncrUpdates
    .CommandText = _
        "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
      & "FROM Sales.Customer " _
      & "WHERE (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("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
    .Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates

次のコード例では、クライアントからサーバーに更新を適用するコマンドを指定します。これは、更新する列が含まれた UPDATE ステートメントです。アンカー値とクライアント ID を再度使用する WHERE 句があります。WHERE 句には、@sync_force_write = 1 も含まれています。このパラメーターを使用すると、競合する更新が強制的に適用されます。@sync_force_write およびその他のセッション変数の詳細については、「セッション変数を使用する方法」を参照してください。

SqlCommand customerUpdates = new SqlCommand();
customerUpdates.CommandText =
    "UPDATE Sales.Customer SET " + 
    "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " + 
    "CustomerType = @CustomerType, " + 
    "UpdateId = @sync_client_id " +           
    "WHERE (CustomerId = @CustomerId) " + 
    "AND (@sync_force_write = 1 " + 
    "OR (UpdateTimestamp <= @sync_last_received_anchor " +
    "OR UpdateId = @sync_client_id)) " + 
    "SET @sync_row_count = @@rowcount";
customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
customerUpdates.Connection = serverConn;
customerSyncAdapter.UpdateCommand = customerUpdates;

Dim customerUpdates As New SqlCommand()
With customerUpdates
    .CommandText = _
        "UPDATE Sales.Customer SET " _
      & "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " _
      & "CustomerType = @CustomerType, " _
      & "UpdateId = @sync_client_id " _
      & "WHERE (CustomerId = @CustomerId) " _
      & "AND (@sync_force_write = 1 " _
      & "OR (UpdateTimestamp <= @sync_last_received_anchor " _
      & "OR UpdateId = @sync_client_id)) " _
      & "SET @sync_row_count = @@rowcount"
    .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
    .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
    .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
    .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
    .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
    .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
    .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
    .Connection = serverConn
End With
customerSyncAdapter.UpdateCommand = customerUpdates

完全なコード例

次の完全なコード例には、既に説明したコード例に加え、同期を実行するためのコードが含まれています。また、SyncStatistics を使用して同期セッションに関する情報を表示する方法と、CreatingSchema イベントおよび SchemaCreated イベントを使用して CustomerId 列の既定値を追加する方法にも注意してください。この例では、「データベースプロバイダーの Utility クラスに関するトピック」で説明されている Utility クラスが必要です。

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
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 and client.
            Utility.MakeDataChangesOnServer("Customer");
            Utility.MakeDataChangesOnClient("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 a Customer SyncGroup. This is not required
            //for the single table we are synchronizing; it is typically
            //used so that changes to multiple related tables are 
            //synchronized at the same time.
            SyncGroup customerSyncGroup = new SyncGroup("Customer");

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

    //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 Customer 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.
            //* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
            //  to the server the changes that the client provider has selected
            //  from the client.

            //Create the SyncAdapter.
            SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");            
            
            //Select inserts from the server.
            SqlCommand customerIncrInserts = new SqlCommand();
            customerIncrInserts.CommandText =  
                "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
                "FROM Sales.Customer " +
                "WHERE (InsertTimestamp > @sync_last_received_anchor " +
                "AND InsertTimestamp <= @sync_new_received_anchor " +
                "AND InsertId <> @sync_client_id)";
            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;

            //Apply inserts to the server.
            SqlCommand customerInserts = new SqlCommand();
            customerInserts.CommandText =
                "INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson, CustomerType, InsertId, UpdateId) " +
                "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType, @sync_client_id, @sync_client_id) " +
                "SET @sync_row_count = @@rowcount";
            customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
            customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
            customerInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
            customerInserts.Connection = serverConn;
            customerSyncAdapter.InsertCommand = customerInserts;
                                    
            
            //Select updates from the server.
            SqlCommand customerIncrUpdates = new SqlCommand();
            customerIncrUpdates.CommandText =
                "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
                "FROM Sales.Customer " +
                "WHERE (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("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
            customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            customerIncrUpdates.Connection = serverConn;
            customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;
            
            //Apply updates to the server.
            SqlCommand customerUpdates = new SqlCommand();
            customerUpdates.CommandText =
                "UPDATE Sales.Customer SET " + 
                "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " + 
                "CustomerType = @CustomerType, " + 
                "UpdateId = @sync_client_id " +           
                "WHERE (CustomerId = @CustomerId) " + 
                "AND (@sync_force_write = 1 " + 
                "OR (UpdateTimestamp <= @sync_last_received_anchor " +
                "OR UpdateId = @sync_client_id)) " + 
                "SET @sync_row_count = @@rowcount";
            customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
            customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
            customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
            customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
            customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
            customerUpdates.Connection = serverConn;
            customerSyncAdapter.UpdateCommand = customerUpdates;


            //Select deletes from the server.
            SqlCommand customerIncrDeletes = new SqlCommand();
            customerIncrDeletes.CommandText =
                "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
                "FROM Sales.Customer_Tombstone " +
                "WHERE (@sync_initialized = 1 " +
                "AND DeleteTimestamp > @sync_last_received_anchor " +
                "AND DeleteTimestamp <= @sync_new_received_anchor " +
                "AND DeleteId <> @sync_client_id)";
            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;

            //Apply deletes to the server.            
            SqlCommand customerDeletes = new SqlCommand();
            customerDeletes.CommandText =
                "DELETE FROM Sales.Customer " +
                "WHERE (CustomerId = @CustomerId) " + 
                "AND (@sync_force_write = 1 " + 
                "OR (UpdateTimestamp <= @sync_last_received_anchor " + 
                "OR UpdateId = @sync_client_id)) " + 
                "SET @sync_row_count = @@rowcount " + 
                "IF (@sync_row_count > 0)  BEGIN " + 
                "UPDATE Sales.Customer_Tombstone " + 
                "SET DeleteId = @sync_client_id " +
                "WHERE (CustomerId = @CustomerId) " + 
                "END";
            customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);       
            customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
            customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
            customerDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
            customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);           
            customerDeletes.Connection = serverConn;
            customerSyncAdapter.DeleteCommand = customerDeletes;     


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

        }
    }

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

            //We use the CreatingSchema event to change the schema
            //by using the API. We use the SchemaCreated event to 
            //change the schema by using SQL.
            this.CreatingSchema +=new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
            this.SchemaCreated +=new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
        }

        private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
        {
            //Set the RowGuid property because it is not copied
            //to the client by default. This is also a good time
            //to specify literal defaults with .Columns[ColName].DefaultValue;
            //but we will specify defaults like NEWID() by calling
            //ALTER TABLE after the table is created.
            Console.Write("Creating schema for " + e.Table.TableName + " | ");                        
            e.Schema.Tables["Customer"].Columns["CustomerId"].RowGuid = true;
        }

        private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
        {        
            //Call ALTER TABLE on the client. This must be done
            //over the same connection and within the same
            //transaction that Sync Framework uses
            //to create the schema on the client.
            Utility util = new Utility();
            Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName);
            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 Uploaded: " + syncStatistics.TotalChangesUploaded);
            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 and client.
        Utility.MakeDataChangesOnServer("Customer")
        Utility.MakeDataChangesOnClient("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 a Customer SyncGroup. This is not required
        'for the single table we are synchronizing; it is typically
        'used so that changes to multiple related tables are 
        'synchronized at the same time.
        Dim customerSyncGroup As New SyncGroup("Customer")

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

    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 Customer 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.
        '* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
        '  to the server the changes that the client provider has selected
        '  from the client.

        '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, SalesPerson, CustomerType " _
              & "FROM Sales.Customer " _
              & "WHERE (InsertTimestamp > @sync_last_received_anchor " _
              & "AND InsertTimestamp <= @sync_new_received_anchor " _
              & "AND InsertId <> @sync_client_id)"
            .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

        'Apply inserts to the server.
        Dim customerInserts As New SqlCommand()
        With customerInserts
            .CommandText = _
                "INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson, CustomerType, InsertId, UpdateId) " _
              & "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType, @sync_client_id, @sync_client_id) " _
              & "SET @sync_row_count = @@rowcount"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Connection = serverConn
        End With
        customerSyncAdapter.InsertCommand = customerInserts


        'Select updates from the server.
        Dim customerIncrUpdates As New SqlCommand()
        With customerIncrUpdates
            .CommandText = _
                "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
              & "FROM Sales.Customer " _
              & "WHERE (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("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Connection = serverConn
        End With
        customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates

        'Apply updates to the server.
        Dim customerUpdates As New SqlCommand()
        With customerUpdates
            .CommandText = _
                "UPDATE Sales.Customer SET " _
              & "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " _
              & "CustomerType = @CustomerType, " _
              & "UpdateId = @sync_client_id " _
              & "WHERE (CustomerId = @CustomerId) " _
              & "AND (@sync_force_write = 1 " _
              & "OR (UpdateTimestamp <= @sync_last_received_anchor " _
              & "OR UpdateId = @sync_client_id)) " _
              & "SET @sync_row_count = @@rowcount"
            .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Connection = serverConn
        End With
        customerSyncAdapter.UpdateCommand = customerUpdates

        'Select deletes from the server.
        Dim customerIncrDeletes As New SqlCommand()
        With customerIncrDeletes
            .CommandText = _
                "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
              & "FROM Sales.Customer_Tombstone " _
              & "WHERE (@sync_initialized = 1 " _
              & "AND DeleteTimestamp > @sync_last_received_anchor " _
              & "AND DeleteTimestamp <= @sync_new_received_anchor " _
              & "AND DeleteId <> @sync_client_id)"
            .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

        'Apply deletes to the server.            
        Dim customerDeletes As New SqlCommand()
        With customerDeletes
            .CommandText = _
                "DELETE FROM Sales.Customer " _
              & "WHERE (CustomerId = @CustomerId) " _
              & "AND (@sync_force_write = 1 " _
              & "OR (UpdateTimestamp <= @sync_last_received_anchor " _
              & "OR UpdateId = @sync_client_id)) " _
              & "SET @sync_row_count = @@rowcount " _
              & "IF (@sync_row_count > 0)  BEGIN " _
              & "UPDATE Sales.Customer_Tombstone " _
              & "SET DeleteId = @sync_client_id " _
              & "WHERE (CustomerId = @CustomerId) " _
              & "END"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
            .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
            .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
            .Connection = serverConn
        End With
        customerSyncAdapter.DeleteCommand = customerDeletes


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

    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

        'We use the CreatingSchema event to change the schema
        'by using the API. We use the SchemaCreated event to 
        'change the schema by using SQL.
        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)
        'Set the RowGuid property because it is not copied
        'to the client by default. This is also a good time
        'to specify literal defaults with .Columns[ColName].DefaultValue;
        'but we will specify defaults like NEWID() by calling
        'ALTER TABLE after the table is created.
        Console.Write("Creating schema for " + e.Table.TableName + " | ")
        e.Schema.Tables("Customer").Columns("CustomerId").RowGuid = True

    End Sub 'SampleClientSyncProvider_CreatingSchema


    Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
        'Call ALTER TABLE on the client. This must be done
        'over the same connection and within the same
        'transaction that Sync Framework uses
        'to create the schema on the client.
        Dim util As New Utility()
        Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName)
        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 Uploaded: " & syncStatistics.TotalChangesUploaded)
        Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
        Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
        Console.WriteLine(String.Empty)

    End Sub 'DisplayStats
End Class 'SampleStats

参照

概念

クライアントとサーバーの同期のアーキテクチャとクラス
 スナップショット、ダウンロード、アップロード、および双方向の各同期を指定する方法

次の方法で共有

クライアントとサーバー間で双方向でデータの増分変更を交換する方法

例

API の主要部分

完全なコード例

参照

概念

その他のリソース