行および列をフィルター選択する方法
このトピックでは、同期するテーブル内の行および列をフィルター選択する方法について説明します。このトピックの例では、次に示す Sync Framework の型を中心に説明します。
サンプル コードを実行する方法については、「一般的なクライアントとサーバーの同期タスクのプログラミング」の「操作方法に関するトピックのサンプル アプリケーション」を参照してください。
フィルター選択について
多くの場合、クライアント データベースに必要なのは、サーバーに格納されているデータの一部分のみです。Sync Framework では、クライアントに必要な行と列を選択し、同期コマンドを手動で作成するか、SqlSyncAdapterBuilder を使用して作成するかを指定できます。データのフィルター選択により、次のことが可能になります。
ネットワーク経由で送信されるデータ量を削減できます。
クライアントで必要となる記憶領域を削減できます。
各クライアントの要件に基づいてカスタム データ パーティションを提供できます。
データ パーティションをそれぞれ異なるクライアントに送信できるので、クライアントがデータを更新する場合の競合を回避または軽減できます (同じデータ値が 2 つのクライアントによって更新される状況がなくなります)。
フィルターは、1 つのテーブルに基づいて設定することも、JOIN 句または複数の SELECT ステートメントを使用して複数のテーブルを参照することもできます。複数のテーブルに基づいてフィルターを作成すると、顧客とその顧客の注文に関するデータのみを必要とする販売員などのユーザーは、データの一部分をダウンロードできます。複数のテーブル間でフィルター選択を行うことは、柔軟性につながります。ただし、フィルターは可能な限り簡潔にしておき、テーブル数が増加した際にはパフォーマンスをテストする必要があります。また、フィルター選択に使用する列にはインデックスも設定する必要があります。
注意
セキュリティ目的でフィルター選択を使用しないでください。クライアントまたはユーザーの ID に基づいてサーバーのデータをフィルター選択できますが、これはセキュリティ機能ではありません。つまり、この方法を使用しても、あるクライアントに属するデータが別のクライアントによって読み取られることを防ぐことはできません。このようなフィルター選択が役立つのは、クライアント データベースに表示されるデータをパーティション分割し、その量を少なくする場合のみです。
Sync Framework では、パーティションの自動管理は行われません。その影響は次のとおりです。
行を更新し、フィルター選択に使用された列の値を変更しても、その行は、その行が含まれているパーティションを持つクライアントから自動的には削除されません。たとえば、郵便番号に基づいて顧客データを販売員にダウンロードするアプリケーションを考えてみます。郵便番号が異なる地域に顧客がオフィスを移転しても、その顧客のデータは、最初にそのデータを所有していた販売員のパーティションから削除されません。こうした機能が必要である場合は、Sync Framework がその更新を削除としてダウンロードできるシステムを開発する必要があります。
アプリケーションがクライアントのパーティション外のデータをクライアントに挿入できないようにするメカニズムはありません。パーティション外の挿入および更新を禁止する制約をクライアントで追加することは可能です。
非キー列に基づいたフィルター
SQL Server の変更追跡および一部のカスタム追跡システムでは、削除された行の主キーのみが保持されます。フィルターが主キーにのみ基づいている場合、SelectIncrementalDeletesCommand プロパティに対して指定するクエリで行の適切なサブセットを識別してクライアントにダウンロードできます。フィルターが主キーの外部の列に基づいている場合、クエリは、削除された行に関してもはや存在しない列を参照しているので、失敗します。この問題に対処するには、次のいずれかの方法を検討してください。
すべてのフィルター列を主キーに含めます。追加の列をキーの末尾に配置して、キーの選択に作用しないようにします。
挿入と更新のみをフィルター選択します。不必要な削除はクライアントにダウンロードされますが、無視されます。
サーバー上で論理削除を実行します。行を削除する代わりに、ON DELETE トリガーを使用して、行に削除済みまたはアーカイブ済みのフラグを設定する列を更新します。変更はクライアントに更新として送信されます。
SQL Server の変更追跡 (SYS_CHANGE_CONTEXT) のコンテキスト列に、データをフィルター選択するために使用できる追加の値をオーバーロードします。パフォーマンスの点からはおそらくこれが最善の選択肢ですが、この列を解析する必要があるので最も複雑な方法です。
例
このトピックのコード例では、Sync Framework サンプル データベースの Customer テーブル、OrderHeader テーブル、および OrderDetail テーブルのデータをフィルター選択する方法を示します。Customer テーブルでは、SalesPerson 列の値が Brenda Diaz である行のみダウンロードされるようにフィルター選択します。その後、このフィルターは他の 2 つのテーブルに拡張されます。この例では、SqlSyncAdapterBuilder を使用してデータをフィルター選択する方法と、手動で同期コマンドを作成してデータをフィルター選択する方法を示しています。同期コマンドの概要については、「スナップショット、ダウンロード、アップロード、および双方向の各同期を指定する方法」を参照してください。
SqlSyncAdapterBuilder の使用
ここでは、SqlSyncAdapterBuilder を使用してコマンドを作成する場合にフィルター選択で使用する API について説明します。このセクションでは、API の主要部分に注目したコード例と、完全なコード例を示します。
API の主要部分
次のコード例では、3 つのテーブルすべてのフィルター句で使用されるフィルター パラメーターを作成します。
SqlParameter filterParameter = new SqlParameter("@SalesPerson", SqlDbType.NVarChar);
Dim filterParameter As New SqlParameter("@SalesPerson", SqlDbType.NVarChar)
次のコード例では、ダウンロードする Customer テーブルの列を指定します。
string[] customerDataColumns = new string[3];
customerDataColumns[0] = "CustomerId";
customerDataColumns[1] = "CustomerName";
customerDataColumns[2] = "CustomerType";
customerBuilder.DataColumns.AddRange(customerDataColumns);
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns);
Dim customerDataColumns(2) As String
customerDataColumns(0) = "CustomerId"
customerDataColumns(1) = "CustomerName"
customerDataColumns(2) = "CustomerType"
customerBuilder.DataColumns.AddRange(customerDataColumns)
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns)
次のコード例では、Customer テーブルについて、どの行をダウンロードするかを指定します。SalesPerson の値はハードコーディングできますが、この例で示すように、変更可能な値が設定されたパラメーターを使用する方が一般的です。最初のコード例のパラメーターが使用されています。
string customerFilterClause = "SalesPerson=@SalesPerson";
customerBuilder.FilterClause = customerFilterClause;
customerBuilder.FilterParameters.Add(filterParameter);
customerBuilder.TombstoneFilterClause = customerFilterClause;
customerBuilder.TombstoneFilterParameters.Add(filterParameter);
Dim customerFilterClause As String = "SalesPerson=@SalesPerson"
With customerBuilder
.FilterClause = customerFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = customerFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
次のコード例では、フィルターを Customer テーブルから OrderHeader テーブルに拡張します。この例では、フィルター句で SELECT ステートメントが使用されています。手動でコマンドを作成する場合は、JOIN 句を使用します。この句を使用すると、コマンドの指定方法をより細かく制御できます。
string orderHeaderFilterClause =
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " +
"WHERE SalesPerson=@SalesPerson)";
orderHeaderBuilder.FilterClause = orderHeaderFilterClause;
orderHeaderBuilder.FilterParameters.Add(filterParameter);
orderHeaderBuilder.TombstoneFilterClause = orderHeaderFilterClause;
orderHeaderBuilder.TombstoneFilterParameters.Add(filterParameter);
Dim orderHeaderFilterClause As String = _
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " _
& "WHERE SalesPerson=@SalesPerson)"
With orderHeaderBuilder
.FilterClause = orderHeaderFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = orderHeaderFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
次のコード サンプルでは、SyncAgent から派生したクラスの @SalesPerson パラメーターの値を指定します。アプリケーションによっては、この値をログイン ID やその他のユーザー入力によって設定できます。
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))
完全なコード例
次の完全なコード例には、既に説明したコード例に加え、同期を実行するためのコードが含まれています。この例では、「データベース プロバイダーの Utility クラスに関するトピック」で説明されている Utility クラスが必要です。サンプルを実行する際は、SyncStatistics によって返される情報に注意してください。初期同期とその後の同期の両方で、行のサブセットがダウンロードされます。
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Request a password for the client database, and delete
//and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetPassword_SqlCeClientSync();
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server.
Utility.MakeDataChangesOnServer("Customer");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
Utility.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Create two SyncGroups so that changes to OrderHeader
//and OrderDetail are made in one transaction. Depending on
//application requirements, you might include Customer
//in the same group.
SyncGroup customerSyncGroup = new SyncGroup("Customer");
SyncGroup orderSyncGroup = new SyncGroup("Order");
//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderDetailSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderDetailSyncTable);
//Specify a value for the @SalesPerson parameter that is added
//in the server synchronization provider. This value would
//typically be provided by a user in the application, but we
//have hardcoded it here for convenience.
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a filter parameter that will be used in the filter clause for
//all three tables.
SqlParameter filterParameter = new SqlParameter("@SalesPerson", SqlDbType.NVarChar);
//Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// changes are made.
// * Specify download-only synchronization.
// * Specify if you want only certain columns at the client.
// * Specify filter clauses for the base tables and tombstone
// tables.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name that is specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
//Customer table.
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
//Specify the columns that you want at the client. If you
//want all columns, this code is not required. In this
//case, we filter out SalesPerson.
string[] customerDataColumns = new string[3];
customerDataColumns[0] = "CustomerId";
customerDataColumns[1] = "CustomerName";
customerDataColumns[2] = "CustomerType";
customerBuilder.DataColumns.AddRange(customerDataColumns);
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns);
//Specify a filter clause, which is an SQL WHERE clause
//without the WHERE keyword. Use the parameter that is
//created above. The value for the parameter is specified
//in the SyncAgent Configuration object.
string customerFilterClause = "SalesPerson=@SalesPerson";
customerBuilder.FilterClause = customerFilterClause;
customerBuilder.FilterParameters.Add(filterParameter);
customerBuilder.TombstoneFilterClause = customerFilterClause;
customerBuilder.TombstoneFilterParameters.Add(filterParameter);
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
//OrderHeader table.
SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);
orderHeaderBuilder.TableName = "Sales.OrderHeader";
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";
//Filter properties: extend the filter to the OrderHeader table.
string orderHeaderFilterClause =
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " +
"WHERE SalesPerson=@SalesPerson)";
orderHeaderBuilder.FilterClause = orderHeaderFilterClause;
orderHeaderBuilder.FilterParameters.Add(filterParameter);
orderHeaderBuilder.TombstoneFilterClause = orderHeaderFilterClause;
orderHeaderBuilder.TombstoneFilterParameters.Add(filterParameter);
SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
orderHeaderSyncAdapter.TableName = "OrderHeader";
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//OrderDetail table.
SqlSyncAdapterBuilder orderDetailBuilder = new SqlSyncAdapterBuilder(serverConn);
orderDetailBuilder.TableName = "Sales.OrderDetail";
orderDetailBuilder.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone";
orderDetailBuilder.SyncDirection = SyncDirection.DownloadOnly;
orderDetailBuilder.CreationTrackingColumn = "InsertTimestamp";
orderDetailBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderDetailBuilder.DeletionTrackingColumn = "DeleteTimestamp";
//Filter properties: extend the filter to the OrderDetail table.
string orderDetailFilterClause =
"OrderId IN (SELECT OrderId FROM Sales.OrderHeader " +
"WHERE CustomerId IN " +
"(SELECT CustomerId FROM Sales.Customer " +
"WHERE SalesPerson=@SalesPerson))";
orderDetailBuilder.FilterClause = orderDetailFilterClause;
orderDetailBuilder.FilterParameters.Add(filterParameter);
orderDetailBuilder.TombstoneFilterClause = orderDetailFilterClause;
orderDetailBuilder.TombstoneFilterParameters.Add(filterParameter);
SyncAdapter orderDetailSyncAdapter = orderDetailBuilder.ToSyncAdapter();
orderDetailSyncAdapter.TableName = "OrderDetail";
this.SyncAdapters.Add(orderDetailSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but you could use this class to handle client
//provider events and other client-side processing.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
}
}
//Handle the statistics returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Request a password for the client database, and delete
'and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetPassword_SqlCeClientSync()
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, True)
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server.
Utility.MakeDataChangesOnServer("Customer")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
Utility.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Create two SyncGroups so that changes to OrderHeader
'and OrderDetail are made in one transaction. Depending on
'application requirements, you might include Customer
'in the same group.
Dim customerSyncGroup As New SyncGroup("Customer")
Dim orderSyncGroup As New SyncGroup("Order")
'Add each table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
customerSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderHeaderSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
Dim orderDetailSyncTable As New SyncTable("OrderDetail")
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderDetailSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderDetailSyncTable)
'Specify a value for the @SalesPerson parameter that is added
'in the server synchronization provider. This value would
'typically be provided by a user in the application, but we
'have hardcoded it here for convenience.
Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a filter parameter that will be used in the filter clause for
'all three tables.
Dim filterParameter As New SqlParameter("@SalesPerson", SqlDbType.NVarChar)
'Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
' * Specify the base table and tombstone table names.
' * Specify the columns that are used to track when
' changes are made.
' * Specify download-only synchronization.
' * Specify if you want only certain columns at the client.
' * Specify filter clauses for the base tables and tombstone
' tables.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name that is specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
'Customer table.
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
With customerBuilder
.TableName = "Sales.Customer"
.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.DownloadOnly
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
End With
'Specify the columns that you want at the client. If you
'want all columns, this code is not required. In this
'case, we filter out SalesPerson.
Dim customerDataColumns(2) As String
customerDataColumns(0) = "CustomerId"
customerDataColumns(1) = "CustomerName"
customerDataColumns(2) = "CustomerType"
customerBuilder.DataColumns.AddRange(customerDataColumns)
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns)
'Specify a filter clause, which is an SQL WHERE clause
'without the WHERE keyword. Use the parameter that is
'created above. The value for the parameter is specified
'in the SyncAgent Configuration object.
Dim customerFilterClause As String = "SalesPerson=@SalesPerson"
With customerBuilder
.FilterClause = customerFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = customerFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
'OrderHeader table.
Dim orderHeaderBuilder As New SqlSyncAdapterBuilder(serverConn)
With orderHeaderBuilder
.TableName = "Sales.OrderHeader"
.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.DownloadOnly
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
End With
'Filter properties: extend the filter to the OrderHeader table.
Dim orderHeaderFilterClause As String = _
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " _
& "WHERE SalesPerson=@SalesPerson)"
With orderHeaderBuilder
.FilterClause = orderHeaderFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = orderHeaderFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
Dim orderHeaderSyncAdapter As SyncAdapter = orderHeaderBuilder.ToSyncAdapter()
orderHeaderSyncAdapter.TableName = "OrderHeader"
Me.SyncAdapters.Add(orderHeaderSyncAdapter)
'OrderDetail table.
Dim orderDetailBuilder As New SqlSyncAdapterBuilder(serverConn)
With orderDetailBuilder
.TableName = "Sales.OrderDetail"
.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.DownloadOnly
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
'Filter properties: extend the filter to the OrderDetail table.
Dim orderDetailFilterClause As String = _
"OrderId IN (SELECT OrderId FROM Sales.OrderHeader " _
& "WHERE CustomerId IN " _
& "(SELECT CustomerId FROM Sales.Customer " _
& "WHERE SalesPerson=@SalesPerson))"
.FilterClause = orderDetailFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = orderDetailFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
Dim orderDetailSyncAdapter As SyncAdapter = orderDetailBuilder.ToSyncAdapter()
orderDetailSyncAdapter.TableName = "OrderDetail"
Me.SyncAdapters.Add(orderDetailSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but you could use this class to handle client
'provider events and other client-side processing.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
End Sub 'New
End Class 'SampleClientSyncProvider
'Handle the statistics returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats
手動で作成したコマンドの使用
ここでは、手動でコマンドを作成する場合にフィルター選択で使用する API について説明します。このセクションでは、API の主要部分に注目したコード例と、完全なコード例を示します。
API の主要部分
次のコード例では、Customer テーブルについて、挿入されたどの列および行をダウンロードするかを指定します。SalesPerson の値はハードコーディングできますが、この例で示すように、変更可能な値が設定されたパラメーターを使用する方が一般的です。この例では、このフィルター パラメーターと共に、増分挿入のダウンロードに必要な他のパラメーターも渡しています。
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
"SELECT CustomerId, CustomerName, CustomerType " +
"FROM Sales.Customer " +
"WHERE SalesPerson = @SalesPerson " +
"AND (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertTimestamp <= @sync_new_received_anchor " +
"AND InsertId <> @sync_client_id)";
customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
Dim customerIncrInserts As New SqlCommand()
With customerIncrInserts
.CommandText = _
"SELECT CustomerId, CustomerName, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertTimestamp <= @sync_new_received_anchor " _
& "AND InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
次のコード例では、フィルターを Customer テーブルから OrderHeader テーブルに拡張します。この例では、JOIN 句を使用して、2 つのテーブル間のリレーションシップを定義しています。
SqlCommand orderHeaderIncrInserts = new SqlCommand();
orderHeaderIncrInserts.CommandText =
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
"FROM Sales.OrderHeader oh " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE c.SalesPerson = @SalesPerson " +
"AND (oh.InsertTimestamp > @sync_last_received_anchor " +
"AND oh.InsertTimestamp <= @sync_new_received_anchor " +
"AND oh.InsertId <> @sync_client_id)";
orderHeaderIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrInserts.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts;
Dim orderHeaderIncrInserts As New SqlCommand()
With orderHeaderIncrInserts
.CommandText = _
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
& "FROM Sales.OrderHeader oh " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE c.SalesPerson = @SalesPerson " _
& "AND (oh.InsertTimestamp > @sync_last_received_anchor " _
& "AND oh.InsertTimestamp <= @sync_new_received_anchor " _
& "AND oh.InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts
次のコード サンプルでは、SyncAgent から派生したクラスの @SalesPerson パラメーターの値を指定します。アプリケーションによっては、この値をログイン ID やその他のユーザー入力によって設定できます。
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
完全なコード例
次の完全なコード例には、既に説明したコード例に加え、同期を実行するためのコードが含まれています。この例では、「データベース プロバイダーの Utility クラスに関するトピック」で説明されている Utility クラスが必要です。サンプルを実行する際は、SyncStatistics によって返される情報に注意してください。初期同期とその後の同期の両方で、行のサブセットがダウンロードされます。
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Request a password for the client database, and delete
//and recreate the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetPassword_SqlCeClientSync();
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server.
Utility.MakeDataChangesOnServer("Customer");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
Utility.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Create two SyncGroups, so that changes to OrderHeader
//and OrderDetail are made in one transaction. Depending on
//application requirements, you might include Customer
//in the same group.
SyncGroup customerSyncGroup = new SyncGroup("Customer");
SyncGroup orderSyncGroup = new SyncGroup("Order");
//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderDetailSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderDetailSyncTable);
//Specify a value for the @SalesPerson parameter that is added
//in the server synchronization provider. This value would
//typically be provided by a user in the application, but we
//have hardcoded it here for convenience.
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for each table, and then define
//the commands to synchronize changes:
//* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
// and SelectIncrementalDeletesCommand are used to select changes
// from the server that the client provider then applies to the client.
//* Specify if you want only certain columns at the client by
// using the SELECT statement in the command.
//* Filter rows by using the WHERE clause in the command.
// In this case, we filter out SalesPerson.
//
//Customer table
//
//Create the SyncAdapter
SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");
//Select inserts from the server
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
"SELECT CustomerId, CustomerName, CustomerType " +
"FROM Sales.Customer " +
"WHERE SalesPerson = @SalesPerson " +
"AND (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertTimestamp <= @sync_new_received_anchor " +
"AND InsertId <> @sync_client_id)";
customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
//Select updates from the server
SqlCommand customerIncrUpdates = new SqlCommand();
customerIncrUpdates.CommandText =
"SELECT CustomerId, CustomerName, CustomerType " +
"FROM Sales.Customer " +
"WHERE SalesPerson = @SalesPerson " +
"AND (UpdateTimestamp > @sync_last_received_anchor " +
"AND UpdateTimestamp <= @sync_new_received_anchor " +
"AND UpdateId <> @sync_client_id " +
"AND NOT (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertId <> @sync_client_id))";
customerIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrUpdates.Connection = serverConn;
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;
//Select deletes from the server
SqlCommand customerIncrDeletes = new SqlCommand();
customerIncrDeletes.CommandText =
"SELECT CustomerId, CustomerName, CustomerType " +
"FROM Sales.Customer_Tombstone " +
"WHERE SalesPerson = @SalesPerson " +
"AND (@sync_initialized = 1 " +
"AND DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor " +
"AND DeleteId <> @sync_client_id)";
customerIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrDeletes.Connection = serverConn;
customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes;
//Add the SyncAdapter to the server synchronization provider
this.SyncAdapters.Add(customerSyncAdapter);
//
//OrderHeader table
//
//Create the SyncAdapter
SyncAdapter orderHeaderSyncAdapter = new SyncAdapter("OrderHeader");
//Select inserts from the server
SqlCommand orderHeaderIncrInserts = new SqlCommand();
orderHeaderIncrInserts.CommandText =
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
"FROM Sales.OrderHeader oh " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE c.SalesPerson = @SalesPerson " +
"AND (oh.InsertTimestamp > @sync_last_received_anchor " +
"AND oh.InsertTimestamp <= @sync_new_received_anchor " +
"AND oh.InsertId <> @sync_client_id)";
orderHeaderIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrInserts.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts;
//Select updates from the server
SqlCommand orderHeaderIncrUpdates = new SqlCommand();
orderHeaderIncrUpdates.CommandText =
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
"FROM Sales.OrderHeader oh " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE c.SalesPerson = @SalesPerson " +
"AND (oh.UpdateTimestamp > @sync_last_received_anchor " +
"AND oh.UpdateTimestamp <= @sync_new_received_anchor " +
"AND oh.UpdateId <> @sync_client_id " +
"AND NOT (oh.InsertTimestamp > @sync_last_received_anchor " +
"AND oh.InsertId <> @sync_client_id))";
orderHeaderIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrUpdates.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalUpdatesCommand = orderHeaderIncrUpdates;
//Select deletes from the server
SqlCommand orderHeaderIncrDeletes = new SqlCommand();
orderHeaderIncrDeletes.CommandText =
"SELECT oht.OrderId, oht.CustomerId, oht.OrderDate, oht.OrderStatus " +
"FROM Sales.OrderHeader_Tombstone oht " +
"JOIN Sales.Customer c ON oht.CustomerId = c.CustomerId " +
"WHERE c.SalesPerson = @SalesPerson " +
"AND (@sync_initialized = 1 " +
"AND oht.DeleteTimestamp > @sync_last_received_anchor " +
"AND oht.DeleteTimestamp <= @sync_new_received_anchor " +
"AND oht.DeleteId <> @sync_client_id)";
orderHeaderIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrDeletes.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalDeletesCommand = orderHeaderIncrDeletes;
//Add the SyncAdapter to the server synchronization provider
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//
//OrderDetail table
//
//Create the SyncAdapter
SyncAdapter orderDetailSyncAdapter = new SyncAdapter("OrderDetail");
//Select inserts from the server
SqlCommand orderDetailIncrInserts = new SqlCommand();
orderDetailIncrInserts.CommandText =
"SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " +
"FROM Sales.OrderDetail od " +
"JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE SalesPerson = @SalesPerson " +
"AND (od.InsertTimestamp > @sync_last_received_anchor " +
"AND od.InsertTimestamp <= @sync_new_received_anchor " +
"AND od.InsertId <> @sync_client_id)";
orderDetailIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderDetailIncrInserts.Connection = serverConn;
orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts;
//Select updates from the server
SqlCommand orderDetailIncrUpdates = new SqlCommand();
orderDetailIncrUpdates.CommandText =
"SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " +
"FROM Sales.OrderDetail od " +
"JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE SalesPerson = @SalesPerson " +
"AND (od.UpdateTimestamp > @sync_last_received_anchor " +
"AND od.UpdateTimestamp <= @sync_new_received_anchor " +
"AND od.UpdateId <> @sync_client_id " +
"AND NOT (od.InsertTimestamp > @sync_last_received_anchor " +
"AND od.InsertId <> @sync_client_id))";
orderDetailIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderDetailIncrUpdates.Connection = serverConn;
orderDetailSyncAdapter.SelectIncrementalUpdatesCommand = orderDetailIncrUpdates;
//Select deletes from the server
SqlCommand orderDetailIncrDeletes = new SqlCommand();
orderDetailIncrDeletes.CommandText =
"SELECT odt.OrderDetailId, odt.OrderId, odt.Product, odt.Quantity " +
"FROM Sales.OrderDetail_Tombstone odt " +
"JOIN Sales.OrderHeader oh ON odt.OrderId = oh.OrderId " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE SalesPerson = @SalesPerson " +
"AND (@sync_initialized = 1 " +
"AND odt.DeleteTimestamp > @sync_last_received_anchor " +
"AND odt.DeleteTimestamp <= @sync_new_received_anchor " +
"AND odt.DeleteId <> @sync_client_id)";
orderDetailIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderDetailIncrDeletes.Connection = serverConn;
orderDetailSyncAdapter.SelectIncrementalDeletesCommand = orderDetailIncrDeletes;
//Add the SyncAdapter to the server synchronization provider
this.SyncAdapters.Add(orderDetailSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but you could use this class to handle client
//provider events and other client-side processing.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
}
}
//Handle the statistics returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Request a password for the client database, and delete
'and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetPassword_SqlCeClientSync()
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, True)
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server.
Utility.MakeDataChangesOnServer("Customer")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
Utility.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Create two SyncGroups so that changes to OrderHeader
'and OrderDetail are made in one transaction. Depending on
'application requirements, you might include Customer
'in the same group.
Dim customerSyncGroup As New SyncGroup("Customer")
Dim orderSyncGroup As New SyncGroup("Order")
'Add each table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
customerSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderHeaderSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
Dim orderDetailSyncTable As New SyncTable("OrderDetail")
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderDetailSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderDetailSyncTable)
'Specify a value for the @SalesPerson parameter that is added
'in the server synchronization provider. This value would
'typically be provided by a user in the application, but we
'have hardcoded it here for convenience.
Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for each table, and then define
'the commands to synchronize changes:
'* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
' and SelectIncrementalDeletesCommand are used to select changes
' from the server that the client provider then applies to the client.
'* Specify if you want only certain columns at the client by
' using the SELECT statement in the command.
'* Filter rows by using the WHERE clause in the command.
' In this case, we filter out SalesPerson.
'
'Customer table.
'
'Create the SyncAdapter.
Dim customerSyncAdapter As New SyncAdapter("Customer")
'Select inserts from the server.
Dim customerIncrInserts As New SqlCommand()
With customerIncrInserts
.CommandText = _
"SELECT CustomerId, CustomerName, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertTimestamp <= @sync_new_received_anchor " _
& "AND InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
'Select updates from the server.
Dim customerIncrUpdates As New SqlCommand()
With customerIncrUpdates
.CommandText = _
"SELECT CustomerId, CustomerName, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (UpdateTimestamp > @sync_last_received_anchor " _
& "AND UpdateTimestamp <= @sync_new_received_anchor " _
& "AND UpdateId <> @sync_client_id " _
& "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertId <> @sync_client_id))"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates
'Select deletes from the server.
Dim customerIncrDeletes As New SqlCommand()
With customerIncrDeletes
.CommandText = _
"SELECT CustomerId, CustomerName, CustomerType " _
& "FROM Sales.Customer_Tombstone " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (@sync_initialized = 1 " _
& "AND DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor " _
& "AND DeleteId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(customerSyncAdapter)
'
'OrderHeader table.
'
'Create the SyncAdapter.
Dim orderHeaderSyncAdapter As New SyncAdapter("OrderHeader")
'Select inserts from the server.
Dim orderHeaderIncrInserts As New SqlCommand()
With orderHeaderIncrInserts
.CommandText = _
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
& "FROM Sales.OrderHeader oh " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE c.SalesPerson = @SalesPerson " _
& "AND (oh.InsertTimestamp > @sync_last_received_anchor " _
& "AND oh.InsertTimestamp <= @sync_new_received_anchor " _
& "AND oh.InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts
'Select updates from the server.
Dim orderHeaderIncrUpdates As New SqlCommand()
With orderHeaderIncrUpdates
.CommandText = _
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
& "FROM Sales.OrderHeader oh " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE c.SalesPerson = @SalesPerson " _
& "AND (oh.UpdateTimestamp > @sync_last_received_anchor " _
& "AND oh.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND oh.UpdateId <> @sync_client_id " _
& "AND NOT (oh.InsertTimestamp > @sync_last_received_anchor " _
& "AND oh.InsertId <> @sync_client_id))"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderHeaderSyncAdapter.SelectIncrementalUpdatesCommand = orderHeaderIncrUpdates
'Select deletes from the server.
Dim orderHeaderIncrDeletes As New SqlCommand()
With orderHeaderIncrDeletes
.CommandText = _
"SELECT oht.OrderId, oht.CustomerId, oht.OrderDate, oht.OrderStatus " _
& "FROM Sales.OrderHeader_Tombstone oht " _
& "JOIN Sales.Customer c ON oht.CustomerId = c.CustomerId " _
& "WHERE c.SalesPerson = @SalesPerson " _
& "AND (@sync_initialized = 1 " _
& "AND oht.DeleteTimestamp > @sync_last_received_anchor " _
& "AND oht.DeleteTimestamp <= @sync_new_received_anchor " _
& "AND oht.DeleteId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderHeaderSyncAdapter.SelectIncrementalDeletesCommand = orderHeaderIncrDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(orderHeaderSyncAdapter)
'
'OrderDetail table.
'
'Create the SyncAdapter.
Dim orderDetailSyncAdapter As New SyncAdapter("OrderDetail")
'Select inserts from the server.
Dim orderDetailIncrInserts As New SqlCommand()
With orderDetailIncrInserts
.CommandText = _
"SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " _
& "FROM Sales.OrderDetail od " _
& "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (od.InsertTimestamp > @sync_last_received_anchor " _
& "AND od.InsertTimestamp <= @sync_new_received_anchor " _
& "AND od.InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts
'Select updates from the server.
Dim orderDetailIncrUpdates As New SqlCommand()
With orderDetailIncrUpdates
.CommandText = _
"SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " _
& "FROM Sales.OrderDetail od " _
& "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (od.UpdateTimestamp > @sync_last_received_anchor " _
& "AND od.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND od.UpdateId <> @sync_client_id " _
& "AND NOT (od.InsertTimestamp > @sync_last_received_anchor " _
& "AND od.InsertId <> @sync_client_id))"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderDetailSyncAdapter.SelectIncrementalUpdatesCommand = orderDetailIncrUpdates
'Select deletes from the server.
Dim orderDetailIncrDeletes As New SqlCommand()
With orderDetailIncrDeletes
.CommandText = _
"SELECT odt.OrderDetailId, odt.OrderId, odt.Product, odt.Quantity " _
& "FROM Sales.OrderDetail_Tombstone odt " _
& "JOIN Sales.OrderHeader oh ON odt.OrderId = oh.OrderId " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (@sync_initialized = 1 " _
& "AND odt.DeleteTimestamp > @sync_last_received_anchor " _
& "AND odt.DeleteTimestamp <= @sync_new_received_anchor " _
& "AND odt.DeleteId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderDetailSyncAdapter.SelectIncrementalDeletesCommand = orderDetailIncrDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(orderDetailSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but you could use this class to handle client
'provider events and other client-side processing.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
End Sub 'New
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats