How can I use Dapper's QueryMultiple with a stored procedure, but with a different parameter value each time?

Question

How can I use Dapper's QueryMultiple with a stored procedure, but with a different parameter value each time?

c# dapper sql-server

Question

I want to call a stored procedure in bulk with Dapper. Each call will return a different set of results, so I was using QueryMultiple. If I create the parameters like this:

    var parameters = new DynamicParameters();
    // The stored proc accepts a single integer, but I want to pass multiple so each call had a new value.
    parameters.Add("LookupID", lookupIds); 

    var resultSet = connection.QueryMultiple("SPName", parameters, commandType: System.Data.CommandType.StoredProcedure);

I get an error that the stored procedure has too many arguments specified. So how else can I accomplish this?

Zach

View more on Stack Overflow

Marc Gravell · Accepted Answer · 6/6/2016 6:55:09 AM

QueryMultiple is about the results, not the inputs. To do what you want, you need to call the SP lots of times. Which you can do either via a simple foreach loop yourself, or many (not all) of the dapper methods will allow a convenience method by passing in an IEnumerable<T> as the outermost object, for example:

int[] ids = ...
var args = ids.Select(id => new { id, val = "ABC" });
db.Execute("whatever", args);

Which will execute the SQL once per item passing in @id and @val for each. In the case of the async API this can also be combined with MARS to remove the cost of latency between calls, but this is opt-in because MARS is not a default option.

Alternatively, you could write a new wrapper SP that takes a single parameter and splits it (the new string_split would be ideal), and do the loop at the server.

How can I use Dapper's QueryMultiple with a stored procedure, but with a different parameter value each time?

Question

Fastest Dapper Plus Extensions

Expert Answer

Performance

Expression Evaluator