通过

如何配置和执行协作同步 (SQL Server)

  • 项目

本主题介绍一个应用程序的几个要点，该应用程序使用 Sync Framework 来同步 SQL Server 和 SQL Server Compact 数据库。此应用程序中的代码主要涉及以下 Sync Framework 类：

有关如何运行示例代码的更多信息，请参见同步 SQL Server 和 SQL Server Compact中的“帮助主题中的示例应用程序”。

如用于协作同步的体系结构和类中所述，同步可以在 SqlSyncProvider 的两个实例之间、SqlCeSyncProvider 的两个实例之间和这两个提供程序的各一个实例之间发生。本主题中的示例代码出自一个双层应用程序；因此，它并不说明如何同步 SqlCeSyncProvider 的两个实例，因为后者要求 N 层配置。有关 N 层配置的示例，请参见 Sync Framework SDK 随附的示例 WebSharingAppDemo-CEProviderEndToEnd。

比较提供程序类型

本主题介绍如何通过使用在 Sync Framework 2.0 中引入的两个同步提供程序来同步 SQL Server 和 SQL Server Compact 数据库：SqlSyncProvider 和 SqlCeSyncProvider。Sync Framework 包括可同步这些数据库的其他提供程序，但这两个新的提供程序出于以下原因通常更适合于此任务：

  • SqlSyncProvider 在功能上与 DbSyncProvider 相同，但它所需的代码要少得多，而且也不需要对 Sync Framework 用于同步数据的查询有很全面的理解。DbSyncProvider 仍适合于非 SQL Server 数据库。

  • SqlSyncProvider 和 SqlCeSyncProvider 可用于客户端-服务器、对等和混合拓扑，而 DbServerSyncProvider 和 SqlCeClientSyncProvider 只适用于客户端-服务器拓扑。SqlSyncProvider 和 SqlCeSyncProvider 还支持更高级的功能，例如基于数据大小而非行数对变更进行批处理。

  • SqlSyncProvider 和 SqlCeSyncProvider 十分灵活且易于设置。这样，您可以同步 SQL Server 的所有版本，包括 SQL Server Express 和 SQL Server Compact。

设置节点和执行同步

在拓扑中同步节点可划分为两个阶段：设置要同步的节点，以及在一对节点之间实际执行同步。对于 SqlSyncProvider 和 SqlCeSyncProvider，设置节点由两个任务构成：

  1. 定义要同步的内容

    通过描述一个或多个“作用域”**来定义要同步的内容。作用域是表的集合，其中某些表或全部表可以筛选。表可以已存在于数据库中，也可以通过使用 Sync Framework 对象描述表，然后在同步基础存储区时在运行时生成它们。有关更多信息，请参见本主题后面的“理解作用域”部分。

    Note重要事项

    在首次同步某一作用域时，如果需要变更某一作用域，则与现有作用域相关联的元数据应删除后重新创建。

  2. 为 Sync Framework 变更跟踪设置数据库

    在描述了表和作用域后，您使用 Sync Framework 对象将设置脚本应用于各节点。这些脚本创建变更跟踪和变更应用基础结构，该基础结构由元数据表、触发器和存储过程构成。

在设置节点后，可以同步这些节点。从开发人员的视角来说，设置同步选项并调用 Synchronize() 更简明。在后台，Sync Framework 使用您指定的作用域和表说明信息为每个作用域和每个同步适配器（每个表一个）生成配置对象。这使得 Sync Framework 可以利用在各个数据库中持久保留的信息并为一对节点之间的每个同步会话生成它所需的信息。SqlSyncProvider 和 SqlCeSyncProvider 都知道在设置过程中创建的变更跟踪表和其他对象，并且它们自动生成所需的 DbSyncAdapter 对象。这将大为减少使用这些提供程序同步数据所需的代码。

下表列出了用于设置数据库和提供程序的类。

SQL Server SQL Server Compact 说明

DbSyncScopeDescription

DbSyncScopeDescription

表示同步作用域，同步作用域是作为一个单元同步的表的逻辑分组。

SqlSyncScopeProvisioning

SqlCeSyncScopeProvisioning

表示针对 DbSyncScopeDescription 对象表示的特定作用域的 SQL Server 或 SQL Server Compact 数据库的设置。

