Sql Bulk Copy Truncating Decimal

According to the reference source, SqlBulkCopy always truncates decimal values instead of rounding, which unfortunately differs from the behavior of the BULK INSERT statement.

The private ConvertValue method calls TdsParser.AdjustSqlDecimalScale if the scale of the source value differs from the scale of the destination column:

switch(type.NullableType) {    case TdsEnums.SQLNUMERICN:    case TdsEnums.SQLDECIMALN:        // ...        if (sqlValue.Scale != metadata.scale) {                                        sqlValue = TdsParser.AdjustSqlDecimalScale(sqlValue, metadata.scale);          }

AdjustSqlDecimalScale in turn calls SqlDecimal.AdjustScale, passing false for 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;}

There's apparently no way to override this behavior and pass true to AdjustScale, so if you want to use SqlBulkCopy, you will need to round the values in the DataTable yourself before calling WriteToServer.

Alternatively, you could write the data to a file and execute BULK INSERT directly, forgoing SqlBulkCopy.