分库分表

注意:数据库连接账号要高级账号


1、动态创建数据库

下面代码将会创建db1和db2数据库

string conn = "server=.;uid=sa;pwd=haosql;database={0}";
var db = new SqlSugarClient(new ConnectionConfig()
{
    DbType = SqlSugar.DbType.SqlServer,
    ConnectionString = string.Format(conn, "db1"),
    InitKeyType=InitKeyType.Attribute
});
db.DbMaintenance.CreateDatabase();
var db2 = new SqlSugarClient(new ConnectionConfig()
{
    DbType = SqlSugar.DbType.SqlServer,
    ConnectionString = string.Format(conn, "db2")
});
db2.DbMaintenance.CreateDatabase();


2、动态建表


下面代码将生成生Order1和Order2 两张表

//注意db必须是同一个上下文
var db = new SqlSugarClient(new ConnectionConfig()
{
    DbType = SqlSugar.DbType.SqlServer,
    ConnectionString = ".;xxx",
    InitKeyType=InitKeyType.Attribute //这个属性必须这么设置
    
});
 
db.MappingTables.Add(typeof(Order).Name, typeof(Order).Name + 1);
db.CodeFirst.InitTables(typeof(Order));
db.MappingTables.Add(typeof(Order).Name, typeof(Order).Name + 2);
db.CodeFirst.InitTables(typeof(Order));


3、实体增、删、查、改

  var list= db.Queryable<Order>().AS("Order1").ToList();//查询Order1的表
  //增 删 改用法和查询一样 Inasertable().AS   Deleteable().AS Updateable().As


4、跨库联表查询

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)
                .AS("xx.dbo.order")
                .AS<OrderItem>("yy.dbo.OrderItem")
                .AS<Custom>("zz.dbo.Custom")
                .Select<ViewOrder>()
                .ToList();


5、多库切换

SqlSugarClient db = new SqlSugarClient(new List<ConnectionConfig>()
{
    new ConnectionConfig(){ ConfigId="1", DbType=DbType.SqlServer, 
    ConnectionString=Config.ConnectionString,InitKeyType=InitKeyType.Attribute,IsAutoCloseConnection=true },
    new ConnectionConfig(){ ConfigId="2", DbType=DbType.MySql, 
    ConnectionString=Config.ConnectionString4 ,InitKeyType=InitKeyType.Attribute ,IsAutoCloseConnection=true}
});
 
//库1
try
{
    db.BeginTran();
    db.Deleteable<Order>().ExecuteCommand();
    
    db.ChangeDatabase("2");//使用库2
    db.Deleteable<Order>().ExecuteCommand();
     
 
    db.CommitTran();
}
catch
{
    db.RollbackTran();
}