Dapper - Pass list of int to stored procedure

Question

Dapper - Pass list of int to stored procedure

c# dapper stored-procedures

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);
    }
}

Chatra

View more on Stack Overflow

Nkosi · Accepted Answer · 7/28/2019 8:40:37 PM

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.

Dapper - Pass list of int to stored procedure

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator