SqlSugar小技巧 - 更新数据

SqlSugar 是一款 老牌 .NET 开源ORM框架，由果糖大数据科技团队维护和更新 ，开箱即用最易上手的ORM框架 。生态圈丰富，目前开源生态仅次于微软的EF Core。文章主要记录一些使用SqlSugar更新数据的小技巧

1 普通更新

默认更新所有字段，即使没有数据改变

Student student = db.Queryable<Student>().First();
//单条更新
{
    student.CreateTime = DateTime.Now;
    db.Updateable<Student>(student).ExecuteCommand(); //右标题1 下面的所有菜单
}

2 按需更新

只更新修改字段

db.Tracking(student);//创建跟踪
student.Name = "a1" + Guid.NewGuid();
//只改修改了name那么只会更新name
//跟踪批量操作不会生效，原因：默认最佳性能（跟踪批量性能差，自已循环）
//可以清空跟踪db.TempItems = null;
db.Updateable(student).ExecuteCommand();

3 批量更新、大数据量更新

//批量更新 默认更新所有字段 Tracking更新按需更新会无效
List<Student> list = db.Queryable<Student>().Take(20).ToList();
foreach (var item in list)
{
    item.Name = "New Name" + DateTime.Now;
}
//db.Tracking(list);  //批量更新按需更新会无效
db.Updateable(list).ExecuteCommand();

//大数据量更新 
foreach (var item in addlist)
{
    item.Name = $"批量修改第二次=BulkUpdate方式";
}
Console.WriteLine("大数据量操作-BulkUpdate方式批量修改1000000条数据开始计时~~");

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
//db.Updateable<Student>(addlist).ExecuteCommand();	//100w数据大约消耗2min
db.Fastest<Student>().BulkUpdate(addlist);			//100w数据大约消耗3s
stopwatch.Stop();
Console.WriteLine($"批量修改1000000条数据共：{stopwatch.ElapsedMilliseconds} ms");

4 忽略某一列不更新

Student studentUp = db.Queryable<Student>().First();
studentUp.SchoolId = 234;
studentUp.Name = "忽略CreateTime不更新";
studentUp.CreateTime = DateTime.Now.AddYears(5);
var result = db.Updateable(studentUp)
   .IgnoreColumns(it => new
   {
       it.CreateTime
   })
   .ExecuteCommand();

5 只更新某列–按需更新

Student studentUp = db.Queryable<Student>().First();
studentUp.SchoolId = 345;
studentUp.Name = "只更Name和CreateTime";
studentUp.CreateTime = DateTime.Now.AddYears(6);
var result = db.Updateable(studentUp).UpdateColumns(it => new { it.Name, it.CreateTime }).ExecuteCommand();

6 NULL列不更新

Student studentUp = db.Queryable<Student>().First();
studentUp.SchoolId = 456;
studentUp.Name = null;

//更新忽略null字段
var result = db.Updateable(studentUp)
    .IgnoreColumns(ignoreAllNullColumns: true)
    .ExecuteCommand();

//更新忽略null并且忽略默认值 (比如int默认值是0就不更新)
var result1 = db.Updateable(studentUp)
    .IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true)
    .ExecuteCommand();

7 无主键/指定列更新

//WhereColumns(it=>new { it.Id,it.Name}) //条件列不会被更新，只会作为条件 
Student studentUp = db.Queryable<Student>().First();
studentUp.SchoolId = 567;
studentUp.Name = null;

var result = db.Updateable(studentUp)
    .WhereColumns(it => new
    {
        it.Id
    }).ExecuteCommand();//更新单 条根据ID 

8 条件更新

Student studentUp = db.Queryable<Student>().First();
studentUp.Name = "条件更新";

var result = db.Updateable(studentUp).Where(it => it.Id == 7003783).ExecuteCommand();

9 重新赋值更新

Student studentUp = db.Queryable<Student>().First();
studentUp.SchoolId = 678;
studentUp.Name = "重新赋值";

// studentUp.Name值的基础上在处理
var result = db.Updateable(studentUp)
        .ReSetValue(it =>
        {
            it.Name = it.Name + "a";
        })
       .ExecuteCommand();

//多个字段          
var result1 = db.Updateable(studentUp)
           .ReSetValue(it =>
           {
               it.Name = it.Name + "a";
               it.CreateTime = DateTime.Now;
           })
          .ExecuteCommand();

10 表达式更新

var result = db.Updateable<Student>()
    .SetColumns(it => new Student()
    {
        Name = "a",
        CreateTime = DateTime.Now
    })
    .Where(c => c.Id == 7003783)
    .ExecuteCommand();//正确没参数我们称为表达式更新  

11 Update where id in 操作

var ids = db.Queryable<Student>()
    .Select(c => c.Id)
    .Take(5).ToList();

var result = db.Updateable<Student>()
             .SetColumns(it => it.Name == "a")
            .Where(it => ids.Contains(it.Id))
            .ExecuteCommand();

12 表达式无实体更新

var result = db.Updateable<DbTableInfo>()
   .AS("dbstudent")
   .SetColumns("StudentName", "表达式无实体更新")
   .Where("id=7003782").ExecuteCommand();

13 根据字典更新

Dictionary<string, object> dt = new Dictionary<string, object>();
dt.Add("id", 7003782);
dt.Add("StudentName", "字典更新");
dt.Add("createTime", DateTime.Now);
var tResult = db.Updateable(dt).AS("dbstudent").WhereColumns("id").ExecuteCommand();

//字典集合
var dtList = new List<Dictionary<string, object>>();
dtList.Add(dt);

var t666 = db.Updateable(dtList).AS("dbstudent").WhereColumns("id").ExecuteCommand();
}