如何解决错误“给定的ColumnMapping与源或目标中的任何列不匹配” [英] How can I solve the error "The given ColumnMapping does not match up with any column in the source or destination"

问题描述

我试图读取一个xml文件并将某些数据复制到列表中，使用bulkcopy我试图用数据库中的表映射，我想将我从xml获取的数据插入到数据库中的表中.I我正面临这个错误。

I was trying to read an xml file and copy certain data into the list,using bulkcopy I tried to map with the table in data base ,I want to unsert the data that i got from xml into table in the database.I am facing this error.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using System.Xml;
using System.Collections;


namespace bulk_xml
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = @"Data Source=DEVSQL01;Initial Catalog=LARGEDEVATR;Integrated Security=True";

            DataSet ds = new DataSet();
            ds.EnforceConstraints = false;
            DataTable sourceData = new DataTable();

            ds.ReadXml(@"C:\Users\t.mahidharreddy\Desktop\BulkData_P\BulkXMLFile.xml");
            //DataTable dt = ds.Tables["AtrEmVolumeArray"];
            sourceData = ds.Tables["AtrEmVolume"];
            DataTable dt= new DataTable();
            ds.Tables.Add("dt");
             dt = sourceData.Copy();
             
             


            XmlDocument doc = new XmlDocument();
            doc.Load(@"C:\Users\t.mahidharreddy\Desktop\BulkData_P\BulkXMLFile.xml");
            
            
            XmlNodeList xnList = doc.SelectNodes(".//AtrEmVolumeArray/AtrEmVolume");
            foreach (XmlNode xn in xnList)
            {

                foreach (XmlNode item in xn.ChildNodes)
                {
                    Console.WriteLine(item.InnerText);


                }


                //Console.ReadLine();
            }
           // Console.WriteLine(dt.Columns.Count.ToString());

            //open the destination data
            using (SqlConnection destinationConnection =
                            new SqlConnection(connectionString))
            {
                // open the connection
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy =
                        new SqlBulkCopy(destinationConnection.ConnectionString,
                            SqlBulkCopyOptions.TableLock))
                {
                    bulkCopy.SqlRowsCopied +=
                        new SqlRowsCopiedEventHandler(OnSqlRowsTransfer);
                    //bulkCopy.NotifyAfter = 100;
                    bulkCopy.BatchSize = 50;
                    bulkCopy.DestinationTableName = "AtrEmVolume";
                   
                 
                    
                    foreach (XmlNode xmlNode in xnList)
                    {
                        int i=0;
                        foreach (XmlNode xmlNode1 in xmlNode.ChildNodes)
                        {


                            
                            bulkCopy.ColumnMappings.Add(
                                dt.Columns[i].ColumnName.ToString().ToUpper(), xmlNode1.ToString().ToUpper());
                            i++;
                        }
                       
                    }
                   

                   dt.Columns.Remove("ATREMVOLUMEARRAY_ID");


                  
                   bulkCopy.WriteToServer(dt);
                }
            }
        }

        


        private static void OnSqlRowsTransfer(object sender,
                SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine("Copied {0} so far...", e.RowsCopied);
        }


    }
}

推荐答案

你应该是能够简化你的代码并只使用数据表。

You should be able to simplify your code and only use the data table.

static void Main(string[] args)
{
    string connectionString = @"Data Source=DEVSQL01;Initial Catalog=LARGEDEVATR;Integrated Security=True";

    DataSet ds = new DataSet();
    ds.EnforceConstraints = false;

    ds.ReadXml(@"C:\Users\t.mahidharreddy\Desktop\BulkData_P\BulkXMLFile.xml");
    DataTable dt = ds.Tables["AtrEmVolumeArray"].Copy();
     
    //open the destination data
    using (SqlConnection destinationConnection = new SqlConnection(connectionString))
    {
        // open the connection
        destinationConnection.Open();
        using (SqlBulkCopy bulkCopy =
                    new SqlBulkCopy(destinationConnection.ConnectionString,
                    SqlBulkCopyOptions.TableLock))
        {
            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsTransfer);
            //bulkCopy.NotifyAfter = 100;
            bulkCopy.BatchSize = 50;
            bulkCopy.DestinationTableName = "AtrEmVolume";
           
            
            dt.Columns.Remove("ATREMVOLUMEARRAY_ID");
            foreach (DataColumn dc in dt.Columns)
            {
                // The source column name should not use ToUpper
                // The destination column name might use ToUpper, 
                // but that depends on the database table
                bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName.ToUpper());
            }
           
        
           bulkCopy.WriteToServer(dt);
        }
    }
}

这篇关于如何解决错误“给定的ColumnMapping与源或目标中的任何列不匹配”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！