Using decimal numbers with specific precision as output parameters with Dapper

Question

I rate Dapper as replacing custom and cumbersome code, and so far everything has been very good and promising. But this morning I came across a problem with dynamic parameters and can not find a solution.

The stored procedure calculates the account balance and the available balance so that the client returns its result in two decimal output parameters. These decimals are declared in the stored procedure using Precision = 18 and Scale = 2. This procedure works great with modern standard methods. But in Dapper I can’t find a way to pass these parameters and specify the scale, so all I get 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), is there a way to pass the scale for the decimal output parameter? Or is there another way to accomplish my task to return exact decimal values?

Answer 1

Well, it seems that there is no way to solve this problem (at least none have found a preliminary answer), so I applied this workaround, which I performed to continue working.

 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 passed the output parameters as Single instead of the expected Decimal type.
Thus, I believe that the SqlParameter.Scale property has a value other than zero, and I can get decimal digits when I try to read the output parameters.
If anyone has a better solution, let me know.

Answer 2

A little late, but I thought that I would say that it was fixed in 2015. Here is the GitHub issue. Usage example:

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

Answer 3

I had the same problem. I changed my USP or request to pass varhcar (10) and successfully assigned the decimal value varchar (10)!