SqlBulkCopy的不工作 [英] SqlBulkCopy Not Working

问题描述

我有一个人口从Excel工作表的DataSet 。我想用SQLBulk复制到插入 Lead_Hdr 表，其中 LeadId 是PK记录。

我在执行下面的code有以下错误：

  

给定ColumnMapping不与在任何列匹配
  源或目的

 字符串构造= ConfigurationManager.ConnectionStrings [构造]的ToString（）。使用（SqlBulkCopy的S =新SqlBulkCopy的（构造，SqlBulkCopyOptions.KeepIdentity））
{
    如果（MySql.State == ConnectionState.Closed）
    {
        MySql.Open（）;
    }    s.DestinationTableName =PCRM_Lead_Hdr;
    s.NotifyAfter = 10000;    ＃地区评论
    s.ColumnMappings.Clear（）;    ＃区域ColumnMapping
    s.ColumnMappings.Add（客户端ID，客户端ID）;
    s.ColumnMappings.Add（LeadID，LeadID）;
    s.ColumnMappings.Add（COMPANY_NAME，COMPANY_NAME）;
    s.ColumnMappings.Add（简称网站，网站）;
    s.ColumnMappings.Add（EMPLOYEECOUNT，EMPLOYEECOUNT）;
    s.ColumnMappings.Add（收入，收入）;
    s.ColumnMappings.Add（地址，地址）;
    s.ColumnMappings.Add（城，城）;    s.ColumnMappings.Add（国家，国家）;
    s.ColumnMappings.Add（ZIP code，邮编code）;
    s.ColumnMappings.Add（CountryId，CountryId）;    s.ColumnMappings.Add（手机，手机）;
    s.ColumnMappings.Add（传真，传真）;
    s.ColumnMappings.Add（时区，时区）;
    s.ColumnMappings.Add（SicNo，SicNo）;
    s.ColumnMappings.Add（SicDesc，SicDesc）;    s.ColumnMappings.Add（的SourceID，的SourceID）;
    s.ColumnMappings.Add（ResearchAnalysis，ResearchAnalysis）;
    s.ColumnMappings.Add（BasketID，BasketID）;
    s.ColumnMappings.Add（PipeLineStatusId，PipeLineStatusId）;    s.ColumnMappings.Add（SurveyId，SurveyId）;
    s.ColumnMappings.Add（NextCallDate，NextCallDate）;
    s.ColumnMappings.Add（CurrentRecStatus，CurrentRecStatus）;
    s.ColumnMappings.Add（AssignedUserId，AssignedUserId）;
    s.ColumnMappings.Add（AssignedDate，AssignedDate）;
    s.ColumnMappings.Add（ToValueAmt，ToValueAmt）;
    s.ColumnMappings.Add（删除，删除）;
    s.ColumnMappings.Add（发行，释放）;    s.ColumnMappings.Add（Insert_Date，Insert_Date）;
    s.ColumnMappings.Add（Insert_By，Insert_By）;
    s.ColumnMappings.Add（Updated_Date，Updated_Date）;
    s.ColumnMappings.Add（Updated_By，Updated_By）;    #endregion
    #endregion    s.WriteToServer（sourceTable会）;    S.CLOSE（）;    MySql.Close（）;
}
 

解决方案

好了，是不是？别列名双方存在？

说实话，我从来没有与映射困扰。我喜欢让事情变得简单 - 我往往有一个临时表，看起来像在服务器上的输入，然后我 SqlBulkCopy的到临时表中，最后运行存储过程从临时表到实际表移到表;优点：

• 与现场数据损坏任何问题，如果进口在任何点
失败

• 我可以把一个交易就在存储过程


• 我可以有没有日志记录的BCP工作，安全知识的存储过程将被记录


• 这是简单;-p（与映射无需复杂）

至于最后一个问题 - 如果你正在处理大量数据，你可以使用更好的吞吐量的IDataReader （因为这是一个流API，其中，为数据表是一个缓冲API）。例如，我倾向于挂钩CSV导入了使用 CsvReader 作为源的使用SqlBulkCopy。另外，我已经写垫片周围的XmlReader 来present每一级元素在一排的IDataReader - 非常快

