Confused about setting up parameters for an SQL Query

c# dapper orm sql sql-server

Question

I'm using Dapper ORM to query my database. I've familiarized myself with the query syntax, but I'm having issues with the parameters.

Here is the query that works:

orders = ctx.Query<OrderView>(myQuery, new { Seller = 104386, StatusID = 2, query = "people"});

This maps the parameters in myQuery (@Seller, @StatusID, @query) to these values and it works correctly.

However, in my program, I generate parameters on the fly and store them in an List<ObjectParameter>, so each parameter has a name and a value. However, I can't get this to work correctly in the query. Here is an example of what doesn't work:

orders = ctx.Query<OrderView>(myQuery, parameters.toArray());

I've also tried converting that to List<SqlParameter> but that doesn't work either. Does anyone know how I can replicate the working query with my parameters list?

Accepted Answer

ObjectParameter is an EF thing (System.Data.Entity.dll) and is not used in dapper. You want DynamicParameters, inside the dapper assembly / namespace:

var args = new DynamicParameters();
...
args.Add(name, value); // there are more complex usages, note
...
connection.Query<OrderView>(myQuery, args);

If you want to take more control - for example you really want to use ObjectParameter - that's fine too: you just write your own type that implements SqlMapper.IDynamicParameters, and pass that to dapper.



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