SqlSyncProviderScopeConfiguration

SqlCeSyncProviderScopeConfiguration

表示针对特定作用域的 SqlSyncProvider 或 SqlCeSyncProvider 使用的配置信息。

DbSyncTableDescription

DbSyncTableDescription

表示在同步作用域中包括的表的架构。

DbSyncColumnDescription

DbSyncColumnDescription

表示属于在同步作用域中包括的表的列的属性。

SqlSyncDescriptionBuilder

SqlCeSyncDescriptionBuilder

表示涉及同步的 SQL Server 或 SQL Server Compact 数据库的作用域和表信息。它用于从 SQL Server 或 SQL Server Compact 数据库提取出 Description 对象。

SqlSyncTableProvisioning

SqlSyncTableProvisioning

表示 DbSyncTableDescription 对象表示的 SQL Server 或 SQL Server Compact 数据库表（具有可选筛选器）的设置。

SqlSyncProviderAdapterConfiguration

SqlSyncProviderAdapterConfiguration

表示 SQL Server 或 SQL Server Compact 数据库中表的同步适配器配置信息。

除了这些主要类型之外，还有四种应该知道的其他重要类型：

了解作用域

备注

本节主题提供有关同步作用域的附加信息。您可以在此时直接转到“代码示例”，但如果您计划在应用程序中使用经过筛选的作用域或多个作用域，则建议您阅读这一节。

理解作用域是表和筛选器的“组合”**十分重要。例如，您可以定义一个名为 sales-WA 的经过筛选的作用域，该作用域只包含来自 customer_sales 表的华盛顿州的销售数据。如果您对同一个表定义其他筛选器，例如 sales-OR，则这是不同的作用域。如果您定义多个筛选器，请注意 Sync Framework 不自动处理不再满足筛选器条件的行的删除。例如，如果某一用户或应用程序更新列中用于筛选的值，则行将从一个作用域移到另一个作用域。该行将发送到该行现在所属于的新的作用域，但该行不从旧作用域中删除。您的应用程序必须处理此情况。

作用域可以完全不同，也可以彼此重叠。如果在它们之间共享公用数据，则这两个作用域将重叠。例如，表 products 可以包括在 sales 作用域和 inventory 作用域中。作用域可以既重叠又经过了筛选。以下应用场景说明筛选和重叠可能发生的方式：

  • 应用场景 1：

    • 作用域 1 是 sales-WA。该作用域包括：products；orders，具有 state=WA 的筛选器；以及 order_details，具有 state=WA 的筛选器。

    • 作用域 2 是 sales-OR。该作用域包括：products；orders，具有 state=OR 的筛选器；以及 order_details，具有 state=OR 的筛选器。

    在这个应用场景中，整个 products 表由两个作用域共享。orders 表和 order_details 表都位于这两个作用域中，但筛选器不重叠；因此，这两个作用域不共享这些表中的行。

  • 应用场景 2：

    • 作用域 1 是 sales-WA。该作用域包括：products；orders，具有 state=WA 的筛选器；以及 order_details，具有 state=WA 的筛选器。

    • 作用域 2 是 sales-Northwest。该作用域包括：products；orders，具有 state=WA OR state=ID 的筛选器；以及 shippers。

    在这个应用场景中，整个 products 表再次由两个作用域共享。orders 表位于这两个作用域中并且筛选器重叠：这两个作用域共享满足筛选器 state=WA 的行。shippers 表和 order_details 表在这两个作用域之间不共享。

有许多不同的方式可用于定义作用域，但它们都必须遵守以下原则：在同步拓扑中的一对数据库之间同步的任何数据只能属于一个作用域。例如，在上面的应用场景 2 中，数据库 A 和数据库 B 可以同步作用域 1；并且数据库 A 和数据库 C 可以同步作用域 2。数据库 A 和数据库 B 不能也同步作用域 2，因为 products 行和 orders 行属于这两个作用域。

代码示例

本节中的代码示例包括上述许多对象并涵盖以下方面：

  • 描述作用域和表

  • 设置服务器

  • 设置客户端

  • 设置同步选项

  • 同步节点

