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