HOW TO：設定及執行共同作業同步處理 (非 SQL Server)
注意
文件中同步處理其他 ADO.NET 相容的資料庫這一節的主題是用來示範如何使用 Sync Framework 來同步處理 SQL Server 以外的資料庫。在這一版中，程式碼範例中使用的是SQL Server，但是程式碼可用於其他 ADO.NET 相容的資料庫，只需要針對 SQL Server 特有的物件 (例如 SqlConnection) 以及顯示的 SQL 查詢進行某些修改。如需 SQL Server 同步處理的詳細資訊，請參閱HOW TO：設定及執行共同作業同步處理 (SQL Server)。
本主題描述使用 Sync Framework 來同步處理許多資料庫的應用程式主要部分。此應用程式中的程式碼著重於下列 Sync Framework 類別：
如需如何執行範例程式碼的詳細資訊，請參閱 同步處理其他 ADO.NET 相容的資料庫中的＜HOW-TO 主題中的範例應用程式＞。
如同共同作業同步處理的架構與類別中所述，同步處理可發生在 DbSyncProvider 的兩個執行個體之間、SqlCeSyncProvider 的兩個執行個體之間，或是每一個的一個執行個體上。本主題的範例程式碼是來自兩層式應用程式，因此它不會示範兩個 SqlCeSyncProvider 執行個體的同步處理，因為這種處理方式需要 N 層式組態。如需 N 層式組態的範例，請參閱 Sync Framework SDK 中所隨附的範例 WebSharingAppDemo-CEProviderEndToEnd。
設定同步處理包含下列步驟：
建立追蹤資料表來儲存中繼資料，並建立預存程序來更新每個伺服器資料庫中的資料和中繼資料。如需詳細資訊，請參閱 HOW TO：佈建伺服器資料庫來進行共同作業同步處理 (非 SQL Server)。
使用結構描述、資料和變更追蹤基礎結構初始化每個伺服器資料庫。
執行同步處理包含下列步驟：
建立伺服器同步處理配接器和同步處理提供者及用戶端同步處理提供者。
初始化每一個用戶端資料庫。
建立同步處理協調者並同步處理節點。
初始化伺服器資料庫
初始化資料庫牽涉到將資料表結構描述和變更追蹤基礎結構及任何需要的初始資料複製到每一個資料庫。如果是使用 DbSyncProvider 同步處理的資料庫，Sync Framework 不會在每一個資料庫中自動建立資料表結構描述或追蹤基礎結構。應用程式必須先確定有這些物件存在，才能嘗試同步處理節點。您可以使用備份和還原或其他技術，將物件複製到每一個節點上，但是只有在變更不是發生於擷取備份的資料庫中時，才可以進行這項處理。如果第一個資料庫在使用中而且正在更新，我們強烈建議您務必僅複製結構描述和變更追蹤基礎結構至每個節點，然後使用 Sync Framework 複製資料。如果您使用其他方法複製資料，節點可能會漏失已在第一個節點上認可的變更。只要至少有一個節點具有資料，Sync Framework 就可以在每個節點上初始化資料。本主題中的範例程式碼使用如下作法：每個資料庫包含兩個資料表，但只有 SyncSamplesDb_Peer1 中的資料表包含資料。資料是在第一個同步處理工作階段期間複製到其他節點。
如需 SqlCeSyncProvider 初始化選項的詳細資訊，請參閱本主題稍後的初始化用戶端資料庫。
執行同步處理
本節的程式碼範例分成下列幾個類別：
將在伺服器資料庫中同步處理之每個資料表的 DbSyncAdapter 物件和命令。
伺服器的 DbSyncProvider 物件和命令。
用戶端的 SqlCeSyncProvider 物件。
用來初始化用戶端資料庫的 SyncSchema 物件和 GenerateSnapshot 方法。
用來初始化伺服器和用戶端資料庫的 SyncOrchestrator 物件。
同步處理配接器
下列屬性會設定在每一個資料表的 DbSyncAdapter 物件上。
| 同步處理配接器屬性 | 使用方式 |
|---|---|
指定資料表的主索引鍵資料行。例如，如果 |
|
透過聯結基底資料表及其變更追蹤資料表，選取自上次同步處理工作階段以來的所有變更。這是可進行同步處理的最大變更集。說明「同步處理知識」(Synchronization Knowledge) 時，這一組變更可能會減少。 |
|
將從另一個節點選取的插入、更新和刪除套用至節點。這些變更是透過使用您為 SelectIncrementalChangesCommand 屬性指定的查詢或程序進行選取。 |
|
InsertMetadataCommand、UpdateMetadataCommand 和 DeleteMetadataCommand |
在節點上更新變更追蹤資料表，以反映從節點選取及套用至另一個節點的變更。這些更新讓 Sync Framework 能夠追蹤發生變更的位置與時間。 |
選取同步處理期間發生衝突之資料列的中繼資料。 |
|
選取可以在節點上清除的中繼資料。清除一般是保留性的：保存中繼資料供特定時間使用。但是應用程式可以使用其他邏輯，以判斷何時清除中繼資料。如需詳細資訊，請參閱 HOW TO：清除共同作業同步處理的中繼資料 (非 SQL Server)。 |
本節的程式碼範例會建立 DbSyncAdapter 物件在同步處理期間執行的命令。這些命令會呼叫 HOW TO：佈建伺服器資料庫來進行共同作業同步處理 (非 SQL Server)中所述的預存程序。本主題結尾的完整程式碼範例中有包含完整的命令集。在此程式碼中，"@" + DbSyncSession 格式的參數為工作階段變數。例如，"@" + DbSyncSession.SyncMinTimestamp 為解析成 @sync_min_timestamp 值的變數。如需詳細資訊，請參閱 HOW TO：使用共同作業同步處理的工作階段變數。
SelectIncrementalChangesCommand 的應用程式程式碼
SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" & DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
UpdateCommand 的應用程式程式碼
SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate";
updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.UpdateCommand = updCustomerCmd;
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
UpdateMetadataCommand 的應用程式程式碼
SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
SelectRowCommand 的應用程式程式碼
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sync.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectRowCommand = selRowCustomerCmd;
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
End With
adapterCustomer.SelectRowCommand = selRowCustomerCmd
定義所有命令之後，配接器會藉由使用下列程式碼加入至提供者。
sampleProvider.SyncAdapters.Add(adapterCustomer);
伺服器同步處理提供者
下列屬性會設定在伺服器的 DbSyncProvider 物件上。
| 同步處理提供者屬性 | 使用方式 |
|---|---|
要針對特定工作階段同步處理的範圍名稱。每個範圍都應該已經存在於伺服器資料庫的範圍資訊資料表中。 |
|
Sync Framework 選取及套用變更至伺服器資料庫所透過的連接。 |
|
傳回時間戳記值，用來選取及套用變更集至每個資料庫。在目前的同步處理工作階段期間，此命令會提供新的時間戳記值。上次同步處理工作階段 timestamp 值之後的變更，與新 timestamp 值之前的變更會進行同步處理。然後新值會儲存並用來做為下一個工作階段的起點。 |
|
從每個基底資料表或追蹤資料表中選取最大時間戳記值，以判斷每個資料表的目的地是否都已經具有來源的所有變更。如果目的地已變更，Sync Framework 通常可以避免執行列舉查詢來提升效能。 這個屬性是選擇性，如果未指定命令，將會針對所有資料表執行列舉查詢。 |
|
從範圍資訊資料表傳回資訊，例如同步處理知識及 Sync Framework 所需要的清除知識。 |
|
更新範圍資訊資料表中的資訊。 |
|
針對指定之範圍中且同樣包含在其他範圍中的所有資料表傳回範圍名稱和資料表名稱。 |
|
針對範圍資訊資料表中的特定範圍更新 |
本節的程式碼範例會建立 DbSyncProvider 物件在同步處理期間執行的命令。SelectScopeInfoCommand 和 UpdateScopeInfoCommand 屬性的命令包含於本主題結尾的完整程式碼範例中。
ScopeName 和 Connection 的應用程式程式碼
下列程式碼範例會設定要同步處理的範圍名稱以及同步處理所透過的連接。在完整的程式碼範例中，DbSyncProvider 物件包含資料表 Customer 的 DbSyncAdapter 物件。當第一次同步處理這個資料表時，便會設定 Sales 範圍的定義。當範圍初次同步處理後，就不應該變更此範圍。變更範圍中的資料表或篩選這些資料表的子句可能會導致非聚合的資料。
SqlConnection peerConnection = new SqlConnection(peerConnString);
sampleDbProvider.Connection = peerConnection;
sampleDbProvider.ScopeName = "Sales";
Dim peerConnection As New SqlConnection(peerConnString)
sampleDbProvider.Connection = peerConnection
sampleDbProvider.ScopeName = "Sales"
如需範圍的詳細資訊，請參閱 HOW TO：佈建伺服器資料庫來進行共同作業同步處理 (非 SQL Server)中的＜定義要同步處理的範圍＞。
SelectNewTimestampCommand 的應用程式程式碼
下列程式碼範例會建立 SelectNewTimestampCommand 屬性的命令。
SqlCommand selectNewTimestampCommand = new SqlCommand();
string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
Dim selectNewTimestampCommand As New SqlCommand()
Dim newTimestampVariable As String = "@" & DbSyncSession.SyncNewTimestamp
With selectNewTimestampCommand
.CommandText = "SELECT " & newTimestampVariable & " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand
SelectTableMaxTimestampsCommand 的應用程式程式碼
下列程式碼範例會建立 SelectTableMaxTimestampsCommand 屬性的命令。在完整的程式碼範例中，有任一節點上未進行任何新變更的同步處理工作階段存在。在這些工作階段中，SelectTableMaxTimestampsCommand 的呼叫表示沒有任何資料變更可同步處理，所以不會呼叫 SelectIncrementalChangesCommand。
SqlCommand selTableMaxTsCmd = new SqlCommand();
selTableMaxTsCmd.CommandType = CommandType.Text;
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
"MAX(local_update_peer_timestamp) AS max_timestamp " +
"FROM Sync.Customer_Tracking";
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
Dim selTableMaxTsCmd As New SqlCommand()
selTableMaxTsCmd.CommandType = CommandType.Text
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
& "MAX(local_update_peer_timestamp) AS max_timestamp " _
& "FROM Sync.Customer_Tracking"
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd
用戶端同步處理提供者
下列程式碼範例會建立 SqlCeSyncProvider 物件、設定範圍和連接以及註冊事件處理常式。如果是 SqlCeSyncProvider，配接器會是私用性質，而且將會根據用戶端資料庫中起始的結構描述來自動建立其命令。
public SqlCeSyncProvider ConfigureCeSyncProvider(string sqlCeConnString)
{
SqlCeSyncProvider sampleCeProvider = new SqlCeSyncProvider();
//Set the scope name
sampleCeProvider.ScopeName = "Sales";
//Set the connection
sampleCeProvider.Connection = new SqlCeConnection(sqlCeConnString);
//Register event handlers
//Register the BeginSnapshotInitialization event handler.
//It is called when snapshot initialization is about to begin
//for a particular scope in a Compact database.
sampleCeProvider.BeginSnapshotInitialization += new EventHandler<DbBeginSnapshotInitializationEventArgs>(sampleCeProvider_BeginSnapshotInitialization);
//Register the EndSnapshotInitialization event handler.
//It is called when snapshot initialization has completed
//for a particular scope in a Compact database.
sampleCeProvider.EndSnapshotInitialization += new EventHandler<DbEndSnapshotInitializationEventArgs>(sampleCeProvider_EndSnapshotInitialization);
return sampleCeProvider;
}
Public Function ConfigureCeSyncProvider(ByVal sqlCeConnString As String) As SqlCeSyncProvider
Dim sampleCeProvider As New SqlCeSyncProvider()
'Set the scope name
sampleCeProvider.ScopeName = "Sales"
'Set the connection
sampleCeProvider.Connection = New SqlCeConnection(sqlCeConnString)
'Register event handlers
'Register the BeginSnapshotInitialization event handler.
'It is called when snapshot initialization is about to begin
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.BeginSnapshotInitialization, AddressOf sampleCeProvider_BeginSnapshotInitialization
'Register the EndSnapshotInitialization event handler.
'It is called when snapshot initialization has completed
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.EndSnapshotInitialization, AddressOf sampleCeProvider_EndSnapshotInitialization
Return sampleCeProvider
End Function
初始化用戶端資料庫
在 SQL Server Compact 用戶端資料庫可以開始從另一個資料庫接收變更之前，此用戶端資料庫必須包含資料表結構描述及任何初始資料，以及 Sync Framework 所需的變更追蹤基礎結構。Sync Framework 提供兩個方法來初始化資料庫。
使用伺服器資料庫來完整初始化用戶端資料庫
如此可在用戶端資料庫中建立資料表，其方式是使用連接至伺服器資料庫執行個體的 DbSyncProvider 物件所公開的資料表、資料行及型別描述資料表。然後會準備用戶端資料庫進行同步處理，而伺服器資料庫中的所有資料列都會下載為累加式插入。從伺服器中選取插入，其方式是使用您為 SelectIncrementalChangesCommand 屬性所指定的查詢或程序。
使用已存在的用戶端資料庫針對此用戶端資料庫進行快照集初始化
快照集初始化的用意是要減少初始化用戶端資料庫所需的時間。當已經使用完整初始化來初始化一個用戶端資料庫之後，後續的資料庫可以使用第一個用戶端資料庫的「快照集」來初始化。快照集是特別準備的 SQL Server Compact 資料庫，其中包含資料表結構描述、資料 (選擇性) 和變更追蹤基礎結構。請將這個快照集複製到需要它的每個用戶端。在用戶端的第一個同步處理工作階段期間，將會更新用戶端特有的中繼資料，而且在建立快照集之後所發生的所有變更都會下載到用戶端資料庫。
注意
只有當 SQL Server Compact 資料庫中沒有任何活動時，才應該產生快照集。在快照集產生期間，並不支援任何型別的並行作業。
下列程式碼範例會執行完整的初始化。此程式碼會檢查用戶端資料庫是否在每次發生同步處理時都需要結構描述。如果提供者具有已經初始化之用戶端資料庫或者是快照集資料庫的連接，ScopeExists 方法會傳回 true。如果此方法傳回 false，將會使用 GetScopeDescription 方法來擷取結構描述，並將其套用到用戶端資料庫。
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName, ceConn))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply(ceConn);
}
}
}
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConfig As New SqlCeSyncScopeProvisioning()
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName, ceConn) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply(ceConn)
End If
End If
End Sub
End Class
下列程式碼範例會產生快照集。此程式碼會連接到剛剛使用範例的 ConfigureCESyncProvider 方法所同步處理的 SQL Server Compact 資料庫。在連接之後將會呼叫 GenerateSnapshot 方法，這樣會建立原始資料庫的複本。當用戶端提供者後續連接到這個新複本，而且執行了同步處理時，就會初始化資料庫。
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
注意
在這個範例中，當主要的應用程式執行時會產生快照集資料庫。在許多案例中，快照集會在離峰時段產生，以便與其他初始化活動區隔。
建立同步處理協調者及同步處理資料庫
本主題之前章節所述的程式碼會示範如何設定同步處理所需的屬性。現在該同步處理節點了。節點一定會以成對方式同步處理，例如 SyncSamplesDb_Peer1 和 SyncSampleCe2。如果是實際執行的應用程式，一般都會部署一份應用程式至每個節點，以便從任何一個節點起始同步處理。
下列程式碼範例會建立衍生自 SyncOrchestrator 的 SampleSyncAgent 類別。SampleSyncAgent 建構函式會採用兩個 RelationalSyncProvider 物件。因為 DbSyncProvider 和 SqlCeSyncProvider 都衍生自 RelationalSyncProvider，所以 SampleSyncAgent 物件可以同步處理這兩個提供者型別的任何組合。程式碼會指定哪個提供者是本機提供者以及哪個提供者是遠端提供者。然後，程式碼會指定變更要先從遠端資料庫上傳至本機資料庫，然後才能以另一個方向下載。程式碼會檢查其中一個提供者是否為 SqlCeSyncProvider 物件，此物件需要針對用戶端資料庫進行結構描述初始化。
public class SampleSyncAgent : SyncOrchestrator
{
public SampleSyncAgent(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
//Check to see if any provider is a SqlCe provider and if it needs to
//be initialized.
CheckIfProviderNeedsSchema(localProvider as SqlCeSyncProvider, remoteProvider as DbSyncProvider);
CheckIfProviderNeedsSchema(remoteProvider as SqlCeSyncProvider, localProvider as DbSyncProvider);
}
//For Compact databases that are not initialized with a snapshot,
//get the schema and initial data from a server database.
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName, ceConn))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply(ceConn);
}
}
}
}
Public Class SampleSyncAgent
Inherits SyncOrchestrator
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
'Check to see if any provider is a SqlCe provider and if it needs to
'be initialized.
CheckIfProviderNeedsSchema(TryCast(localProvider, SqlCeSyncProvider), _
TryCast(remoteProvider, DbSyncProvider))
CheckIfProviderNeedsSchema(TryCast(remoteProvider, SqlCeSyncProvider), _
TryCast(localProvider, DbSyncProvider))
End Sub
'For Compact databases that are not initialized with a snapshot,
'get the schema and initial data from a server database.
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConfig As New SqlCeSyncScopeProvisioning()
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName, ceConn) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply(ceConn)
End If
End If
End Sub
End Class
下列程式碼會設定同步處理工作階段，其方式是呼叫 ConfigureDbSyncProvider 或 ConfigureCeSyncProvider 方法，範例應用程式會使用該方法來為每一個工作階段設定適當的提供者屬性。然後此程式碼會呼叫 SampleSyncAgent 的 Synchronize 方法來同步處理每一對資料庫。
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
//First session: Synchronize two databases by using two instances
//of DbSyncProvider.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
'First session: Synchronize two databases by using two instances
'of DbSyncProvider.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
完整的程式碼範例
以下完整程式碼範例包含了先前所描述的程式碼範例及其他程式碼，以便顯示同步處理統計資料及事件資訊。此範例需要 Utility 類別，詳情請參閱資料庫提供者公用程式類別的 HOW-TO 主題。
using System;
using System.Collections.Generic;
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.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The Utility class handles all functionality that is not
//directly related to synchronization, such as holding peerConnection
//string information and making changes to the server database.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
//First session: Synchronize two databases by using two instances
//of DbSyncProvider.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Make a change in one of the databases.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer");
try
{
//Subsequent synchronization. Changes are now synchronized between all
//nodes.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Return data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1);
Utility.CleanUpNode(Utility.ConnStr_DbSync2);
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncOrchestrator.
public class SampleSyncAgent : SyncOrchestrator
{
public SampleSyncAgent(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
//Check to see if any provider is a SqlCe provider and if it needs to
//be initialized.
CheckIfProviderNeedsSchema(localProvider as SqlCeSyncProvider, remoteProvider as DbSyncProvider);
CheckIfProviderNeedsSchema(remoteProvider as SqlCeSyncProvider, localProvider as DbSyncProvider);
}
//For Compact databases that are not initialized with a snapshot,
//get the schema and initial data from a server database.
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName, ceConn))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply(ceConn);
}
}
}
}
public class SampleSyncProvider
{
public SqlCeSyncProvider ConfigureCeSyncProvider(string sqlCeConnString)
{
SqlCeSyncProvider sampleCeProvider = new SqlCeSyncProvider();
//Set the scope name
sampleCeProvider.ScopeName = "Sales";
//Set the connection
sampleCeProvider.Connection = new SqlCeConnection(sqlCeConnString);
//Register event handlers
//Register the BeginSnapshotInitialization event handler.
//It is called when snapshot initialization is about to begin
//for a particular scope in a Compact database.
sampleCeProvider.BeginSnapshotInitialization += new EventHandler<DbBeginSnapshotInitializationEventArgs>(sampleCeProvider_BeginSnapshotInitialization);
//Register the EndSnapshotInitialization event handler.
//It is called when snapshot initialization has completed
//for a particular scope in a Compact database.
sampleCeProvider.EndSnapshotInitialization += new EventHandler<DbEndSnapshotInitializationEventArgs>(sampleCeProvider_EndSnapshotInitialization);
return sampleCeProvider;
}
public void sampleCeProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
Console.WriteLine("Full Initialization Process Started.....");
Console.WriteLine(
string.Format("CreatingSchame Event fired for Database {0}", e.Connection.Database)
);
}
public void sampleCeProvider_BeginSnapshotInitialization(object sender, DbBeginSnapshotInitializationEventArgs e)
{
Console.WriteLine("");
Console.WriteLine("Snapshot initialization process started");
Console.WriteLine(
string.Format("BeginSnapshotInitialization event fired for scope {0}", e.ScopeName)
);
}
public void sampleCeProvider_EndSnapshotInitialization(object sender, DbEndSnapshotInitializationEventArgs e)
{
Console.WriteLine("EndSnapshotInitialization event fired");
Dictionary<string, DbSnapshotInitializationTableStatistics> tableStats =
e.TableInitializationStatistics;
foreach (string tableName in tableStats.Keys)
{
DbSnapshotInitializationTableStatistics ts = tableStats[tableName];
Console.WriteLine("\tTable Name: " + tableName);
Console.WriteLine("\tTotal Rows: " + ts.TotalRows);
Console.WriteLine("\tRows Intialized: " + ts.RowsInitialized);
Console.WriteLine("\tStart Time: " + ts.StartTime);
Console.WriteLine("\tEnd Time: " + ts.EndTime);
}
Console.WriteLine("Snapshot initialization process completed");
Console.WriteLine("");
}
public DbSyncProvider ConfigureDbSyncProvider(string peerConnString)
{
DbSyncProvider sampleDbProvider = new DbSyncProvider();
SqlConnection peerConnection = new SqlConnection(peerConnString);
sampleDbProvider.Connection = peerConnection;
sampleDbProvider.ScopeName = "Sales";
//Create a DbSyncAdapter object for the Customer table and associate it
//with the DbSyncProvider. Following the DataAdapter style in ADO.NET,
//DbSyncAdapter is the equivalent for synchronization. The commands that
//are specified for the DbSyncAdapter object call stored procedures
//that are created in each peer database.
DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer");
//Specify the primary key, which Sync Framework uses
//to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId");
//Specify the command to select incremental changes.
//In this command and other commands, session variables are
//used to pass information at runtime. DbSyncSession.SyncMetadataOnly
//and SyncMinTimestamp are two of the string constants that
//the DbSyncSession class exposes. You could also include
//@sync_metadata_only and @sync_min_timestamp directly in your
//queries:
//* sync_metadata_only is used by Sync Framework as an optimization
// in some queries.
//* The value of the sync_min_timestamp session variable is compared to
// values in the sync_row_timestamp column in the tracking table to
// determine which rows to select.
SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;
//Specify the command to insert rows.
//The sync_row_count session variable is used in this command
//and other commands to return a count of the rows affected by an operation.
//A count of 0 indicates that an operation failed.
SqlCommand insCustomerCmd = new SqlCommand();
insCustomerCmd.CommandType = CommandType.StoredProcedure;
insCustomerCmd.CommandText = "Sync.sp_Customer_ApplyInsert";
insCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
insCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.InsertCommand = insCustomerCmd;
//Specify the command to update rows.
//The value of the sync_min_timestamp session variable is compared to
//values in the sync_row_timestamp column in the tracking table to
//determine which rows to update.
SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate";
updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.UpdateCommand = updCustomerCmd;
//Specify the command to delete rows.
//The value of the sync_min_timestamp session variable is compared to
//values in the sync_row_timestamp column in the tracking table to
//determine which rows to delete.
SqlCommand delCustomerCmd = new SqlCommand();
delCustomerCmd.CommandType = CommandType.StoredProcedure;
delCustomerCmd.CommandText = "Sync.sp_Customer_ApplyDelete";
delCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.DeleteCommand = delCustomerCmd;
//Specify the command to select any conflicting rows.
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sync.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectRowCommand = selRowCustomerCmd;
//Specify the command to insert metadata rows.
//The session variables in this command relate to columns in
//the tracking table.
SqlCommand insMetadataCustomerCmd = new SqlCommand();
insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
insMetadataCustomerCmd.CommandText = "Sync.sp_Customer_InsertMetadata";
insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd;
//Specify the command to update metadata rows.
SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;
//Specify the command to delete metadata rows.
SqlCommand delMetadataCustomerCmd = new SqlCommand();
delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
delMetadataCustomerCmd.CommandText = "Sync.sp_Customer_DeleteMetadata";
delMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd;
//Add the adapter to the provider.
sampleDbProvider.SyncAdapters.Add(adapterCustomer);
// Configure commands that relate to the provider itself rather
// than the DbSyncAdapter object for each table:
// * SelectNewTimestampCommand: Returns the new high watermark for
// the current synchronization session.
// * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge,
// and a scope version (timestamp).
// * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.
// * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table
// or tracking table, to determine whether for each table the destination already
// has all of the changes from the source. If a destination table has all the changes,
// SelectIncrementalChangesCommand is not called for that table.
// There are additional commands related to metadata cleanup that are not
// included in this application.
//Select a new timestamp.
//During each synchronization, the new value and
//the last value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
SqlCommand selectNewTimestampCommand = new SqlCommand();
string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
//Specify the command to select local replica metadata.
SqlCommand selReplicaInfoCmd = new SqlCommand();
selReplicaInfoCmd.CommandType = CommandType.Text;
selReplicaInfoCmd.CommandText = "SELECT " +
"scope_id, " +
"scope_local_id, " +
"scope_sync_knowledge, " +
"scope_tombstone_cleanup_knowledge, " +
"scope_timestamp " +
"FROM Sync.ScopeInfo " +
"WHERE scope_name = @" + DbSyncSession.SyncScopeName;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd;
//Specify the command to update local replica metadata.
SqlCommand updReplicaInfoCmd = new SqlCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "UPDATE Sync.ScopeInfo SET " +
"scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
"scope_id = @" + DbSyncSession.SyncScopeId + ", " +
"scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
"WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
" ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
"SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
//Return the maximum timestamp from the Customer_Tracking table.
//If more tables are synchronized, the query should UNION
//all of the results. The table name is not schema-qualified
//in this case because the name was not schema qualified in the
//DbSyncAdapter constructor.
SqlCommand selTableMaxTsCmd = new SqlCommand();
selTableMaxTsCmd.CommandType = CommandType.Text;
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
"MAX(local_update_peer_timestamp) AS max_timestamp " +
"FROM Sync.Customer_Tracking";
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
return sampleDbProvider;
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
Console.WriteLine(String.Empty);
}
}
}
}
Imports System
Imports System.Collections.Generic
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.SqlServerCe
Class Program
Shared Sub Main(ByVal args As String())
'The Utility class handles all functionality that is not
'directly related to synchronization, such as holding connection
'string information and making changes to the server database.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
'First session: Synchronize two databases by using two instances
'of DbSyncProvider.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Make a change in one of the databases.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer")
Try
'Subsequent synchronization. Changes are now synchronized between all
'nodes.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Return data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1)
Utility.CleanUpNode(Utility.ConnStr_DbSync2)
'Exit.
Console.Write(vbLf & "Press Enter to close the window.")
Console.ReadLine()
End Sub
'Create a class that is derived from
'Microsoft.Synchronization.SyncOrchestrator.
Public Class SampleSyncAgent
Inherits SyncOrchestrator
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
'Check to see if any provider is a SqlCe provider and if it needs to
'be initialized.
CheckIfProviderNeedsSchema(TryCast(localProvider, SqlCeSyncProvider), _
TryCast(remoteProvider, DbSyncProvider))
CheckIfProviderNeedsSchema(TryCast(remoteProvider, SqlCeSyncProvider), _
TryCast(localProvider, DbSyncProvider))
End Sub
'For Compact databases that are not initialized with a snapshot,
'get the schema and initial data from a server database.
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConfig As New SqlCeSyncScopeProvisioning()
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName, ceConn) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply(ceConn)
End If
End If
End Sub
End Class
Public Class SampleSyncProvider
Public Function ConfigureCeSyncProvider(ByVal sqlCeConnString As String) As SqlCeSyncProvider
Dim sampleCeProvider As New SqlCeSyncProvider()
'Set the scope name
sampleCeProvider.ScopeName = "Sales"
'Set the connection
sampleCeProvider.Connection = New SqlCeConnection(sqlCeConnString)
'Register event handlers
'Register the BeginSnapshotInitialization event handler.
'It is called when snapshot initialization is about to begin
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.BeginSnapshotInitialization, AddressOf sampleCeProvider_BeginSnapshotInitialization
'Register the EndSnapshotInitialization event handler.
'It is called when snapshot initialization has completed
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.EndSnapshotInitialization, AddressOf sampleCeProvider_EndSnapshotInitialization
Return sampleCeProvider
End Function
Public Sub sampleCeProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
Console.WriteLine("Full initialization process started...")
Console.WriteLine(String.Format("CreatingSchema event fired for database {0}", e.Connection.Database))
End Sub
Public Sub sampleCeProvider_BeginSnapshotInitialization(ByVal sender As Object, ByVal e As DbBeginSnapshotInitializationEventArgs)
Console.WriteLine("")
Console.WriteLine("Snapshot initialization process started...")
Console.WriteLine(String.Format("BeginSnapshotInitialization event fired for scope {0}", e.ScopeName))
End Sub
Public Sub sampleCeProvider_EndSnapshotInitialization(ByVal sender As Object, ByVal e As DbEndSnapshotInitializationEventArgs)
Console.WriteLine("EndSnapshotInitialization event fired")
Dim tableStats As Dictionary(Of String, DbSnapshotInitializationTableStatistics) = e.TableInitializationStatistics
For Each tableName As String In tableStats.Keys
Dim ts As DbSnapshotInitializationTableStatistics = tableStats(tableName)
Console.WriteLine(vbTab & "Table Name: " & tableName)
Console.WriteLine(vbTab & "Total Rows: " & ts.TotalRows)
Console.WriteLine(vbTab & "Rows Intialized: " & ts.RowsInitialized)
Console.WriteLine(vbTab & "Start Time: " & ts.StartTime)
Console.WriteLine(vbTab & "End Time: " & ts.EndTime)
Next
Console.WriteLine("Snapshot initialization process completed")
Console.WriteLine("")
End Sub
Public Function ConfigureDbSyncProvider(ByVal peerConnString As String) As DbSyncProvider
Dim sampleDbProvider As New DbSyncProvider()
Dim peerConnection As New SqlConnection(peerConnString)
sampleDbProvider.Connection = peerConnection
sampleDbProvider.ScopeName = "Sales"
'Create a DbSyncAdapter object for the Customer table and associate it
'with the DbSyncProvider. Following the DataAdapter style in ADO.NET,
'DbSyncAdapter is the equivalent for synchronization. The commands that
'are specified for the DbSyncAdapter object call stored procedures
'that are created in each peer database.
Dim adapterCustomer As New DbSyncAdapter("Customer")
'Specify the primary key, which Sync Framework uses
'to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId")
'Specify the command to select incremental changes.
'In this command and other commands, session variables are
'used to pass information at runtime. DbSyncSession.SyncMetadataOnly
'and SyncMinTimestamp are two of the string constants that
'the DbSyncSession class exposes. You could also include
'@sync_metadata_only and @sync_min_timestamp directly in your
'queries:
'* sync_metadata_only is used by Sync Framework as an optimization
' in some queries.
'* The value of the sync_min_timestamp session variable is compared to
' values in the sync_row_timestamp column in the tracking table to
' determine which rows to select.
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" & DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
'Specify the command to insert rows.
'The sync_row_count session variable is used in this command
'and other commands to return a count of the rows affected by an operation.
'A count of 0 indicates that an operation failed.
Dim insCustomerCmd As New SqlCommand()
With insCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyInsert"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.InsertCommand = insCustomerCmd
'Specify the command to update rows.
'The value of the sync_min_timestamp session variable is compared to
'values in the sync_row_timestamp column in the tracking table to
'determine which rows to update.
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
'Specify the command to delete rows.
'The value of the sync_min_timestamp session variable is compared to
'values in the sync_row_timestamp column in the tracking table to
'determine which rows to delete.
Dim delCustomerCmd As New SqlCommand()
With delCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyDelete"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.DeleteCommand = delCustomerCmd
'Specify the command to select any conflicting rows.
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
End With
adapterCustomer.SelectRowCommand = selRowCustomerCmd
'Specify the command to insert metadata rows.
'The session variables in this command relate to columns in
'the tracking table.
Dim insMetadataCustomerCmd As New SqlCommand()
With insMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_InsertMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd
'Specify the command to update metadata rows.
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
'Specify the command to delete metadata rows.
Dim delMetadataCustomerCmd As New SqlCommand()
With delMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_DeleteMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd
'Add the adapter to the provider.
sampleDbProvider.SyncAdapters.Add(adapterCustomer)
' Configure commands that relate to the provider itself rather
' than the DbSyncAdapter object for each table:
' * SelectNewTimestampCommand: Returns the new high watermark for
' the current synchronization session.
' * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge,
' and a scope version (timestamp).
' * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.
' * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table
' or tracking table, to determine whether for each table the destination already
' has all of the changes from the source. If a destination table has all the changes,
' SelectIncrementalChangesCommand is not called for that table.
' There are additional commands related to metadata cleanup that are not
' included in this application.
'Select a new timestamp.
'During each synchronization, the new value and
'the last value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
Dim selectNewTimestampCommand As New SqlCommand()
Dim newTimestampVariable As String = "@" & DbSyncSession.SyncNewTimestamp
With selectNewTimestampCommand
.CommandText = "SELECT " & newTimestampVariable & " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand
'Specify the command to select local replica metadata.
Dim selReplicaInfoCmd As New SqlCommand()
With selReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "SELECT " _
& "scope_id, " _
& "scope_local_id, " _
& "scope_sync_knowledge, " _
& "scope_tombstone_cleanup_knowledge, " _
& "scope_timestamp " _
& "FROM Sync.ScopeInfo " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName
.Parameters.Add("@" & DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
End With
sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd
'Specify the command to update local replica metadata.
Dim updReplicaInfoCmd As New SqlCommand()
With updReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "UPDATE Sync.ScopeInfo SET " _
& "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
& "scope_id = @" + DbSyncSession.SyncScopeId + ", " _
& "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " _
& " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " _
& "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"
.Parameters.Add("@" & DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" & DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" & DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd
'Return the maximum timestamp from the Customer_Tracking table.
'If more tables are synchronized, the query should UNION
'all of the results. The table name is not schema-qualified
'in this case because the name was not schema qualified in the
'DbSyncAdapter constructor.
Dim selTableMaxTsCmd As New SqlCommand()
selTableMaxTsCmd.CommandType = CommandType.Text
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
& "MAX(local_update_peer_timestamp) AS max_timestamp " _
& "FROM Sync.Customer_Tracking"
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd
Return sampleDbProvider
End Function
End Class
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
Console.WriteLine([String].Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
Console.WriteLine([String].Empty)
End Sub
End Class
End Class
請參閱
概念
HOW TO：佈建伺服器資料庫來進行共同作業同步處理 (非 SQL Server)
同步處理其他 ADO.NET 相容的資料庫