Dapper: Not able to parse floats (Error parsing column)

Question

I am retrieving data from SQL Server from a StoredProcedure using Dapper and I'm getting error `Specified cast is not valid.` and details: `Error parsing column 4 (SubTotal=0.00 - Decimal)` On SQL Server side the column `SubTotal` is `decimal(18, 2) NULLABLE` and on .NET side it's `decimal?`. The data being retrieved is `0.00`. I checked this answer: Dapper,decimal to double? Error parsing column X As per answer, I replaced `il.Emit(OpCodes.Ldtoken, unboxType);` with `il.Emit(OpCodes.Ldtoken, Nullable.GetUnderlyingType(unboxType) ?? unboxType);` on line 2360 and still getting the same error. Anyone has any ideas about this? Thanks. Update: I tried making column non-nullable. Also tried changing column to `float` (on SQL Server) and `double` (on .NET side). None of these worked and I was getting the same error. Then I changed column to `int` and now code works fine. However, I'm working with monetary values and would like to use floating point numbers. Will investigate further... I'm e…

Accepted Answer

Seems like this is not Dapper specific, as I just verified the below snippet works as expected.

Try enumerating your column names explictly (instead of select *) so that the procedure returns exactly what should be mapped to `PaymentTransactions`. Its possible there is another non-decimal column that is misnamed?

This is using Dapper v1.13 on .Net45:

Procedure:

```
create procedure dbo.Test
as
select  [SubTotal] = cast('0.01' as decimal(18,2)) 
union all 
select null;
```

Linqpad:

```
void Main()
{
    using (IDbConnection cnn = GetOpenConnection())
    {
        var users = cnn.Query<Sale>("yak.dbo.test", new { }, commandType: CommandType.StoredProcedure);
        users.Dump();

}
}

public static readonly string connectionString = "Data Source=.;Initial Catalog=tempdb;Integrated Security=True";

public static IDbConnection GetOpenConnection()
{
  var connection = new SqlConnection(connectionString);
  connection.Open();
  return connection;
}

public class Sale
{
   public decimal? SubTotal;
}
```

Returns:

Dapper: Not able to parse floats (Error parsing column)

Solution

Problem

Related problems