Problem while fetching result of SQL MONEY type output parameter

Topics: Data Access Application Block
Apr 3, 2013 at 1:54 PM
Hello,
We are currently using version 2.0 of enterprise library. We would like to know what is the equivalent DBType of SQL Server data type – MONEY? While searching, we found SQL data type MONEY to be equivalent to DBType.Decimal. However, when we use DBType.Decimal to fetch value of an output parameter of type MONEY, we do not get the correct result. The decimal part of the result is truncated. We even tried setting the scale and precision values using the AddParameter method, but the decimal part of the result still gets truncated.
This issue gets resolved when we use DBType.Double for the output parameter of type MONEY.

Can you please tell us which is the correct DBType which is equivalent to SQL data type MONEY? We would also like to know as to why we face an issue when we use DBType.Decimal?
Apr 4, 2013 at 6:24 AM
Edited May 1, 2013 at 9:32 PM
It looks like the scale never gets set. Even if you use the overload that accepts the scale the value is not used. This results in the value being left at the default value of 0 which causes no decimal places to be returned.

A fix that would work without modifying the Enterprise Library source code would be to check if you are dealing with a SqlParameter and if so then set the precision:
db.AddParameter(dbCommand, "@moneyValue", DbType.Decimal, int.MaxValue, ParameterDirection.Output,
    false, 19, 4, "", DataRowVersion.Default, null);

ConfigureScale(dbCommand.Parameters[0], 4);

db.ExecuteNonQuery(dbCommand);

// ....
public void ConfigureScale(DbParameter dbParameter, byte scale)
{
    SqlParameter sqlParameter = dbParameter as SqlParameter;

    if (sqlParameter != null)
    {
        sqlParameter.Scale = scale;
    }
}

Alternately, you could modify the source code for SqlDatabase to set the scale properly.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to