在介绍了上述各方面后，我们将提供一个完整的控制台应用程序，该应用程序将上述各示例和一些附加代码组合在一起来同步一个由四个节点构成的拓扑。该拓扑由一个 SQL Server 服务器、一个 SQL Server 客户端和两个 SQL Server Compact 客户端构成。

描述作用域和表

下面的代码示例描述名为 filtered_customer 的一个作用域，并且将两个表添加到该作用域：Customer 和 CustomerContact。这两个表已存在于服务器数据库中，因此，使用 GetDescriptionForTable 方法从服务器数据库中检索架构。将包括来自 Customer 表的所有列，但只包括来自 CustomerContact 表的两列。

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");

// Definition for Customer.
DbSyncTableDescription customerDescription =
    SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);

scopeDesc.Tables.Add(customerDescription);

// Definition for CustomerContact, including the list of columns to include.
Collection<string> columnsToInclude = new Collection<string>();
columnsToInclude.Add("CustomerId");
columnsToInclude.Add("PhoneType");
DbSyncTableDescription customerContactDescription =
    SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);

scopeDesc.Tables.Add(customerContactDescription);

Dim scopeDesc As New DbSyncScopeDescription("filtered_customer")

' Definition for Customer. 
Dim customerDescription As DbSyncTableDescription = _
    SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn)

scopeDesc.Tables.Add(customerDescription)


' Definition for CustomerContact, including the list of columns to include. 
Dim columnsToInclude As New Collection(Of String)()
columnsToInclude.Add("CustomerId")
columnsToInclude.Add("PhoneType")
Dim customerContactDescription As DbSyncTableDescription = _
    SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn)

scopeDesc.Tables.Add(customerContactDescription)

设置服务器

下面的代码示例为 filtered_customer 作用域创建一个设置对象，指定基表不应在服务器数据库中创建，并且指定与同步相关的所有对象都应在名为 "Sync" 的数据库架构中创建。作为作用域设置的一部分，该代码对 Customer 表定义一个筛选器。将只同步与该筛选器匹配的行。没有对 CustomerContact 表定义任何筛选器；因此，将同步来自该表的所有行。在定义设置选项后，调用 Apply 方法以便在服务器数据库中创建变更跟踪基础结构；并且设置脚本将写入某一文件。

SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverConfig.ObjectSchema = "Sync";

// Specify which column(s) in the Customer table to use for filtering data, 
// and the filtering clause to use against the tracking table.
// "[side]" is an alias for the tracking table.
serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";

// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn);

// Write the configuration script to a file. You can modify 
// this script if necessary and run it against the server
// to customize behavior.
File.WriteAllText("SampleConfigScript.txt",
    serverConfig.Script("SyncSamplesDb_SqlPeer1"));

Dim serverConfig As New SqlSyncScopeProvisioning(scopeDesc)
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip)
serverConfig.ObjectSchema = "Sync"

' Specify which column(s) in the Customer table to use for filtering data, 
' and the filtering clause to use against the tracking table. 
' "[side]" is an alias for the tracking table. 
serverConfig.Tables("Sales.Customer").AddFilterColumn("CustomerType")
serverConfig.Tables("Sales.Customer").FilterClause = "[side].[CustomerType] = 'Retail'"

' Configure the scope and change-tracking infrastructure. 
serverConfig.Apply(serverConn)

' Write the configuration script to a file. You can modify 
' this script if necessary and run it against the server 
' to customize behavior. 
File.WriteAllText("SampleConfigScript.txt", serverConfig.Script("SyncSamplesDb_SqlPeer1"))

设置客户端

在此应用程序中，用两种不同的方法设置客户端：

  • 基于从服务器或其他客户端数据库检索的作用域信息的 SQL Server 或 SQL Server Compact 客户端数据库的完全初始化。

    基于 SqlSyncDescriptionBuilder 和 SqlCeSyncDescriptionBuilder 对象提供的架构信息在客户端数据库中创建用户对象和同步对象。作为第一个同步会话的一部分，为同步准备客户端数据库，并且所有行都作为增量插入下载到该客户端数据库。

  • 通过使用预先存在的客户端数据库对 SQL Server Compact 客户端数据库执行快照初始化。

    快照初始化旨在减少初始化客户端数据库所需的时间。在通过使用完全初始化来初始化一个客户端数据库后，可以通过使用这个第一个客户端数据库的“快照”**初始化后续数据库。快照是专门准备的 SQL Server Compact 数据库，包含表架构、数据（可选）和变更跟踪基础结构。将此快照复制到要求它的每个客户端。在客户端的第一个同步会话期间，将更新特定于客户端的元数据，并且自创建快照后发生的任何变更都将下载到客户端数据库。