I have a DataSet populated from Excel Sheet. I wanted to use SQLBulk Copy to Insert Records in Lead_Hdr table where LeadId is PK.

I am having following error while executing the code below:

The given ColumnMapping does not match up with any column in the source or destination

string ConStr=ConfigurationManager.ConnectionStrings["ConStr"].ToString();

using (SqlBulkCopy s = new SqlBulkCopy(ConStr,SqlBulkCopyOptions.KeepIdentity))
{
    if (MySql.State==ConnectionState.Closed)
    {
        MySql.Open();
    }

    s.DestinationTableName = "PCRM_Lead_Hdr";
    s.NotifyAfter = 10000;

    #region Comment
    s.ColumnMappings.Clear();

    #region ColumnMapping
    s.ColumnMappings.Add("ClientID", "ClientID");
    s.ColumnMappings.Add("LeadID", "LeadID");
    s.ColumnMappings.Add("Company_Name", "Company_Name");
    s.ColumnMappings.Add("Website", "Website");
    s.ColumnMappings.Add("EmployeeCount", "EmployeeCount");
    s.ColumnMappings.Add("Revenue", "Revenue");
    s.ColumnMappings.Add("Address", "Address");
    s.ColumnMappings.Add("City", "City");

    s.ColumnMappings.Add("State", "State");
    s.ColumnMappings.Add("ZipCode", "ZipCode");
    s.ColumnMappings.Add("CountryId", "CountryId");

    s.ColumnMappings.Add("Phone", "Phone");
    s.ColumnMappings.Add("Fax", "Fax");
    s.ColumnMappings.Add("TimeZone", "TimeZone");
    s.ColumnMappings.Add("SicNo", "SicNo");
    s.ColumnMappings.Add("SicDesc", "SicDesc");

    s.ColumnMappings.Add("SourceID", "SourceID");
    s.ColumnMappings.Add("ResearchAnalysis", "ResearchAnalysis");
    s.ColumnMappings.Add("BasketID", "BasketID");
    s.ColumnMappings.Add("PipeLineStatusId", "PipeLineStatusId");

    s.ColumnMappings.Add("SurveyId", "SurveyId");
    s.ColumnMappings.Add("NextCallDate", "NextCallDate");
    s.ColumnMappings.Add("CurrentRecStatus", "CurrentRecStatus");
    s.ColumnMappings.Add("AssignedUserId", "AssignedUserId");
    s.ColumnMappings.Add("AssignedDate", "AssignedDate");
    s.ColumnMappings.Add("ToValueAmt", "ToValueAmt");
    s.ColumnMappings.Add("Remove", "Remove");
    s.ColumnMappings.Add("Release", "Release");

    s.ColumnMappings.Add("Insert_Date", "Insert_Date");
    s.ColumnMappings.Add("Insert_By", "Insert_By");
    s.ColumnMappings.Add("Updated_Date", "Updated_Date");
    s.ColumnMappings.Add("Updated_By", "Updated_By");

    #endregion
    #endregion

    s.WriteToServer(sourceTable);

    s.Close();

    MySql.Close();
}

解决方案

Well, is it right? Do the column names exist on both sides?

To be honest, I've never bothered with mappings. I like to keep things simple - I tend to have a staging table that looks like the input on the server, then I SqlBulkCopy into the staging table, and finally run a stored procedure to move the table from the staging table into the actual table; advantages:

• no issues with live data corruption if the import fails at any point
• I can put a transaction just around the SPROC
• I can have the bcp work without logging, safe in the knowledge that the SPROC will be logged
• it is simple ;-p (no messing with mappings)

As a final thought - if you are dealing with bulk data, you can get better throughput using IDataReader (since this is a streaming API, where-as DataTable is a buffered API). For example, I tend to hook CSV imports up using CsvReader as the source for a SqlBulkCopy. Alternatively, I have written shims around XmlReader to present each first-level element as a row in an IDataReader - very fast.

这篇关于SqlBulkCopy的不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！