SqlBulkCopy - 数据源中给定的字符串类型的值无法转换为指定目标列的货币类型。

Question

当我尝试从 DataTable 进行 SqlBulkCopy 时，出现了这个异常。

Error Message: The given value of type String from the data source cannot be converted to type money of the specified target column.
Target Site: System.Object ConvertValue(System.Object, System.Data.SqlClient._SqlMetaData, Boolean, Boolean ByRef, Boolean ByRef)

我明白错误信息的含义，但是我如何获得更多信息，例如此错误发生在哪一行/字段？数据表格由第三方填充，可以包含多达200列和10k行。返回的列取决于发送给第三方的请求。所有数据表格列都是字符串类型。我的数据库列并非全部为varchar，因此，在执行插入之前，我使用以下代码格式化数据表格值（已删除不重要的代码）：

//--- create lists to hold the special data type columns
List<DataColumn> IntColumns = new List<DataColumn>();
List<DataColumn> DecimalColumns = new List<DataColumn>();
List<DataColumn> BoolColumns = new List<DataColumn>();
List<DataColumn> DateColumns = new List<DataColumn>();

foreach (DataColumn Column in dtData.Columns)
{
    //--- find the field map that tells the system where to put this piece of data from the 3rd party
    FieldMap ColumnMap = AllFieldMaps.Find(a => a.SourceFieldID.ToLower() == Column.ColumnName.ToLower());

    //--- get the datatype for this field in our system
    Type FieldDataType = Nullable.GetUnderlyingType(DestinationType.Property(ColumnMap.DestinationFieldName).PropertyType);

    //--- find the field data type and add to respective list
    switch (Type.GetTypeCode(FieldDataType))
    {
        case TypeCode.Int16:
        case TypeCode.Int32:
        case TypeCode.Int64: { IntColumns.Add(Column); break; }
        case TypeCode.Boolean: { BoolColumns.Add(Column); break; }
        case TypeCode.Double:
        case TypeCode.Decimal: { DecimalColumns.Add(Column); break; }
        case TypeCode.DateTime: { DateColumns.Add(Column); break; }
    }

    //--- add the mapping for the column on the BulkCopy object
    BulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(Column.ColumnName, ColumnMap.DestinationFieldName));
}

//--- loop through all rows and convert the values to data types that match our database's data type for that field
foreach (DataRow dr in dtData.Rows)
{
    //--- convert int values
    foreach (DataColumn IntCol in IntColumns)
        dr[IntCol] = Helpers.CleanNum(dr[IntCol].ToString());

    //--- convert decimal values
    foreach (DataColumn DecCol in DecimalColumns)
        dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());

    //--- convert bool values
    foreach (DataColumn BoolCol in BoolColumns)
        dr[BoolCol] = Helpers.ConvertStringToBool(dr[BoolCol].ToString());

    //--- convert date values
    foreach (DataColumn DateCol in DateColumns)
        dr[DateCol] = dr[DateCol].ToString().Replace("T", " ");
}

try
{
    //--- do bulk insert
    BulkCopy.WriteToServer(dtData);
    transaction.Commit();
}
catch (Exception ex)
{
    transaction.Rollback();

    //--- handles error
    //--- this is where I need to find the row & column having an issue
}

这段代码应该为所有目标字段格式化所有值。在出现此错误的情况下，清理数据的函数将删除任何不是0-9或。(小数点)的字符。在数据库中，抛出此错误的字段可以为空。

2级异常产生了以下错误:

Error Message: Failed to convert parameter value from a String to a Decimal.
Target Site: System.Object CoerceValue(System.Object, System.Data.SqlClient.MetaType, Boolean ByRef, Boolean ByRef, Boolean)

第三级异常出现以下错误：

Error Message: Input string was not in a correct format
Target Site: Void StringToNumber(System.String, System.Globalization.NumberStyles, NumberBuffer ByRef, System.Globalization.NumberFormatInfo, Boolean)

有人有什么想法来修复吗？或者有任何想法获取更多信息吗？

- Ricketts

---

查看文档，您可以使用NotifyAfter属性，在复制N行时触发事件。它可以帮助您确定默认发生的行所在的批次。 - shahkalpesh

