SqlBulkCopy - 来自数据源的 String 类型的给定值无法转换为指定目标列的类型 money [英] SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column

问题描述

我在尝试从 DataTable 执行 SqlBulkCopy 时遇到此异常.

I'm getting this exception when trying to do an SqlBulkCopy from a DataTable.

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)

我明白错误的含义，但我如何获得更多信息，例如发生这种情况的行/字段?数据表由第 3 方填充，最多可包含 200 列和 10k 行.返回的列取决于发送给第 3 方的请求.所有数据表 列都是字符串类型.我的数据库中的列并不都是 varchar，因此，在执行插入之前，我使用以下代码格式化数据表值(删除了不重要的代码):

I understand what the error is saying, but how I can I get more information, such as the row/field this is happening on? The datatable is populated by a 3rd party and can contain up to 200 columns and up to 10k rows. The columns that are returned depend on the request sent to the 3rd party. All of the datatable columns are of string type. The columns in my database are not all varchar, therefore, prior to executing the insert, I format the datatable values using the following code (non important code removed):

//--- 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 或 .(小数点).这个抛出错误的字段在数据库中可以为空.

This code should format all values for their destination fields. In the case of this error, the decimal, the function that cleans that up will remove any character that is not 0-9 or . (decimal point). This field that is throwing the error would be nullable in the database.

2 级异常有这个错误:

The level 2 exception has this error:

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)

和级别 3 异常有这个错误:

and the level 3 exception has this error:

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)

有没有人有任何想法可以解决?或者有什么想法可以获取更多信息?

Does anyone have any ideas to fix? or any ideas to get more info?

推荐答案

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

@Corey - It just simply strips out all invalid characters. However, your comment made me think of the answer.

问题是我的数据库中的许多字段都可以为空.使用 SqlBulkCopy 时，不会插入空字符串作为空值.因此，在我的字段不是 varchar(位、整数、十进制、日期时间等)的情况下，它试图插入一个空字符串，这显然对该数据类型无效.

The problem was that many of the fields in my database are nullable. When using SqlBulkCopy, an empty string is not inserted as a null value. So in the case of my fields that are not varchar (bit, int, decimal, datetime, etc) it was trying to insert an empty string, which obviously is not valid for that data type.

解决方案是修改我的循环，在那里我验证这个值(对每个不是字符串的数据类型重复)

The solution was to modify my loop where I validate the values to this (repeated for each datatype that is not string)

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

进行上述调整后，一切正常插入.

After making the adjustments above, everything inserts without issues.

这篇关于SqlBulkCopy - 来自数据源的 String 类型的给定值无法转换为指定目标列的类型 money的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！