c# - 如何同步两个不同数据库表中的不同列名？

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 9 年前。

Improve this question

您好，我想同步 2 个不同的数据库、表和列。我创建了 SYNC 应用程序。通过使用同步框架。它由 azure 托管。我还阅读了以下文章:

http://jtabadero.wordpress.com/2011/08/19/part-4-synchronizing-tables-with-different-table-names-and-column-names/ http://www.devart.com/dotconnect/oracle/docs/SyncFramework.html
但我有一个 Nullreference 异常。 OrderTable 没有正确的列。看照片吧如何解决这个问题。

namespace WorkerRole1
{
    public class WorkerRole : RoleEntryPoint
    {
        public override void Run()
        {
            // This is a sample worker implementation. Replace with your logic.
            Trace.TraceInformation("WorkerRole1 entry point called", "Information");
            Setup();
            while (true)
            {
                Sync();
                Thread.Sleep(10000);
                Trace.TraceInformation("Working", "Information");
            }
        }

    private void Setup()
    {

        string scopeName = "DifferentSchemaScope";
        string MemberSQLAzureConnectionString = ConfigurationManager.ConnectionStrings["MemberSQLAzureConnectionString"].ConnectionString;
        string HubSQLAzureConnectionString = ConfigurationManager.ConnectionStrings["HubSQLAzureConnectionString"].ConnectionString;
        using (SqlConnection sqlMemberAzureConn = new SqlConnection(MemberSQLAzureConnectionString))
        {

            using (SqlConnection sqlHubAzureConn = new SqlConnection(HubSQLAzureConnectionString))
            {


                if (sqlHubAzureConn.State == System.Data.ConnectionState.Open && sqlMemberAzureConn.State == ConnectionState.Open)
                {


                    DbSyncScopeDescription myScope = new DbSyncScopeDescription(scopeName);

                    DbSyncTableDescription serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("myTest.SourceOrderTable", sqlHubAzureConn);
                    serverTableDesc.GlobalName = "OrderTable";

                    DbSyncTableDescription clientTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("myTest.DestinationOrderTable", sqlMemberAzureConn);


                    clientTableDesc.GlobalName = "OrderTable";

                    myScope.Tables.Add(serverTableDesc);
                    myScope.Tables.Add(clientTableDesc);




                    SqlSyncScopeProvisioning sqlServerProv = new SqlSyncScopeProvisioning(myScope);
                    SqlSyncScopeProvisioning sqlAzureProv = new SqlSyncScopeProvisioning(myScope);

                    sqlServerProv.PopulateFromScopeDescription(myScope);




                    myScope.Tables["OrderTable"].Columns.Remove(myScope.Tables["OrderTable"].Columns["OrderQty"]);
                    myScope.Tables["OrderTable"].Columns["OrderId"].IsPrimaryKey = true;
                    sqlAzureProv.PopulateFromScopeDescription(myScope);



                    if (!sqlServerProv.ScopeExists(scopeName, sqlHubAzureConn))
                    {

                        sqlServerProv.Apply(sqlHubAzureConn);

                    }

                    // sqlAzureProv.SetCreateTableDefault(DbSyncCreationOption.Skip);

                    if (!sqlAzureProv.ScopeExists(scopeName, sqlMemberAzureConn))
                    {

                        sqlAzureProv.Apply(sqlMemberAzureConn);
                    }
                }
            }
        }
    }


    private void Sync()
    {

        string scopeName = "DifferentSchemaScope";
        string MemberSQLAzureConnectionString = ConfigurationManager.ConnectionStrings["MemberSQLAzureConnectionString"].ConnectionString; //CloudConfigurationManager.GetSetting("MemberSQLAzureConnectionString");
        string HubSQLAzureConnectionString = ConfigurationManager.ConnectionStrings["HubSQLAzureConnectionString"].ConnectionString;  //CloudConfigurationManager.GetSetting("HubSQLAzureConnectionString");
        using (SqlConnection sqlMemberAzureConn = new SqlConnection(MemberSQLAzureConnectionString))
        {

            using (SqlConnection sqlHubAzureConn = new SqlConnection(HubSQLAzureConnectionString))
            {

                var localProvider = new SqlSyncProvider(scopeName, sqlHubAzureConn);
                var remoteProvider = new SqlSyncProvider(scopeName, sqlMemberAzureConn);

                remoteProvider.ChangesSelected += remoteProvider_ChangesSelected;

                SyncOrchestrator syncOrchestrator = new SyncOrchestrator
                {
                    LocalProvider = localProvider,
                    RemoteProvider = remoteProvider,
                    Direction = SyncDirectionOrder.UploadAndDownload
                };

                syncOrchestrator.Synchronize();
            }
        }
    }



    void remoteProvider_ChangesSelected(object sender, DbChangesSelectedEventArgs e)
    {
        if (e.Context.DataSet.Tables.Contains("OrderTable"))
        {
            DataTable dataTable = new DataTable();
            dataTable = e.Context.DataSet.Tables["OrderTable"];

            //rename the columns to match the destination table’s column names
            dataTable.Columns["OrderId"].ColumnName = "OrderNo";
            dataTable.Columns["OrderDesc"].ColumnName = "OrderDetail";

        }
    }

    public override bool OnStart()
    {
        // Set the maximum number of concurrent connections 
        ServicePointManager.DefaultConnectionLimit = 12;

        // For information on handling configuration changes
        // see the MSDN topic at http://go.microsoft.com/fwlink/?LinkId=166357.

        return base.OnStart();
    }
}

}

SQL:

id 应该是主键......

创建架构 myTest

创建表 [myTest].[SourceOrderTable]( [OrderId] [int] IDENTITY(1,1) NOT NULL, [OrderDesc] nvarchar NULL)

CREATE TABLE [myTest].[DestinationOrderTable](
[OrderNo] [int] IDENTITY(1,1) NOT NULL,
[OrderDetail] [nvarchar](50) NULL,
[OrderQty] int NULL)

但它不起作用。它在“void remoteProvider_ChangesSelected(object sender, DbChangesSelectedEventArgs e)”上产生了错误，这就是为什么remoteprovider列名称没有更改。remoteProvider列名称是“OrderNo,OrderDetail”，但它必须是“OrderId，OrderDesc”，看图片:

错误图片:

Answer 1

检查您的同步方向。它说上传和下载，您将 ChangesSelected 绑定(bind)到作为您的目的地的远程提供商上。

该事件将在下载时触发​​，您的“目的地”现在就是您的来源。所以数据集中的内容是有效的，这就是选择更改的目标表中的结构(现在是源，因为下载时同步方向相反)

由于您正在进行双向同步，因此两个提供程序上都应该有 ChangesSelected 事件。

本地提供商 ChangesSelected 应映射 OrderId -> OrderNo/OrderDesc->OrderDetail

远程提供程序 ChangesSelected 应该与 OrderNo -> OrderId/OrderDetail->OrderDesc 相反