.Net5 框架搭建（七）：FreeSql配置多个数据库

前言

因为项目关系，需要连接到多个数据库，之前我框架选择的ORM是FreeSql，所以这次我们对他进行一次简单的升级改造

方案方式一：定义多个IFreeSql

像这样子要几个连接实例，就写几个类
优点：简单粗暴
缺点：100个连接实例，就得写100个。。

方式二：引用IdleBus重写IFreeSql

IdleBus 空闲对象管理容器，有效组织对象重复利用，自动创建、销毁，解决【实例】过多且长时间占用的问题。有时候想做一个单例对象重复使用提升性能，但是定义多了，有的又可能一直空闲着占用资源。专门解决：又想重复利用，又想少占资源的场景。

添加引用IdleBus

重写IFreeSql里面的方法

MultiFreeSql.cs

using FreeSql;
using FreeSql.Internal;
using FreeSql.Internal.CommonProvider;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;

namespace CuoDing.Core.Extensions
{
    public class MultiFreeSql : MultiFreeSql { }

    public partial class MultiFreeSql : BaseDbProvider, IFreeSql
    {
        internal TDBKey _dbkeyMaster;
        internal AsyncLocal _dbkeyCurrent = new AsyncLocal();
        BaseDbProvider _ormMaster => _ib.Get(_dbkeyMaster) as BaseDbProvider;
        BaseDbProvider _ormCurrent => _ib.Get(object.Equals(_dbkeyCurrent.Value, default(TDBKey)) ? _dbkeyMaster : _dbkeyCurrent.Value) as BaseDbProvider;
        internal IdleBus _ib;

        public MultiFreeSql()
        {
            _ib = new IdleBus(TimeSpan.FromMinutes(10));
            _ib.Notice += (_, __) => { };
        }

        public override IAdo Ado => _ormCurrent.Ado;
        public override IAop Aop => _ormCurrent.Aop;
        public override ICodeFirst CodeFirst => _ormCurrent.CodeFirst;
        public override IDbFirst DbFirst => _ormCurrent.DbFirst;
        public override GlobalFilter GlobalFilter => _ormCurrent.GlobalFilter;
        public override void Dispose() => _ib.Dispose();

        public override CommonExpression InternalCommonExpression => _ormCurrent.InternalCommonExpression;
        public override CommonUtils InternalCommonUtils => _ormCurrent.InternalCommonUtils;

        public override ISelect CreateSelectProvider(object dywhere)
        {
            return _ormCurrent.CreateSelectProvider(dywhere);
        }
        public override IDelete CreateDeleteProvider(object dywhere)
        {
            return _ormCurrent.CreateDeleteProvider(dywhere);
        }
        public override IUpdate CreateUpdateProvider(object dywhere)
        {
            return _ormCurrent.CreateUpdateProvider(dywhere);
        }
        public override IInsert CreateInsertProvider()
        {
            return _ormCurrent.CreateInsertProvider();
        }
        public override IInsertOrUpdate CreateInsertOrUpdateProvider()
        {
            return _ormCurrent.CreateInsertOrUpdateProvider();
        }
    }

    public static class MultiFreeSqlExtensions
    {
        public static IFreeSql Change(this IFreeSql fsql, TDBKey dbkey)
        {
            var multiFsql = fsql as MultiFreeSql;
            if (multiFsql == null) throw new Exception("fsql 类型不是 MultiFreeSql");
            multiFsql._dbkeyCurrent.Value = dbkey;
            return multiFsql;
        }

        public static IFreeSql Register(this IFreeSql fsql, TDBKey dbkey, Func create)
        {
            var multiFsql = fsql as MultiFreeSql;
            if (multiFsql == null) throw new Exception("fsql 类型不是 MultiFreeSql");
            if (multiFsql._ib.TryRegister(dbkey, create))
                if (multiFsql._ib.GetKeys().Length == 1)
                    multiFsql._dbkeyMaster = dbkey;
            return multiFsql;
        }
    }
}

多数据库配置并注入服务

这里我把一些扩展的注入服务都放在一个单独的程序集里面
MultiDBSetup.cs

using CuoDing.Core.Common;
using FreeSql;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace CuoDing.Core.Extensions
{
    /// 
    /// 多数据库扩展
    /// 
    public static class MultiDBSetup
    {
        public static void AddMultiDB(this IServiceCollection services)
        {
            var fsql = new MultiFreeSql();
            List dbsList = Appsettings.app("Dbs").ToList();
            if (dbsList.Any())
            {
                foreach (var dbObj in dbsList)
                {
                    fsql.Register(dbObj.Name, () => new FreeSqlBuilder().UseConnectionString(dbObj.DbType, dbObj.ConnectionString).Build());

                }
            }
            services.AddSingleton(fsql);

        }
    }
}

MultiDb.cs

using FreeSql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CuoDing.Core.Extensions
{
    /// 
    /// 多数据库
    /// 
    public class MultiDb
    {
        /// 
        /// 数据库命名
        /// 
        public string Name { get; set; }

        /// 
        /// 数据库类型
        /// 
        public DataType DbType { get; set; }

        /// 
        /// 数据库字符串
        /// 
        public string ConnectionString { get; set; }
    }
}

appsettings.json

  "DbType": "1",
  //多数据库连接配置
  "DbsOpen": true,//是否开启
  "Dbs": [
    {
      "Name": "DB1",
      "DbType": "SqlServer",
      "ConnectionString": "Data Source=127.0.0.1;Initial Catalog=StdTrainningFormal;Persist Security Info=True;User ID=sa1;Password=1112222;Pooling=true;Min Pool Size=1;Connection LifeTime=20"
    },
    {
      "Name": "DB2",
      "DbType": "Oracle",
      "ConnectionString": "user id=ssss;password=aaaaaa;data source=//127.0.0.1:1521/orcl;Pooling=true;Min Pool Size=1"
    },
    {
      "Name": "DB3",
      "DbType": "MySql",
      "ConnectionString": "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root; Initial Catalog=cccddd;Charset=utf8; SslMode=none;Min pool size=1"
    }
  ]

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
            #region 多数据配置
            if (Configuration["DbsOpen"].ToBool())
            {
                services.AddIdleBus();
            }
            #endregion
}

测试

前期准备，这里我使用的数据库分别SqlServer跟MySql，然后我在各种的数据库中创建了test_user表
SqlServer添加的测试数据如下

MySql添加的测试数据如下

写一个简单的控制器来测试，我们想要的结果

using CuoDing.Core.Extensions;
using CuoDing.Core.Model.Enum;
using CuoDing.Core.Model.ViewModels;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace CuoDing.Core.WebApi.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class TestFreesqlController : ControllerBase
    {
        private readonly IFreeSql _fsql;
        private readonly ILogger _logger;
        public TestFreesqlController(IFreeSql fsql, ILogger logger)
        {
            _fsql = fsql;
            _logger = logger;
        }

        [HttpGet]
        public dynamic TestMultiFreesql()
        {
            DXResult dXResult = new DXResult();
            dXResult.code = DXCode.Success;
            try
            {
                
                Dictionary dict = new Dictionary();
                var list1 = _fsql.Select

.Net5 框架搭建（七）：FreeSql配置多个数据库

.Net相关栏目本月热门文章