【愚公系列】2023年05月 .NET CORE工具案例-基于SqlSugar的多库多表融合查询

(文章目录)

---

前言

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

一般比较大型复杂的系统都会遇到如下问题:

• 多库查询：比如 MYSQL和一个SQLSERVER进行联表查询 ，或者SQLITE和MYSQL进行联表查询
• 多服务器查询：A服务器和B服务器查询

SqlSugar其实是可以支持以上2种的，下面来着重介绍SqlSugar多库多表融合查询

SqlSugar官网：https://www.donet5.com/Doc/1/1226

一、基于SqlSugar的多库多表融合查询

多库多表查询架构图如下：

1.安装包

SqlSugarCore

2.订单表

[Tenant("db2")] //实体标为db2
public class OrderItem
{
           [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
           public int ItemId { get; set; }
           public int OrderId { get; set; }
           public decimal? Price { get; set; }
           [SqlSugar.SugarColumn(IsNullable = true)]
           public DateTime? CreateTime { get; set; }
           [Navigate(NavigateType.OneToOne,nameof(OrderId))] //设置关系 对应Order表主键
           public Order Order { get; set; }
}
[Tenant("db1")] //实体标为db1
public class Order
{
          [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
          public int Id { get; set; }
          public string Name { get; set; }
          public decimal Price { get; set; }
          [SugarColumn(IsNullable = true)]
          public DateTime CreateTime { get; set; }
          [SugarColumn(IsNullable = true)]
          public int CustomId { get; set; }
          [Navigate(NavigateType.OneToMany, nameof(OrderItem.OrderId))]//
          public List<OrderItem> Items { get; set; }
}

3.添加连接

3.1 初始化添加

var db = new SqlSugarClient(new List<ConnectionConfig>()
{
  new ConnectionConfig(){ConfigId="db1",DbType=DbType.MySql,
  ConnectionString="Server=localhost;Database=test;Uid=root;Pwd=123456;",IsAutoCloseConnection=true},

  new ConnectionConfig(){ConfigId="db2",DbType=DbType.SqlServer,
  ConnectionString="Server=localhost;User=sa;Password=1;Database=test;Encrypt=True;TrustServerCertificate=True;",IsAutoCloseConnection=true }
});

3.2 动态添加

if(!db.IsAnyConnection(configId))//当前上下文不存在则添加
    db.AddConnection(new ConnectionConfig{
                           DbType = DbType.SqlServer,ConfigId = "1",
                IsAutoCloseConnection = true,
                ConnectionString = 。。});

4.查询

4.1 子表对主表（一对一）查询

1、通过Tenant自动映射

//通过实体类特性Tenant自动映射不同数据库进行查询
var list=db.QueryableWithAttr<OrderItem>()
.Includes(z => z.Order)
.ToList(); //1行代码就搞定了2个库联表查询

2、不通过Tenant自动映射

//不通过特性实现跨库导航
var list = db.GetConnection("db2").Queryable<OrderItem>()//Orderitem是db1
               .CrossQuery(typeof(Order), "db1")//Order是db2
               .Includes(z => z.Order)
               .ToList();

4.2 主表对子表（一对多）查询

var list = db.QueryableWithAttr<Order>()
                   .Includes(z => z.Items)//跨库一对多
                   .ToList();

4.3 用户权限角色（多对多）查询

配置用户权限角色三表

var db = new SqlSugarClient(new List<ConnectionConfig>()
   {
       new ConnectionConfig(){ConfigId="A",DbType=DbType.Sqlite,ConnectionString="DataSource=/A_DB.sqlite",IsAutoCloseConnection=true},
       new ConnectionConfig(){ConfigId="B",DbType=DbType.Sqlite,ConnectionString="DataSource=/B_DB.sqlite",IsAutoCloseConnection=true  },
       new ConnectionConfig(){ConfigId="AB",DbType=DbType.Sqlite,ConnectionString="DataSource=/AB_DB.sqlite",IsAutoCloseConnection=true  }
 });p　

实体

 [Tenant("A")]//指定为A库
 public partial class OperatorInfo
 {
     /// <summary>
     /// 主键
     /// </summary>
     [SugarColumn(IsPrimaryKey = true)]
     public int id { get; set; }

     /// <summary>
     /// 姓名
     /// </summary>
     public string realname { get; set; }

     /// <summary>
     /// 多角色
     /// </summary>
     [Navigate(typeof(OptRole), nameof(OptRole.operId), nameof(OptRole.roleId))]//配置导航
     public List<Role> Roles { get; set; }
 }


 [Tenant("B")]//指定为B库
 public partial class Role
 {
     /// <summary>
     /// 角色
     /// </summary>
     [SugarColumn(IsPrimaryKey = true )]
     public int id { get; set; }

     /// <summary>
     /// 角色名称
     /// </summary>
     public string name { get; set; }

 }


 [Tenant("AB")]//指定为AB库
 public partial class OptRole
 {
     /// <summary>
     ///
     /// </summary>
     [SugarColumn(IsPrimaryKey = true)]
     public int id { get; set; }

     /// <summary>
     ///
     /// </summary>
     public int operId { get; set; }

     /// <summary>
     ///
     /// </summary>
     public int roleId { get; set; }


 }

//3个库3个表进行多对多查询
var x=db.QueryableWithAttr<OperatorInfo>()
.Includes(z => z.Roles).ToList();

//多个表联表
var x=db.QueryableWithAttr<TB>()
.Includes(z => z.A1)
.Includes(z => z.A2)
.Includes(z => z.A3).ToList();
 
//多个表嵌套联表
var x=db.QueryableWithAttr<TB>()
.Includes(z =>z.A1, z.Province,z=>z.City)//4个层级
.Includes(z => z.A2)
.Includes(z => z.A3).ToList();

5.事务

//开启事务
try
{
    //db我们称为主Db
    db.BeginTran(); //开启多库 （db.Ado.BeginTran是单库事务）事务支持单库和多库

    db.GetConnection("db1").Insertable(new Order()//var childDb=db.GetConnection(1);我们称为子DB，子DB不具有租户方法，具有单当库操作方法
    {
        CreateTime = DateTime.Now,
        CustomId = 2,
        Name = "小米10",
        Price = 1
    }).ExecuteCommand();

    db.GetConnection("db2").Insertable(new OrderItem()
    {
        CreateTime = DateTime.Now,
        OrderId = 1,
        Price = 1
    }).ExecuteCommand();

    //提交事务
    db.CommitTran();

}
catch (Exception ex)
{
    //回滚事务
    db.RollbackTran();
}