.NET 6 Windows服务用Dapper调用存储过程反复报I/O中止错误

问题概述 #

.NET 6 Windows服务通过Dapper 2.0.151 + System.Data.SqlClient 4.8.6调用存储过程usp_GetFeed（返回7万行数据）时，每2-3次就触发传输层错误：

Error during SearchPlugin.PullDataFromFeeds: Feed usp_GetFeed failed: [115520ms] ExecuteStoredProcedureAsync:

System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The I/O operation has been aborted because of either a thread exit or an application request.)
System.ComponentModel.Win32Exception (995): The I/O operation has been aborted because of either a thread exit or an application request.
at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)

at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)

--- End of stack trace from previous location ---

at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 647

at Search.Sync.SearchPlugin.Data.DataLayer.ExecuteStoredProcedureAsync(String sql) in C:_git\GlobalClearance\Search.Sync.Service\Search.Sync.SearchPlugin\Data\DataLayer.cs:line 245
ClientConnectionId:8cefc850-0dbd-4837-bbf5-6ef1e736ba86

SQL Server作业中运行该存储过程耗时约2分钟，无异常；已尝试设置Dapper.SqlMapper.Settings.CommandTimeout = 0，问题依旧。

核心原因 #

该错误并非命令超时，而是TCP连接层面的I/O操作被中止，可能由连接生命周期管理不当、线程被提前终止、网络配置限制或旧版SqlClient的异步缺陷导致。

解决方案 #

1. 修正数据库连接生命周期 #

避免长期持有类级别的_connection实例，改用短连接+using语句确保每次调用都创建新连接并正确释放：

public async Task<ExecuteStoredProcedureResult<TOutput>> ExecuteStoredProcedureAsync<TOutput>(string sql)
{
    var result = new ExecuteStoredProcedureResult<TOutput>();
    var sw = new Stopwatch();
    sw.Start();

    // 每次调用创建新连接，using自动释放
    using var connection = new SqlConnection(_connectionString);
    try
    {
        await connection.OpenAsync();
        result.Output = await connection.QueryAsync<TOutput>(sql, 
            commandType: CommandType.StoredProcedure, 
            commandTimeout: _commandTimeoutInSeconds);
        result.Success = true;
    }
    catch (Exception ex)
    {
        result.Errors.Add($"{ex}");
    }

    sw.Stop();
    result.Duration = (int)sw.Elapsed.TotalMilliseconds; // 适配Duration的int类型

    return result;
}

说明：ADO.NET连接池会自动复用连接，无需担心创建新连接的性能开销；长期持有连接容易因TCP闲置超时、服务器端连接回收导致传输错误。

2. 传递取消令牌避免线程提前中止 #

错误提示提到“线程退出或应用请求中止”，需确保取消令牌正确传递到数据库操作层面，避免上层任务取消时直接终止I/O操作：

// 修改方法签名添加取消令牌参数
public async Task<ExecuteStoredProcedureResult<TOutput>> ExecuteStoredProcedureAsync<TOutput>(string sql, CancellationToken cancellationToken = default)
{
    var result = new ExecuteStoredProcedureResult<TOutput>();
    var sw = new Stopwatch();
    sw.Start();

    using var connection = new SqlConnection(_connectionString);
    try
    {
        await connection.OpenAsync(cancellationToken);
        // 使用CommandDefinition统一传递配置和取消令牌
        var command = new CommandDefinition(sql, 
            commandType: CommandType.StoredProcedure, 
            commandTimeout: _commandTimeoutInSeconds,
            cancellationToken: cancellationToken);
        result.Output = await connection.QueryAsync<TOutput>(command);
        result.Success = true;
    }
    catch (OperationCanceledException)
    {
        result.Errors.Add("任务被主动取消");
    }
    catch (Exception ex)
    {
        result.Errors.Add($"{ex}");
    }

    sw.Stop();
    result.Duration = (int)sw.Elapsed.TotalMilliseconds;

    return result;
}

3. 迁移到Microsoft.Data.SqlClient #

System.Data.SqlClient已进入维护模式，不再积极修复异步传输相关bug，建议替换为官方推荐的Microsoft.Data.SqlClient：

• 卸载System.Data.SqlClient NuGet包，安装Microsoft.Data.SqlClient最新稳定版
• 将代码中的System.Data.SqlClient命名空间替换为Microsoft.Data.SqlClient

该库针对.NET 6有更好的兼容性，修复了多个TCP传输和异步操作的已知问题。

4. 优化结果集处理减少内存压力 #

7万行数据一次性加载到内存可能引发GC频繁触发或内存异常，改用流式异步枚举分批处理：

// 改为返回流式结果，避免一次性加载全部数据
public async IAsyncEnumerable<TOutput> ExecuteStoredProcedureStreamAsync<TOutput>(string sql, [EnumeratorCancellation] CancellationToken cancellationToken = default)
{
    using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync(cancellationToken);
    var command = new CommandDefinition(sql, 
        commandType: CommandType.StoredProcedure, 
        commandTimeout: _commandTimeoutInSeconds,
        cancellationToken: cancellationToken);
    
    // Dapper 2.0+支持直接返回IAsyncEnumerable
    await foreach (var item in connection.QueryAsync<TOutput>(command).AsAsyncEnumerable().WithCancellation(cancellationToken))
    {
        yield return item;
    }
}

5. 检查SQL Server与网络配置 #

• 在SQL Server执行sp_configure 'remote query timeout'，确保超时时间大于存储过程运行时间（默认600秒，足够覆盖2分钟执行时长）
• 启用SQL Server TCP keepalive配置，避免闲置连接被防火墙/路由器断开
• 确认Windows服务所在服务器的防火墙未拦截SQL Server端口（默认1433）的长时间连接

内容的提问来源于stack exchange，提问作者Rick Hodder