如何通过存储过程一次性从数据库中删除多个ID？

当前你的存储过程仅支持单个ID的软删除操作，仓储层通过循环调用存储过程实现批量删除，这种方式效率较低。下面提供两种优化方案，实现一次性批量处理多个ID：

方案一：使用表值参数（推荐，类型安全且性能优） #

1. 创建表值类型 #

首先在SQL Server中定义一个用于传递整数列表的表值类型：

CREATE TYPE [dbo].[IntListType] AS TABLE(
    [Id] INT NOT NULL
)

2. 修改存储过程 #

更新存储过程，将单个@Id参数替换为表值参数@Ids，并批量处理数据：

ALTER PROCEDURE [dbo].[YourProcedureName]
    @mode NVARCHAR(20),
    @Ids [dbo].[IntListType] READONLY, -- 新增表值参数
    @Master NVARCHAR(50) -- 保留原有的其他参数
AS
BEGIN
    IF(@mode='DELETE')
    BEGIN
        UPDATE ProjectCostingTypes 
        SET [IsDeleted] = 1, [ModifiedBy] = 1, [ModifiedOn] = GETDATE() 
        WHERE [ProjectCostingTypeId] IN (SELECT Id FROM @Ids)
        
        SELECT IsDeleted, ProjectCostingTypeId
        FROM ProjectCostingTypes 
        WHERE ProjectCostingTypeId IN (SELECT Id FROM @Ids)
    END
END

3. 修改仓储层C#代码 #

直接将List<int>转换为表值参数，一次性调用存储过程：

public async Task<string> DeleteMultipleProjectCostingTypeByID(List<int> Ids)
{
    try
    {
        using (var connection = _context.CreateConnection())
        {
            // 构建表值参数对应的DataTable
            var idTable = new DataTable();
            idTable.Columns.Add("Id", typeof(int));
            foreach (var id in Ids)
            {
                idTable.Rows.Add(id);
            }

            var parameters = new DynamicParameters();
            parameters.Add("@Ids", idTable.AsTableValuedParameter("dbo.IntListType"));
            parameters.Add(APIConstants.PARAM_NAME_MODE, APIConstants.PARAM_VALUE_DELETE);
            parameters.Add(APIConstants.PARAM_NAME_MASTER, APIConstants.PROJECT_COSTING_TYPE);

            // 批量执行存储过程
            await connection.QueryAsync<ProjectCostingTypes>(APIConstants.DELETE_PROJECT_COSTING_TYPES, parameters, commandType: CommandType.StoredProcedure);

            return "Deleted data successfully";
        }
    }
    catch (Exception ex)
    {
        return ex.Message;
    }
}

方案二：使用逗号分隔的ID字符串（无需创建表值类型，适合小批量ID） #

1. 修改存储过程 #

将参数改为接受逗号分隔的ID字符串，通过STRING_SPLIT函数分割后批量处理：

ALTER PROCEDURE [dbo].[YourProcedureName]
    @mode NVARCHAR(20),
    @IdList NVARCHAR(MAX), -- 替换原@Id为逗号分隔的字符串参数
    @Master NVARCHAR(50)
AS
BEGIN
    IF(@mode='DELETE')
    BEGIN
        UPDATE ProjectCostingTypes 
        SET [IsDeleted] = 1, [ModifiedBy] = 1, [ModifiedOn] = GETDATE() 
        WHERE [ProjectCostingTypeId] IN (SELECT CAST(value AS INT) FROM STRING_SPLIT(@IdList, ','))
        
        SELECT IsDeleted, ProjectCostingTypeId
        FROM ProjectCostingTypes 
        WHERE [ProjectCostingTypeId] IN (SELECT CAST(value AS INT) FROM STRING_SPLIT(@IdList, ','))
    END
END

2. 修改仓储层C#代码 #

将List<int>拼接为逗号分隔的字符串，传递给存储过程：

public async Task<string> DeleteMultipleProjectCostingTypeByID(List<int> Ids)
{
    try
    {
        using (var connection = _context.CreateConnection())
        {
            string idStr = string.Join(",", Ids);

            var parameters = new DynamicParameters();
            parameters.Add("@IdList", idStr);
            parameters.Add(APIConstants.PARAM_NAME_MODE, APIConstants.PARAM_VALUE_DELETE);
            parameters.Add(APIConstants.PARAM_NAME_MASTER, APIConstants.PROJECT_COSTING_TYPE);

            await connection.QueryAsync<ProjectCostingTypes>(APIConstants.DELETE_PROJECT_COSTING_TYPES, parameters, commandType: CommandType.StoredProcedure);

            return "Deleted data successfully";
        }
    }
    catch (Exception ex)
    {
        return ex.Message;
    }
}

内容的提问来源于stack exchange，提问作者Nikkhil N