How to pass a list of integer to a Stored Procedure with Dapper?

c# dapper stored-procedures

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);
    }
}
1
0
7/28/2019 4:10:03 PM

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.

1
7/28/2019 8:40:37 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow