Dapper.NET存储过程参数报错及Web API重复值处理需求咨询

先提个小建议：你现有代码里Task.Run(() => ...).Wait()这种写法会阻塞线程，在Web API里非常不推荐，容易导致性能瓶颈甚至死锁，咱们直接改成异步await的方式，同时加上事务保证操作的原子性（要么删和加都成功，要么都回滚）。

优化后的Web API代码 #

public async Task<CoCApiResponse> CreateVersion(CreateVersion versionObj)
{
    try
    {
        // 用await替代Wait()，非阻塞等待删除完成
        await versionRepository.DeleteVersions(objVersionCollection);
        // 异步创建新版本，直接获取结果
        var createdVersion = await versionRepository.CreateVersion(versionObj.version, lstVersion);
        
        return new CoCApiResponse 
        { 
            Success = true, 
            Data = createdVersion,
            Message = "重复版本已删除，新版本创建成功"
        };
    }
    catch (Exception ex)
    {
        return new CoCApiResponse 
        { 
            Success = false, 
            Message = $"操作失败：{ex.Message}"
        };
    }
}

Repository层实现建议 #

1. DeleteVersions（异步删除重复记录）

public async Task DeleteVersions(IEnumerable<Version> duplicateVersions)
{
    // 先提取要删除的版本标识（比如Id或者唯一键，根据你的重复判断规则来）
    var versionIds = duplicateVersions.Select(v => v.Id).ToList();
    
    using (var conn = new SqlConnection(_connectionString))
    {
        await conn.OpenAsync();
        // 执行删除，用Dapper的参数化查询避免SQL注入
        await conn.ExecuteAsync(@"
            DELETE FROM VersionTable 
            WHERE Id IN @VersionIds", 
            new { VersionIds = versionIds });
    }
}

2. CreateVersion（异步新增版本）

public async Task<Version> CreateVersion(string versionNumber, List<Version> versionData)
{
    // 这里假设你要插入单条，批量的话可以调整为ExecuteAsync
    var newVersion = versionData.First();
    newVersion.VersionNumber = versionNumber;
    
    using (var conn = new SqlConnection(_connectionString))
    {
        await conn.OpenAsync();
        // 插入后返回新创建的记录（包含自增Id等）
        return await conn.QuerySingleAsync<Version>(@"
            INSERT INTO VersionTable (VersionNumber, Description, CreatedDate)
            VALUES (@VersionNumber, @Description, GETDATE())
            SELECT Id, VersionNumber, Description, CreatedDate FROM VersionTable WHERE Id = SCOPE_IDENTITY()", 
            newVersion);
    }
}

关键注意事项：事务处理 #

因为删除和插入是两个独立操作，必须加事务保证原子性，否则可能出现删了旧记录但新记录插入失败的情况。调整下API和Repository的代码：

带事务的Web API

public async Task<CoCApiResponse> CreateVersion(CreateVersion versionObj)
{
    using (var conn = new SqlConnection(_connectionString))
    {
        await conn.OpenAsync();
        using (var transaction = conn.BeginTransaction())
        {
            try
            {
                // 把事务传递给Repository方法
                await versionRepository.DeleteVersions(objVersionCollection, transaction);
                var createdVersion = await versionRepository.CreateVersion(versionObj.version, lstVersion, transaction);
                
                transaction.Commit();
                return new CoCApiResponse { Success = true, Data = createdVersion };
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return new CoCApiResponse { Success = false, Message = ex.Message };
            }
        }
    }
}

支持事务的Repository方法

public async Task DeleteVersions(IEnumerable<Version> duplicateVersions, SqlTransaction transaction)
{
    var versionIds = duplicateVersions.Select(v => v.Id).ToList();
    // 执行时传入transaction参数
    await transaction.Connection.ExecuteAsync(@"
        DELETE FROM VersionTable 
        WHERE Id IN @VersionIds", 
        new { VersionIds = versionIds }, transaction: transaction);
}

public async Task<Version> CreateVersion(string versionNumber, List<Version> versionData, SqlTransaction transaction)
{
    var newVersion = versionData.First();
    newVersion.VersionNumber = versionNumber;
    
    return await transaction.Connection.QuerySingleAsync<Version>(@"
        INSERT INTO VersionTable (VersionNumber, Description, CreatedDate)
        VALUES (@VersionNumber, @Description, GETDATE())
        SELECT Id, VersionNumber, Description, CreatedDate FROM VersionTable WHERE Id = SCOPE_IDENTITY()", 
        newVersion, transaction: transaction);
}

内容的提问来源于stack exchange，提问作者Ramesh Rajendran