sqlsugar 使用汇总 (大数据写入、更新，大数据更新 ORM, db.Fastest文档)

https://www.donet5.com/Home/Doc?typeId=2404  大数据写入、更新，大数据更新 ORM, db.Fastest文档

//插入 100万 10秒不到 

db.Fastest<RealmAuctionDatum>().BulkCopy(GetList());//性能 比现有任何Bulkcopy都要快30%


//如果数据库现有数据比较多出现比较慢，这个时候可以试试分页

db.Fastest<Order>().PageSize(300000).BulkCopy(insertObjs);

await db.Fastest<RealmAuctionDatum>().BulkCopyAsync(GetList()) //用异步await不能少,1000以上的数据；

更新：

db.Fastest<tb_users>().BulkUpdate(list);

await db.Fastest<RealmAuctionDatum>().BulkUpdateAsync(GetList())

更新：

public static int UpdateRoleById(Sys_Role model)
        {
            return DB.Updateable(model).UpdateColumns(it => new
            {
                it.Name,
                it.Remark,
                it.OperatorId,
                it.OperateTime
            }).ExecuteCommand();  //跟新指定列
            // return DB.Updateable(model).IgnoreColumns(it => new{it.CreateTime,it.CreatorId}).ExecuteCommand();//更新忽略指定的列
            //return DB.Updateable(model).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();//更新忽略所有为Null的列
        }

根据条件判段是否执行过滤，我们可以用WhereIf来实现，true执行过滤，false则不执行

var list = db.Queryable<Student>()


    .WhereIF(!string.IsNullOrEmpty(a),it => it.Name == a)


    .WhereIF(!string.IsNullOrEmpty(b), it => it.Name == b).ToList();

//result.RowList = DB.Queryable<Sys_Log,Sys_User>((it, su) => new object[] { JoinType.Left, it.CreatorId == su.Id })
            //                   .Select((it,su) => new Sys_Log { Id=it.Id,Name=it.Name, Operation=it.Operation,Status=it.Status,LoginIp=it.LoginIp,CreateTime=it.CreateTime, CreatorName =su.UserName})
            //                   .WhereIF(!string.IsNullOrEmpty(login), Creator == login)
            //                   .WhereIF(operation != 0, it => it.Operation == operation)
            //                   .WhereIF(logstatus != 0, it => it.Status == logstatus)
            //                   .WhereIF(date1.Year !=1,it => it.CreateTime >= date1)
            //                   .WhereIF(date2.Year !=1, it => it.CreateTime <= date2)
            //                   .ToPageList(pageNum, pageSize, ref totalNumber);
            result.RowList = DB.Queryable<Sys_Log>()
                    .WhereIF(!string.IsNullOrEmpty(login),it =>it.Creator == login)
                    .WhereIF(operation != 0, it => it.Operation == operation)
                    .WhereIF(logstatus != 0, it => it.Status == logstatus)
                    .WhereIF(date1.Year != 1, it => it.CreateTime >= date1)
                    .WhereIF(date2.Year != 1, it => it.CreateTime <= date2)
                    .WithCache()
                    .ToPageList(pageNum, pageSize, ref totalNumber);

--Mapper

DB.Queryable<Merchant_Model, Sys_User>((su, sr) => new object[] { JoinType.Left, su.AgentId == sr.Id })
                .WhereIF(!string.IsNullOrEmpty(query), su => su.Name.Contains(query))
                .Mapper((su) => su.AgentName = su.AgentId == 0 ? "无上级代理" : su.AgentName)
                .OrderBy(su => su.Id, OrderByType.Desc)
                .WithCache()
                .ToPageList(pageN

um, pageSize, ref totalNumber);

使用缓存查询

var list=db.Queryable<Student, School>((s1, s2) => s1.Id == s2.Id).Select(s1 => s1).WithCache().ToList();//可以设置过期时间WithCache(60)

删除缓存

我们需要删除缓存也相当方便，只需要在对该表操作的时候加 RemoveDataCache 就能把查询中引用该表的缓存全部清除

db.Deleteable<Student>().Where(it => it.Id == 1).RemoveDataCache().ExecuteCommand();

el-table 

<el-table-column label="用户类型" prop="userType">
					<template scope="scope">
						<el-tag type="success" v-if="scope.row.userType == 1">管理员</el-tag>
						<el-tag type="info" v-if="scope.row.userType == 2">普通用户</el-tag>
					</template>
				</el-table-column>

in list 

DB.Queryable<Sys_User>().In(it => it.UserType, new int[] { 3, 4, 5 }).WithCache().ToList()

//返回值为List<string>

List<string> data = db.Queryable<Student>().Select(f => f.Name).ToList();

只更新一个列，主键要有值

var t3 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();

忽略更新的列

var t4 = db.Updateable(updateObj)


.IgnoreColumns(it => new { it.Name, it.TestId }).ExecuteCommand();

动态建表：每月建一个表

var list = DB.Queryable<ReportStatistics_Model>().AS("sys_order_"+ DateTime.Now.ToString("YYYYMM")).Where(it => it.IspId == ispId).ToList();

---事务开始

try
            {
                //事务开始
                this.A1.Db.Ado.BeginTran();                //插入
                this.A1.Db.Saveable<AAA>(lstNewDetail).ExecuteCommand();
                this.A1.Db.Saveable<AAA>(report).ExecuteCommand();                //提交事务
                this.A1.Db.Ado.CommitTran();                return new BaseRes<string>(true, VEJson.Msg.Gen.Success, report.PdfPath);
            }
            catch (Exception ex)
            {
                this.A1.Db.Ado.RollbackTran();
                throw ex;
            }