Skip to content
FreeSql 官方文档FreeSql 官方文档
指南
扩展
服务支持
github icon
    • 基于FreeSql扩展
      • 多个 IFreeSql实例,如何注入使用
        • 一、定义多个 IFreeSql
          • 二、利用 IdleBus 重写 IFreeSql
            • 1、MultiFreeSql.cs 代码定义
              • 2、MultiFreeSqlExtensions
                • 3、定义和注入,其中MultiFreeSql.cs
                  • 4、如何使用
                  • 三、静态类管理多数据库?
                    • 参考
                    • 技巧:ISelect 如何拷贝(copy)复用,克隆(clone)
                      • Docker+ FreeSql
                        • FreeSql 如何实现审计日志
                          • Mysql 5.5 兼容性
                            • In多列查询,表达式自定义实现
                              • 技巧:自定义解析表达式树,实现动态聚合列 sum(case when ...)

                              多个 IFreeSql实例,如何注入使用

                              author iconnicyecalendar icon2022年6月24日timer icon大约 4 分钟word icon约 1323 字

                              此页内容
                              • 一、定义多个 IFreeSql
                              • 二、利用 IdleBus 重写 IFreeSql
                                • 1、MultiFreeSql.cs 代码定义
                                • 2、MultiFreeSqlExtensions
                                • 3、定义和注入,其中MultiFreeSql.cs
                                • 4、如何使用
                              • 三、静态类管理多数据库?
                              • 参考

                              # 多个 IFreeSql实例,如何注入使用

                              多库切换,动态切库,动态注册数据库

                              # 一、定义多个 IFreeSql

                              该方法适用于固定数据库,固定配置项

                              1、定义两个标识类:

                              class MySqlFlag {}
                              class SqlServerFlag {}
                              
                              1
                              2

                              2、在 Startup.cs 中单例注入

                              public void ConfigureServices(IServiceCollection services)
                              {
                                  var fsql1 = new FreeSqlBuilder().UseConnectionString(DataType.MySql, "str1")
                                      .Build<MySqlFlag>();
                                  var fsql2 = new FreeSqlBuilder().UseConnectionString(DataType.MySql, "str1")
                                      .Build<SqlServerFlag>();
                              
                                  services.AddSingleton<IFreeSql<MySqlFlag>>(fsql1);
                                  services.AddSingleton<IFreeSql<SqlServerFlag>>(fsql2);
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10

                              3、在 Controller 中使用

                              [Route("api/[controller]")]
                              [ApiController]
                              public class ValuesController : ControllerBase
                              {
                                  public ValuesController(IFreeSql<MySqlFlag> mysql, IFreeSql<SqlServerFlag> sqlserver)
                                  {
                                  }
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8

                              # 二、利用 IdleBus 重写 IFreeSql

                              在 asp.netopen in new window core 利用 IdleBus 重写 IFreeSql 支持多库操作,支持多库动态配置

                              <ItemGroup>
                                  <PackageReference Include="FreeSql" Version="3.2.664" />
                                  <PackageReference Include="IdleBus" Version="1.5.2" />
                                  <PackageReference Include="FreeSql.Provider.Sqlite" Version="3.2.664" />
                              </ItemGroup>
                              
                              1
                              2
                              3
                              4
                              5

                              # 1、MultiFreeSql.cs 代码定义

                              using System;
                              using System.Threading;
                              using FreeSql.Internal;
                              using FreeSql.Internal.CommonProvider;
                              
                              namespace FreeSql
                              {
                                  public class MultiFreeSql : MultiFreeSql<string>
                                  {
                                      public MultiFreeSql(TimeSpan timeSpan) : base(timeSpan)
                                      {
                                      }
                              
                                      public MultiFreeSql(IdleBus<string, IFreeSql> idleBus) : base(idleBus)
                                      {
                                      }
                                  }
                                  public class MultiFreeSql<TDBKey> : BaseDbProvider, IFreeSql
                                  {
                                      internal TDBKey _dbkeyMaster;
                                      internal AsyncLocal<TDBKey> _dbkeyCurrent = new AsyncLocal<TDBKey>();
                                      BaseDbProvider _ormMaster => _ib.Get(_dbkeyMaster) as BaseDbProvider;
                                      BaseDbProvider _ormCurrent => _ib.Get(Equals(_dbkeyCurrent.Value, default(TDBKey)) ? _dbkeyMaster : _dbkeyCurrent.Value) as BaseDbProvider;
                                      internal IdleBus<TDBKey, IFreeSql> _ib;
                              
                                      public MultiFreeSql(TimeSpan timeSpan)
                                      {
                                          _ib = new IdleBus<TDBKey, IFreeSql>(timeSpan);
                                          _ib.Notice += (_, __) => { };
                                      }
                              
                                      public MultiFreeSql(IdleBus<TDBKey, IFreeSql> idleBus)
                                      {
                                          _ib = idleBus;
                                      }
                              
                                      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<T1> CreateSelectProvider<T1>(object dywhere) => _ormCurrent.CreateSelectProvider<T1>(dywhere);
                                      public override IDelete<T1> CreateDeleteProvider<T1>(object dywhere) => _ormCurrent.CreateDeleteProvider<T1>(dywhere);
                                      public override IUpdate<T1> CreateUpdateProvider<T1>(object dywhere) => _ormCurrent.CreateUpdateProvider<T1>(dywhere);
                                      public override IInsert<T1> CreateInsertProvider<T1>() => _ormCurrent.CreateInsertProvider<T1>();
                                      public override IInsertOrUpdate<T1> CreateInsertOrUpdateProvider<T1>() => _ormCurrent.CreateInsertOrUpdateProvider<T1>();
                                  }
                              }
                              
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              14
                              15
                              16
                              17
                              18
                              19
                              20
                              21
                              22
                              23
                              24
                              25
                              26
                              27
                              28
                              29
                              30
                              31
                              32
                              33
                              34
                              35
                              36
                              37
                              38
                              39
                              40
                              41
                              42
                              43
                              44
                              45
                              46
                              47
                              48
                              49
                              50
                              51
                              52
                              53
                              54

                              所以 MultiFreeSql 支持外部定义IdleBus,用于配置 Idlebus的Notice事件

                               //可传递IdleBus
                                  IdleBus idlebus  = new IdleBus<string, IFreeSql>(TimeSpan.FromHours(2));
                                  idlebus.Notice += (_, __) => { };
                                  IFreeSql fsql = new MultiFreeSql(idlebus);
                              
                              1
                              2
                              3
                              4

                              # 2、MultiFreeSqlExtensions

                              using System;
                              
                              namespace FreeSql
                              {
                                  public static class MultiFreeSqlExtensions
                                  {
                                      public static IFreeSql ChangeDatabaseByKey<TDBKey>(this IFreeSql fsql, TDBKey dbkey)
                                      {
                                          var multiFsql = fsql as MultiFreeSql<TDBKey>;
                                          if (multiFsql == null) throw new Exception("fsql 类型不是 MultiFreeSql<TDBKey>");
                                          multiFsql._dbkeyCurrent.Value = dbkey;
                                          return multiFsql;
                                      }
                              
                                      public static IDisposable Change<TDBKey>(this IFreeSql fsql, TDBKey dbkey)
                                      {
                                          var multiFsql = fsql as MultiFreeSql<TDBKey>;
                                          if (multiFsql == null) throw new Exception("fsql 类型不是 MultiFreeSql<TDBKey>");
                                          var olddbkey = multiFsql._dbkeyCurrent.Value;
                                          multiFsql.ChangeDatabaseByKey(dbkey);
                                          return new DBChangeDisposable(() => multiFsql.ChangeDatabaseByKey(olddbkey));
                                      }
                              
                                      public static IFreeSql Register<TDBKey>(this IFreeSql fsql, TDBKey dbkey, Func<IFreeSql> create)
                                      {
                                          var multiFsql = fsql as MultiFreeSql<TDBKey>;
                                          if (multiFsql == null) throw new Exception("fsql 类型不是 MultiFreeSql<TDBKey>");
                                          if (multiFsql._ib.TryRegister(dbkey, create))
                                              if (multiFsql._ib.GetKeys().Length == 1)
                                                  multiFsql._dbkeyMaster = dbkey;
                                          return multiFsql;
                                      }
                                  }
                              
                                  class DBChangeDisposable : IDisposable
                                  {
                                      Action _cancel;
                                      public DBChangeDisposable(Action cancel) => _cancel = cancel;
                                      public void Dispose() => _cancel?.Invoke();
                                  }
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              14
                              15
                              16
                              17
                              18
                              19
                              20
                              21
                              22
                              23
                              24
                              25
                              26
                              27
                              28
                              29
                              30
                              31
                              32
                              33
                              34
                              35
                              36
                              37
                              38
                              39
                              40
                              41

                              # 3、定义和注入,其中MultiFreeSql.cs

                              public static IServiceCollection AddMultiFreeSql(this IServiceCollection services)
                              {
                                  var fsql = new MultiFreeSql(TimeSpan.FromHours(2));
                              
                                  fsql.Register("db1", () => 
                                  new FreeSqlBuilder()
                                      .UseAutoSyncStructure(true)
                                      .UseConnectionString(DataType.Sqlite, "Data Source=|DataDirectory|\\SampleApp1.db;")
                                      .Build()
                                  );
                                  fsql.Register("db2", () => 
                                  new FreeSqlBuilder()
                                      .UseAutoSyncStructure(true)
                                      .UseConnectionString(DataType.Sqlite, "Data Source=|DataDirectory|\\SampleApp2.db;")
                                      .Build()
                                  );
                              
                                  services.AddSingleton<IFreeSql>(fsql);
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              14
                              15
                              16
                              17
                              18
                              19

                              # 4、如何使用

                              额外增加 Register/Change 两个扩展方法,其他跟 IFreeSql 用法几乎一样

                              //查db1
                              var db1_list = fsql.Select<T>().ToList();
                              
                              //切换 db2 数据库,一旦切换之后 fsql 操作都是针对 db2
                              fsql.Change("db2");
                              var db2_list = fsql.Select<T>().ToList();
                              
                              using (fsql.Change("db1"))
                              {
                                  //查询 db1
                                  var b3 = _fsql.Select<T>().ToList();
                                  //插入到 db1
                                  fsql.Insert(new T{ UserName = "db1" }).ExecuteAffrows();
                              }
                              //还是查db2
                              var db2 = fsql.Select<T>().ToList();
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              14
                              15
                              16

                              示例

                              • 定义SysUser
                              public class SysUser
                              {
                                  [Column(IsPrimary = true, IsIdentity = true)]
                                  public int Id { get; set; }
                                  public string UserName { get; set; }
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              • 创建HomeController
                              private readonly IFreeSql _fsql;
                              public HomeController( IFreeSql fsql)
                              {
                                  _fsql = fsql;
                              }
                              
                              1
                              2
                              3
                              4
                              5

                              获取db1,db2的数据库信息,插入db1,db2数据

                              /// <summary>
                              /// 获取db1,db2,插入db1,db2
                              /// </summary>
                              /// <returns></returns>
                              [HttpGet("get1")]
                              public IEnumerable<SysUser> Get1()
                              {
                                  //查询 db1
                                  var b0 = _fsql.Select<SysUser>().ToList();
                              
                                  var b1 = _fsql.Change("db2");
                                  //查询 db2
                                  var b2 = _fsql.Select<SysUser>().ToList();
                                  //插入到 db2
                                  var c0 = _fsql.Insert(new SysUser { UserName = "db2" }).ExecuteAffrows();
                                  using (_fsql.Change("db1"))
                                  {
                                      //查询 db1
                                      var b3 = _fsql.Select<SysUser>().ToList();
                                      //插入到 db1
                                      _fsql.Insert(new SysUser { UserName = "db1" }).ExecuteAffrows();
                                  }
                              
                                  //查询 db2
                                  var db2 = _fsql.Select<SysUser>().ToList();
                                  _fsql.Change("db2");
                                  return db2;
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              14
                              15
                              16
                              17
                              18
                              19
                              20
                              21
                              22
                              23
                              24
                              25
                              26
                              27
                              28
                              • 默认从第一个数据库中获取
                              [HttpGet("getdb1")]
                              public IEnumerable<SysUser> GetDB1()
                              {
                                  //查询 db1
                                  var b0 = _fsql.Select<SysUser>().ToList();
                                  return b0;
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              • 注册第3个数据库
                              [HttpGet("register")]
                              public void Register()
                              {
                                  _fsql.Register("db3", () =>
                                  {
                                      return new FreeSqlBuilder().UseAutoSyncStructure(true)
                                      .UseMonitorCommand(
                                      cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText)
                                      )
                                      .UseConnectionString(DataType.Sqlite, "Data Source=|DataDirectory|\\SampleApp3.db;")
                                      .Build();
                                  });
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              • 根据dbname change,但只对本次请求有效
                              [HttpGet("change")]
                              public int Change(string dbname)
                              {
                                //仅对本次请求有效
                                _fsql.Change(dbname);
                                return 1;
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              • 获取DB3,在未注册前,会报错
                              [HttpGet("getdb3")]
                              public IEnumerable<SysUser> Get3()
                              {
                                  _fsql.Change("db3");
                                  //查询 db1
                                  var b0 = _fsql.Select<SysUser>().ToList();
                                  return b0;
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8

                              如果需要分布多事务 TCC/Saga 请移步到:https://github.com/2881099/FreeSql.Cloudopen in new window

                              # 三、静态类管理多数据库?

                              基于第二种方法 MultiFreeSql 增加静态类如下:

                              using System;
                              
                              namespace FreeSql
                              {
                                public class StaticDB : StaticDB<string> { }
                                
                                public abstract class StaticDB<DBKey>
                                {
                                    protected static Lazy<IFreeSql> multiFreeSql = new Lazy<IFreeSql>(() => new MultiFreeSql(TimeSpan.FromHours(2))
                                    );
                                    public static IFreeSql Instance => multiFreeSql.Value;
                                }
                              }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              • 根据静态类
                              #region 1.静态类的注册方式
                              IFreeSql db = StaticDB.Instance;
                              
                              db.Register("db1", () =>
                              {
                                  return new FreeSqlBuilder()
                                      .UseAutoSyncStructure(true)
                                      .UseMonitorCommand(
                                      cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText)
                                      )
                                      .UseConnectionString(DataType.Sqlite, "Data Source=|DataDirectory|\\SampleApp1.db;")
                                      .Build();
                              });
                              db.Register("db2", () =>
                              {
                                  return new FreeSqlBuilder()
                                      .UseAutoSyncStructure(true)
                                      .UseMonitorCommand(
                                      cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText)
                                      )
                                      .UseConnectionString(DataType.Sqlite, "Data Source=|DataDirectory|\\SampleApp2.db;")
                                      .Build();
                              });
                              #endregion
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              14
                              15
                              16
                              17
                              18
                              19
                              20
                              21
                              22
                              23
                              24
                              • 创建StaticDBController,可以直接使用静态方法,不使用DI,但使用方式和注入方式相同
                                 private readonly IFreeSql _fsql = StaticDB.Instance;
                              
                                  /// <summary>
                                  /// 获取db1,db2,插入db1,db2
                                  /// </summary>
                                  /// <returns></returns>
                                  [HttpGet("get1")]
                                  public IEnumerable<SysUser> Get1()
                                  {
                                      //查询 db1
                                      var b0 = _fsql.Select<SysUser>().ToList();
                              
                                      var b1 = _fsql.Change("db2");
                                      //查询 db2
                                      var b2 = _fsql.Select<SysUser>().ToList();
                                      //插入到 db2
                                      var c0 = _fsql.Insert(new SysUser { UserName = "db2" }).ExecuteAffrows();
                                      using (_fsql.Change("db1"))
                                      {
                                          //查询 db1
                                          var b3 = _fsql.Select<SysUser>().ToList();
                                          //插入到 db1
                                          _fsql.Insert(new SysUser { UserName = "db1" }).ExecuteAffrows();
                                      }
                              
                                      //查询 db2
                                      var db2 = _fsql.Select<SysUser>().ToList();
                                      _fsql.Change("db2");
                                      return db2;
                                  }
                              
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                              10
                              11
                              12
                              13
                              14
                              15
                              16
                              17
                              18
                              19
                              20
                              21
                              22
                              23
                              24
                              25
                              26
                              27
                              28
                              29
                              30

                              # 参考

                              SampleApp/HomeController.cs at master · luoyunchong/SampleApp (github.com)open in new window

                              SampleApp/MultiFreeSql.cs at master · luoyunchong/SampleApp (github.com)open in new window

                              多个 IFreeSql 实例,如何注入使用? · Issue #44 · dotnetcore/FreeSql (github.com)open in new window

                              与此文章有点区别,增加了一些配置项和静态方法的理解,把Change 下放到MultiFreeSqlExtensions扩展中,从而保持简单,静态方法和DI保持一致的写法

                              edit icon在 GitHub 上编辑此页open in new window
                              上次编辑于: 2022/6/25 上午12:45:08
                              贡献者: igeekfan
                              上一页
                              基于FreeSql扩展
                              下一页
                              技巧:ISelect 如何拷贝(copy)复用,克隆(clone)
                              Copyright © 2018-present nicye
                              Copyright © 2022 nicye

                              该应用可以安装在你的 PC 或移动设备上。这将使该 Web 应用程序外观和行为与其他应用程序相同。它将在出现在应用程序列表中,并可以固定到主屏幕,开始菜单或任务栏。此 Web 应用程序还将能够与其他应用程序和你的操作系统安全地进行交互。

                              详情