Using decimal with specific precision as output parameters with Dapper

c# dapper

Question

I am evaluating Dapper as a replacement for custom and cumbersome code and so far all was very good and promising. But this morning I have stumbled on a problem with Dynamic parameters and cannot find a solution.

A stored procedure calculates the Account Balance and the Available Balance for a customer returning its result in two decimal output parameters. These decimals are declared in the stored procedure with Precision=18 and Scale=2. This procedure works perfectly with the current standard methods. But in Dapper I cannot find a way to pass these parameters and specify the scale so all I get back is the integer part of the decimal value.

using (IDbConnection connection = OpenConnection())
{
    var args = new DynamicParameters(new { custID = customerID});

    // No way to set the scale here?
    args.Add("@accnt", dbType: DbType.Decimal, direction: ParameterDirection.Output);
    args.Add("@avail", dbType: DbType.Decimal, direction: ParameterDirection.Output);

    var results = connection.QueryMultiple("Customer_CalcBalance", args, commandType:CommandType.StoredProcedure);
    decimal account = args.Get<decimal>("@accnt");
    decimal availab = args.Get<decimal>("@avail");
}

And here is the question(s), there is a way to pass the scale for a decimal output parameter? Or there is a different way to accomplish my goal to get back the exact decimal values?

Accepted Answer

Well, seems that there is no way to resolve this problem (at least none has found a tentative answer) so I put this workaround that I have implemented to continue with the rest of the work.

var args = new DynamicParameters(new { custID = customerID});
args.Add("@accnt", dbType: DbType.Single, direction: ParameterDirection.Output);
args.Add("@avail", dbType: DbType.Single, direction: ParameterDirection.Output);

var results = connection.QueryMultiple("Customer_CalcBalance", args, commandType:CommandType.StoredProcedure);
decimal account = args.Get<decimal>("@accnt");
decimal availab = args.Get<decimal>("@avail");

I have passed the output parameters as Single instead of the expected type Decimal.
In this way, I suppose the SqlParameter.Scale property is set to something different than zero and I could get the decimals digits when I try to read the output parameters.
If someone has a better solution let me know.


Popular Answer

A little late to the party, but I thought I would mention that this was fixed in 2015. Here is the GitHub issue. Example usage:

public void Issue261_Decimals()
{
    var parameters = new DynamicParameters();
    parameters.Add("c", dbType: DbType.Decimal, direction: ParameterDirection.Output, precision: 10, scale: 5);
    connection.Execute("create proc #Issue261 @c decimal(10,5) OUTPUT as begin set @c=11.884 end");
    connection.Execute("#Issue261", parameters, commandType: CommandType.StoredProcedure);
    var c = parameters.Get<Decimal>("c");
    c.IsEqualTo(11.884M);
}



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why