CODEWITHSUNDEEP
c#dapper
cs0815
cs0815

Reputation: 17388

generic parameterised sql query with dapper

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".

Upvotes: 4

Views: 3571

Answers (1)

robobot3000
robobot3000

Reputation: 589

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

Upvotes: 4

Related Questions