Non-Stored Procedure Query (CommandType=Text) with Dapper and parameters

Question

I am trying to call a piece of SQL with parameters in Dapper. It is NOT a stored procedure (I have that working fine with parameters)

inputCustomerName = "Our Customer";
inputStationName  = Null;

    var parameters = new
    {
        customerName = inputCustomerName ,
        stationName = inputStationName    
    };     

    ...

    using (var dbConn = dataProvider.CreateConnection)
    {
        dbConn.ConnectionString = connectionString;
        dbConn.Open();
        returnValue = dbConn.Query<T>(sql: sql, commandType: commandType, param: parameters);
        dbConn.Close();
    }

The relevant part of the SQL is

"    ...

     WHERE 
        Customer = ISNULL(@customerName,Customer)
        AND Station = ISNULL(@stationName,Station)
";

I keep getting "Invalid type owner for DynamicMethod". (I got this also when using DynamicParameters instead of the anomymous object).

The SQL runs fine on the database itself (given I declare and @populate @customerName and stationName).

I suspect I have done something quite simple wrong - can anyone enlighten me?

http://stackoverflow.com/questions/30435185/dapper-throws-invalid-type-owner-for-dynamicmethod is this helping? — Lenny32, May 04 '17 at 12:35
Read it before I posted, Not my situaition, I don't think - I am passing in an object with properties as params, not an array. — kpollock, May 04 '17 at 12:37
Can you try to set the value of inputStationName to DBNull.Value instead of null? — Lenny32, May 04 '17 at 12:43
I have previously tried populating both parameters with a value, it makes no difference. — kpollock, May 04 '17 at 12:48

kpollock · Accepted Answer · 2017-05-04T13:49:15.480

The answer was, in the end, an issue in code not included in the question - for which I thoroughly apologise.

The issue was that T was an Interface, and that was what was causing Dapper to blow up. There's probably something under the hood that means you can only use classes, not interfaces as the type param to Query. Though I have never seen that restriction explicitly stated.

Shame, (but I can imagine a few reasons why)

Non-Stored Procedure Query (CommandType=Text) with Dapper and parameters

1 Answers1