SqlSugar多DbContext配置 & 跨库事务

SqlSugar 是一款 老牌 .NET 开源ORM框架，由果糖大数据科技团队维护和更新 ，开箱即用最易上手的ORM框架 ，51Job和Boss直招简历数超过 国外框架 Nhibernate PetaPoco, 仅次于Dapper和EF Core , 占Dapper 40%

一、开发环境

操作系统：`Windows 11`
开发环境：`VisualSutido 2022`、`NetCore 3.1`、`SqlSugarCore 5.1.3.32`、`mysql 5.7`

二、具体配置

配置文件 appsettings.json

"ConnectionStrings": {
  "nxin_qlw_zlw": "Server=数据库地址;port=端口;database=库名;user=账号;password=密码;charset=utf8;Pooling=True;Max Pool Size=3000;Connect Timeout=60;Connection Lifetime=3600;sslMode=None;AllowLoadLocalInfile=true;",
  "nxin_qlw_business": "Server=数据库地址;port=端口;database=库名;user=账号;password=密码;charset=utf8;Pooling=True;Max Pool Size=3000;Connect Timeout=60;Connection Lifetime=3600;sslMode=None;AllowLoadLocalInfile=true;"
}

上下文抽象基类 DbContextBase

using System;
using SqlSugar;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.Extensions.Options;

namespace Renzicu.Infrastructure.Contexts
{
    public abstract class DbContextBase
    {
        public readonly SqlSugarClient SugarClient;
        public DbContextBase(IOptionsSnapshot<ConnectionConfig> namedOptionsAccessor)
        {
            SugarClient = new SqlSugarClient(namedOptionsAccessor.Get(this.GetType().Name));
        }
    }
}

单库 DbContext

using System;
using SqlSugar;
using System.Threading.Tasks;
using System.Collections.Generic;
using Microsoft.Extensions.Options;
using Renzicu.Infrastructure.Contexts;

namespace Renzicu
{
    public class ZlwContext : DbContextBase
    {
        public ZlwContext(IOptionsSnapshot<ConnectionConfig> namedOptionsAccessor) : base(namedOptionsAccessor)
        {
        }
    }
}

多库 DbContext

using System;
using SqlSugar;
using System.Collections.Generic;
using Microsoft.Extensions.Options;

namespace Renzicu.Infrastructure.Contexts
{
    public class MultipleWriteContext
    {
        public readonly SqlSugarClient SugarClient;
        public MultipleWriteContext(IOptionsSnapshot<List<ConnectionConfig>> namedOptionsAccessor)
        {
            SugarClient = new SqlSugarClient(namedOptionsAccessor.Get(this.GetType().Name));
        }

        /// <summary>
        /// 切换库上下文
        /// </summary>
        public SqlSugarProvider GetDbContext<T>()
        {
            return SugarClient.GetConnection(typeof(T).Name);
        }
    }
}

DbContext 服务注册扩展方法

using System;
using SqlSugar;
using System.Linq;
using System.Collections.Generic;
using Renzicu.Infrastructure.Contexts;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace Renzicu.Extensions
{
    public static class ServiceCollectionExtensions
    {
        public static void AddSugarDbContext<T>(this IServiceCollection services, IConfiguration configuration, string connectionStringName, bool enableSqlLog) where T : DbContextBase
        {
            services.AddScoped<T>();
            var name = typeof(T).Name;
            var options = services.AddOptions<ConnectionConfig>(name);
            services.Configure<ConnectionConfig>(name, op =>
            {
                op.ConfigId = name;
                op.ConnectionString = configuration.GetConnectionString(connectionStringName);
                op.DbType = DbType.MySql;
                op.IsAutoCloseConnection = true;
                if (enableSqlLog)
                {
                    op.AopEvents = new AopEvents
                    {
                        OnLogExecuting = (sql, pars) =>
                        {
                            Console.WriteLine(DateTime.Now);
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine($"{string.Join(", ", pars?.Select(it => it.ParameterName + "=" + it.Value))}");
                            Console.WriteLine(sql);
                            Console.ResetColor();
                        }
                    };
                }
            });
        }

        public static void AddSugarMultipleDbContext<T>(this IServiceCollection services, IConfiguration configuration, bool enableSqlLog, params (Type dbContextType, string connectionStringName)[] dbs) where T : class
        {
            services.AddScoped<T>();
            var name = typeof(T).Name;
            var options = services.AddOptions<List<ConnectionConfig>>(name);
            services.Configure<List<ConnectionConfig>>(name, ops =>
            {
                foreach (var item in dbs)
                {
                    var op = new ConnectionConfig
                    {
                        ConfigId = item.dbContextType.Name,
                        ConnectionString = configuration.GetConnectionString(item.connectionStringName),
                        DbType = DbType.MySql,
                        IsAutoCloseConnection = true,
                    };
                    if (enableSqlLog)
                    {
                        op.AopEvents = new AopEvents
                        {
                            OnLogExecuting = (sql, pars) =>
                            {
                                Console.WriteLine(DateTime.Now);
                                Console.ForegroundColor = ConsoleColor.Green;
                                Console.WriteLine($"{string.Join(", ", pars?.Select(it => it.ParameterName + "=" + it.Value))}");
                                Console.WriteLine(sql);
                                Console.ResetColor();
                            }
                        };
                    }
                    ops.Add(op);
                }
            });
        }
    }
}

Starup 注入 DbContext

// 单库上下文
services.AddSugarDbContext<ZlwContext>(Configuration, "nxin_qlw_zlw", _env.IsDevelopment());
services.AddSugarDbContext<QlwBusinessContext>(Configuration, "nxin_qlw_business", _env.IsDevelopment());
// 多库上下文
services.AddSugarMultipleDbContext<MultipleWriteContext>(Configuration, _env.IsDevelopment()
                                                    , (typeof(ZlwContext), "nxin_qlw_zlw")
                                                    , (typeof(QlwBusinessContext), "nxin_qlw_business")
                                                    );

三、跨库事务

代码不完整，仅作参考示例

public class DXProductionDailyController : Controller
{
    private readonly MultipleWriteContext _multiple;
    public DXProductionDailyController(ZlwContext _zlwContext, MultipleWriteContext _multiple)
    {
        this._zlwContext = _zlwContext;
        this._multiple = _multiple;
    }

    private void MultipleTest()
    {
        try
        {
            _multiple.SugarClient.BeginTran();

            var db1 = _multiple.GetDbContext<ZlwContext>();
            var db2 = _multiple.GetDbContext<QlwBusinessContext>();

            db1.Ado.ExecuteCommand("insert into test_1(Val) values(100)");
            //throw new Exception();

            db2.Ado.ExecuteCommand("insert into test_2(Val) values(200)");
            //throw new Exception();

            _multiple.SugarClient.CommitTran();
        }
        catch (Exception ex)
        {
            _multiple.SugarClient.RollbackTran();
        }
    }
}