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 executing a stored procedure as follows

var transaction = this.db.Query<PaymentTransactions>("usp_PaymentTransactionsGetSingleIfPaid", new { registrationId }, commandType: CommandType.StoredProcedure);

The relevant part of the stored procedure that returns information is below.

SELECT * FROM PaymentTransactions WHERE RegistrationId = @registrationId AND TransactionStatus = 'SUCCESS';

UPDATE 2:

Dapper is working fine. Maybe there was something wrong with my dev environment. All it took was VS restart.

Answer 1

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:

Answer 2

Don't laugh, but I had this exact same problem with Dapper in an ASP.NET MVC project and the solution as in the comment from @erdinger worked also for me:

• Close Visual Studio
• Start Visual Studio again

The problem was fixed this way...