generic parameterised sql query with dapper

c# dapper

Question

I am using this generic method:

protected IEnumerable<T> ExecuteSprocQuery<T>(string sproc, object objectParams)
{
    using (var conn = OpenConnection())
    {
    var list = conn.Query<T>(sproc, null, commandType: CommandType.StoredProcedure);
    return list;
    }
}

to invoke stored procedures like this:

ExecuteSprocQuery<SomePoco>("SomeSproc", new { P1 = p1, P2 = p2 }).ToList().FirstOrDefault();

I am trying to implement something similar for parameterised sql queries:

protected IEnumerable<T> ExecuteQuery<T>(string sqlString, object objectParams)
{
    using (var conn = OpenConnection())
    {
    var list = conn.Query<T>(sqlString, null, commandType: CommandType.Text);
    return list;
    }
}

but something like this throws an error:

ExecuteQuery<int?>("Select id from [dbo].[TableName] where [X] = @x ", new { @x  = 1}).FirstOrDefault();

Any ideas?

PS:

The error is:

Additional information: Must declare the scalar variable "@x".

Accepted Answer

It looks like you are never passing the params object to Query() inside your methods. Without the code for your SP it's hard to tell, but it might be accepting nulls as parameters and thus not failing, while raw SQL will most probably just crash.

var list = conn.Query<T>(sproc, objectParams, commandType: CommandType.StoredProcedure);

var list = conn.Query<T>(sqlString, objectParams, commandType: CommandType.Text);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why