你的 CleanDecimal 方法是尝试验证数据还是只是剥离无效字符？你是否尝试使用 Decimal.TryParse() 来检查数据是否实际上处于正确的格式？试一下看看它是否能找出问题值。 - Corey

以下是如何获取有关导致问题的特定列的更多信息的方法：https://medium.com/@shokrano/solving-the-the-given-value-of-type-string-from-the-data-source-cannot-be-converted-to-type-9e34ea3d815d - Shahar Shokrani

Answer 1

对于那些遇到类似有关 nvarchar 而不是 money 的错误消息的人：

来自数据源的类型为 String 的给定值无法转换为指定目标列的类型 nvarchar。

这可能是由于列长度过短造成的。

例如，如果您的列被定义为 nvarchar(20)，而您有一个包含 40 个字符的字符串，则可能会出现此错误。

来源

- Robotnik

---

这对我的使用情况场景有所帮助。谢谢！ - InfiniteFlash

Answer 2

请使用SqlBulkCopyColumnMapping。

示例：

private void SaveFileToDatabase(string filePath)
{
    string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["MHMRA_TexMedEvsConnectionString"].ConnectionString.ToString();

    String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
    //Create Connection to Excel work book 
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
    {
        //Create OleDbCommand to fetch data from Excel 
        using (OleDbCommand cmd = new OleDbCommand("Select * from [Crosswalk$]", excelConnection))
        {
            excelConnection.Open();
            using (OleDbDataReader dReader = cmd.ExecuteReader())
            {
                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                {
                    //Give your Destination table name 
                    sqlBulk.DestinationTableName = "PaySrcCrosswalk";

                    // this is a simpler alternative to explicit column mappings, if the column names are the same on both sides and data types match
                    foreach(DataColumn column in dt.Columns) {
                         s.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
                     }
                   
                    sqlBulk.WriteToServer(dReader);
                }
            }
        }
    }
}  

- Joshy Joseph

---

请看问题中的代码示例，其中设置了ColumnMappings。这与列的映射无关，而是指数据输入到这些列中的格式。 - Ricketts

尽管他们说SqlBulkCopy可以自动映射这些列，但事实上你确实需要指定它们，否则你会遇到这些类型的错误。至少对我来说是这样。 - Micro

我以前使用过自动映射，但是在一个包含近120列的数据集中，我遇到了与varchar不同的bigint相同的问题。简单地显式映射列解决了这个问题。真是让人费解。 - DotThoughts

这对我很有效。我之前使用了另一个答案中的 ToDataTable() 扩展方法，但它失败了。失败的原因是数据库中的列顺序与类中属性的顺序不同。 - Chris

直到我手动指定列映射之前，我也遇到了随机错误。在我的情况下，因为我已经手动设置了批量复制表列，所以我可以自动创建映射：foreach (DataColumn column in bulkCopyTable.Columns) { sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping( column.ColumnName, column.ColumnName)); } - dbruning

Answer 3

我不认为"请使用..."加上一些与问题无关的随机代码是一个好答案，但我相信其精神是正确的，因此我决定回答这个问题。

当您使用Sql Bulk Copy时，它会尝试直接将您的输入数据与服务器上的数据对齐。因此，它会取服务器表并执行类似于以下SQL语句的操作：

INSERT INTO [schema].[table] (col1, col2, col3) VALUES

因此，即使您的列名称匹配（例如：col1、col3、col2），但如果您将它们分别指定为列 1、列 3 和列 2，它也会插入如下所示：

INSERT INTO [schema].[table] (col1, col2, col3) VALUES
                          ('col1', 'col3', 'col2')

对于 Sql Bulk Insert 来说，确定列映射将会增加额外的工作量和开销。因此，它允许你选择... 要么确保你的代码和 SQL 表列以相同顺序排列，要么明确声明按列名对齐。

因此，如果您的问题是列错位，这可能是导致错误的主要原因，那么这篇文章就是为您准备的。

简而言之

using System.Data;
//...
myDataTable.Columns.Cast<DataColumn>().ToList().ForEach(x => 
    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName)));

这将使用现有的DataTable，尝试将其插入到创建的BulkCopy对象中，并明确地将名称映射到名称。当然，如果出于某种原因，您决定将DataTable列命名方式与SQL Server列不同...那就是您的问题。

- Suamere

---

