国产精品ORM框架-SqlSugar详解 SqlSugar初识 专题一-CSDN博客

sqlsugar 官网-CSDN博客

Queryable 什么时候操作库

Queryable是一个引用类型

Queryable拷贝机制

{ISugarQueryable<Student> query = db.Queryable<Student>();List<Student> studentList = query.ToList();Student[] studentList1 = query.ToArray();//ISugarQueryable 对象的内部，包含了如何生成Sql语句；--包含了生成Sql语句的规则//包含了返回值；//作用：在调用给你的时候，可以把查询的对象，表达式目录树。。。解析，组合成成Sql语句，但是并不会到数据库中去查询；//如果在Tolist  Toarray的时候，ISugarQueryable 马上执行，开始按照规则生成Sql语句，就马上去数据库中去执行；//从queryable变成了List 这个时候类型发生变化 已经生成了SQL或者执行了数据库
}{Console.WriteLine("===============================");var able = db.Queryable<Student>();able.Where(it => it.Id > 0);able.Where(it => it.Id > 0);var list = able.ToList();//where id>0 and tid>0
}{var query = db.Queryable<Student>().Where(it => it.Id == 1);{int count = query.Count();List<Student> list = query.ToList();Student[] array = query.ToArray();string sqlString = query.ToSqlString();}//{//    int count = query.Clone().Count();//当query用于2个地方的时候一定要加Clone这点和EF有本质区别//    var list = query.Clone().ToList();//}
}

方法列表

方法名 描述 返回值
SqlQuery< T > 查询所有返回实体集合 List
SqlQuery<T,T2> 可以返回2个结果集 Tuple<List, List>
SqlQuerySingle 查询第一条记录 T
SqlQueryDynamic 查查询所有返回匿名对象 dynamic
GetDataTable 查询所有 DataTable
GetDataReader 读取DR需要手动释放DR DataReader
GetDataSetAll 获取多个结果集 DataSet
ExecuteCommand 返回受影响行数，一般用于增删改 int
GetScalar 获取首行首列 object
GetString 获取首行首列 string
GetInt 获取首行首列 int
GetLong 获取首行首列 long
GetDouble 获取首行首列 Double
GetDecimal 获取首行首列 Decimal
GetDateTime 获取首行首列 DateTime

 #region 输出Sql语句+数据库表初始化db.Aop.OnLogExecuting = (s, p) =>{Console.WriteLine("----------------------------");Console.WriteLine($"Sql语句:{s}");};if (db.DbMaintenance.IsAnyTable("UinitBlukTable", false)){db.DbMaintenance.DropTable<UinitBlukTable>();}db.CodeFirst.InitTables<UinitBlukTable>();#endregion {//上面列表中 SqlQuery 等方法都可以不一定是GetDataTable{var dt = db.Ado.GetDataTable("select * from UinitBlukTable");}//参数1：简化用法{string sql = "select * from UinitBlukTable where id=@id and name like @name";var dt = db.Ado.GetDataTable(sql, new { id = 1, name = "%jack%" });}//参数2：复杂用法{string sql = "select * from   UinitBlukTable where id=@id and name like @name";var parameter = new List<SugarParameter>(){new SugarParameter("@id",1),new SugarParameter("@name","%jack%") //执行sql语句};var dt = db.Ado.GetDataTable(sql, parameter);}{//原生SQL用实体 sql 查询//比db.SqlQueryable兼容性更强，支持复杂SQL存储过程，缺点没有自带的分页操作List<UinitBlukTable> t1 = db.Ado.SqlQuery<UinitBlukTable>("select * from UinitBlukTable");//原生SQL用匿名对象 sql 查询List<dynamic> t2 = db.Ado.SqlQuery<dynamic>("select * from UinitBlukTable");//插入 更新操作一般用string sql = $"INSERT INTO [dbo].[UinitBlukTable] ([Id] ,[Name] ,[Create]) VALUES ('10000' ,'Richard' ,'{DateTime.Now.ToString()}')";db.Ado.ExecuteCommand(sql);}//调用存储过程{//带有output的存储过程 {var tableName = new SugarParameter("@tableName", "Company");var reFieldsStr = new SugarParameter("@ReFieldsStr", "*");var orderString = new SugarParameter("@orderString", "Id");var whereString = new SugarParameter("@whereString", "1=1");var pageSize = new SugarParameter("@pageSize", "10");var pageIndex = new SugarParameter("@PageIndex", 1);var TotalRecord = new SugarParameter("@TotalRecord", null, true);//设置为output var dt = db.Ado.UseStoredProcedure().GetDataTable("SP_CustomPager", tableName, reFieldsStr, orderString, whereString, pageSize, pageIndex, TotalRecord);//返回dt //数据总条数object recordCount = TotalRecord.Value;}}//查询两个结果集{var views = db.Ado.SqlQuery<Company, Company, Company>("select * from Company; select * from Company; select * from Company");//多实体var t1list = views.Item1;var t2list = views.Item2;}}

