sqlsugar同步异步事务/部分或者指定更新字段/多表查询/分页等 简单使用笔记
------笔记内容----------
=====Sqlsugar 简单使用
var sqldb = BaseDal.Db; //查询 //sql 查询 // var pinfo = await sqldb.Ado.SqlQueryAsync<Y_project>("you sql", new { }); //linq lambda 查询 var pmodel = await sqldb.Queryable<Y_project, Y_constructionSchedule>((p, csp) => p.ID == csp.projectInfo && p.ID == 1212158).Select(p => p).FirstAsync(); var pmodel2 = await sqldb.Queryable<Y_project>().Where(c => c.ID == 1212158).FirstAsync(); var pmodel3 = await sqldb.Queryable<Y_project>().Where(c => c.ID == 1212159).WhereIF(1 > 2, c => c.ID > 10).FirstAsync(); //查询并且分页 var tcount = new SqlSugar.RefAsync<int>(); var projectAble = await sqldb.Queryable<Y_project>().ToPageListAsync(1, 3, tcount); //多表联合查询并且分页 var tcount2 = new RefAsync<int>(); var pmodel4 = await sqldb.Queryable<Y_project, Y_constructionSchedule>((p, c) => new SqlSugar.JoinQueryInfos(SqlSugar.JoinType.Left, p.ID == c.projectInfo && p.companycode == "xiao")).ToPageListAsync(1, 10, tcount2); //新增一个实体,并返回新增的key //var ymodel = new Y_project(); //int getthisId = await sqldb.Insertable<Y_project>(ymodel).ExecuteReturnIdentityAsync(); //更新一个实体并忽略那些字段等(需要有key) //await sqldb.Updateable<Y_project>(ymodel).IgnoreColumns(p => new { p.createBy, p.createTime }).ExecuteReturnIdentityAsync(); //同步事务 //sqldb.Ado.BeginTran(); ////you logic //sqldb.Ado.CommitTran(); ////异步事务 //var resultInfo = await sqldb.Ado.UseTranAsync<int>(() => // { // return 1; // }); //if (resultInfo.Data > 0) //{ // //you logic //}
var sqldb = BaseDal.Db; //查询 //sql 查询 var pinfo = await sqldb.Ado.SqlQueryAsync<Y_project>("sql", new { }); //linq lambda 查询 var pmodel = await sqldb.Queryable<Y_project, Y_constructionSchedule>((p, csp) => p.ID == csp.projectInfo && p.ID == 1212158).Select(p => p).FirstAsync(); var pmodel2 = await sqldb.Queryable<Y_project>().Where(c => c.ID == 1212158).FirstAsync(); var pmodel3 = await sqldb.Queryable<Y_project>().Where(c => c.ID == 1212159).WhereIF(1 > 2, c => c.ID > 10).FirstAsync(); //查询并且分页 var tcount = new SqlSugar.RefAsync<int>(); var projectAble = await sqldb.Queryable<Y_project>().ToPageListAsync(1, 3, tcount); //多表联合查询并且分页 var tcount2 = new RefAsync<int>(); var pmodel4 = await sqldb.Queryable<Y_project, Y_constructionSchedule>((p, c) => new SqlSugar.JoinQueryInfos(SqlSugar.JoinType.Left, p.ID == c.projectInfo && p.companycode == "xiao")).ToPageListAsync(1, 10, tcount2); //新增一个实体,并返回新增的key //var ymodel = new Y_project(); //int getthisId = await sqldb.Insertable<Y_project>(ymodel).ExecuteReturnIdentityAsync(); //更新一个实体并忽略那些字段等(需要有key) //await sqldb.Updateable<Y_project>(ymodel).IgnoreColumns(p => new { p.createBy, p.createTime }).ExecuteReturnIdentityAsync(); //同步事务 sqldb.Ado.BeginTran(); //you logic sqldb.Ado.CommitTran(); //异步事务 var resultInfo = await sqldb.Ado.UseTranAsync<int>(() => { return 1; }); if (resultInfo.Data > 0) { //you logic }
===================同步事务
public void DoYnsync() { try { DbScoped.SugarScope.BeginTran(); //you logic DbScoped.SugarScope.CommitTran(); } catch (Exception) { DbScoped.SugarScope.RollbackTran(); throw; } }
===================异步事务
public async Task<ApiResultDto> testsqlsugar() { var resultd = await DbScoped.Sugar.UseTranAsync(async () => { var listData = await DbScoped.Sugar.Queryable<OnlyTest01Entity>().Where(c => c.id > 0).ToListAsync(); listData[0].bookprice = 666; listData[0].isok = false; listData[0].pname = "qq爱"; listData[0].id = 0; await DbScoped.Sugar.Insertable(listData[0]).ExecuteCommandAsync();// 直接查询出再去掉id直接写入ok //await DbScoped.Sugar.Updateable<OnlyTest01Entity>(listData[0]).ExecuteCommandAsync(); //listData[1].bookprice = 999; //await DbScoped.Sugar.Updateable<OnlyTest01Entity>(listData[1]).ExecuteCommandAsync(); //throw new Exception("测试异步事务");//ok }); return resultd.IsSuccess ? ApiResultDto.ToResultSuccess(data: resultd.ErrorException) : ApiResultDto.ToResultFail(data: resultd.ErrorException); }
===================Sqlsugar多表排查,排序,分页
int totalCount = 0; var getTotalCount = new RefAsync<int>();//分页要加这个东东 //多表分页 var list = await DbScoped.Sugar.Queryable<Inspections, SysUserDetails, InstituTion> ((st, sc, sr) => new JoinQueryInfos(JoinType.Left, st.Ins_user_id == sc.Id, JoinType.Left, st.Ins_institiution_id == sr.Id)) .WhereIF(SelectDto.Bge_Id > 0, (st, sc, sr) => st.Bge_id == SelectDto.Bge_Id) .WhereIF(getTime > temptime, (st, sc, sr) => st.Ins_time >= startTime&&st.Ins_time<=enTime) .Select((st, sc, sr) => new GetInspectionDetailShowInfoDto { Id = st.Id,*** }).OrderBy(c=>c.Ins_time,OrderByType.Desc) .ToPageListAsync(SelectDto.PageIndex, SelectDto.PageSize, getTotalCount); totalCount = getTotalCount.Value;// 这里的分页是需要这样来写的 list.Count; decimal pageCount = Math.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(SelectDto.PageSize)); return OutputDto.ToResult("获取成功!", data: new PageModel<object>() { Total = totalCount, PageNum = Convert.ToInt32(pageCount), PageIndex = SelectDto.PageIndex, PageSize = SelectDto.PageSize, Data = list });
==================Sqlsugar多表查询,排序,分页
var dbData = await DbScoped.Sugar.Queryable<SsysTaxTypeEntity>().LeftJoin<SsysBigTaxTypeEntity>((c, b) => c.tax_big_type_id == b.id) .WhereIF(systype <= 0, (c, b) => c.s_branch_id == branchid) .WhereIF(!string.IsNullOrEmpty(dto.code), (c, b) => c.code.Contains(dto.code)) .WhereIF(!string.IsNullOrEmpty(dto.describe), (c, b) => c.code.Contains(dto.describe)) .WhereIF(dto.taxrate > 0, (c, b) => c.taxrate == dto.taxrate) .WhereIF(!string.IsNullOrEmpty(dto.mcard), (c, b) => c.mcard.Contains(dto.mcard)) .Select((c, b) => new EditSsysTaxTypeDto { id = c.id, code = c.code, ******, s_branch_id = c.s_branch_id, tax_big_type_id = c.tax_big_type_id, tax_big_type_name = b.describe, update_date = c.update_date, update_user_wno = c.update_user_wno }).ToPageListAsync(dto.pageIndex, dto.pageSize, totalcount);
=================sqlsugar官网的更新案例
//根据主键更新单条 参数 Class var result= db.Updateable(updateObj).ExecuteCommand();//实体有多少列更新多少列 //只更新修改字段 5.1.1-preview11 新功能 db.Tracking(updateObj);//创建跟踪 updateObj.Name = "a1" + Guid.NewGuid();//只改修改了name那么只会更新name db.Updateable(updateObj).ExecuteCommand();//跟踪批量操作不会生效,原因:默认最佳性能(跟踪批量性能差,自已循环) //可以清空跟踪db.TempItems = null; //批量更新参数 List<Class> var result= db.Updateable(updateObjs).ExecuteCommand(); //大数据批量更新 适合列多数据多的更新 (MySql连接字符串要加AllowLoadLocalInfile=true ) db.Fastest<RealmAuctionDatum>().BulkUpdate(GetList());
=====================只更新部分或者指定更新等操作
==v 1.2 不更新某列 不更新 TestId和CreateTime var result=db.Updateable(updateObj).IgnoreColumns(it => new { it.CreateTime,it.TestId }).ExecuteCommand()
==v 1.3 只更新某列
只更新 Name 和 CreateTime var result=db.Updateable(updateObj).UpdateColumns(it => new { it.Name,it.CreateTime }).ExecuteCommand();
如有疑问或者错误的地方,请跟帖,本人会第一时间答复以及相互学习,谢谢!个人会不断的上传自己的学习心得!
好了今天就先到这里,下次有时间再更新,如果存在不合理的地方,欢迎大家多多指教留言!!!
分类:
Dapper/SqlSugar
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析