Sqlsugar事务高级进阶AOP

1.在Model层配置SysUserInfo类

public class SysUserInfo : RootEntityTkey<long>
    {
        /// <summary>
        /// 登录账号
        /// </summary>
        [SugarColumn(Length = 200, IsNullable = true, ColumnDescription = "登录账号")]
        public string LoginName { get; set; }

        /// <summary>
        /// 登录密码
        /// </summary>
        [SugarColumn(Length = 200, IsNullable = true)]
        public string LoginPWD { get; set; }

        /// <summary>
        /// 真实姓名
        /// </summary>
        [SugarColumn(Length = 200, IsNullable = true)]
        public string RealName { get; set; }

        /// <summary>
        /// 状态
        /// </summary>
        public int Status { get; set; } = 0;

        /// <summary>
        /// 部门
        /// </summary>
        [SugarColumn(IsNullable = true)]
        public long DepartmentId { get; set; } = -1;

        /// <summary>
        /// 备注
        /// </summary>
        [SugarColumn(Length = 2000, IsNullable = true)]
        public string Remark { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime CreateTime { get; set; } = DateTime.Now;

        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTime UpdateTime { get; set; } = DateTime.Now;

        /// <summary>
        /// 关键业务修改时间
        /// </summary>
        public DateTime CriticalModifyTime { get; set; } = DateTime.Now;

        /// <summary>
        ///最后异常时间 
        /// </summary>
        public DateTime LastErrorTime { get; set; } = DateTime.Now;

        /// <summary>
        ///错误次数 
        /// </summary>
        public int ErrorCount { get; set; } = 0;


        /// <summary>
        /// 登录账号
        /// </summary>
        [SugarColumn(Length = 200, IsNullable = true)]
        public string Name { get; set; }

        // 性别
        [SugarColumn(IsNullable = true)]
        public int Sex { get; set; } = 0;

        // 年龄
        [SugarColumn(IsNullable = true)]
        public int Age { get; set; }

        // 生日
        [SugarColumn(IsNullable = true)]
        public DateTime Birth { get; set; } = DateTime.Now;

        // 地址
        [SugarColumn(Length = 200, IsNullable = true)]
        public string Address { get; set; }

        [SugarColumn(DefaultValue = "1")]
        public bool Enable { get; set; } = true;

        [SugarColumn(IsNullable = true)]
        public bool IsDeleted { get; set; } = false;

        /// <summary>
        /// 租户Id
        /// </summary>
        [SugarColumn(IsNullable = false, DefaultValue = "0")]
        public long TenantId { get; set; }

        [SugarColumn(IsIgnore = true)]
        public List<string> RoleNames { get; set; }

        [SugarColumn(IsIgnore = true)]
        public List<long> Dids { get; set; }

        [SugarColumn(IsIgnore = true)]
        public string DepartmentName { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<long> RIDs { get; set; }
    }

2.在Model层中配置Department部门表

///<summary>
/// 部门表
///</summary>
public class Department : RootEntityTkey<long>
{
    public long Pid { get; set; }
    /// <summary>
    /// Desc:部门关系编码
    /// Default:
    /// Nullable:True
    /// </summary>
    public string CodeRelationship { get; set; }
    /// <summary>
    /// Desc:部门名称
    /// Default:
    /// Nullable:True
    /// </summary>
    public string Name { get; set; }
    /// <summary>
    /// Desc:负责人
    /// Default:
    /// Nullable:True
    /// </summary>
    [SugarColumn(IsNullable = true)]
    public string Leader { get; set; }
    /// <summary>
    /// Desc:排序
    /// Default:
    /// Nullable:True
    /// </summary>
    public int OrderSort { get; set; } = 0;
    /// <summary>
    /// Desc:部门状态（0正常 1停用）
    /// Default:0
    /// Nullable:True
    /// </summary>
    public bool Status { get; set; } = false;
    /// <summary>
    /// Desc:删除标志（0代表存在 2代表删除）
    /// Default:0
    /// Nullable:True
    /// </summary>
    public bool IsDeleted { get; set; } = false;
    /// <summary>
    /// Desc:创建者
    /// Default:
    /// Nullable:True
    /// </summary>
    [SugarColumn(IsNullable = true)]
    public string CreateBy { get; set; }
    /// <summary>
    /// Desc:创建时间
    /// Default:
    /// Nullable:True
    /// </summary>
    [SugarColumn(IsNullable = true)]
    public DateTime? CreateTime { get; set; }
    /// <summary>
    /// Desc:更新者
    /// Default:
    /// Nullable:True
    /// </summary>
    [SugarColumn(IsNullable = true)]
    public string ModifyBy { get; set; }
    /// <summary>
    /// Desc:更新时间
    /// Default:
    /// Nullable:True
    /// </summary>
    [SugarColumn(IsNullable = true)]
    public DateTime? ModifyTime { get; set; }



    [SugarColumn(IsIgnore = true)]
    public bool hasChildren { get; set; } = true;

    [SugarColumn(IsIgnore = true)]
    public List<long> PidArr { get; set; }
}

3.在Model层中配置DepartmentVo类部门表视图模型

///<summary>
/// 部门表视图模型
///</summary>
public class DepartmentVo
{
    public string CodeRelationship { get; set; }
    public string Name { get; set; }
    public string Leader { get; set; }
    public int OrderSort { get; set; } = 0;
    public bool IsDeleted { get; set; } = false;
    public string CreateBy { get; set; }
    public DateTime? CreateTime { get; set; }
}

4.在Repository层和Service层中添加

ISqlSugarClient Db { get; }

public ISqlSugarClient Db => _dbBaset;

ISqlSugarClient Db { get; }

public ISqlSugarClient Db => _baseRepository.Db;

5.配置IDepartmentService及其实现类DepartmentServices

public interface IDepartmentService
{
    Task<bool> TestTranPropagation2();
}

public class DepartmentServices : BaseService<Department, UserVo>, IDepartmentService
{
    private readonly IBaseRepository<Department> _dal;

    public DepartmentServices(IMapper mapper, IBaseRepository<Department> baseRepository) : base(mapper, baseRepository)
    {
        _dal = baseRepository;
    }


    /// <summary>
    /// 测试使用同事务
    /// </summary>
    /// <returns></returns>
    [UseTran(Propagation = Propagation.Required)]
    public async Task<bool> TestTranPropagation2()
    {
        TimeSpan timeSpan = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        var id = timeSpan.TotalSeconds.ObjToLong();
        var insertDepartment = await _dal.Add(new Department()
        {
            Id = id,
            Name = $"department name {id}",
            CodeRelationship = "",
            OrderSort = 0,
            Status = true,
            IsDeleted = false,
            Pid = 0
        });

        await Console.Out.WriteLineAsync($"db context id : {base.Db.ContextID}");

        return true;
    }
}

6.配置IUserService及其实现类UserService

public interface IUserService
{
    Task<List<UserVo>> Query();
    Task<bool> TestTranPropagation();
}

public class UserService : BaseService<SysUserInfo, UserVo>, IUserService
{
    private readonly IDepartmentService _departmentServices;

    public UserService(IDepartmentService departmentServices, IMapper mapper, IBaseRepository<SysUserInfo> baseRepository) : base(mapper, baseRepository)
    {
        _departmentServices = departmentServices;
    }


    /// <summary>
    /// 测试使用同事务
    /// </summary>
    /// <returns></returns>
    [UseTran(Propagation = Propagation.Required)]
    public async Task<bool> TestTranPropagation()
    {
        await Console.Out.WriteLineAsync($"db context id : {base.Db.ContextID}");
        var sysUserInfos = await base.Query();

        TimeSpan timeSpan = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        var id = timeSpan.TotalSeconds.ObjToLong();
        var insertSysUserInfo = await base.Add(new SysUserInfo()
        {
            Id = id,
            Name = $"user name {id}",
            Status = 0,
            CreateTime = DateTime.Now,
            UpdateTime = DateTime.Now,
            CriticalModifyTime = DateTime.Now,
            LastErrorTime = DateTime.Now,
            ErrorCount = 0,
            Enable = true,
            TenantId = 0,
        });

        await _departmentServices.TestTranPropagation2();

        return true;
    }
}

7.配置Propagation和UseTranAttribute以实现[UseTran(Propagation = Propagation.Required)]功能

public enum Propagation
{
    /// <summary>
    /// 默认：如果当前没有事务，就新建一个事务，如果已存在一个事务中，加入到这个事务中。
    /// </summary>
    Required = 0,

    /// <summary>
    /// 使用当前事务，如果没有当前事务，就抛出异常
    /// </summary>
    Mandatory = 1,

    /// <summary>
    /// 以嵌套事务方式执行
    /// </summary>
    Nested = 2,
}

[AttributeUsage(AttributeTargets.Method, Inherited = true)]
public class UseTranAttribute : Attribute
{
    public Propagation Propagation { get; set; } = Propagation.Required;
}

8.重新配置IUnitOfWorkManage和UnitOfWorkManage，增加有参方法

public interface IUnitOfWorkManage
{
    SqlSugarScope GetDbClient();

    /// <summary>
    /// 获取DB，保证唯一性
    /// </summary>
    /// <returns></returns>
    int TranCount { get; }

    UnitOfWork CreateUnitOfWork();

    /// <summary>
    /// 开始事务的方法
    /// </summary>
    void BeginTran();
    void BeginTran(MethodInfo method);

    /// <summary>
    /// 提交事务的方法
    /// </summary>
    void CommitTran();
    void CommitTran(MethodInfo method);

    /// <summary>
    /// 回滚事务的方法
    /// </summary>
    void RollbackTran();
    void RollbackTran(MethodInfo method);
}


public class UnitOfWorkManage : IUnitOfWorkManage
{
    private readonly ILogger<UnitOfWorkManage> _logger;
    private readonly ISqlSugarClient _sqlSugarClient;

    private int _tranCount { get; set; }
    public int TranCount => _tranCount;
    public readonly ConcurrentStack<string> TranStack = new();

    public UnitOfWorkManage(ISqlSugarClient sqlSugarClient, ILogger<UnitOfWorkManage> logger)
    {
        _sqlSugarClient = sqlSugarClient;
        _logger = logger;
        _tranCount = 0;
    }

    /// <summary>
    /// 获取DB，保证唯一性
    /// </summary>
    /// <returns></returns>
    public SqlSugarScope GetDbClient()
    {
        // 必须要as，后边会用到切换数据库操作
        return _sqlSugarClient as SqlSugarScope;
    }


    public UnitOfWork CreateUnitOfWork()
    {
        UnitOfWork uow = new UnitOfWork();
        uow.Logger = _logger;
        uow.Db = _sqlSugarClient;
        uow.Tenant = (ITenant)_sqlSugarClient;
        uow.IsTran = true;

        uow.Db.Open();
        uow.Tenant.BeginTran();
        _logger.LogDebug("UnitOfWork Begin");
        return uow;
    }

    public void BeginTran()
    {
        lock (this)
        {
            _tranCount++;
            GetDbClient().BeginTran();
        }
    }

    public void BeginTran(MethodInfo method)
    {
        lock (this)
        {
            GetDbClient().BeginTran();
            TranStack.Push(method.GetFullName());
            _tranCount = TranStack.Count;
        }
    }

    public void CommitTran()
    {
        lock (this)
        {
            _tranCount--;
            if (_tranCount == 0)
            {
                try
                {
                    GetDbClient().CommitTran();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    GetDbClient().RollbackTran();
                }
            }
        }
    }

    public void CommitTran(MethodInfo method)
    {
        lock (this)
        {
            string result = "";
            while (!TranStack.IsEmpty && !TranStack.TryPeek(out result))
            {
                Thread.Sleep(1);
            }


            if (result == method.GetFullName())
            {
                try
                {
                    GetDbClient().CommitTran();

                    _logger.LogDebug($"Commit Transaction");
                    Console.WriteLine($"Commit Transaction");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    GetDbClient().RollbackTran();
                    _logger.LogDebug($"Commit Error , Rollback Transaction");
                }
                finally
                {
                    while (!TranStack.TryPop(out _))
                    {
                        Thread.Sleep(1);
                    }

                    _tranCount = TranStack.Count;
                }
            }
        }
    }

    public void RollbackTran()
    {
        lock (this)
        {
            _tranCount--;
            GetDbClient().RollbackTran();
        }
    }

    public void RollbackTran(MethodInfo method)
    {
        lock (this)
        {
            string result = "";
            while (!TranStack.IsEmpty && !TranStack.TryPeek(out result))
            {
                Thread.Sleep(1);
            }

            if (result == method.GetFullName())
            {
                GetDbClient().RollbackTran();
                _logger.LogDebug($"Rollback Transaction");
                Console.WriteLine($"Rollback Transaction");
                while (!TranStack.TryPop(out _))
                {
                    Thread.Sleep(1);
                }

                _tranCount = TranStack.Count;
            }
        }
    }
}

9.配置MethodInfoExtension类

public static class MethodInfoExtensions
{
    public static string GetFullName(this MethodInfo method)
    {
        if (method.DeclaringType == null)
        {
            return $@"{method.Name}";
        }

        return $"{method.DeclaringType.FullName}.{method.Name}";
    }
}

10.在CustomProfile文件中进行mapper绑定SysUserInfo和UserVo

// 用户
CreateMap<SysUserInfo, UserVo>().ForMember(a => a.UserName, o => o.MapFrom(d => d.Name));
CreateMap<UserVo, SysUserInfo>().ForMember(a => a.Name, o => o.MapFrom(d => d.UserName));

11.在控制层TransactionController控制器中使用

[HttpGet]
public async Task<object> TestTRanPropagation()
{
    return await _userService.TestTranPropagation();
}