更新表（二）

配置输出Sql语句

db.Aop.OnLogExecuting = (s, p) =>
                    {
                        Console.WriteLine("----------------------------");
                        Console.WriteLine($"Sql语句:{s}");
                    };

单条更新 用传入的实体内容将数据库内容完全覆盖

Student student = db.Queryable<Student>().First();
student.CreateTime = DateTime.Now;
db.Updateable<Student>(student).ExecuteCommand();

按需更新 这个是实体改什么，数据库就改什么

db.Tracking(student);
student.Name = "Test";
db.Updateable(student).ExecuteCommand();

批量修改

List<Student> students1 = db.Queryable<Student>().Take(10).ToList();
foreach (var item in students1)
{
     item.Name = "New Name " + DateTime.Now.ToString();
}
db.Updateable(students1).ExecuteCommand();

//批量数据不能进行按需修改
db.Tracking(students1);
foreach (var item in students1)
{
     item.Name = "New Name " + DateTime.Now.ToString();
}
db.Updateable(students1).ExecuteCommand();

大量数据操作

//插入大批量数据                  
if (db.DbMaintenance.IsAnyTable("Student"))
{
    db.DbMaintenance.DropTable("Student");
}
db.CodeFirst.InitTables(typeof(Student));
List<Student> students2 = new List<Student>();
for (int i = 0; i < 100000; i++)
{
    students2.Add(new Student()
   {
      CreateTime = DateTime.Now,
      Name = $"Name_Update_{i}",
      SchoolId = i,
   }); ;
}
db.Fastest<Student>().BulkCopy(students2);


///修改大批量数据
foreach (var item in students2)
{
    item.Name = "NewDate" + DateTime.Now.ToString();
}
Console.WriteLine("开始批量修改计时");


//普通更新
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
db.Updateable<Student>(students2).ExecuteCommand();
stopwatch.Stop();
Console.WriteLine($"批量修改1000000条数据共：{stopwatch.ElapsedMilliseconds} ms");


//使用fastest更新 好像不通过OnLogExecuting这个通道
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
db.Fastest<Student>().BulkUpdate(students2);
stopwatch.Stop();
Console.WriteLine($"批量修改1000000条数据共：{stopwatch.ElapsedMilliseconds} ms");

忽略更新某列

Student student1 = db.Queryable<Student>().First(); 
student1.SchoolId = 234; 
student1.Name = "忽略某一列"; 
student1.CreateTime = DateTime.Now.AddDays(3); 
var result = db.Updateable(student1) 
               .IgnoreColumns(
                  it => new { it.CreateTime, it.Name }
                  )
               .ExecuteCommand();

按需更新的另外一种写法

Student student1 = db.Queryable<Student>().First();
student1.SchoolId = 234;
student1.Name = "忽略某列";
student1.CreateTime = DateTime.Now.AddDays(3);
var result = db.Updateable(student1)
               .UpdateColumns(it => new { it.SchoolId, it.Name })
               .ExecuteCommand();

更新时忽略空的值和默认值

Student student2 = db.Queryable<Student>().First();
student2.SchoolId = 0;
student2.CreateTime = null;

var result = db.Updateable(student2)
               .IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true)
               .ExecuteCommand();

更新数据的时候，WhereColumns选择指定列

Student student3 = db.Queryable<Student>().Where(it => it.SchoolId == 3).First();
student3.Name = "Test" + DateTime.Now.ToString();
var result = db.Updateable(student3)
               .WhereColumns(it => new { it.SchoolId })
               .ExecuteCommand();

条件更新

Student student4 = db.Queryable<Student>().First();
student4.Name = "Test1" + DateTime.Now.ToString();

db.Updateable(student4).Where(it => it.SchoolId == 3)
  .UpdateColumns(it => new { it.Name })
  .ExecuteCommand();

在实例的基础上重新复制更新

Student student5 = db.Queryable<Student>().First();
student.SchoolId = 5;
student.Name = "test";

var result = db.Updateable(student5)
               .ReSetValue(it =>
               {
                    it.Name = it.Name + "Reset";
                    it.CreateTime = DateTime.Now.AddDays(3);
               })
               .ExecuteCommand();

表达式更新

var result = db.Updateable<Student>()
               .SetColumns(it => new Student()
                    {
                        Name = "FuncTest",
                        CreateTime = DateTime.Now.AddDays(3),
                    })
               .Where(it => it.SchoolId == 5)
               .ExecuteCommand();

in的更新

var ids = db.Queryable<Student>()
            .Where(x => x.SchoolId > 5 && x.SchoolId <= 10)
            .Select(x => x.SchoolId)
            .ToList();

var result = db.Updateable<Student>()
               .SetColumns(it => new Student() 
                                  { 
                                     CreateTime = DateTime.Now.AddDays(1)                   
                                  }
                          )
               .Where(it => ids.Contains(it.SchoolId))
               .ExecuteCommand();

表达式无实体更新

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

根据字典更新

Dictionary<string, object> dt = new Dictionary<string, object>();
                    dt.Add("SchoolId", 8);
                    dt.Add("StudentName", "字典更新2");
                    dt.Add("createTime", DateTime.Now);
                    //var Result = db.Updateable(dt).AS("Student")
                    //    .WhereColumns("SchoolId").ExecuteCommand();

List<Dictionary<string, object>> dtList = new List<Dictionary<string, object>>();
                    dtList.Add(dt);
                    var Result1 = db.Updateable(dtList)
                                    .AS("Student")
                                    .WhereColumns("SchoolId")
                                    .ExecuteCommand();