Note重要事项

只有在 SQL Server Compact 数据库中没有任何活动时，才应生成快照。在快照生成期间不支持任何类型的并发操作。

下面的代码示例首先从服务器检索作用域信息，并且使用检索的基表和变更跟踪架构设置一个 SQL Server Compact 客户端数据库。该代码然后基于来自 SQL Server Compact 客户端数据库的作用域信息设置一个 SQL Server 客户端数据库。

// Create a SQL Server Compact database and provision it based on scope
// information that is retrieved from the server. Compact databases
// do not support separate schemas, so we prefix the name of all 
// synchronization-related objects with "Sync" so that they are easy to
// identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
DbSyncScopeDescription clientSqlCe1Desc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", null, "Sync", serverConn);
SqlCeSyncScopeProvisioning clientSqlCe1Config = new SqlCeSyncScopeProvisioning(clientSqlCe1Desc);
clientSqlCe1Config.ObjectPrefix = "Sync";
clientSqlCe1Config.Apply(clientSqlCe1Conn);

// Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
// information that is retrieved from the SQL Server Compact database. We could
// have also retrieved this information from the server.
DbSyncScopeDescription clientSqlDesc = SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn);
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlDesc);
clientSqlConfig.ObjectSchema = "Sync";
clientSqlConfig.Apply(clientSqlConn);

' Create a SQL Server Compact database and provision it based on scope 
' information that is retrieved from the server. Compact databases 
' do not support separate schemas, so we prefix the name of all 
' synchronization-related objects with "Sync" so that they are easy to 
' identify. 
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
Dim clientSqlCe1Desc As DbSyncScopeDescription = _
    SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", Nothing, "Sync", serverConn)
Dim clientSqlCe1Config As New SqlCeSyncScopeProvisioning(clientSqlCe1Desc)
clientSqlCe1Config.ObjectPrefix = "Sync"
clientSqlCe1Config.Apply(clientSqlCe1Conn)


' Provision the existing database SyncSamplesDb_SqlPeer2 based on scope 
' information that is retrieved from the SQL Server Compact database. We could 
' have also retrieved this information from the server. 
Dim clientSqlDesc As DbSyncScopeDescription = _
    SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn)
Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlDesc)
clientSqlConfig.ObjectSchema = "Sync"
clientSqlConfig.Apply(clientSqlConn)

下面的代码示例从 SyncSampleClient1.sdf 数据库生成一个名为 SyncSampleClient2.sdf 的快照。该代码然后将 SyncSampleClient2.sdf 与服务器数据库同步。

// Create a snapshot from the SQL Server Compact database, which will be used to
// initialize a second Compact database. Again, this database could be provisioned
// by retrieving scope information from another database, but we want to 
// demonstrate the use of snapshots, which provide a convenient deployment
// mechanism for Compact databases.
SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization("Sync");
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf");

// The new SQL Server Compact client synchronizes with the server, but
// no data is downloaded because the snapshot already contains 
// all of the data from the first Compact database.
syncOrchestrator = new SampleSyncOrchestrator(
    new SqlSyncProvider("filtered_customer", serverConn, null, "Sync"),
    new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync")
    );
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");

' Create a snapshot from the SQL Server Compact database, which will be used to 
' initialize a second Compact database. Again, this database could be provisioned 
' by retrieving scope information from another database, but we want to 
' demonstrate the use of snapshots, which provide a convenient deployment 
' mechanism for Compact databases. 
Dim syncStoreSnapshot As New SqlCeSyncStoreSnapshotInitialization("Sync")
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf")

