HOW TO：在用戶端上建立資料檢視
此主題示範如何使用 Sync Framework 結合兩個或更多伺服器資料表為在用戶端上單一的資料表。本主題的範例將重點放在下列 Sync Framework 型別與事件：
如需有關如何執行範例程式碼的詳細資訊，請參閱撰寫一般用戶端和伺服器同步處理工作中的＜HOW-TO 主題中的範例應用程式＞。
範例
下列程式碼範例示範如何在 Sync Framework 範例資料庫中同步 (Synchronize) Customer 和 CustomerContact 資料表。在初始同步處理過程中，系統會使用累加插入指令選取兩個資料表上的資料，將它們下載到用戶端，然後插入 CustomerInfo 資料表中。在後續同步處理期間，系統會將合格的變更下載到用戶端。
API 的主要部分
本節提供的程式碼範例將指出讓您可以在結合資料表時使用之 API 的重要部分。下列程式碼範例指出 SyncTable 和 SyncAdapter 物件都使用 CustomerInfo 這個名稱。在兩個地方都指定名稱，就能讓 SyncAgent 和提供者將 Customer 和 CustomerContact 資料表中的選取轉換為 CustomerInfo 資料表的插入。
SyncTable customerInfoSyncTable = new SyncTable("CustomerInfo");
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerInfoSyncTable);
SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo");
Dim customerInfoSyncTable As New SyncTable("CustomerInfo")
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerInfoSyncTable)
Dim customerInfoSyncAdapter As New SyncAdapter("CustomerInfo")
下列程式碼範例包含 SelectIncrementalInsertsCommand 屬性的查詢。此查詢使用聯結，以確保在初始同步處理過程中只下載有連絡資訊的客戶到用戶端。在後續的同步處理中，系統會下載針對 CustomerContact 資料表的插入，而針對 Customer 資料表的插入，則只有在該客戶的資料列也插入了 CustomerContact 資料表時才會進行下載。請注意，選取清單不包含所有的資料行。就如所有在同步處理中使用的查詢一樣，每個查詢的邏輯是根據應用程式的需求而定。例如，您可以下載所有的客戶，即使系統中並沒有他們的連絡資訊。
SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand();
customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text;
customerInfoIncrementalInsertsCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.InsertTimestamp > @sync_last_received_anchor " +
"AND c.InsertTimestamp <= @sync_new_received_anchor) OR " +
"(cc.InsertTimestamp > @sync_last_received_anchor " +
"AND cc.InsertTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand;
Dim customerInfoIncrementalInsertsCommand As New SqlCommand()
With customerInfoIncrementalInsertsCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " _
& "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " _
& "(cc.InsertTimestamp > @sync_last_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand
下列程式碼範例包含 SelectIncrementalDeletesCommand 屬性的查詢。此查詢使用兩個 SELECT 陳述式以及一個 UNION 來選取 Customer 和 CustomerContact 資料表中的刪除。第一個 SELECT 與 CustomerContact 資料表聯結，以擷取 PhoneType 資料行。此資料行是 CustomerInfo 資料表的複合索引鍵的一部分。其邏輯如下：
如果系統刪除了某個客戶，所有該客戶的資料列都會由用戶端中刪除。
如果系統刪除了連絡資料，只有該資料列會由用戶端中刪除。
如果同時刪除了該客戶的客戶資料列和連絡資訊，則系統最少會下載一個額外的刪除。當變更套用至用戶端資料庫時，這樣做並不會造成任何錯誤。
SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand();
customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text;
customerInfoIncrementalDeletesCommand.CommandText =
"SELECT c.CustomerId, cc.PhoneType " +
"FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor)) " +
"UNION " +
"SELECT CustomerId, PhoneType " +
"FROM Sales.CustomerContact_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand;
Dim customerInfoIncrementalDeletesCommand As New SqlCommand()
With customerInfoIncrementalDeletesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, cc.PhoneType " _
& "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor)) " _
& "UNION " _
& "SELECT CustomerId, PhoneType " _
& "FROM Sales.CustomerContact_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_initialized", SqlDbType.Bit)
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand
下列程式碼範例會為 CustomerInfo 資料表建立複合主索引鍵。此索引鍵與 CustomerContact 資料表的索引鍵相符。Sync Framework 能夠從伺服器上的資料表來推斷結構描述，但在這個案例中必須指定索引鍵。您也可以手動建立結構描述，如 HOW TO：初始化用戶端資料庫及使用資料表結構描述中所述。
string[] customerInfoPrimaryKey = new string[2];
customerInfoPrimaryKey[0] = "CustomerId";
customerInfoPrimaryKey[1] = "PhoneType";
e.Schema.Tables["CustomerInfo"].PrimaryKey = customerInfoPrimaryKey;
Dim customerInfoPrimaryKey(1) As String
customerInfoPrimaryKey(0) = "CustomerId"
customerInfoPrimaryKey(1) = "PhoneType"
e.Schema.Tables("CustomerInfo").PrimaryKey = customerInfoPrimaryKey
完整的程式碼範例
下列的完整程式碼範例包含先前所述的程式碼範例，以及其他的程式碼，可用來執行同步處理。此範例需要 Utility 類別，詳情請參閱 資料庫提供者公用程式類別的 HOW-TO 主題。請注意，在後續的同步處理中不會下載 Utility 類別中插入 Customer 資料表內的資料，因為 CustomerContact 資料表中沒有對應的資料列。
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Request a password for the client database, and delete
//and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetPassword_SqlCeClientSync();
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server.
Utility.MakeDataChangesOnServer("Customer");
Utility.MakeDataChangesOnServer("CustomerContact");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
Utility.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Add the Customer table: specify a synchronization direction of
//DownloadOnly, and that an existing table should be dropped.
SyncTable customerInfoSyncTable = new SyncTable("CustomerInfo");
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerInfoSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the CustomerInfo table. The CustomerInfo
//table on the client is a combination of the Customer and CustomerContact
//tables on the server. This table is download-only, as specified in
//SampleSyncAgent.
SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo");
//Specify synchronization commands. The CustomerInfo table
//is download-only, so we do not define commands to apply changes to
//the server. Each command joins the base tables or tombstone tables
//to select the appropriate incremental changes. For this application,
//the logic is as follows:
//* Select all inserts for customers that have contact information.
// This results in more than one row for a customer if that customer
// has more than one phone number.
//* Select all updates for customer and contact information that has
// already been downloaded.
//* Select all deletes for customer and contact information that has
// already been downloaded. If a customer has been deleted, delete
// all of the rows for that customer. If a phone number has been
// deleted, delete only that row.
//Select inserts.
SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand();
customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text;
customerInfoIncrementalInsertsCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.InsertTimestamp > @sync_last_received_anchor " +
"AND c.InsertTimestamp <= @sync_new_received_anchor) OR " +
"(cc.InsertTimestamp > @sync_last_received_anchor " +
"AND cc.InsertTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand;
//Select updates.
SqlCommand customerInfoIncrementalUpdatesCommand = new SqlCommand();
customerInfoIncrementalUpdatesCommand.CommandType = CommandType.Text;
customerInfoIncrementalUpdatesCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " +
"AND c.UpdateTimestamp <= @sync_new_received_anchor " +
"AND c.InsertTimestamp <= @sync_last_received_anchor) " +
"OR (cc.UpdateTimestamp > @sync_last_received_anchor " +
"AND cc.UpdateTimestamp <= @sync_new_received_anchor " +
"AND cc.InsertTimestamp <= @sync_last_received_anchor))";
customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalUpdatesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand;
//Select deletes.
SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand();
customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text;
customerInfoIncrementalDeletesCommand.CommandText =
"SELECT c.CustomerId, cc.PhoneType " +
"FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor)) " +
"UNION " +
"SELECT CustomerId, PhoneType " +
"FROM Sales.CustomerContact_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand;
//Add the SyncAdapter to the provider.
this.SyncAdapters.Add(customerInfoSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but here we use this class to handle client
//provider events.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
//Handle the two schema-related events.
this.CreatingSchema += new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
this.SchemaCreated += new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
}
private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
Console.Write("Creating schema for " + e.Table.TableName + " | ");
//Create a compostite primary key for the CustomerInfo table.
string[] customerInfoPrimaryKey = new string[2];
customerInfoPrimaryKey[0] = "CustomerId";
customerInfoPrimaryKey[1] = "PhoneType";
e.Schema.Tables["CustomerInfo"].PrimaryKey = customerInfoPrimaryKey;
}
private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
{
Console.WriteLine("Schema created for " + e.Table.TableName);
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Request a password for the client database, and delete
'and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetPassword_SqlCeClientSync()
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, True)
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server.
Utility.MakeDataChangesOnServer("Customer")
Utility.MakeDataChangesOnServer("CustomerContact")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
Utility.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Add the Customer table: specify a synchronization direction of
'DownloadOnly, and that an existing table should be dropped.
Dim customerInfoSyncTable As New SyncTable("CustomerInfo")
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerInfoSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the CustomerInfo table. The CustomerInfo
'table on the client is a combination of the Customer and CustomerContact
'tables on the server. This table is download-only, as specified in
'SampleSyncAgent.
Dim customerInfoSyncAdapter As New SyncAdapter("CustomerInfo")
'Specify synchronization commands. The CustomerInfo table
'is download-only, so we do not define commands to apply changes to
'the server. Each command joins the base tables or tombstone tables
'to select the appropriate incremental changes. For this application,
'the logic is as follows:
'* Select all inserts for customers that have contact information.
' This results in more than one row for a customer if that customer
' has more than one phone number.
'* Select all updates for customer and contact information that has
' already been downloaded.
'* Select all deletes for customer and contact information that has
' already been downloaded. If a customer has been deleted, delete
' all of the rows for that customer. If a phone number has been
' deleted, delete only that row.
'Select inserts.
Dim customerInfoIncrementalInsertsCommand As New SqlCommand()
With customerInfoIncrementalInsertsCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " _
& "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " _
& "(cc.InsertTimestamp > @sync_last_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand
'Select updates.
Dim customerInfoIncrementalUpdatesCommand As New SqlCommand()
With customerInfoIncrementalUpdatesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " _
& "AND c.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND c.InsertTimestamp <= @sync_last_received_anchor) " _
& "OR (cc.UpdateTimestamp > @sync_last_received_anchor " _
& "AND cc.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_last_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand
'Select deletes.
Dim customerInfoIncrementalDeletesCommand As New SqlCommand()
With customerInfoIncrementalDeletesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, cc.PhoneType " _
& "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor)) " _
& "UNION " _
& "SELECT CustomerId, PhoneType " _
& "FROM Sales.CustomerContact_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_initialized", SqlDbType.Bit)
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand
'Add the SyncAdapter to the provider.
Me.SyncAdapters.Add(customerInfoSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but here we use this class to handle client
'provider events.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
'Handle the two schema-related events.
AddHandler Me.CreatingSchema, AddressOf SampleClientSyncProvider_CreatingSchema
AddHandler Me.SchemaCreated, AddressOf SampleClientSyncProvider_SchemaCreated
End Sub 'New
Private Sub SampleClientSyncProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
Console.Write("Creating schema for " + e.Table.TableName + " | ")
'Create a compostite primary key for the CustomerInfo table.
Dim customerInfoPrimaryKey(1) As String
customerInfoPrimaryKey(0) = "CustomerId"
customerInfoPrimaryKey(1) = "PhoneType"
e.Schema.Tables("CustomerInfo").PrimaryKey = customerInfoPrimaryKey
End Sub 'SampleClientSyncProvider_CreatingSchema
Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
Console.WriteLine("Schema created for " + e.Table.TableName)
End Sub 'SampleClientSyncProvider_SchemaCreated
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats