Question
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);
}
}
Accepted Answer
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.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow