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