如何清除协作同步的元数据（非 SQL Server）
备注
本节文档中的主题同步其他与 ADO.NET 兼容的数据库旨在说明如何通过使用 Sync Framework 同步并非 SQL Server 的数据库。在此版本中，SQL Server 用于代码示例中，但通过对特定于 SQL Server 的对象（例如 SqlConnection）以及所示的 SQL 查询进行一些修改，这些代码可用于其他与 ADO.NET 兼容的数据库。有关 SQL Server 同步的信息，请参见如何配置和执行协作同步 (SQL Server)。
本主题说明如何在 Sync Framework 中清除对等数据库同步的服务器元数据。本主题中的代码介绍以下 Sync Framework 类：
有关如何运行示例代码的信息，请参见同步其他与 ADO.NET 兼容的数据库中的“帮助主题中的示例应用程序”。
了解元数据清除
清除涉及删除已经从基表中删除的行的元数据。对等同步使用两种元数据：
用于跟踪同步的每个表的插入、更新和删除操作的表级元数据。
基表中的每一行都有一行元数据。如果从基表中删除某行并且作用域中的所有节点都已接收到该行，则可以安全地删除元数据行。
用于跟踪每个节点从其他节点接收到的变更的数据库级元数据。
对于每个节点数据库，此元数据通常存储在一个作用域表中。始终不应删除该作用域表中的行，除非该作用域被删除。
有关元数据的更多信息，请参见如何为协作同步设置服务器数据库（非 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 接收到删除操作之后发生的，同步将成功。
.gif "Note") 重要事项 |
|---|
有意运行以下示例代码将使示例数据库处于不一致状态。运行此代码后，删除这些数据库，然后通过执行用于数据库提供程序帮助主题的安装脚本中的脚本“用于协作方案的自定义变更跟踪”重新创建它们。 |
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