SqlSugar 是一款 老牌 .NET 开源ORM框架,由果糖大数据科技团队维护和更新 ,开箱即用,最易上手的ORM框架 ,51Job和Boss直招简历数超过国外框架 Nhibernate PetaPoco, 仅次于Dapper和EF Core , 占Dapper 40% 。
一般比较大型复杂的系统都会遇到如下问题:
SqlSugar其实是可以支持以上2种的,下面来着重介绍SqlSugar多库多表融合查询
SqlSugar官网:https://www.donet5.com/Doc/1/1226
多库多表查询架构图如下:
SqlSugarCore
[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; }
}
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 }
});
if(!db.IsAnyConnection(configId))//当前上下文不存在则添加
db.AddConnection(new ConnectionConfig{
DbType = DbType.SqlServer,ConfigId = "1",
IsAutoCloseConnection = true,
ConnectionString = 。。});
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();
var list = db.QueryableWithAttr<Order>()
.Includes(z => z.Items)//跨库一对多
.ToList();
配置用户权限角色三表
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
{
///
/// 主键
///
[SugarColumn(IsPrimaryKey = true)]
public int id { get; set; }
///
/// 姓名
///
public string realname { get; set; }
///
/// 多角色
///
[Navigate(typeof(OptRole), nameof(OptRole.operId), nameof(OptRole.roleId))]//配置导航
public List<Role> Roles { get; set; }
}
[Tenant("B")]//指定为B库
public partial class Role
{
///
/// 角色
///
[SugarColumn(IsPrimaryKey = true )]
public int id { get; set; }
///
/// 角色名称
///
public string name { get; set; }
}
[Tenant("AB")]//指定为AB库
public partial class OptRole
{
///
///
///
[SugarColumn(IsPrimaryKey = true)]
public int id { get; set; }
///
///
///
public int operId { get; set; }
///
///
///
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();
//开启事务
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();
}