generic parameterised sql query with 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".

You're not passing the objectParams into the Query call. Is that a typo? — juharr
– juharr, Commented Oct 20, 2014 at 13:15

robobot3000 · Accepted Answer · 2014-10-20 13:17:13Z

4

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

answered Oct 20, 2014 at 13:17

robobot3000

5892 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cs0815 Over a year ago

Thanks. It is Monday after all (-:

Collectives™ on Stack Overflow

generic parameterised sql query with dapper

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related