入门：客户端与服务器同步
本主题介绍一个控制台应用程序，该程序先下载一个初始数据集，然后从一个表中下载一组增量变更。该应用程序简单明了，但是其代码在很多方面均建立在 Sync Services for ADO.NET 文档内容的基础之上。如果您曾经阅读过用于客户端与服务器同步的体系结构和类，应该已对该应用程序中使用的主要类有所了解。
只通过阅读示例代码，就可以学习到很多相关知识。但是，运行该应用程序并查看其运行情况则更加直观。在运行代码前，请确保安装了以下产品：
Sync Services
该应用程序需要引用 Microsoft.Synchronization.Data.dll、Microsoft.Synchronization.Data.Server.dll 和 Microsoft.Synchronization.Data.SqlServerCe.dll。
SQL Server Compact 3.5 SP1 Service Pack 1
该应用程序需要引用 System.Data.SqlServerCe.dll。
应使用 SQL Server Compact 3.5 SP1 之外的 SQL Server 版本作为服务器数据库。
示例代码在连接字符串中使用
localhost。若要使用随同 Visual Studio 一起安装的 SQL Server Express 的实例，请将localhost变更为.\sqlexpress。若要使用远程服务器，请将localhost变更为适当的服务器名称。Sync Services 示例数据库。执行在用于 Sync Services 帮助主题的安装脚本中提供的两个脚本。建议您查看这两个脚本，以便了解如何在服务器数据库中处理变更跟踪。
该应用程序由六个类组成：
SampleServerSyncProvider。此类派生自 DbServerSyncProvider 并包含 SyncAdapter。SampleClientSyncProvider。此类派生自 SqlCeClientSyncProvider。在本示例中，此类只包含一个到客户端数据库的连接字符串。SampleStats。此类使用SyncAgent返回的统计信息。Program。此类设置同步并调用Utility类的方法。Utility。此类处理与同步不直接相关的所有功能，如保存连接字符串信息和变更服务器数据库。完整的Utility类用在其他主题中。可从 用于 Sync Services 帮助主题的 Utility 类 获得完整的类。
API 的要点
在查看完整的代码示例之前，建议您查看以下示例，它们阐释了本应用程序中使用的 API 的几个要点。
创建 SyncTable
以下代码示例创建 Customer 表的 SyncTable 对象，并指定同步方向以及在客户端上创建该表的方式。在本例中，如果客户端数据库中已经存在该表，在第一次同步期间将删除该表。
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
使用 SqlSyncAdapterBuilder
本节中的每个代码示例都为 Customer 表创建 SyncAdapter。同步适配器为服务器同步提供程序提供与服务器数据库交互所需的特定命令。在此应用程序中，同步适配器是通过使用 SqlSyncAdapterBuilder 创建的。第一个示例说明如何将 SqlSyncAdapterBuilder 用于自定义变更跟踪系统。第二个示例说明如何将 SqlSyncAdapterBuilder 用于 SQL Server 变更跟踪（在 SQL Server 2008 中提供）。有关变更跟踪的更多信息，请参见跟踪服务器数据库中的变更。
有关如何手动而不使用生成器创建命令的信息，请参见如何将增量数据变更下载到客户端。
使用自定义变更跟踪系统
若要使用自定义变更跟踪系统，请为 SqlSyncAdapterBuilder 和 SyncAdapter 指定以下信息：
用于同步的表的名称和 tombstone 表。逻辑删除表用于跟踪服务器数据库中的删除操作。有关更多信息，请参见跟踪服务器数据库中的变更。如果表位于 dbo 之外的某个架构中，则必须指定该架构。
同步方向。此选项控制
SqlSyncAdapterBuilder要创建的命令。有关这些命令的更多信息，请参见如何指定快照同步、下载同步、上载同步和双向同步。服务器数据库中的跟踪列。这些列用于跟踪发生变更的时间，以便只下载新的变更。可以包括其他列以跟踪发生变更的位置。有关更多信息，请参见如何使用自定义变更跟踪系统。
SyncAdapter的名称。它必须与SyncTable的名称相符。因此，它不应包括架构名称。
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
customerBuilder.SyncDirection = SyncDirection.DownloadOnly
customerBuilder.CreationTrackingColumn = "InsertTimestamp"
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp"
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp"
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
使用 SQL Server 变更跟踪
若要使用 SQL Server 变更跟踪，请为 SqlSyncAdapterBuilder 和 SyncAdapter 指定以下信息：
要同步的表的名称。
同步方向。此选项控制
SqlSyncAdapterBuilder要创建的命令。有关这些命令的更多信息，请参见如何指定快照同步、下载同步、上载同步和双向同步。要使用的变更跟踪的类型。默认情况下，Sync Services 将要求您指定自定义变更跟踪列。在本代码示例中，指定 SQL Server 变更跟踪。
SyncAdapter的名称。它必须与SyncTable的名称相符。因此，它不应包括架构名称。
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
指定新定位点命令
以下代码示例指定一条从服务器中检索新定位点值的命令。该值存储在客户端数据库中，并且由同步变更的命令使用。在每次同步期间，将使用新的定位点值以及来自先前同步的上一个定位点值：将对在这两个作为上下限的定位点值之间做出的变更进行同步。
在这种情况下，MIN_ACTIVE_ROWVERSION 从 SQL Server 数据库返回一个时间戳值。（MIN_ACTIVE_ROWVERSION 是在 SQL Server 2005 Service Pack 2 中引入的。）使用时间戳值的原因是为 SqlSyncAdapterBuilder 指定的跟踪列包含时间戳值。如果跟踪列包含日期值，则可以使用某个函数（如 GETUTCDATE()）而不是 MIN_ACTIVE_ROWVERSION。有关定位点的更多信息，请参见跟踪服务器数据库中的变更。
SyncSession 类包含几个可在同步命令中使用的字符串常量。SyncNewReceivedAnchor 是这些常量之一。此外，还可以在查询中直接使用 @sync_new_received_anchor 文本。
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand
调用同步方法
以下代码示例实例化 SampleSyncAgent，并调用 Synchronize 方法。在 SampleSyncAgent 类中，SampleClientSyncProvider 指定为 LocalProvider，SampleServerSyncProvider 指定为 RemoteProvider，并且指定了前面介绍过的同步表。
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
在 SampleStats 类中，SyncAgent 返回的统计信息用于向用户提供有关同步会话的反馈。有关更多信息，请参见如何处理事件和对业务逻辑进行编程。
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
完整的代码示例
现在，您已经看到了同步所涉及代码的主要部分，它们包含在一个带有详细注释的完整应用程序之中。在运行此应用程序之后，建议您阅读对常见客户端与服务器同步任务进行编程节中的主题。您将看到的类与在本主题的代码示例中使用的类相同。但是，它们以一种更为复杂的方式应用于其他表。
使用自定义变更跟踪的完整示例
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The Utility class handles all functionality that is not
//directly related to synchronization, such as holding connection
//string information and making changes to the server database.
Utility util = new Utility();
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
util.SetClientPassword();
util.RecreateClientDatabase();
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server.
util.MakeDataChangesOnServer();
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
util.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Add the Customer table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the Customer table by using
//the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// changes are made.
// * Specify download-only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but you could use this class to handle client
//provider events and other client-side processing.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = util.ClientConnString;
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
public class Utility
{
private static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public string ClientConnString
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
//Return the server connection string.
public string ServerConnString
{
get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public void CleanUpServer()
{
using(SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public void RecreateClientDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(this.ClientConnString);
sqlCeEngine.CreateDatabase();
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The 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 util As New Utility()
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Delete and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
util.SetClientPassword()
util.RecreateClientDatabase()
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server.
util.MakeDataChangesOnServer()
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
util.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Add the Customer table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(util.ServerConnString)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the Customer table by using
'the SqlSyncAdapterBuilder:
' * Specify the base table and tombstone table names.
' * Specify the columns that are used to track when
' changes are made.
' * Specify download-only synchronization.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
customerBuilder.SyncDirection = SyncDirection.DownloadOnly
customerBuilder.CreationTrackingColumn = "InsertTimestamp"
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp"
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp"
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but you could use this class to handle client
'provider events and other client-side processing.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = util.ClientConnString
End Sub 'New
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats
Public Class Utility
Private Shared _clientPassword As String
'Get and set the client database password.
Public Shared Property Password() As String
Get
Return _clientPassword
End Get
Set(ByVal value As String)
_clientPassword = value
End Set
End Property
'Have the user enter a password for the client database file.
Public Sub SetClientPassword()
Console.WriteLine("Type a strong password for the client")
Console.WriteLine("database, and then press Enter.")
Utility.Password = Console.ReadLine()
End Sub 'SetClientPassword
'Return the client connection string with the password.
Public ReadOnly Property ClientConnString() As String
Get
Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
End Get
End Property
'Return the server connection string.
Public ReadOnly Property ServerConnString() As String
Get
Return "Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"
End Get
End Property
'Make server changes that are synchronized on the second
'synchronization.
Public Sub MakeDataChangesOnServer()
Dim rowCount As Integer = 0
Dim serverConn As New SqlConnection(Me.ServerConnString)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandText = _
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
& "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
& "UPDATE Sales.Customer " _
& "SET SalesPerson = 'James Bailey' " _
& "WHERE CustomerName = 'Tandem Bicycle Store' " _
& "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
serverConn.Open()
rowCount = sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)
End Sub 'MakeDataChangesOnServer
'Revert changes that were made during synchronization.
Public Sub CleanUpServer()
Dim serverConn As New SqlConnection(Me.ServerConnString)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = "usp_InsertSampleData"
serverConn.Open()
sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
End Sub 'CleanUpServer
'Delete the client database.
Public Sub RecreateClientDatabase()
Dim clientConn As New SqlCeConnection(Me.ClientConnString)
Try
If File.Exists(clientConn.Database) Then
File.Delete(clientConn.Database)
End If
Finally
clientConn.Dispose()
End Try
Dim sqlCeEngine As New SqlCeEngine(Me.ClientConnString)
sqlCeEngine.CreateDatabase()
End Sub 'RecreateClientDatabase
End Class 'Utility
使用 SQL Server 变更跟踪的完整示例
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The Utility class handles all functionality that is not
//directly related to synchronization, such as holding connection
//string information and making changes to the server database.
Utility util = new Utility();
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
util.SetClientPassword();
util.RecreateClientDatabase();
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server.
util.MakeDataChangesOnServer();
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
util.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Add the Customer table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = change_tracking_current_version()";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the Customer table by using
//the SqlSyncAdapterBuilder:
// * Specify the base table name.
// * Specify that the server uses SQL Server change tracking.
// * Specify download-only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but you could use this class to handle client
//provider events and other client-side processing.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = util.ClientConnString;
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
public class Utility
{
private static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public string ClientConnString
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
//Return the server connection string.
public string ServerConnString
{
get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public void CleanUpServer()
{
using(SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public void RecreateClientDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(this.ClientConnString);
sqlCeEngine.CreateDatabase();
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The 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 util As New Utility()
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Delete and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
util.SetClientPassword()
util.RecreateClientDatabase()
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server.
util.MakeDataChangesOnServer()
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
util.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Add the Customer table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(util.ServerConnString)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = _
"SELECT " + newAnchorVariable + " = change_tracking_current_version()"
.Parameters.Add(newAnchorVariable, SqlDbType.BigInt)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the Customer table by using
'the SqlSyncAdapterBuilder:
' * Specify the base table names.
' * Specify that the server uses SQL Server change tracking.
' * Specify download-only synchronization.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but you could use this class to handle client
'provider events and other client-side processing.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = util.ClientConnString
End Sub 'New
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats
Public Class Utility
Private Shared _clientPassword As String
'Get and set the client database password.
Public Shared Property Password() As String
Get
Return _clientPassword
End Get
Set(ByVal value As String)
_clientPassword = value
End Set
End Property
'Have the user enter a password for the client database file.
Public Sub SetClientPassword()
Console.WriteLine("Type a strong password for the client")
Console.WriteLine("database, and then press Enter.")
Utility.Password = Console.ReadLine()
End Sub 'SetClientPassword
'Return the client connection string with the password.
Public ReadOnly Property ClientConnString() As String
Get
Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
End Get
End Property
'Return the server connection string.
Public ReadOnly Property ServerConnString() As String
Get
Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"
End Get
End Property
'Make server changes that are synchronized on the second
'synchronization.
Public Sub MakeDataChangesOnServer()
Dim rowCount As Integer = 0
Dim serverConn As New SqlConnection(Me.ServerConnString)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandText = _
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
& "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
& "UPDATE Sales.Customer " _
& "SET SalesPerson = 'James Bailey' " _
& "WHERE CustomerName = 'Tandem Bicycle Store' " _
& "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
serverConn.Open()
rowCount = sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)
End Sub 'MakeDataChangesOnServer
'Revert changes that were made during synchronization.
Public Sub CleanUpServer()
Dim serverConn As New SqlConnection(Me.ServerConnString)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = "usp_InsertSampleData"
serverConn.Open()
sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
End Sub 'CleanUpServer
'Delete the client database.
Public Sub RecreateClientDatabase()
Dim clientConn As New SqlCeConnection(Me.ClientConnString)
Try
If File.Exists(clientConn.Database) Then
File.Delete(clientConn.Database)
End If
Finally
clientConn.Dispose()
End Try
Dim sqlCeEngine As New SqlCeEngine(Me.ClientConnString)
sqlCeEngine.CreateDatabase()
End Sub 'RecreateClientDatabase
End Class 'Utility