My stored procedure SQL statemet is very simple.
Delete From TableName where ID IN (@id)
I want to pass list or array from C# code and wants to RETURN Number of rows deleted.
Below is my code, somehow I am not convinced and think this is not the right way to do. What is the efficient way of doing 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);
}
}
Create a model to store the ids in the parameter. To get affected rows, take the result of the execute call.
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 approach
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 execute the Statement multiple times. Once for every object in the array list. It will still give you the total affected rows of executing all the statements.