SqlBulkCopy无法工作

Question

SqlBulkCopy无法工作

20

我有一个从Excel表格中填充的DataSet。我想使用SQLBulkCopy将记录插入到Lead_Hdr表中，其中LeadId是主键。

在执行下面的代码时，我遇到了以下错误：

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

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();
}

- Sandhurst

7个回答

22

这么说，对吗？两边都存在列名吗？

老实说，我从来不管映射。我喜欢保持简单 - 我倾向于在服务器上创建一个看起来像输入的暂存表，然后使用 SqlBulkCopy 插入临时表，最后运行存储过程将表从暂存表移动到实际表；好处：

如果导入失败，没有现有数据损坏的问题
我可以在 SPROC 周围加上事务
我可以让 bcp 在不记录日志的情况下工作，并确信 SPROC 会被记录
它很简单 ;-p（不必处理映射）

最后一点想法 - 如果您正在处理大量数据，则可以使用 IDataReader 获得更好的吞吐量（因为这是一种流式 API，而 DataTable 是缓冲 API）。例如，我倾向于使用 CsvReader 作为 SqlBulkCopy 的源来连接 CSV 导入。或者，我编写了包装 XmlReader 的代码，以将每个一级元素呈现为 IDataReader 中的一行 - 非常快。

- Marc Gravell

2

我对“Staging”这个术语还不熟悉，您能否解释一下？ - Sandhurst

6

一个分拣表是一个你将数据倾倒进去的表，通常不带有索引以提高写入速度。把数据倾倒进分拣表中，操作数据后，使用存储过程从分拣表写入到生产表（在事务中封装以防数据损坏）。 - Michael Meadows

3

请记住，暂存表中的数据是短暂的，因此在放置数据之前请清除它，并且不要使用它来检索数据，除非将其转换为输出到另一组表中。同时，请记住，暂存数据往往比您的“真实”数据规范性较低。 - Michael Meadows

2

其中之一的原因是：SqlBukCOpy是区分大小写的。按照以下步骤操作：

在这种情况下，您必须使用C＃中的“Contain”方法来查找源表中的列。
一旦您的目标列与源列匹配，请获取该列的索引并在SqlBukCOpy中指定其列名。

例如：

//Get Column from Source table 
  string sourceTableQuery = "Select top 1 * from sourceTable";
   DataTable dtSource=SQLHelper.SqlHelper.ExecuteDataset(transaction, CommandType.Text, sourceTableQuery).Tables[0];// i use sql helper for executing query you can use corde sw

 for (int i = 0; i < destinationTable.Columns.Count; i++)
                        {    //check if destination Column Exists in Source table
                            if (dtSource.Columns.Contains(destinationTable.Columns[i].ToString()))//contain method is not case sensitive
                            {
                                int sourceColumnIndex = dtSource.Columns.IndexOf(destinationTable.Columns[i].ToString());//Once column matched get its index
                                bulkCopy.ColumnMappings.Add(dtSource.Columns[sourceColumnIndex].ToString(), dtSource.Columns[sourceColumnIndex].ToString());//give coluns name of source table rather then destination table so that it would avoid case sensitivity
                            }

                        }
                        bulkCopy.WriteToServer(destinationTable);
                        bulkCopy.Close();

- Asad

2

Marc的答案是我推荐的方法（使用暂存表）。这样，如果您的数据源不改变，将来导入时就会遇到更少的问题。

然而，根据我的经验，您可以检查以下问题：

1. 源和表中的列名是否匹配？ 2. 列类型是否匹配？

如果您认为已经做到了上述检查，但仍然无法成功导入，则可以尝试以下方法：

1. 允许表中所有列为空值。 2. 注释掉所有列映射。 3. 逐一添加列，直到找到出现问题的位置。

这应该能找出错误所在。

- Tony Basallo

1

我会选择分阶段的想法，但是这是我处理大小写敏感性的方法。很高兴接受我的linq被批评。

using (SqlConnection connection = new SqlConnection(conn_str))
{
        connection.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = string.Format("[{0}].[{1}].[{2}]", targetDatabase, targetSchema, targetTable);
            var targetColumsAvailable = GetSchema(conn_str, targetTable).ToArray();
            foreach (var column in dt.Columns)
            {
                if (targetColumsAvailable.Select(x => x.ToUpper()).Contains(column.ToString().ToUpper()))
                {
                    var tc = targetColumsAvailable.Single(x => String.Equals(x, column.ToString(), StringComparison.CurrentCultureIgnoreCase));
                    bulkCopy.ColumnMappings.Add(column.ToString(), tc);
                }
            }

            // Write from the source to the destination.
            bulkCopy.WriteToServer(dt);
            bulkCopy.Close();
        }
}

和辅助方法

private static IEnumerable<string> GetSchema(string connectionString, string tableName)
        {



   using (SqlConnection connection = new SqlConnection(connectionString))
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandText = "sp_Columns";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("@table_name", SqlDbType.NVarChar, 384).Value = tableName;

            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return (string)reader["column_name"];
                }
            }
        }
    }

- Dickster

0

思考了很长时间才回答......即使列名大小写相同，如果数据类型不同，也会出现相同的错误。因此，请检查列名及其数据类型。

P.S.：暂存表是导入的终极方式。

- jocheng

0

我发现的是表格中的列和输入中的列必须至少匹配。你可以在表格中拥有更多的列，输入仍将被加载。如果你少了列，则会收到错误提示。

- Rich

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tareq · Accepted Answer

40

我在从Access复制数据到SQLSERVER 2005时遇到了同样的问题，发现列映射在两个数据源上都是大小写敏感的，而不管数据库是否敏感。

- Tareq

我遇到了同样的问题，这是一个已知的 bug： http://connect.microsoft.com/VisualStudio/feedback/details/94135/sqlbulkcopy-column-mappings-case-sensitivity - marcob

2

我正在使用SQL SERVER 2008 R2。这仍然是一个问题。所以大家要小心，显然在Sql 2008中列仍然区分大小写（在2013年）。我还更新了我的sql server 2008 R2到最新的服务包等。 - Harvey Darvey

2

虽然Marc的反馈很好，但这应该是被接受的答案，因为大小写敏感性可能是此错误的最常见原因（请参阅与SqlBulkCopy类相关的所有stackoverflow问题）。问题并不在于任何版本的SQL Server，因为SQL Server不关心列大小写，而是SqlBulkCopy .NET类强制执行大小写敏感性，而这个类应该有一个属性来禁用大小写敏感性。 - Chris Smith