服务器到客户端返回到服务器同步
本文关键字:服务器 同步 客户端 返回 | 更新日期: 2023-09-27 18:32:18
我正在开发一个使用实体框架 6.0 与 localdb 交互的 WPF 应用程序
因此,在过去的几周里,我一直在尝试在以下数据库类型之间进行同步设置:
服务器:SQL Server 2008 数据库
客户端:localdb (SQL Express)
虽然我可以将数据库的结构从服务器转移到客户端上,但它不会带来关系。
这会以某种方式更改 ADO.NET 实体数据模型的生成方式。为服务器结构生成实体数据模型时,它会将多对一对多关系生成为集合(多对多),但在生成的 localdb(未生成关系)上,它保留表结构。这会导致我的应用程序出现问题。如果同步无法正常工作,则无法完全迁移到脱机应用程序。
服务器到客户端(客户端初始化):
using System;
using System.IO;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
namespace DatabaseSetup
{
class Program
{
static void Main()
{
try
{
CreateLocalDatabase();
ProvisionServer();
ProvisionClient();
Sync();
}
catch (Exception ex)
{
Console.WriteLine($"Exception thrown: {ex.Source}");
Console.WriteLine($"Exception thrown: {ex.Data}");
Console.WriteLine($"Exception thrown: {ex.Message}");
Console.ReadLine();
}
}
public static void CreateLocalDatabase()
{
var conn = new SqlConnection(LocalDb);
var command = new SqlCommand(@"CREATE DATABASE ********", conn);
try
{
conn.Open();
Console.WriteLine(command.ExecuteNonQuery() <= 0
? @"Creating '********' Database on '(localdb)'v11.0'"
: @"Database '********' already exists. Attempting to synchronize.");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if(conn.State == ConnectionState.Open)
conn.Close();
}
}
public static void ProvisionServer()
{
Console.WriteLine(@"Attempting to provision server for synchronization...");
// connect to server database
var serverConnection = new SqlConnection(Server);
// define a new scope named ProductsScope
var scopeDescription = DatabaseScope(serverConnection);
// create a server scope provisioning object based on the ProductScope
var serverProvision = new SqlSyncScopeProvisioning(serverConnection, scopeDescription);
if(!serverProvision.ScopeExists("DatabaseScope"))
{ // skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
Console.WriteLine(@"Provisioning complete.");
}
else
{
Console.WriteLine(@"Server already provisioned.");
}
}
public static void ProvisionClient()
{
Console.WriteLine(@"Attempting to provision client for synchronization...");
// create a connection to the SyncExpressDB database
var clientConnection = new SqlConnection(Client);
// create a connection to the SyncDB server database
var serverConnection = new SqlConnection(Server);
// get the description of ProductsScope from the SyncDB server database
var scopeDesc = DatabaseScope(serverConnection);
// create server provisioning object based on the ProductsScope
var clientProvision = new SqlSyncScopeProvisioning(clientConnection, scopeDesc);
if (!clientProvision.ScopeExists("DatabaseScope"))
{
// starts the provisioning process
clientProvision.Apply();
Console.WriteLine(@"Provisioning complete.");
}
else
{
Console.WriteLine(@"Client already provisioned.");
}
}
public static void Sync()
{
//Define conections
Console.WriteLine(@"Attempting to synchronize.");
var serverConnection = new SqlConnection(Server);
var clientConnection = new SqlConnection(Client);
//Create Sync Orchestrator
var syncOrchestrator = new SyncOrchestrator
{
Direction = SyncDirectionOrder.DownloadAndUpload,
LocalProvider = new SqlSyncProvider("DatabaseScope", clientConnection),
RemoteProvider = new SqlSyncProvider("DatabaseScope", serverConnection)
};
((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += Program_ApplyChangeFailed;
var syncStats = syncOrchestrator.Synchronize();
Console.WriteLine("'n'nSynchronization complete:");
Console.WriteLine($"Start Time: {syncStats.SyncStartTime}");
Console.WriteLine($"Uploaded: {syncStats.UploadChangesTotal}");
Console.WriteLine($"Downloaded: {syncStats.DownloadChangesTotal}");
Console.WriteLine($"Time Elapsed: {syncStats.SyncEndTime}");
Console.Read();
}
private static DbSyncScopeDescription DatabaseScope(SqlConnection connection)
{
//Define scope
var scopeTables = new Collection<DbSyncTableDescription>();
foreach (var table in TableList)
{
scopeTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(table, connection));
}
var databaseScope = new DbSyncScopeDescription("DatabaseScope");
foreach(var table in scopeTables)
{
databaseScope.Tables.Add(table);
}
return databaseScope;
}
static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
{
// display conflict type
Console.WriteLine(e.Conflict.Type);
// display error message
Console.WriteLine(e.Error);
}
}
}
这是我设置的操作,当用户在想要同步时单击应用程序上的按钮时发生:
客户端和服务器之间的同步:
using System;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Windows;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Outreach.Resources;
namespace WpfApplication
{
class DatabaseSynchronization
{
public static void Sync()
{
//Define conections
Console.WriteLine(@"Attempting to synchronize.");
var clientConnection = new SqlConnection(Constants.Client);
var serverConnection = new SqlConnection(Constants.Server);
//Create Sync Orchestrator
var syncOrchestrator = new SyncOrchestrator();
syncOrchestrator.LocalProvider = new SqlSyncProvider("DatabaseScope", clientConnection);
syncOrchestrator.RemoteProvider = new SqlSyncProvider("DatabaseScope", serverConnection);
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += Program_ApplyChangeFailed;
var syncStats = syncOrchestrator.Synchronize();
Console.WriteLine("'n'nSynchronization complete:");
Console.WriteLine($"Start Time: {syncStats.SyncStartTime}");
Console.WriteLine($"Uploaded: {syncStats.UploadChangesTotal}");
Console.WriteLine($"Downloaded: {syncStats.DownloadChangesTotal}");
Console.WriteLine($"Time Elapsed: {syncStats.SyncEndTime}");
Console.Read();
}
static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
{
// display conflict type
Console.WriteLine(e.Conflict.Type);
// display error message
Console.WriteLine(e.Error);
}
private static DbSyncScopeDescription DatabaseScope(SqlConnection connection)
{
//Define scope
var scopeTables = new Collection<DbSyncTableDescription>();
foreach(var table in Constants.MsoTableList)
{
scopeTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(table, connection));
}
var outreachScope = new DbSyncScopeDescription("DatabaseScope");
foreach(var table in scopeTables)
{
outreachScope.Tables.Add(table);
}
return outreachScope;
}
public static void ProvisionServer()
{
Console.WriteLine(@"Attempting to provision server for synchronization...");
// connect to server database
var serverConnection = new SqlConnection(Constants.Server);
// define a new scope named ProductsScope
var scopeDescription = DatabaseScope(serverConnection);
// create a server scope provisioning object based on the ProductScope
var serverProvision = new SqlSyncScopeProvisioning(serverConnection, scopeDescription);
if(!serverProvision.ScopeExists("DatabaseScope"))
{ // skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
Console.WriteLine(@"Provisioning complete.");
}
else
{
Console.WriteLine(@"Server already provisioned.");
}
}
public static void ProvisionClient()
{
Console.WriteLine(@"Attempting to provision client for synchronization...");
// create a connection to the SyncExpressDB database
var clientConnection = new SqlConnection(Constants.Client);
// create a connection to the SyncDB server database
var serverConnection = new SqlConnection(Constants.Server);
// get the description of ProductsScope from the SyncDB server database
var scopeDesc = DatabaseScope(serverConnection);
// create server provisioning object based on the ProductsScope
var clientProvision = new SqlSyncScopeProvisioning(clientConnection, scopeDesc);
if(!clientProvision.ScopeExists("DatabaseScope"))
{
// starts the provisioning process
clientProvision.Apply();
Console.WriteLine(@"Provisioning complete.");
}
else
{
Console.WriteLine(@"Client already provisioned.");
}
}
}
}
我做错了什么吗?
有没有比同步框架更好的选择?
同步框架不执行架构同步。 它预配表只是为了同步数据。如果要包含完整的服务器架构,则必须自己编写脚本并在客户端上执行。如果仅在同步 FK 之后,则可以在预配时将其作为数据库同步表描述的一部分包含在内。