Question
My SQL statemet saved process is really straightforward.
Delete From TableName where ID IN (@id)
I need to provide a list or an array from C# code and get the number of removed rows.
Here is my code; for some reason, I'm not sure this is the best course of action. What is the most effective approach to go about this?
using (SqlConnection connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
DynamicParameters parameters = new DynamicParameters();
foreach (var id in ids)
{
parameters.Add("@Id", id, DbType.Int32, ParameterDirection.Input);
await connection.ExecuteAsync(
ProcedureNames.DeleteRules,
parameters,
commandType: CommandType.StoredProcedure);
}
}
Accepted Answer
To save the ids in the parameter, create a model. Take the execute call's result to find the rows that were impacted.
using (SqlConnection connection = new SqlConnection(_connectionString)) {
await connection.OpenAsync();
var affectedRows = await connection.ExecuteAsync(
ProcedureNames.DeleteRules,
new { id = ids.ToArray() }, //<-- Passing collection
commandType: CommandType.StoredProcedure);
}
Another strategy
using (SqlConnection connection = new SqlConnection(_connectionString)) {
await connection.OpenAsync();
var affectedRows = await connection.ExecuteAsync(
ProcedureNames.DeleteRules,
ids.Select(id => new { id = id }).ToArray(), //<-- Passing collection
commandType: CommandType.StoredProcedure);
}
would repeat the Statement's execution. for each item in the array list, once. It will still display the total number of rows impacted by running each query.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow