Sql Bulk Copy Truncating Decimal Sign

Question

When I paste decimal values ​​into Sql Server 2005 from C # DataTable using bulk copy, the values ​​are truncated instead of rounded.

• The data type in the DataTable is decimal.
• The data type in the database is Decimal (19.3)
• The value in the DataTable is 1.0005
• Insert value in database 1.000 (I expected 1.001)

The code I use is pretty simple:

var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, null) { DestinationTableName = tableName};
bulkCopy.WriteToServer(dataTable);

Does anyone know how to fix this?

Answer 1

According to the original source, it SqlBulkCopyalways truncates decimal values ​​instead of rounding, which, unfortunately, differs from the behavior of the BULK INSERT statement.

ConvertValue TdsParser.AdjustSqlDecimalScale :

switch(type.NullableType) {
    case TdsEnums.SQLNUMERICN:
    case TdsEnums.SQLDECIMALN:
        // ...

        if (sqlValue.Scale != metadata.scale) {                            
            sqlValue = TdsParser.AdjustSqlDecimalScale(sqlValue, metadata.scale);  
        }

AdjustSqlDecimalScale SqlDecimal.AdjustScale, false fRound:

static internal SqlDecimal AdjustSqlDecimalScale(SqlDecimal d, int newScale) {
    if (d.Scale != newScale) {
        return SqlDecimal.AdjustScale(d, newScale - d.Scale, false /* Don't round, truncate.  MDAC 69229 */);
    }

    return d;
}

-, true AdjustScale, , SqlBulkCopy, DataTable WriteToServer.

BULK INSERT , SqlBulkCopy.