Dapper - 将 int 列表传递给存储过程

Question

我的存储过程SQL语句非常简单。

Delete From TableName where ID IN (@id)

我想从 C# 代码传递列表或数组，并希望返回已删除的行数。

下面是我的代码，不知怎的，我不相信并认为这不是正确的方法。这样做的有效方法是什么？

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

    }

}

Answer 1

创建一个模型以将 id 存储在参数中。要获取受影响的行，请获取执行调用的结果。using (SqlConnection connection = new SqlConnection(_connectionString)) {&nbsp; &nbsp; await connection.OpenAsync();&nbsp; &nbsp; var affectedRows = await connection.ExecuteAsync(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ProcedureNames.DeleteRules,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new { id = ids.ToArray() }, //<-- Passing collection&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; commandType: CommandType.StoredProcedure);}另一种方法using (SqlConnection connection = new SqlConnection(_connectionString)) {&nbsp; &nbsp; await connection.OpenAsync();&nbsp; &nbsp; var affectedRows = await connection.ExecuteAsync(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ProcedureNames.DeleteRules,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ids.Select(id => new { id = id }).ToArray(), //<-- Passing collection&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; commandType: CommandType.StoredProcedure);}会多次执行该语句。对于数组列表中的每个对象一次。它仍然会为您提供执行所有语句的受影响的总行数。