大家好,
我知道,我正在谈论的版本现在已经相当过时了,但这是我在工作场所坚持使用的工具。这是我关于StackOverflow的第一个问题,我希望我能正确格式化呵呵;-)请原谅我的长篇大论,我习惯于提供很多细节,从某种意义上说,我觉得我提供的细节越多,答案就越准确;-)
在近 10 年的 IT 工作中,我总是能够通过谷歌搜索精心选择的关键字和表达式来找到问题的答案(即我的问题的解决方案)。好吧,看起来前面提到的同步框架要么不是很为互联网社区所熟知,要么对于大多数凡人来说,试图理解其最简单的概念真的很痛苦。经过广泛的研究,我必须找到一个使用 Sync Framework 1.0 和 C# 语言同步 SQL Express 的简单示例,甚至在 MSDN 上也没有!我对 ASP.NET/C#相当陌生,但我理解这些概念,并且我有一个工作Web应用程序,可以成功地从SQL Server 2008数据库中存储和检索数据。它已经被客户使用了两年。我们现在要求客户端能够使其数据脱机,并能够脱机更新数据,然后与服务器同步。更新、插入和删除将发生在两端。
我试图找到的是非常简单(或者我认为):使用 SQL Server 更改跟踪信息(不是自定义更改跟踪)来同步服务器(SQL Server 2008)和客户端计算机(SQL Server 2008 Express,而不是精简版)的 C# 代码示例。最简单的情况是包含几列的单个表。我相当有信心理解SQL Server部分,并且我已经准备好了数据库的两端以接收来自客户端Web应用程序的同步请求(启用了更改跟踪,PrimaryKeyID具有数据类型GUID,服务器上应用程序的用户帐户具有VIEW_CHANGE_TRACKING权限等)。
我知道它是充当两者之间的接口并管理同步会话(在 C# 中)的 Web 应用程序。我很天真地认为唯一要做的就是提供两个连接字符串,告诉要同步哪些表并指定双向同步。显然,这比那个更复杂,呵呵。在一次绝望的尝试中,我试图将我的代码基于Microsoft以下内容,并将其改编为 SQL Express(该示例适用于 Compact)。我快要认输了,可耻地低下了头:-(
http://msdn.microsoft.com/en-us/library/bb726015%28v=sql.100%29.aspx
基于上述内容(第二部分"使用 SQL Server 更改跟踪的完整示例"),我删除了不需要的所有内容:与密码、统计信息和对数据应用更改的代码相关的内容。 为了清楚起见,我还删除了MS的众多评论行。我已经在 SQL Server 本身的两端手动应用更改,在 SSMS 中执行脚本(因此必须生成更改跟踪信息,并且在 Web 应用请求同步时可用)。问题1:我这么说错了吗?最后,我更改了一些内容,试图使用与SQL Express相关的对象而不是Compact。
问题 2:Microsoft处的代码显然能够分辨它是此副本的初始(第一次)还是后续同步。我不知道它怎么可能!
最后,以最简单的形式保留的代码如下所示(带有问题 3、4、5 ;-),但显示了一些错误。我提前非常感谢您的帮助。欢迎任何意见和/或建议。我敢肯定,如果/当这个问题得到解决时,它将使很多人受益。 我会继续研究它(老板不会给我选择;-)我保证如果我成功同步,我会在这里发布解决方案!
谢谢大家,祝大家有美好的一天!
最亲切的问候,
Zyxy
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
//using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
//using Microsoft.Synchronization.Data.SqlServerCe;
namespace some_namespace
{
public class SyncProgram
{
public SyncProgram()
{
// empty constructor
}
public static bool MainSync() // Entry point, say, called by a Sync button on an ASPX page.
{
bool boolSyncRes = false; // tells whether sync was a success or not
// Initial sync: they create a new instance of the Orchestrator.
ZyxySyncOrchestrator zyxySyncOrchestrator = new ZyxySyncOrchestrator();
// Subsequent synchronization.
// They don't. there was only irrelevant stats stuff here.
boolSyncRes = true;
return boolSyncRes;
}
}
public class ZyxySyncOrchestrator : SyncOrchestrator
{
public ZyxySyncOrchestrator()
{
Utility util = new Utility();
this.LocalProvider = new ZyxyServerSyncProvider(); // QUESTION 3: ??? cannot implicitly convert type DbServerSyncProvider to Microsoft.Synchronization.SyncProvider
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new ZyxyServerSyncProvider(); // cannot implicitly convert type DbServerSyncProvider to Microsoft.Synchronization.SyncProvider
// QUESTION 4: Is the following code actually creating the base (user) table ZyxySync
// (as opposed to its change tracking metadata table)??
// I wasn't sure whether this part of the code on Microsoft's webpage was part of
// populating the db with sample data and structure or if it's really meant to deal with
// the change tracking metadata.
SyncTable zyxySyncTable = new SyncTable("ZyxySync");
zyxySyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
zyxySyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(zyxySyncTable);
}
}
//Create a class that is derived from Microsoft.Synchronization.Server.DbServerSyncProvider.
public class ZyxyServerSyncProvider : DbServerSyncProvider
{
public ZyxyServerSyncProvider()
{
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//Retrieve a new anchor value from the server. We use a timestamp value
//that is retrieved and stored in the client database.
//During each sync the new and last anchor values are used to determine the set of changes
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 ZyxySync table by using
//the SqlSyncAdapterBuilder.
// Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
SqlSyncAdapterBuilder zyxyBuilder = new SqlSyncAdapterBuilder(serverConn);
zyxyBuilder.TableName = "dbo.ZyxySync";
zyxyBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter zyxySyncAdapter = zyxyBuilder.ToSyncAdapter();
zyxySyncAdapter.TableName = "ZyxySync";
this.SyncAdapters.Add(zyxySyncAdapter);
}
}
// Class derived from Microsoft.Synchronization.Data.Server.DbServerSyncProvider
// QUESTION 5: Or should have I used the two below? I believe they only apply to SQL Compact...
//Microsoft.Synchronization.Data.ClientSyncProvider
//Microsoft.Synchronization.Data.ServerSyncProvider
//http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.clientsyncprovider%28v=sql.100%29.aspx
//http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.server.dbserversyncprovider%28d=printer,v=sql.100%29.aspx
public class ZyxyClientSyncProvider : DbServerSyncProvider
{
public ZyxyClientSyncProvider()
{
Utility util = new Utility();
SqlConnection clientConn = new SqlConnection(util.ClientConnString);
this.Connection = clientConn;
}
}
public class Utility
{
public string ClientConnString
{
get { return @"Data Source=localhostLocalExpressInstance;Initial Catalog=DatabaseName;User ID=UserName;Password=WontTellYou;"; }
}
public string ServerConnString
{
get { return @" Data Source=ServerNameServerInstance;Initial Catalog=DatabaseName;User ID=UserName;Password=WontTellYou;"; }
}
}
}
SyncOrchestrator 将无法与 DBServerSyncProvider 一起使用。
在同步框架中,有两种类型的数据库提供程序:脱机提供程序和对等/协作提供程序。 (它们都在离线场景中工作,所以这很令人困惑)。
脱机提供程序用于中心辐射型拓扑。只有客户端跟踪同步的内容。服务器甚至不知道它在同步中的部分。这是 Visual Studio 中的本地数据库缓存项目项使用的同一提供程序。唯一支持的现成数据库是 SqlCeClientSyncProvider 和 DBServerSyncProvider,并使用 SyncAgent 进行同步。
对等提供程序可用于对等同步以及中心辐射型方案。每个对等方都维护有关同步内容的元数据。这使用更新的SyncOrchestrator/SqlCeSyncProvider/SqlSyncProvider(适用于SQL Server,Express,LocalDB和SQL Azure)。这使用自定义更改跟踪。
您不能交换 SyncAgent 和 SyncOrchestrator 使用的提供程序。您也不能重用 SQL 命令,因为它们在跟踪、选择、应用更改和记录同步内容的方式上有所不同。
我设法让它工作,所以这里有一个简单的代码示例可以工作(无论如何在我的情况下)。除了上述步骤(启用更改跟踪,设置正确的用户权限等)之外,我不了解的是以下内容:
1)我发现我可以设置它,以便同步框架和同步会话都在客户端进行管理。在不依赖于服务器上安装的内容的情况下,我能够使用 SF 2.1 而不是旧的 1.0。这帮了大忙。
2) 在准备同步会话时,必须首先预配数据库,使其准备好进行同步。我所做的是使用客户端连接字符串运行以下 C#(以便它预配客户端数据库),然后使用服务器连接字符串再次运行它(以便它预配服务器数据库)。这是一个运行一次的程序(在两端)来准备数据库。您不会为您建立的每个同步会话运行它。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Security.Principal;
using System.IO;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServer; // SF 2.1
using Microsoft.Synchronization.SimpleProviders; // SF 2.1
using Microsoft.Synchronization.MetadataStorage; // SF 2.1
// ZYXY: Based on:
// http://msdn.microsoft.com/en-us/library/ff928603.aspx
// NOTES:
// - Microsoft Sync Framework 2.1 redistributable package must be installed on Client computers but is not required on the Server, as long as a server-side synchronization setup is performed by a client computer.
// This is a run once program.
namespace DISS_Database_Sync_Provisioning_Console
{
class Program
{
static void Main(string[] args)
{
SqlConnection sqlConn = new SqlConnection("Data Source=ServerName\InstanceName;Initial Catalog=SomeDatabase;User ID=SOmeUser;Password=SomePassword;");
Console.Write("Provisioning database...");
// define a new scope named DISS_Sync_Scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("DISS_Sync_Scope");
// get the description of the ZyxySync table
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.ZyxySync", sqlConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the DISS_Sync_Scope
SqlSyncScopeProvisioning sqlProvision = new SqlSyncScopeProvisioning(sqlConn, scopeDesc);
// skipping the creation of base table since table already exists
sqlProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
sqlProvision.Apply();
sqlConn.Close();
sqlConn.Dispose();
Console.Write("nDatabase has been successfully configured for synchronization. Please press any key to exit.");
Console.Read();
}
}
}
3) 以下是每次启动同步时运行的代码(例如,当用户在其 Web 应用程序中单击其"同步"按钮时)。
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Security.Principal;
using System.IO;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServer; // SF 2.1
using Microsoft.Synchronization.SimpleProviders; // SF 2.1
using Microsoft.Synchronization.MetadataStorage; // SF 2.1
namespace diss_ssmb
{
public class SyncProgram
{
public SyncProgram()
{
// empty constructor
}
public static bool MainSync() // Entry point, say, called by a Sync button on an ASPX page.
{
bool boolSyncRes = false; // tells whether sync was a success or not
// Initial sync: they create a new instance of the Orchestrator.
ZyxySyncOrchestrator zyxySyncOrchestrator = new ZyxySyncOrchestrator();
// Subsequent synchronization.
// They don't. there was only irrelevant stats stuff here.
boolSyncRes = true;
return boolSyncRes;
}
}
public class ZyxySyncOrchestrator : SyncOrchestrator
{
public ZyxySyncOrchestrator()
{
Utility util = new Utility();
this.LocalProvider = new ZyxyClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new ZyxyServerSyncProvider();
SyncTable zyxySyncTable = new SyncTable("ZyxySync");
zyxySyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
zyxySyncTable.SyncDirection = SyncDirection.Bidirectional;
// this.Configuration.SyncTables.Add(zyxySyncTable);
this.Synchronize();
}
}
public class ZyxyServerSyncProvider : SqlSyncProvider
{
public ZyxyServerSyncProvider()
{
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
this.ScopeName = "DISS_Sync_Scope";
//Retrieve a new anchor value from the server. We use a timestamp value
//that is retrieved and stored in the client database.
//During each sync the new and last anchor values are used to determine the set of changes
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; // SF 2.1 commented out because SelectNewAnchorCommand isn't there.
SqlSyncAdapterBuilder zyxyBuilder = new SqlSyncAdapterBuilder(serverConn);
zyxyBuilder.TableName = "dbo.ZyxySync";
zyxyBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter zyxySyncAdapter = zyxyBuilder.ToSyncAdapter();
zyxySyncAdapter.TableName = "ZyxySync";
// this.SyncAdapters.Add(zyxySyncAdapter); // SF 2.1 commented out because SelectNewAnchorCommand isn't there.
}
}
public class ZyxyClientSyncProvider : SqlSyncProvider
{
public ZyxyClientSyncProvider()
{
Utility util = new Utility();
SqlConnection clientConn = new SqlConnection(util.ClientConnString);
this.Connection = clientConn;
this.ScopeName = "DISS_Sync_Scope";
}
}
public class Utility
{
public string ClientConnString
{
get { return @"Some connection string such as in the above code sample"; }
}
public string ServerConnString
{
get { return @"Some serverconnection string such as in the above code sample"; }
}
}
}
4) 当两个连续同步会话之间的两端同时发生插入、更新和删除时,上述成功双向同步,但是,当不需要解决冲突时(例如,当同一记录在两端更新时)。在必须解决冲突的情况下,我必须做进一步的测试。默认情况下,同步框架如何解决此类冲突?我假设我们可以调整这些设置,以告诉它根据...- 时间戳值- 副本 ID- 用户角色- 交易类型- ...
无论如何,我真的希望这对某人有所帮助,因为我很难从网络上弄清楚!祝你好运!
Zyxy