.NET 5 ORM 八大实用技巧 干货 – SqlSugar ORM

  SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()    {                 DbType = DbType.SqlServer,                 ConnectionString = Config.ConnectionString,                 InitKeyType = InitKeyType.Attribute,                 IsAutoCloseConnection = true                    });     db.Aop.OnLogExecuted = (sql, p) =>     {          //执行时间超过1秒         if (db.Ado.SqlExecutionTime.TotalSeconds > 1)          {             //代码CS文件名             var fileName= db.Ado.SqlStackTrace.FirstFileName;             //代码行数             var fileLine = db.Ado.SqlStackTrace.FirstLine;             //方法名             var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;             //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息          }    };

db.Aop.OnDiffLogEvent = it => {                 var editBeforeData = it.BeforeData;//操作前记录  包含： 字段描述 列名 值 表名 表描述                 var editAfterData = it.AfterData;//操作后记录   包含： 字段描述 列名 值  表名 表描述                 var sql = it.Sql;                 var parameter = it.Parameters;                 var data = it.BusinessData;//这边会显示你传进来的对象                 var time = it.Time;                 var  diffType=it.DiffType;//enum insert 、update and delete                                      //Write logic };    //添加 db.Insertable(new Student() { Name = "beforeName" }) .EnableDiffLogEvent(new {  title="我是插入"}) //启用日志并添加业务对象 .ExecuteReturnIdentity();   
//修改 db.Updateable<Student>(new Student()             {                 Id = id,                 CreateTime = DateTime.Now,                 Name = "afterName",                 SchoolId = 2             }) .EnableDiffLogEvent() //启动日志 .ExecuteCommand();   
//删除 db.Deleteable<Student>(id) .EnableDiffLogEvent()//启动日志 .ExecuteCommand();

ublic class UnitJsonTest {    [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]    public int Id { get; set; }     [SqlSugar.SugarColumn(ColumnDataType ="varchar(max)", IsJson = true)]    public Order Order { get; set; }    public string Name{get;set;} } Db.Insertable(new UnitJsonTest() { Name="json1",Order = new Order { Id = 1, Name = "order1" } }).ExecuteCommand(); var list = Db.Queryable<UnitJsonTest>().ToList();

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();

下面代码将生成生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));

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

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

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(); }

//有自动赋值的外键 db.Insertable(new Order() {     Name = "订单 1",         CustomId = 1,         Price = 100,         CreateTime = DateTime.Now,         Id = 0,//自增列         Items = new List<OrderItem>() {                 new OrderItem(){                     CreateTime=DateTime.Now,                     OrderId=0,//需要自动获取订单的自增列                     Price=1,                     ItemId=1                 }             }     })     .AddSubList(it => it.Items.First().OrderId  )//设置item表的OrderId等于订单自增列     .ExecuteReturnPrimaryKey();  

ICacheService myCache = new HttpRuntimeCache();    SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()    {                  ConnectionString = Config.ConnectionString,                  DbType = DbType.SqlServer,                  IsAutoCloseConnection = true,                  ConfigureExternalServices = new ConfigureExternalServices()                  {                      DataInfoCacheService = myCache //配置我们创建的缓存类                  }     });  db.Queryable<Student>().Where(it => it.Id > 0).WithCache().ToList();//设置缓存默认一天  db.Queryable<Student>().WithCache(1000).ToList();//设置具体过期时间

db.Deleteable<Student>().RemoveDataCache().Where(it => it.Id == 1).ExecuteCommand();  //remove所有引用Student表的缓存，包含多表查询

var orderField = "order';drop table order"; var orderInfo= db.EntityMaintenance.GetEntityInfo<Order>();  if (orderInfo.Columns.Any(it => it.DbColumnName != orderField))  {     throw new Exception("请不要非法注入"); }  db.Queryable<Order>().OrderBy(orderField).ToList();