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(pageNum, 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;
}