服务器到客户端返回到服务器同步

本文关键字:服务器 同步 客户端 返回 | 更新日期: 2023-09-27 18:32:18

我正在开发一个使用实体框架 6.0localdb 交互的 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 之后,则可以在预配时将其作为数据库同步表描述的一部分包含在内。