' The new SQL Server Compact client synchronizes with the server, but 
' no data is downloaded because the snapshot already contains 
' all of the data from the first Compact database. 
syncOrchestrator = New SampleSyncOrchestrator( _
    New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"), _
    New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")

设置同步选项

下面的代码示例是来自应用程序中的 SampleSyncOrchestrator 类的构造函数。该构造函数采用两个 RelationalSyncProvider 对象，因为 SqlSyncProvider 和 SqlCeSyncProvider 都是从 RelationalSyncProvider 派生的。这些代码指定哪一提供程序是本地提供程序以及哪一提供程序是远程提供程序。然后，它指定变更首先从远程数据库上载到本地数据库，然后按反向下载。

public SampleSyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{

    this.LocalProvider = localProvider;
    this.RemoteProvider = remoteProvider;
    this.Direction = SyncDirectionOrder.UploadAndDownload;
}

Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)

    Me.LocalProvider = localProvider
    Me.RemoteProvider = remoteProvider
    Me.Direction = SyncDirectionOrder.UploadAndDownload
End Sub

同步节点

下面的代码示例为三个不同的同步会话实例化提供程序：这三个同步会话分别在服务器和 SQL Server 客户端之间、SQL Server 客户端和 SQL Server Compact 客户端之一之间以及服务器与另一个 SQL Server Compact 客户端之间。在前两个会话期间同步七行：四行来自 CustomerContact，三行来自满足筛选条件的 Customer。在第三个会话中未同步行，因为快照已包含来自第一个 SQL Server Compact 数据库的所有数据。应用程序显示 Synchronize() 方法返回的统计信息。

SampleSyncOrchestrator syncOrchestrator;
SyncOperationStatistics syncStats;

// Data is downloaded from the server to the SQL Server client.
syncOrchestrator = new SampleSyncOrchestrator(
    new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
    new SqlSyncProvider("filtered_customer", serverConn, null, "Sync")
    );
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");

// Data is downloaded from the SQL Server client to the 
// first SQL Server Compact client.
syncOrchestrator = new SampleSyncOrchestrator(
    new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"),
    new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync")
    );
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");

// Create a snapshot from the SQL Server Compact database, which will be used to
// initialize a second Compact database. Again, this database could be provisioned
// by retrieving scope information from another database, but we want to 
// demonstrate the use of snapshots, which provide a convenient deployment
// mechanism for Compact databases.
SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization("Sync");
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf");

// The new SQL Server Compact client synchronizes with the server, but
// no data is downloaded because the snapshot already contains 
// all of the data from the first Compact database.
syncOrchestrator = new SampleSyncOrchestrator(
    new SqlSyncProvider("filtered_customer", serverConn, null, "Sync"),
    new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync")
    );
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");

Dim syncOrchestrator As SampleSyncOrchestrator
Dim syncStats As SyncOperationStatistics