那个！非常感谢。这解释了我一直遇到的很多问题。 - trailmax

我遇到了类似的异常，即使将我的类属性与表格对齐也无法解决它。非常好的解决方案！ - David Peters

DataTable.Columns没有Cast方法。 - JWiley

@JWiley 你必须是一个LINQ用户才能看到Cast扩展。要包含LINQ功能，请在文件顶部键入using System.Linq;。或者，写出.Columns.Cast<...调用，获取错误，在错误上放置光标，并使用Ctrl + .控制周期快捷键（默认情况下）。这应该包括所需的基本库。 - Suamere

谢谢你！我停工几周后（当时它还能用），进行了数据库刷新，旧的表结构被恢复了。我没有意识到两列被交换了，直到我读到这篇文章。你帮我省了些功夫，虽然我认为列映射是可行的，但我还是老派的，喜欢让我的数据源直接匹配我的表以进行批量复制。 - j.hull

Answer 4

@Corey - 它只是简单地去除所有无效字符。不过，你的评论让我想到了答案。

问题在于我的数据库中许多字段是可为空的。使用 SqlBulkCopy 时，空字符串不会被插入为 null 值。因此，在我的非 varchar 字段（如 bit、int、decimal、datetime 等）的情况下，它试图插入一个空字符串，显然这对于该数据类型是无效的。

解决方法是修改我的循环，针对每个非字符串数据类型都进行值验证（对于每个数据类型都要重复此操作）。

//--- convert decimal values
foreach (DataColumn DecCol in DecimalColumns)
{
     if(string.IsNullOrEmpty(dr[DecCol].ToString()))
          dr[DecCol] = null; //--- this had to be set to null, not empty
     else
          dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());
}

在进行了上述调整之后，所有内容都可以无误地插入。

- Ricketts

Answer 5

请确保在实体类中添加的列值与目标表中的顺序相同，并且具有相应的get set属性。

- santhoshraj

---

为什么被踩了？我遇到了这个例外问题，是由于列顺序不正确所致。 - Nicklas Møller Jepsen

Answer 6

可能不适用于所有人，但对我非常有用：

所以，我遇到了这个确切的问题。我似乎遇到的问题是，当我的DataTable没有一个ID列时，目标表格却有一个带有主键的ID列。

当我将我的DataTable调整为具有ID列时，复制就可以正常工作了。

在我的场景中，Id列并不是非常重要，因此我从目标表格中删除了此列，并且SqlBulkCopy无任何问题地工作。

- Ashetynw

Answer 7

在尝试映射字符串长度的列时，你需要注意另一个问题， 例如TK_NO nvarchar(50)，你必须将其映射到与目标字段相同的长度。

- Medhat Makram

---

这只是一条评论质量很高的答案。 - Mathews Sunny

Answer 8

我偶尔遇到了同样的错误。注意：列映射都是正确的，这就是代码大部分时间能正常工作的原因。

我发现根本原因是字符串长度问题。目标表的列数据类型为nvarchar(255)，而发送的值的字符串长度超过了255个字符。

通过增加数据库中的列长度来解决了这个问题。

附注：遗憾的是，错误消息不会告诉你是哪个表的哪一列导致了这个错误。你必须手动猜测/找出来。

- Hemant Chauhan

Answer 9

我的问题在于列映射而不是值。我正在从开发系统中提取数据，创建目标表，批量复制内容，然后从生产系统中提取数据，调整目标表并批量复制内容，因此两个批量复制的列顺序不匹配。

// explicitly setting the column mapping even though the source & destination column names
// are the same as the column orders will affect the bulk copy giving data conversion errors
foreach (DataColumn column in p_dataTable.Columns)
{
  bulkCopy.ColumnMappings.Add(
    new()
    {
      SourceColumn = column.ColumnName,
      DestinationColumn = column.ColumnName
    }
  );
}

bulkCopy.WriteToServer(p_dataTable);

- Pierre Collard Suero

Answer 10

简短回答：将类型从nvarchar（size）更改为nvarchar（Max），这是字符串长度大小的问题。

注意：以上所有建议都促使我写下这个简短的答案。

- suntosh aqula

---

更喜欢在回答开头写一个长的解释，然后再加上一个快速总结，而不是只给出一个简短的回答。 - Elikill58