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 主題中的範例應用程式>。
了解中繼資料清除
清除包括刪除已自基底資料表刪除之資料列的中繼資料。對等同步處理使用兩種中繼資料:
追蹤每個同步處理之資料表的插入、更新和刪除項目的資料表層級中繼資料。
基底資料表中每個資料列都有一個中繼資料列。如果資料列已自基底資料表中刪除,而且所有範圍中的所有節點都已接到,便可以安全地刪除中繼資料列。
追蹤每個節點從其他節點所接到之變更的資料庫層級中繼資料。
這項中繼資料一般會儲存在每個節點資料庫的一個範圍資料表中。範圍資料表中的資料列絕對不可刪除,除非卸除此範圍。
如需中繼資料的詳細資訊,請參閱 HOW TO:佈建伺服器資料庫來進行共同作業同步處理 (非 SQL Server)中的<針對每個資料表的中繼資料建立追蹤資料表>。
中繼資料清除是由應用程式所處理。若為 SQL Server Compact 資料庫,請使用 SqlCeSyncStoreMetadataCleanup 物件:
PerformCleanup 是您從應用程式呼叫的方法。
RetentionInDays 是指定當呼叫 PerformCleanup 時要刪除多久之前之變更追蹤中繼資料 (以天為單位) 的屬性。
對於其他資料庫而言,中繼資料清除有三個元件:
您從應用程式呼叫的 CleanupMetadata 方法。
您為每個資料表之 DbSyncAdapter 物件的 SelectMetadataForCleanupCommand 屬性所指定的命令。CleanupMetadata 方法會使用這項指令來選取可以刪除的資料列。
您為 SelectOverlappingScopesCommand 和 UpdateScopeCleanupTimestampCommand 屬性所指定的命令:
CleanupMetadata 方法會在清除之前使用 SelectOverlappingScopesCommand,針對指定的範圍中同時也包含在其他範圍的所有資料表傳回範圍名稱和資料表名稱。
CleanupMetadata 方法會在清除之後使用 UpdateScopeCleanupTimestampCommand,針對 scope_info 資料表中的特定範圍更新 scope_cleanup_timestamp 資料行。這會標記一個點,此範圍所執行的清除將會到這個點為止。
清除命令與 DbSyncAdapter 物件上的其他命令不同,清除命令不會當做每個同步處理工作階段的一部分自動呼叫,只會在應用程式呼叫 CleanupMetadata 方法時呼叫。您為 SelectMetadataForCleanupCommand 屬性指定的命令可以使用適合您應用程式的任何邏輯,但一般來說,它是保留性的:早於特定時間長度的中繼資料會遭到刪除。如果某個節點嘗試同步處理中繼資料已經清除的變更,便會擲回 DbOutdatedSyncException 類型的例外狀況。隨即引發 SyncPeerOutdated 事件,此事件會提供 DbOutdatedEventArgs 物件的存取權。有兩個選項可處理這個事件:
將 Action 屬性設定為 PartialSync。這樣會針對有中繼資料存在的資料進行同步處理,但會遺漏某些刪除。
請將 Action 屬性設定為 AbortSync (預設值)。這樣會結束同步處理工作階段。用戶端應該在下一個同步處理工作階段重新初始化,以便讓它擁有正確的資料。
API 的主要部分
下列程式碼範例會指定 SelectMetadataForCleanupCommand 屬性的命令。呼叫的預存程序 sp_Customer_SelectMetadata
會以小時為單位的時間長度當做參數。這是中繼資料保留期。早於此期間的中繼資料會遭到清除。如果您傳遞值 -1 到程序,則不管時間早晚,所有中繼資料都會遭到清除。
注意
此範例示範一種中繼資料清除做法。查詢或程序沒任何需求,就可以使用保留值做為參數,或使用 -1 來表示所有中繼資料都應該清除。
SqlCommand selMetadataCustomerCmd = new SqlCommand();
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata";
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays;
selMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;
Dim selMetadataCustomerCmd As New SqlCommand()
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata"
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays
selMetadataCustomerCmd.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd
下列程式碼範例會建立由清除命令所呼叫的預存程序。
CREATE PROCEDURE Sync.sp_Customer_SelectMetadata
@metadata_aging_in_days int,
@sync_scope_local_id int
AS
IF @metadata_aging_in_days = -1
BEGIN
SELECT CustomerId,
local_update_peer_timestamp as sync_row_timestamp,
case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id)
then case when (restore_timestamp is null) then local_update_peer_timestamp else restore_timestamp end else scope_update_peer_timestamp end as sync_update_peer_timestamp,
case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id)
then local_update_peer_key else scope_update_peer_key end as sync_update_peer_key,
case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id)
then local_create_peer_timestamp else scope_create_peer_timestamp end as sync_create_peer_timestamp,
case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id)
then local_create_peer_key else scope_create_peer_key end as sync_create_peer_key
FROM Sync.Customer_Tracking
WHERE sync_row_is_tombstone = 1
END
ELSE
BEGIN
SELECT CustomerId,
local_update_peer_timestamp as sync_row_timestamp,
case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id)
then case when (restore_timestamp is null) then local_update_peer_timestamp else restore_timestamp end else scope_update_peer_timestamp end as sync_update_peer_timestamp,
case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id)
then local_update_peer_key else scope_update_peer_key end as sync_update_peer_key,
case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id)
then local_create_peer_timestamp else scope_create_peer_timestamp end as sync_create_peer_timestamp,
case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id)
then local_create_peer_key else scope_create_peer_key end as sync_create_peer_key
FROM Sync.Customer_Tracking
WHERE sync_row_is_tombstone = 1 AND
DATEDIFF(day, last_change_datetime, GETDATE()) > @metadata_aging_in_days
END
下列程式碼範例會指定 SelectOverlappingScopesCommand 屬性的命令。這個命令和下一個命令 (UpdateScopeCleanupTimestampCommand) 會讓 Sync Framework 在資料表包含於一個以上之範圍的情況下適當處理清除動作。
SqlCommand overlappingScopesCmd = new SqlCommand();
overlappingScopesCmd.CommandType = CommandType.StoredProcedure;
overlappingScopesCmd.CommandText = "Sync.sp_SelectSharedScopes";
overlappingScopesCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd;
Dim overlappingScopesCmd As New SqlCommand()
With overlappingScopesCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_SelectSharedScopes"
.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
End With
sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd
下列程式碼範例會建立由重疊範圍命令所呼叫的預存程序。
CREATE PROCEDURE Sync.sp_SelectSharedScopes
@sync_scope_name nvarchar(100)
AS
SELECT ScopeTableMap2.table_name AS sync_table_name,
ScopeTableMap2.scope_name AS sync_shared_scope_name
FROM Sync.ScopeTableMap ScopeTableMap1 JOIN Sync.ScopeTableMap ScopeTableMap2
ON ScopeTableMap1.table_name = ScopeTableMap2.table_name
AND ScopeTableMap1.scope_name = @sync_scope_name
WHERE ScopeTableMap2.scope_name <> @sync_scope_name
下列程式碼範例會指定 UpdateScopeCleanupTimestampCommand 屬性的命令。
SqlCommand updScopeCleanupInfoCmd = new SqlCommand();
updScopeCleanupInfoCmd.CommandType = CommandType.Text;
updScopeCleanupInfoCmd.CommandText = "UPDATE scope_info set " +
" scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp +
" WHERE scope_name = @" + DbSyncSession.SyncScopeName +
" AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp < @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" +
" SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt);
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;
Dim updScopeCleanupInfoCmd As New SqlCommand()
With updScopeCleanupInfoCmd
.CommandType = CommandType.Text
.CommandText = "UPDATE scope_info set " _
& " scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp _
& " WHERE scope_name = @" + DbSyncSession.SyncScopeName _
& " AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp < @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" _
& " SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"
.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd
下列程式碼範例會呼叫 CleanupMetadata 方法。此程式碼會具現化提供者,並呼叫 SampleSyncProvider
類別中的 ConfigureDbSyncProvider
方法。所有需要的 DbSyncAdapter 和 DbSyncProvider 屬性都在此類別中定義。其中包括 SelectMetadataForCleanupCommand 屬性。傳遞給 ConfigureDbSyncProvider
方法的值 7
是中繼資料保留期 (以天為單位)。
sampleSyncProvider = new SampleSyncProvider();
DbSyncProvider provider1 = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7);
if (provider1.CleanupMetadata() == true)
{
Console.WriteLine(String.Empty);
Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database.");
Console.WriteLine("Metadata more than 7 days old was deleted.");
}
else
{
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.");
}
sampleSyncProvider = New SampleSyncProvider()
Dim provider1 As DbSyncProvider = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7)
If provider1.CleanupMetadata() = True Then
Console.WriteLine([String].Empty)
Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database.")
Console.WriteLine("Metadata more than 7 days old was deleted.")
Else
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.")
End If
完整的程式碼範例
下列完整程式碼範例包含先前所描述的程式碼範例以及其他的程式碼,可用來變更並同步處理這些變更。該程式碼執行下列步驟:
同步處理 SyncSamplesDb_Peer1 (Node1) 和 SyncSamplesDb_Peer3 (Node3)。五個資料列上傳到 Node3。
同步處理 Node3 和 SyncSampleCe1 (CeNode1)。
更新在 Node1 上的資料列。
針對 Node1 上超過 7 天的中繼資料,呼叫 CleanupMetadata。CleanupMetadata 方法順利傳回,但是沒有清除任何中繼資料,因為沒有在 Node1 上進行任何超過 7 天的刪除作業。
同步處理 Node1 和 Node3。同步處理成功,因為所有相關中繼資料仍然可在兩個節點上使用。
從 Node3 刪除資料列。
針對 Node3 上的所有中繼資料,呼叫 CleanupMetadata。上一個步驟中刪除的中繼資料已清除。
同步處理 Node1 和 Node3。同步處理失敗,因為同步處理知識不再符合節點的狀態。擲回 DbOutdatedSyncException 型別的例外狀況。
只能清除其他節點不再需要的中繼資料,這一點很重要。如果第二個清除發生在 Node1 已從 Node3 接到刪除之後,同步處理就會成功。
注意
執行下列範例程式碼是有意讓範例資料庫處於不一致的狀態。在您執行此程式碼之後,請卸除資料庫,然後執行資料庫提供者的安裝指令碼 HOW-TO 主題中的 "Custom Change Tracking for Collaborative Scenarios" 指令碼,藉以重新建立資料庫。
using System;
using System.IO;
using System.Collections.Generic;
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);
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize.
sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
//The integer passed to ConfigureDbSyncProvider is how old that metadata
//can be (in days) before it is deleted when CleanupMetadata() is called.
//The integer value is only relevant if CleanupMetadata() is called, as
//demonstrated later in this application.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, 7));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, 7),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
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);
}
//Update a row on peer 1.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Instantiate a provider, connect to peer 1, and delete tombstone metadata that
//is older than 7 days.
sampleSyncProvider = new SampleSyncProvider();
DbSyncProvider provider1 = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7);
if (provider1.CleanupMetadata() == true)
{
Console.WriteLine(String.Empty);
Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database.");
Console.WriteLine("Metadata more than 7 days old was deleted.");
}
else
{
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.");
}
//Synchronize.
try
{
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, 7));
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);
}
//Delete a row on peer 3.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync3, "Customer");
//Instantiate a provider, connect to peer 3, and delete all tombstone metadata.
sampleSyncProvider = new SampleSyncProvider();
DbSyncProvider provider3 = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, -1);
if (provider3.CleanupMetadata() == true)
{
Console.WriteLine(String.Empty);
Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer3 database.");
Console.WriteLine("All metadata was deleted.");
}
else
{
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.");
}
//Synchronize.
try
{
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, 7));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine(String.Empty);
Console.WriteLine("Synchronization failed due to outdated synchronization knowledge,");
Console.WriteLine("which is expected in this sample application.");
Console.WriteLine("Drop and recreate the sample databases.");
Console.WriteLine(String.Empty);
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
Console.WriteLine(String.Empty);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Return peer data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1);
Utility.CleanUpNode(Utility.ConnStr_DbSync3);
//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);
return sampleCeProvider;
}
public DbSyncProvider ConfigureDbSyncProvider(string peerConnString, int metadataAgingInDays)
{
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;
//Specify the command to select metadata rows for cleanup.
SqlCommand selMetadataCustomerCmd = new SqlCommand();
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata";
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays;
selMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;
//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.
// * SelectOverlappingScopesCommand: returns the scope name and table name for all tables
// in the specified scope that are also included in other scopes.
// * UpdateScopeCleanupTimestampCommand: updates the scope_cleanup_timestamp column for a
// particular scope in the scope_info table, to mark the point up to which cleanup
// has been performed for the scope.
//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;
//Specify the command to select table and scope names for
//any tables that are in more than one scope.
SqlCommand overlappingScopesCmd = new SqlCommand();
overlappingScopesCmd.CommandType = CommandType.StoredProcedure;
overlappingScopesCmd.CommandText = "Sync.sp_SelectSharedScopes";
overlappingScopesCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd;
//Specify the command that updates the scope information table
//to indicate to which point metadata has been cleaned up for a scope.
SqlCommand updScopeCleanupInfoCmd = new SqlCommand();
updScopeCleanupInfoCmd.CommandType = CommandType.Text;
updScopeCleanupInfoCmd.CommandText = "UPDATE scope_info set " +
" scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp +
" WHERE scope_name = @" + DbSyncSession.SyncScopeName +
" AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp < @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" +
" SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt);
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;
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.IO
Imports System.Collections.Generic
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 peerConnection
'string information and making changes to the server database.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Dim sampleSyncProvider As New SampleSyncProvider()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize.
sampleSyncProvider = New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
'The integer passed to ConfigureDbSyncProvider is how old that metadata
'can be (in days) before it is deleted when CleanupMetadata() is called.
'The integer value is only relevant if CleanupMetadata() is called, as
'demonstrated later in this application.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider( _
Utility.ConnStr_DbSync1, 7), _
sampleSyncProvider.ConfigureDbSyncProvider( _
Utility.ConnStr_DbSync3, 7))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider( _
Utility.ConnStr_DbSync3, 7), _
sampleSyncProvider.ConfigureCeSyncProvider( _
Utility.ConnStr_SqlCeSync1))
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
'Update a row on peer 1.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Instantiate a provider, connect to peer 1, and delete tombstone metadata that
'is older than 7 days.
sampleSyncProvider = New SampleSyncProvider()
Dim provider1 As DbSyncProvider = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7)
If provider1.CleanupMetadata() = True Then
Console.WriteLine([String].Empty)
Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database.")
Console.WriteLine("Metadata more than 7 days old was deleted.")
Else
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.")
End If
'Synchronize.
Try
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider( _
Utility.ConnStr_DbSync1, 7), _
sampleSyncProvider.ConfigureDbSyncProvider( _
Utility.ConnStr_DbSync3, 7))
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
'Delete a row on peer 3.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync3, "Customer")
'Instantiate a provider, connect to peer 3, and delete all tombstone metadata.
sampleSyncProvider = New SampleSyncProvider()
Dim provider3 As DbSyncProvider = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, -1)
If provider3.CleanupMetadata() = True Then
Console.WriteLine([String].Empty)
Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer3 database.")
Console.WriteLine("All metadata was deleted.")
Else
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.")
End If
'Synchronize.
Try
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider( _
Utility.ConnStr_DbSync1, 7), _
sampleSyncProvider.ConfigureDbSyncProvider( _
Utility.ConnStr_DbSync3, 7))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
Catch ex As DbOutdatedSyncException
Console.WriteLine([String].Empty)
Console.WriteLine("Synchronization failed due to outdated synchronization knowledge,")
Console.WriteLine("which is expected in this sample application.")
Console.WriteLine("Drop and recreate the sample databases.")
Console.WriteLine([String].Empty)
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
& " Clean up knowledge: ") + ex.MissingCleanupKnowledge.ToString())
Console.WriteLine([String].Empty)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Return peer data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1)
Utility.CleanUpNode(Utility.ConnStr_DbSync3)
'Exit.
Console.Write(vbLf & "Press Enter to close the window.")
Console.ReadLine()
End Sub
End Class
'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)
Return sampleCeProvider
End Function
Public Function ConfigureDbSyncProvider(ByVal peerConnString As String, ByVal metadataAgingInDays As Integer) 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
'Specify the command to select metadata rows for cleanup.
Dim selMetadataCustomerCmd As New SqlCommand()
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata"
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays
selMetadataCustomerCmd.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd
'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.
' * SelectOverlappingScopesCommand: returns the scope name and table name for all tables
' in the specified scope that are also included in other scopes.
' * UpdateScopeCleanupTimestampCommand: updates the scope_cleanup_timestamp column for a
' particular scope in the scope_info table, to mark the point up to which cleanup
' has been performed for the scope.
'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
'Specify the command to select table and scope names for
'any tables that are in more than one scope.
Dim overlappingScopesCmd As New SqlCommand()
With overlappingScopesCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_SelectSharedScopes"
.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
End With
sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd
'Specify the command that updates the scope information table
'to indicate to which point metadata has been cleaned up for a scope.
Dim updScopeCleanupInfoCmd As New SqlCommand()
With updScopeCleanupInfoCmd
.CommandType = CommandType.Text
.CommandText = "UPDATE scope_info set " _
& " scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp _
& " WHERE scope_name = @" + DbSyncSession.SyncScopeName _
& " AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp < @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" _
& " SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"
.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd
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