' Data is downloaded from the server to the SQL Server client. 
syncOrchestrator = New SampleSyncOrchestrator( _
    New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"), _
    New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")

' Data is downloaded from the SQL Server client to the 
' first SQL Server Compact client. 
syncOrchestrator = New SampleSyncOrchestrator( _
    New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"), _
    New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")

' Create a snapshot from the SQL Server Compact database, which will be used to 
' initialize a second Compact database. Again, this database could be provisioned 
' by retrieving scope information from another database, but we want to 
' demonstrate the use of snapshots, which provide a convenient deployment 
' mechanism for Compact databases. 
Dim syncStoreSnapshot As New SqlCeSyncStoreSnapshotInitialization("Sync")
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf")

' The new SQL Server Compact client synchronizes with the server, but 
' no data is downloaded because the snapshot already contains 
' all of the data from the first Compact database. 
syncOrchestrator = New SampleSyncOrchestrator( _
    New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"), _
    New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")

完整的代码示例

下面的完整代码示例包括了前面部分中介绍的代码示例以及用于显示同步统计信息和事件信息的其他代码。示例需要 Utility 类，可通过用于数据库提供程序帮助主题的 Utility 类获得该类。

using System;
using System.Collections.ObjectModel;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;

namespace Microsoft.Samples.Synchronization
{
    class Program
    {
        static void Main(string[] args)
        {

            // Create the connections over which provisioning and synchronization
            // are performed. The Utility class handles all functionality that is not
            // directly related to synchronization, such as holding connection 
            // string information and making changes to the server database.
            SqlConnection serverConn = new SqlConnection(Utility.ConnStr_SqlSync_Server);
            SqlConnection clientSqlConn = new SqlConnection(Utility.ConnStr_SqlSync_Client);
            SqlCeConnection clientSqlCe1Conn = new SqlCeConnection(Utility.ConnStr_SqlCeSync1);
            SqlCeConnection clientSqlCe2Conn = new SqlCeConnection(Utility.ConnStr_SqlCeSync2);

            // Create a scope named "filtered_customer", and add two tables to the scope.
            // GetDescriptionForTable gets the schema of each table, so that tracking 
            // tables and triggers can be created for that table. For Customer, we add
            // the entire table. For CustomerContact, we add only two of the columns.
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");

            // Definition for Customer.
            DbSyncTableDescription customerDescription =
                SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);

            scopeDesc.Tables.Add(customerDescription);

            // Definition for CustomerContact, including the list of columns to include.
            Collection<string> columnsToInclude = new Collection<string>();
            columnsToInclude.Add("CustomerId");
            columnsToInclude.Add("PhoneType");
            DbSyncTableDescription customerContactDescription =
                SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);

            scopeDesc.Tables.Add(customerContactDescription);

            // Create a provisioning object for "filtered_customer". We specify that
            // base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1),
            // and that all synchronization-related objects should be created in a 
            // database schema named "Sync". If you specify a schema, it must already exist
            // in the database.
            SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
            serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
            serverConfig.ObjectSchema = "Sync";

            // Specify which column(s) in the Customer table to use for filtering data, 
            // and the filtering clause to use against the tracking table.
            // "[side]" is an alias for the tracking table.
            serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
            serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";

            // Configure the scope and change-tracking infrastructure.
            serverConfig.Apply(serverConn);

            // Write the configuration script to a file. You can modify 
            // this script if necessary and run it against the server
            // to customize behavior.
            File.WriteAllText("SampleConfigScript.txt",
                serverConfig.Script("SyncSamplesDb_SqlPeer1"));


            // Provision each of the client databases.           

            // Create a SQL Server Compact database and provision it based on scope
            // information that is retrieved from the server. Compact databases
            // do not support separate schemas, so we prefix the name of all 
            // synchronization-related objects with "Sync" so that they are easy to
            // identify.
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
            DbSyncScopeDescription clientSqlCe1Desc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", null, "Sync", serverConn);
            SqlCeSyncScopeProvisioning clientSqlCe1Config = new SqlCeSyncScopeProvisioning(clientSqlCe1Desc);
            clientSqlCe1Config.ObjectPrefix = "Sync";
            clientSqlCe1Config.Apply(clientSqlCe1Conn);

            // Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
            // information that is retrieved from the SQL Server Compact database. We could
            // have also retrieved this information from the server.
            DbSyncScopeDescription clientSqlDesc = SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn);
            SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlDesc);
            clientSqlConfig.ObjectSchema = "Sync";
            clientSqlConfig.Apply(clientSqlConn);


            // Initial synchronization sessions. 7 rows are synchronized:
            // all rows (4) from CustomerContact, and the 3 rows from Customer 
            // that satisfy the filtering criteria.
            SampleSyncOrchestrator syncOrchestrator;
            SyncOperationStatistics syncStats;

            // Data is downloaded from the server to the SQL Server client.
            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
                new SqlSyncProvider("filtered_customer", serverConn, null, "Sync")
                );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "initial");

            // Data is downloaded from the SQL Server client to the 
            // first SQL Server Compact client.
            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"),
                new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync")
                );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "initial");

            // Create a snapshot from the SQL Server Compact database, which will be used to
            // initialize a second Compact database. Again, this database could be provisioned
            // by retrieving scope information from another database, but we want to 
            // demonstrate the use of snapshots, which provide a convenient deployment
            // mechanism for Compact databases.
            SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization("Sync");
            syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf");

            // The new SQL Server Compact client synchronizes with the server, but
            // no data is downloaded because the snapshot already contains 
            // all of the data from the first Compact database.
            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlSyncProvider("filtered_customer", serverConn, null, "Sync"),
                new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync")
                );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "initial");


            // Make changes on the server: 1 insert, 1 update, and 1 delete.
            Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "Customer");

            // Synchronize again. Three changes were made on the server, but
            // only two of them applied to rows that are in the "filtered_customer"
            // scope. The other row is not synchronized.
            // Notice that the order of synchronization is different from the initial
            // sessions, but the two changes are propagated to all nodes.
            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"),
                new SqlSyncProvider("filtered_customer", serverConn, null, "Sync")
                );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "subsequent");

            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
                new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync")
            );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "subsequent");

            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"),
                new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync")
            );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "subsequent");

            serverConn.Close();
            serverConn.Dispose();
            clientSqlConn.Close();
            clientSqlConn.Dispose();
            clientSqlCe1Conn.Close();
            clientSqlCe1Conn.Dispose();
            clientSqlCe2Conn.Close();
            clientSqlCe2Conn.Dispose();

            Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Server);
            Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Client);

            Console.Write("\nPress any key to exit.");
            Console.Read();

        }

    }

    public class SampleSyncOrchestrator : SyncOrchestrator
    {
        public SampleSyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
        {

            this.LocalProvider = localProvider;
            this.RemoteProvider = remoteProvider;
            this.Direction = SyncDirectionOrder.UploadAndDownload;
        }

        public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
        {
            Console.WriteLine(String.Empty);
            if (syncType == "initial")
            {
                Console.WriteLine("****** Initial Synchronization ******");
            }
            else if (syncType == "subsequent")
            {
                Console.WriteLine("***** Subsequent Synchronization ****");
            }

            Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
            Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
            Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
            Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
            Console.WriteLine(String.Empty);
        }
    }
}

