データのスナップショットをクライアントにダウンロードする方法

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

このトピックでは、データのスナップショットをサーバーデータベースから SQL Server Compact クライアントデータベースにダウンロードする方法について説明します。このトピックの例では、次に示す Sync Framework の型を中心に説明します。

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

例

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

API の主要部分

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

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

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

次のコード例では、サーバーから行を選択してクライアントに適用するコマンドを指定しています。これは、同期する列を含む SELECT ステートメントです。WHERE 句を追加して行をフィルター選択することもできます。フィルター選択の詳細については、「行および列をフィルター選択する方法」を参照してください。データは同期のたびに完全に更新されるため、ダウンロードのみの同期および双方向同期に必要な追跡列がコマンドに含まれていません。

SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText = 
    "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
    "FROM Sales.Customer";
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
this.SyncAdapters.Add(customerSyncAdapter);

Dim customerSyncAdapter As New SyncAdapter("Customer")
Dim customerIncrInserts As New SqlCommand()
customerIncrInserts.CommandText = _
    "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
  & "FROM Sales.Customer"
customerIncrInserts.Connection = serverConn
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
Me.SyncAdapters.Add(customerSyncAdapter)

完全なコード例

次の完全なコード例には、既に説明したコード例に加え、同期を実行するためのコードが含まれています。また、SyncStatistics を使用して同期セッションに関する情報を表示する方法にも注意してください。この例では、「データベースプロバイダーの 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.
            Utility.MakeDataChangesOnServer("Customer");

            //Subsequent synchronization. There was one insert,
            //one update, and one delete made on the server;
            //therefore, the row count is identical, but the
            //data is different.
            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
            //Snapshot, and that any existing tables should be dropped.
            SyncTable customerSyncTable = new SyncTable("Customer");
            customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            customerSyncTable.SyncDirection = SyncDirection.Snapshot;
            customerSyncTable.SyncGroup = customerSyncGroup;
            this.Configuration.SyncTables.Add(customerSyncTable);

            SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
            orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            orderHeaderSyncTable.SyncDirection = SyncDirection.Snapshot;
            orderHeaderSyncTable.SyncGroup = orderSyncGroup;
            this.Configuration.SyncTables.Add(orderHeaderSyncTable);

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

    //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 SyncAdapter for each table, and then define
            //the command to select rows from the table. With the Snapshot
            //option, you do not download incremental changes. However,
            //you still use the SelectIncrementalInsertsCommand to select
            //the rows to download for each snapshot. The commands include
            //only those columns that you want on the client.

            //Customer table.
            SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");
            SqlCommand customerIncrInserts = new SqlCommand();
            customerIncrInserts.CommandText = 
                "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
                "FROM Sales.Customer";
            customerIncrInserts.Connection = serverConn;
            customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
            this.SyncAdapters.Add(customerSyncAdapter);

            //OrderHeader table.
            SyncAdapter orderHeaderSyncAdapter = new SyncAdapter("OrderHeader");
            SqlCommand orderHeaderIncrInserts = new SqlCommand();
            orderHeaderIncrInserts.CommandText = 
                "SELECT OrderId, CustomerId, OrderDate, OrderStatus " +
                "FROM Sales.OrderHeader";
            orderHeaderIncrInserts.Connection = serverConn;
            orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts;
            this.SyncAdapters.Add(orderHeaderSyncAdapter);
            
            //OrderDetail table.
            SyncAdapter orderDetailSyncAdapter = new SyncAdapter("OrderDetail");
            SqlCommand orderDetailIncrInserts = new SqlCommand();            
            orderDetailIncrInserts.CommandText = 
                "SELECT OrderDetailId, OrderId, Product, Quantity " +
                "FROM Sales.OrderDetail";
            orderDetailIncrInserts.Connection = serverConn;
            orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts;
            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 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")

        'Subsequent synchronization. There was one insert,
        'one update, and one delete made on the server;
        'therefore, the row count is identical, but the
        'data is different.
        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
        'Snapshot, and that any existing tables should be dropped.
        Dim customerSyncTable As New SyncTable("Customer")
        customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        customerSyncTable.SyncDirection = SyncDirection.Snapshot
        customerSyncTable.SyncGroup = customerSyncGroup
        Me.Configuration.SyncTables.Add(customerSyncTable)

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

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

    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 SyncAdapter for each table, and then define
        'the command to select rows from the table. With the Snapshot
        'option, you do not download incremental changes. However,
        'you still use the SelectIncrementalInsertsCommand to select
        'the rows to download for each snapshot. The commands include
        'only those columns that you want on the client.
        'Customer table.
        Dim customerSyncAdapter As New SyncAdapter("Customer")
        Dim customerIncrInserts As New SqlCommand()
        customerIncrInserts.CommandText = _
            "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
          & "FROM Sales.Customer"
        customerIncrInserts.Connection = serverConn
        customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
        Me.SyncAdapters.Add(customerSyncAdapter)

        'OrderHeader table.
        Dim orderHeaderSyncAdapter As New SyncAdapter("OrderHeader")
        Dim orderHeaderIncrInserts As New SqlCommand()
        orderHeaderIncrInserts.CommandText = _
            "SELECT OrderId, CustomerId, OrderDate, OrderStatus " _
          & "FROM Sales.OrderHeader"
        orderHeaderIncrInserts.Connection = serverConn
        orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts
        Me.SyncAdapters.Add(orderHeaderSyncAdapter)

        'OrderDetail table.
        Dim orderDetailSyncAdapter As New SyncAdapter("OrderDetail")
        Dim orderDetailIncrInserts As New SqlCommand()
        orderDetailIncrInserts.CommandText = _
            "SELECT OrderDetailId, OrderId, Product, Quantity " _
          & "FROM Sales.OrderDetail"
        orderDetailIncrInserts.Connection = serverConn
        orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts
        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

参照

概念

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

Share via

データのスナップショットをクライアントにダウンロードする方法

例

API の主要部分

完全なコード例

参照

概念

その他のリソース