Excel导入到数据库
困难点： 数据的验证
insert into not exists (只插入不更新)
非主键用例
完整用例（增、删、改）

数据清洗一把梭

    #region 输出Sql语句+数据库表初始化db.Aop.OnLogExecuting = (s, p) =>{Console.WriteLine("----------------------------");Console.WriteLine($"Sql语句:{s}");};if (db.DbMaintenance.IsAnyTable("UinitBlukTable", false)){db.DbMaintenance.DropTable<UinitBlukTable>();}db.CodeFirst.InitTables<UinitBlukTable>();#region 只插入不更新{List<UinitBlukTable> list = new List<UinitBlukTable>();list.Add(new UinitBlukTable() { Id = 1, Name = "a", Create = DateTime.Now });list.Add(new UinitBlukTable() { Id = 2, Name = "a", Create = DateTime.Now });list.Add(new UinitBlukTable() { Id = 3, Name = "a", Create = DateTime.Now.AddYears(-2) });list.Add(new UinitBlukTable() { Id = 4, Name = "", Create = DateTime.Now.AddYears(-2) });{db.Deleteable<UinitBlukTable>().ExecuteCommand();var x = db.Storageable(list).SplitInsert(it => !it.Any()).ToStorage();x.AsInsertable.ExecuteCommand();//插入可插入部分}//缩写{db.Deleteable<UinitBlukTable>().ExecuteCommand();var x = db.Storageable(list).SplitInsert(it => !it.Any()).ToStorage().AsInsertable.ExecuteCommand();}}#endregion#endregion 完整案例{List<UinitBlukTable> list2 = new List<UinitBlukTable>();list2.Add(new UinitBlukTable() { Id = 1, Name = "a", Create = DateTime.Now });list2.Add(new UinitBlukTable() { Id = 2, Name = "a", Create = DateTime.Now });list2.Add(new UinitBlukTable() { Id = 3, Name = "a", Create = DateTime.Now.AddYears(-2) });list2.Add(new UinitBlukTable() { Id = 4, Name = "", Create = DateTime.Now.AddYears(-2) });var x = db.Storageable(list2).SplitError(it => string.IsNullOrEmpty(it.Item.Name), "名称不能为空").SplitError(it => it.Item.Create < DateTime.Now.AddYears(-1), "不是今年的数据").SplitDelete(it => it.Item.Create < DateTime.Now.AddYears(-10))//删除10年前数据//.Saveable() // Saveable 等于下面2行.SplitUpdate(it => it.Any())//数据库存在更新 根据主键.SplitInsert(it => true)//其余插入 .ToStorage();//输出统计输出错误信息     Console.WriteLine("插入 {0} 更新{1} 错误数据{2} 不计算数据{3} 删除数据{4},总共{5}",x.InsertList.Count,x.UpdateList.Count,x.ErrorList.Count,x.IgnoreList.Count,x.DeleteList.Count,x.TotalList.Count);Console.WriteLine("====================导入数据+验证=========================");foreach (var item in x.ErrorList){Console.WriteLine("id等于" + item.Item.Id + " : " + item.StorageMessage);}x.AsInsertable.ExecuteCommand(); //执行插入x.AsUpdateable.ExecuteCommand(); //执行更新x.AsDeleteable.ExecuteCommand(); //执行删除　　}
}

--new 20240716

并发累计（累加）
防止提交覆盖(乐观锁)
不依赖库同步

并发幂等版本栈

 public class ULockEntity{[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]public int Id { get; set; }public string? Name { get; set; }[SugarColumn(IsEnableUpdateVersionValidation = true)]//标识版本字段public long Ver { get; set; }//支持Guid long string DateTime (不推荐DateTime 时间有精度问题)/