はじめに : クライアントとサーバーの同期
このトピックでは、1 つのテーブルから初期データセットをダウンロードした後、一連の増分変更をダウンロードするコンソール アプリケーションについて説明します。このアプリケーションは簡単ですが、Sync Framework のドキュメント全体で、さまざまな形でベースとして使用されるコードです。このアプリケーションで使用されている主なクラスについては、「クライアントとサーバーの同期のアーキテクチャとクラス」で説明されています。
コード例は、目を通して理解することもできますが、アプリケーションを実行して動作を確認すると、より知識を深めることができます。コードを実行する前に、次のコンポーネントがインストールされていることを確認してください。
Sync Framework
このアプリケーションでは、Microsoft.Synchronization.Data.dll、Microsoft.Synchronization.Data.Server.dll、および Microsoft.Synchronization.Data.SqlServerCe.dll への参照が必要です。
SQL Server Compact Service Pack 1
このアプリケーションでは、System.Data.SqlServerCe.dll への参照が必要です。
サーバー データベースとして機能する、SQL Server Compact 以外のバージョンの SQL Server。
このコード例では、接続文字列に
localhostを使用しています。Visual Studio と共にインストールされる SQL Server Express のインスタンスを使用するには、localhostを.\sqlexpressに変更します。リモート サーバーを使用するには、localhostを適切なサーバー名に変更します。Sync Framework サンプル データベース。「データベース プロバイダーのセットアップ スクリプトに関するトピック」の 2 つのスクリプトを実行します。これらのスクリプトを見直して、サーバー データベースで変更追跡がどのように処理されるかを確認することをお勧めします。
このアプリケーションは、次の 6 つのクラスで構成されています。
SampleServerSyncProvider: このクラスは、DbServerSyncProvider から派生し、SyncAdapter を含んでいます。SampleClientSyncProvider: このクラスは SqlCeClientSyncProvider から派生しています。この例では、クライアント データベースへの接続文字列のみが、このクラスに含まれています。SampleStats: このクラスでは、SyncAgentによって返される統計情報を使用します。Program: このクラスは、同期を設定し、Utilityクラスからメソッドを呼び出します。Utility: このクラスでは、接続文字列情報の保持やサーバー データベースへの変更など、同期に直接関係しないすべての機能を処理します。完全なUtilityクラスは、他のトピックで使用されています。この完全なクラスは、「データベース プロバイダーの Utility クラスに関するトピック」から入手できます。
API の主要部分
完全なコード例を参照する前に、このアプリケーションで使用されている API のいくつかの主要なセクションを説明している次の例を確認することをお勧めします。
SyncTable の作成
次のコード例では、Customer テーブルの SyncTable オブジェクトを作成し、同期の方向を指定して、クライアントにテーブルを作成する方法を指定します。この場合、テーブルが既にクライアント データベースに存在すると、最初の同期中にそのテーブルが削除されます。
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
SqlSyncAdapterBuilder の使用
このセクションの各コード例では、Customer テーブルの SyncAdapter を作成します。同期アダプターにより、サーバー同期プロバイダーで、サーバー データベースの操作に必要な特定のコマンドを使用できるようになります。このアプリケーションでは、同期アダプターは SqlSyncAdapterBuilder を使用して作成されます。最初の例では、SqlSyncAdapterBuilder をカスタム変更追跡システムと共に使用する方法を示します。2 番目の例では、SqlSyncAdapterBuilder を (SQL Server 2008 で利用できる) SQL Server の変更追跡システムと共に使用する方法を示します。変更追跡の詳細については、「サーバー データベースの変更の追跡」を参照してください。
ビルダーを使用せずに手動でコマンドを作成する方法については、「データの増分変更をクライアントにダウンロードする方法」を参照してください。
カスタム変更追跡システムの使用
カスタム変更追跡システムを使用するには、SqlSyncAdapterBuilder および SyncAdapter に対して次の情報を指定します。
同期するテーブルと廃棄テーブルの名前。廃棄テーブルは、サーバー データベースにおける削除操作の追跡に使用されます。詳細については、「サーバー データベースの変更の追跡」を参照してください。dbo 以外のスキーマにテーブルがある場合は、スキーマを指定する必要があります。
同期の方向。これにより、
SqlSyncAdapterBuilderによって作成されるコマンドを制御します。コマンドの詳細については、「スナップショット、ダウンロード、アップロード、および双方向の各同期を指定する方法」を参照してください。サーバー データベースで追跡中の列。この列は、新しい変更のみがダウンロード対象となるように、変更時の追跡に使用されます。変更箇所を追跡するための追加の列を含めることができます。詳細については、「カスタム変更追跡システムを使用する方法」を参照してください。
SyncAdapterの名前。これは、SyncTableの名前と一致する必要があります。したがって、スキーマ名は含めないでください。
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";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter(false, false, false, false);
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
Dim customerBuilder As 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"
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
SQL Server の変更追跡の使用
SQL Server の変更追跡を使用するには、SqlSyncAdapterBuilder および SyncAdapter に対して次の情報を指定します。
同期するテーブルの名前。
同期の方向。これにより、
SqlSyncAdapterBuilderによって作成されるコマンドを制御します。コマンドの詳細については、「スナップショット、ダウンロード、アップロード、および双方向の各同期を指定する方法」を参照してください。使用する変更追跡の種類。Sync Framework の既定では、カスタム変更追跡列の指定が想定されます。このコード例では、SQL Server の変更追跡を指定します。
SyncAdapterの名前。これは、SyncTableの名前と一致する必要があります。したがって、スキーマ名は含めないでください。
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
新しいアンカー コマンドの指定
次のコード例では、サーバーから新しいアンカー値を取得するコマンドを指定します。この値は、クライアント データベースに格納され、変更を同期するコマンドによって使用されます。同期のたびに、新しいアンカー値と、前回の同期の最後のアンカー値が使用されます。その結果、この範囲の一連の変更が同期されます。
この場合、MIN_ACTIVE_ROWVERSION は、SQL Server データベースからタイムスタンプ値を返します (MIN_ACTIVE_ROWVERSION は SQL Server 2005 Service Pack 2 で導入されました)。タイムスタンプ値が使用されるのは、SqlSyncAdapterBuilder に指定されている追跡中の列にタイムスタンプ値が含まれるためです。追跡中の列に日付値が含まれている場合は、MIN_ACTIVE_ROWVERSION ではなく GETUTCDATE() などの関数を使用します。アンカーの詳細については、「サーバー データベースの変更の追跡」を参照してください。
SyncSession クラスには、同期コマンドで使用できるいくつかの文字列定数が含まれています。SyncNewReceivedAnchor はこうした定数の 1 つです。また、クエリでは、リテラルの @sync_new_received_anchor を使用することもできます。
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
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand
Synchronize メソッドの呼び出し
次のコード例では、SampleSyncAgent をインスタンス化し、Synchronize メソッドを呼び出します。SampleSyncAgent クラスでは、SampleClientSyncProvider が LocalProvider として指定され、SampleServerSyncProvider が RemoteProvider として指定されます。また、既に説明されている同期テーブルも指定されます。
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
SampleStats クラスでは、SyncAgent によって返される統計情報を使用して、同期セッションに関するフィードバックがユーザーに提供されます。詳細については、「イベントを操作する方法とビジネス ロジックをプログラムする方法」を参照してください。
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
完全なコード例
同期にかかわるコードの主要なセクションを確認できました。ここで、これらのセクションをまとめてコメントを加えた完全なアプリケーションを記載します。このアプリケーションを実行したら、「一般的なクライアントとサーバーの同期タスクのプログラミング」内のトピックを参照することをお勧めします。このトピックのコード例で使用されているのと同じクラスがありますが、より高度な方法で他のテーブル全体に適用されています。
カスタム変更追跡を使用する完全な例
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();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetClientPassword();
Utility.RecreateCompactDatabase();
//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();
//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.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
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 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.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
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";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter(false, false, false, false);
customerSyncAdapter.TableName = "Customer";
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 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);
}
}
public class Utility
{
private static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public static void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public static string ConnStr_SqlCeClientSync
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
//Return the server connection string.
public static string ConnStr_DbServerSync
{
get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public static void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public static void CleanUpServer()
{
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public static void RecreateCompactDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(Utility.ConnStr_SqlCeClientSync))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(Utility.ConnStr_SqlCeClientSync);
sqlCeEngine.CreateDatabase();
}
}
}
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()
'Delete and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetClientPassword()
Utility.RecreateCompactDatabase()
'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()
'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.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
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
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the Customer 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.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
Dim customerBuilder As 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"
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
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 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
Public Class Utility
Private Shared _clientPassword As String
'Get and set the client database password.
Public Shared Property Password() As String
Get
Return _clientPassword
End Get
Set(ByVal value As String)
_clientPassword = value
End Set
End Property
'Have the user enter a password for the client database file.
Public Shared Sub SetClientPassword()
Console.WriteLine("Type a strong password for the client")
Console.WriteLine("database, and then press Enter.")
Utility.Password = Console.ReadLine()
End Sub 'SetClientPassword
'Return the client connection string with the password.
Public Shared ReadOnly Property ConnStr_SqlCeClientSync() As String
Get
Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
End Get
End Property
'Return the server connection string.
Public Shared ReadOnly Property ConnStr_DbServerSync() As String
Get
Return "Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"
End Get
End Property
'Make server changes that are synchronized on the second
'synchronization.
Public Shared Sub MakeDataChangesOnServer()
Dim rowCount As Integer = 0
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandText = _
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
& "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
& "UPDATE Sales.Customer " _
& "SET SalesPerson = 'James Bailey' " _
& "WHERE CustomerName = 'Tandem Bicycle Store' " _
& "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
serverConn.Open()
rowCount = sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)
End Sub 'MakeDataChangesOnServer
'Revert changes that were made during synchronization.
Public Shared Sub CleanUpServer()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = "usp_InsertSampleData"
serverConn.Open()
sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
End Sub 'CleanUpServer
'Delete the client database.
Public Shared Sub RecreateCompactDatabase()
Dim clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)
Try
If File.Exists(clientConn.Database) Then
File.Delete(clientConn.Database)
End If
Finally
clientConn.Dispose()
End Try
Dim sqlCeEngine As New SqlCeEngine(Utility.ConnStr_SqlCeClientSync)
sqlCeEngine.CreateDatabase()
End Sub 'RecreateClientDatabase
End Class 'Utility
SQL Server の変更追跡を使用する完全な例
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();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetClientPassword();
Utility.RecreateCompactDatabase();
//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();
//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.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
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 + " = change_tracking_current_version()";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the Customer table by using
//the SqlSyncAdapterBuilder:
// * Specify the base table name.
// * Specify that the server uses SQL Server change tracking.
// * Specify download-only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
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 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);
}
}
public class Utility
{
private static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public static void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public static string ConnStr_SqlCeClientSync
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
//Return the server connection string.
public static string ConnStr_DbServerSync
{
get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public static void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public static void CleanUpServer()
{
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public static void RecreateCompactDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(Utility.ConnStr_SqlCeClientSync))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(Utility.ConnStr_SqlCeClientSync);
sqlCeEngine.CreateDatabase();
}
}
}
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()
'Delete and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetClientPassword()
Utility.RecreateCompactDatabase()
'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()
'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.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
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 + " = change_tracking_current_version()"
.Parameters.Add(newAnchorVariable, SqlDbType.BigInt)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the Customer table by using
'the SqlSyncAdapterBuilder:
' * Specify the base table names.
' * Specify that the server uses SQL Server change tracking.
' * Specify download-only synchronization.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
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 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
Public Class Utility
Private Shared _clientPassword As String
'Get and set the client database password.
Public Shared Property Password() As String
Get
Return _clientPassword
End Get
Set(ByVal value As String)
_clientPassword = value
End Set
End Property
'Have the user enter a password for the client database file.
Public Shared Sub SetClientPassword()
Console.WriteLine("Type a strong password for the client")
Console.WriteLine("database, and then press Enter.")
Utility.Password = Console.ReadLine()
End Sub 'SetClientPassword
'Return the client connection string with the password.
Public Shared ReadOnly Property ConnStr_SqlCeClientSync() As String
Get
Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
End Get
End Property
'Return the server connection string.
Public Shared ReadOnly Property ConnStr_DbServerSync() As String
Get
Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"
End Get
End Property
'Make server changes that are synchronized on the second
'synchronization.
Public Shared Sub MakeDataChangesOnServer()
Dim rowCount As Integer = 0
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandText = _
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
& "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
& "UPDATE Sales.Customer " _
& "SET SalesPerson = 'James Bailey' " _
& "WHERE CustomerName = 'Tandem Bicycle Store' " _
& "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
serverConn.Open()
rowCount = sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)
End Sub 'MakeDataChangesOnServer
'Revert changes that were made during synchronization.
Public Shared Sub CleanUpServer()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = "usp_InsertSampleData"
serverConn.Open()
sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
End Sub 'CleanUpServer
'Delete the client database.
Public Shared Sub RecreateCompactDatabase()
Dim clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)
Try
If File.Exists(clientConn.Database) Then
File.Delete(clientConn.Database)
End If
Finally
clientConn.Dispose()
End Try
Dim sqlCeEngine As New SqlCeEngine(Utility.ConnStr_SqlCeClientSync)
sqlCeEngine.CreateDatabase()
End Sub 'RecreateClientDatabase
End Class 'Utility