Abp中集成Dapper 有时候遇到The transaction associated with this command is not the connection‘s active

大概翻译成中文说是：与此命令关联的事务不是连接的活动事务（在水之云的框架执行后台作业里也都会出现这种情况）

说的问题很清楚，应该是没有释放事务，但是新添加的接口，调同一个sql查询语句或者新写一个查询语句都行，但是具体原因不太清楚，

反正在执行新的语句时，会重新调用var connection = await GetDbConnectionAsync(); 就是创建数据库链接

既然都重新创建数据库链接了，那就再重新创建一个当前链接的事务，会不会有影响还不清楚，但是abp官方文档写的使用查询sql语句时，都会传一个DbTransaction，如下：

public class PersonDapperRepository : DapperRepository<MyAppDbContext>, ITransientDependency
{
    public PersonDapperRepository(IDbContextProvider<MyAppDbContext> dbContextProvider)
        : base(dbContextProvider)
    {
    }

    public virtual async Task<List<string>> GetAllPersonNames()
    {
        return (await DbConnection.QueryAsync<string>("select Name from People", transaction: DbTransaction))
            .ToList();
    }

    public virtual async Task<int> UpdatePersonNames(string name)
    {
        return await DbConnection.ExecuteAsync("update People set Name = @NewName", new { NewName = name },
            DbTransaction);
    }
}

不传也可以，但是就会出现以上问题，

改造如下可解决：

var dbTransaction = await GetDbTransactionAsync(); //重点在此

using var reader = await connection.QueryMultipleAsync(sb.ToString(), queryParams, transaction: dbTransaction);

using System.Data;
using System.Text;
using Dapper;
using Tzkj.AchievementGather.InformationService.EntityFrameworkCore;
using Tzkj.Shared.Application.Contracts.Permissions;
using Volo.Abp;
using Volo.Abp.Application.Dtos;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Repositories.Dapper;
using Volo.Abp.EntityFrameworkCore;

namespace Tzkj.AchievementGather.InformationService.DapperQuery;

public class InformationServiceDapperRepositoryBase : DapperRepository<IInformationServiceDbContext>, ITransientDependency
{
    public IAbpLazyServiceProvider LazyServiceProvider { get; set; }
    protected IPermissionCheck Permission => LazyServiceProvider.LazyGetRequiredService<IPermissionCheck>();
    public InformationServiceDapperRepositoryBase(IDbContextProvider<IInformationServiceDbContext> dbContextProvider) : base(dbContextProvider)
    {
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="pageIndex"></param>
    /// <param name="pageSize"></param>
    /// <param name="tablesJoinStr"></param>
    /// <param name="columnsStr"></param>
    /// <param name="whereStr"></param>
    /// <param name="orderByStr"></param>
    /// <param name="queryParams"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentException"></exception>
    protected async Task<PagedResultDto<T>> GetPagedResultAsync<T>(
        string tablesJoinStr,
        string columnsStr,
        string whereStr,
        string orderByStr,
        int pageIndex,
        int pageSize,
        DynamicParameters queryParams = null)
    {
        if (pageIndex <= 0 || pageSize <= 0)
        {
            //throw new ArgumentException("pageIndex与pageSize 不可以小于或等于0");
            throw new UserFriendlyException("pageIndex与pageSize 不可以小于或等于0");
        }
        var skip = (pageIndex - 1) * pageSize;
        var sb = new StringBuilder();
        sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tablesJoinStr, whereStr);
        sb.AppendFormat(@"SELECT {0}
                                FROM {1}
                                WHERE {2}
                                ORDER BY {3}
                                LIMIT @skip,@take", columnsStr, tablesJoinStr, whereStr, orderByStr);

        queryParams ??= new DynamicParameters();
        queryParams.Add("skip", skip, DbType.Int32);
        queryParams.Add("take", pageSize, DbType.Int32);
        var connection = await GetDbConnectionAsync();
        var dbTransaction = await GetDbTransactionAsync(); //重点在此
        using var reader = await connection.QueryMultipleAsync(sb.ToString(), queryParams, transaction: dbTransaction);
        var total = await reader.ReadFirstAsync<long>();
        var list = await reader.ReadAsync<T>();
        return new PagedResultDto<T>(total, list.ToList());
    }
}

注意：一定要使用上面官方的写法，不要随意简化写，比如简化成：

//这样是错误的，因为继承的QueryMultipleAsync接口参数顺序是sql语句---查询参数--上下文事物dbTransaction

await connection.QueryMultipleAsync(sb.ToString(), dbTransaction);

但是可以写成如下：加上定义的查询参数queryParam，就可以省略掉 transaction:

await connection.QueryMultipleAsync(sb.ToString(), queryParam,dbTransaction);