Imports System
Imports System.Collections.ObjectModel
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.SqlServer
Imports Microsoft.Synchronization.Data.SqlServerCe

Namespace Microsoft.Samples.Synchronization

    Class Program

        Public Shared Sub Main(ByVal args As String())

            ' Create the connections over which provisioning and synchronization 
            ' are performed. The Utility class handles all functionality that is not 
            ' directly related to synchronization, such as holding connection 
            ' string information and making changes to the server database. 
            Dim serverConn As New SqlConnection(Utility.ConnStr_SqlSync_Server)
            Dim clientSqlConn As New SqlConnection(Utility.ConnStr_SqlSync_Client)
            Dim clientSqlCe1Conn As New SqlCeConnection(Utility.ConnStr_SqlCeSync1)
            Dim clientSqlCe2Conn As New SqlCeConnection(Utility.ConnStr_SqlCeSync2)

            ' Create a scope named "filtered_customer", and add two tables to the scope. 
            ' GetDescriptionForTable gets the schema of each table, so that tracking 
            ' tables and triggers can be created for that table. For Customer, we add 
            ' the entire table. For CustomerContact, we add only two of the columns. 
            Dim scopeDesc As New DbSyncScopeDescription("filtered_customer")

            ' Definition for Customer. 
            Dim customerDescription As DbSyncTableDescription = _
                SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn)

            scopeDesc.Tables.Add(customerDescription)


            ' Definition for CustomerContact, including the list of columns to include. 
            Dim columnsToInclude As New Collection(Of String)()
            columnsToInclude.Add("CustomerId")
            columnsToInclude.Add("PhoneType")
            Dim customerContactDescription As DbSyncTableDescription = _
                SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn)

            scopeDesc.Tables.Add(customerContactDescription)

            ' Create a provisioning object for "filtered_customer". We specify that 
            ' base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1), 
            ' and that all synchronization-related objects should be created in a 
            ' database schema named "Sync". If you specify a schema, it must already exist 
            ' in the database. 
            Dim serverConfig As New SqlSyncScopeProvisioning(scopeDesc)
            serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip)
            serverConfig.ObjectSchema = "Sync"

            ' Specify which column(s) in the Customer table to use for filtering data, 
            ' and the filtering clause to use against the tracking table. 
            ' "[side]" is an alias for the tracking table. 
            serverConfig.Tables("Sales.Customer").AddFilterColumn("CustomerType")
            serverConfig.Tables("Sales.Customer").FilterClause = "[side].[CustomerType] = 'Retail'"

            ' Configure the scope and change-tracking infrastructure. 
            serverConfig.Apply(serverConn)

            ' Write the configuration script to a file. You can modify 
            ' this script if necessary and run it against the server 
            ' to customize behavior. 
            File.WriteAllText("SampleConfigScript.txt", serverConfig.Script("SyncSamplesDb_SqlPeer1"))


            ' Provision each of the client databases. 

            ' Create a SQL Server Compact database and provision it based on scope 
            ' information that is retrieved from the server. Compact databases 
            ' do not support separate schemas, so we prefix the name of all 
            ' synchronization-related objects with "Sync" so that they are easy to 
            ' identify. 
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
            Dim clientSqlCe1Desc As DbSyncScopeDescription = _
                SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", Nothing, "Sync", serverConn)
            Dim clientSqlCe1Config As New SqlCeSyncScopeProvisioning(clientSqlCe1Desc)
            clientSqlCe1Config.ObjectPrefix = "Sync"
            clientSqlCe1Config.Apply(clientSqlCe1Conn)


            ' Provision the existing database SyncSamplesDb_SqlPeer2 based on scope 
            ' information that is retrieved from the SQL Server Compact database. We could 
            ' have also retrieved this information from the server. 
            Dim clientSqlDesc As DbSyncScopeDescription = _
                SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn)
            Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlDesc)
            clientSqlConfig.ObjectSchema = "Sync"
            clientSqlConfig.Apply(clientSqlConn)


            ' Initial synchronization sessions. 7 rows are synchronized: 
            ' all rows (4) from CustomerContact, and the 3 rows from Customer 
            ' that satisfy the filtering criteria. 
            Dim syncOrchestrator As SampleSyncOrchestrator
            Dim syncStats As SyncOperationStatistics

            ' Data is downloaded from the server to the SQL Server client. 
            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"), _
                New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "initial")

            ' Data is downloaded from the SQL Server client to the 
            ' first SQL Server Compact client. 
            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"), _
                New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "initial")

            ' Create a snapshot from the SQL Server Compact database, which will be used to 
            ' initialize a second Compact database. Again, this database could be provisioned 
            ' by retrieving scope information from another database, but we want to 
            ' demonstrate the use of snapshots, which provide a convenient deployment 
            ' mechanism for Compact databases. 
            Dim syncStoreSnapshot As New SqlCeSyncStoreSnapshotInitialization("Sync")
            syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf")

            ' The new SQL Server Compact client synchronizes with the server, but 
            ' no data is downloaded because the snapshot already contains 
            ' all of the data from the first Compact database. 
            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"), _
                New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "initial")


            ' Make changes on the server: 1 insert, 1 update, and 1 delete. 
            Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "Customer")


            ' Synchronize again. Three changes were made on the server, but 
            ' only two of them applied to rows that are in the "filtered_customer" 
            ' scope. The other row is not synchronized. 
            ' Notice that the order of synchronization is different from the initial 
            ' sessions, but the two changes are propagated to all nodes. 

            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"), _
                New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "subsequent")

            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"), _
                New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "subsequent")

            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"), _
                New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "subsequent")


            serverConn.Close()
            serverConn.Dispose()
            clientSqlConn.Close()
            clientSqlConn.Dispose()
            clientSqlCe1Conn.Close()
            clientSqlCe1Conn.Dispose()
            clientSqlCe2Conn.Close()
            clientSqlCe2Conn.Dispose()

            Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Server)
            Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Client)

            Console.Write(vbLf & "Press any key to exit.")
            Console.Read()
        End Sub

    End Class

    Public Class SampleSyncOrchestrator
        Inherits SyncOrchestrator
        Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)

            Me.LocalProvider = localProvider
            Me.RemoteProvider = remoteProvider
            Me.Direction = SyncDirectionOrder.UploadAndDownload
        End Sub

        Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
            Console.WriteLine([String].Empty)
            If syncType = "initial" Then
                Console.WriteLine("****** Initial Synchronization ******")
            ElseIf syncType = "subsequent" Then
                Console.WriteLine("***** Subsequent Synchronization ****")
            End If

            Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
            Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
            Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
            Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
            Console.WriteLine([String].Empty)
        End Sub
    End Class
End Namespace

请参阅

概念

协作同步概述
用于协作同步的体系结构和类