Using decimal with specific precision as output parameters with 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?

So the output parameters are decimal right? But you want that to come back to you truncated (e.g. 101.78 becomes 101.00)? — Mike Perrenoud
– Mike Perrenoud, Commented Jul 16, 2013 at 14:22
No, the exact contrary. They come out truncated without decimals. Running the SP on SSMS gives 101.78, running the code above returns 101.00. I want the decimals. — Steve
– Steve, Commented Jul 16, 2013 at 14:41

Taylor Buchanan · Accepted Answer · 2018-02-09 17:25:39Z

4

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

answered Feb 9, 2018 at 17:25

Taylor Buchanan

4,8472 gold badges30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Over a year ago

Well, good to know. I need to go back to that work and test it. By the way, which version of Dapper does it?

Taylor Buchanan Over a year ago

Looks like it was included in v1.40

Steve · Accepted Answer · 2013-07-26 07:48:01Z

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.

ageroh · Accepted Answer · 2016-06-16 09:57:12Z

0

I had the same issue. I changed my USP or query to pass varhcar(10) and assign varchar(10) to decimal value with success!

answered Jun 16, 2016 at 9:57

ageroh

1321 silver badge4 bronze badges

Collectives™ on Stack Overflow

Using decimal with specific precision as output parameters with Dapper

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related