.Net6添加Sqlsugar

1、appsettings.json添加数据库链接配置

"ConnectionStrings": {
  //"TrustServerCertificate=True": null, 添加serilog后出现错误【证书链由不受信任的颁发机构颁发】
  "Server": "Data Source=.;Initial Catalog=PlayDB;User ID=sa;Password=123456;TrustServerCertificate=True"
},

2、依赖注入Sqlsugar

• 添加类SqlSugarInit

public static class SqlSugarInit
{
    public static void AddSqlSugarSetup(this IServiceCollection services, string conn)
    {
        if (services == null) throw new ArgumentNullException(nameof(services));

        services.AddSingleton<ISqlSugarClient>(s =>
        {
            var sqlSugar = new SqlSugarScope(new ConnectionConfig()
                {
                    DbType = DbType.SqlServer,
                    ConnectionString = conn,
                    IsAutoCloseConnection = true,
                },
                db =>
                {
                    //单例参数配置，所有上下文生效
                    db.Aop.OnLogExecuting = (sql, pars) =>
                    {
                        //获取IOC对象不要求在一个上下文
                        //vra log=s.GetService<Log>()

                        //获取IOC对象要求在一个上下文
                        //var appServive = s.GetService<IHttpContextAccessor>();
                        //var log= appServive?.HttpContext?.RequestServices.GetService<Log>();

                    };
                });
            return sqlSugar;
        });


    }
}

• Program.cs

//依赖注入sqlsugar
builder.Services.AddSqlSugarSetup(builder.Configuration["ConnectionStrings:Server"]);

创建仓储

• 添加接口IRepository

public class Repository<T> : SimpleClient<T>, IRepository<T> where T : class, new()
{
    public Repository(ISqlSugarClient db)
    {
        Context = db;
    }

    public async Task<T> QueryById(object objId)
    {
        return await Context.Queryable<T>().In(objId).SingleAsync();
    }

    public async Task<List<T>> QueryAll()
    {
        return await Context.Queryable<T>().ToListAsync();
    }

    public async Task<List<T>> Query(Expression<Func<T, bool>>? whereExpression)
    {
        return await Context.Queryable<T>().WhereIF(whereExpression != null, whereExpression).ToListAsync();
    }

    public async Task<int> Add(T entity)
    {
        var i = await Context.Insertable(entity).ExecuteCommandAsync();
        return i;
    }
}

• 添加类Repository

public class Repository<T> : SimpleClient<T>, IRepository<T> where T : class, new()
{
    public Repository(ISqlSugarClient db)
    {
        Context = db;
    }

    public async Task<T> QueryById(object objId)
    {
        return await Context.Queryable<T>().In(objId).SingleAsync();
    }

    public async Task<List<T>> QueryAll()
    {
        return await Context.Queryable<T>().ToListAsync();
    }

    public async Task<List<T>> Query(Expression<Func<T, bool>>? whereExpression)
    {
        return await Context.Queryable<T>().WhereIF(whereExpression != null, whereExpression).ToListAsync();
    }

    public async Task<int> Add(T entity)
    {
        var i = await Context.Insertable(entity).ExecuteCommandAsync();
        return i;
    }
}

添加工作单元

• 添加接口IUnitOfWork

public interface IUnitOfWork
{
    /// <summary>
    /// 开启工作单元处理
    /// </summary>
    /// <param name="context"></param>
    void BeginTransaction(ActionExecutingContext context);

    /// <summary>
    /// 提交工作单元处理
    /// </summary>
    /// <param name="resultContext"></param>
    void CommitTransaction(ActionExecutedContext resultContext);

    /// <summary>
    /// 回滚工作单元处理
    /// </summary>
    /// <param name="resultContext"></param>
    void RollbackTransaction(ActionExecutedContext resultContext);

    /// <summary>
    /// 执行完毕（无论成功失败）
    /// </summary>
    /// <param name="context"></param>
    /// <param name="resultContext"></param>
    void OnCompleted(ActionExecutingContext context, ActionExecutedContext resultContext);
}

• 添加类UnitOfWork

public class UnitOfWork : IUnitOfWork
{
    private readonly ISqlSugarClient _sqlSugarClient;

    public UnitOfWork(ISqlSugarClient sqlSugarClient)
    {
        _sqlSugarClient = sqlSugarClient;
    }
    public void BeginTransaction(ActionExecutingContext context)
    {
        _sqlSugarClient.AsTenant().BeginTran();
    }

    public void CommitTransaction(ActionExecutedContext resultContext)
    {
        _sqlSugarClient.AsTenant().CommitTran();
    }

    public void OnCompleted(ActionExecutingContext context, ActionExecutedContext resultContext)
    {
        _sqlSugarClient.Dispose();
    }

    public void RollbackTransaction(ActionExecutedContext resultContext)
    {
        _sqlSugarClient.AsTenant().RollbackTran();
    }
}

• 注入到容器中

//依赖注入工作单元
builder.Services.AddTransient<IUnitOfWork, UnitOfWork>();

通过特性封装事务

• 创建工作单元特性

[AttributeUsage(AttributeTargets.Method)]
public class UnitOfWorkAttribute:System.Attribute{}

• 创建过滤器

public class UnitOfWorkFilter : IAsyncActionFilter, IOrderedFilter
{
    private readonly ILogger<UnitOfWorkFilter> _logger;

    public UnitOfWorkFilter(ILogger<UnitOfWorkFilter> logger)
    {
        _logger = logger;
    }

    /// <summary>
    /// 过滤器排序
    /// </summary>
    internal const int FilterOrder = 999;

    /// <summary>
    /// 排序属性
    /// </summary>
    public int Order => FilterOrder;

    /// <summary>
    /// 拦截请求
    /// </summary>
    /// <param name="context">动作方法上下文</param>
    /// <param name="next">中间件委托</param>
    public async Task OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
    {
        // 获取动作方法描述器
        var actionDescriptor = context.ActionDescriptor as ControllerActionDescriptor;
        var method = actionDescriptor.MethodInfo;

        // 获取请求上下文
        var httpContext = context.HttpContext;

        // 如果没有定义工作单元过滤器，则跳过
        if (!method.IsDefined(typeof(UnitOfWorkAttribute), true))
        {
            // 调用方法
            _ = await next();

            return;
        }

        // 打印工作单元开始消息
        _logger.LogInformation($@"{nameof(UnitOfWorkFilter)} Beginning");

        // 解析工作单元服务
        var _unitOfWork = httpContext.RequestServices.GetRequiredService<IUnitOfWork>();

        // 调用开启事务方法
        _unitOfWork.BeginTransaction(context);

        // 获取执行 Action 结果
        var resultContext = await next();

        if (resultContext == null || resultContext.Exception == null)
        {
            // 调用提交事务方法
            _unitOfWork.CommitTransaction(resultContext);
        }
        else
        {
            // 调用回滚事务方法
            _unitOfWork.RollbackTransaction(resultContext);
        }

        // 调用执行完毕方法
        _unitOfWork.OnCompleted(context, resultContext);

        // 打印工作单元结束消息  
        _logger.LogInformation($@"{nameof(UnitOfWorkFilter)} Ending");

    }
}

• 过滤器添加到通信管道中

builder.Services.Configure<MvcOptions>(options =>
{
    //工作单元过滤器
    options.Filters.Add<UnitOfWorkFilter>();
});

• 接下来是调用方式，很简单，只要在你想启用工作单元的接口上（也就是Controller中的Action），加上工作单元的特性就可以了

参考

1. https://blog.csdn.net/qq_60147611/article/details/132054855
2. https://www.donet5.com/Home/Doc