SqlSugar小技巧 - 新增或修改

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

1 新增或修改

//不存在就插入，存在就修改
StudentInfo studentInfo = new StudentInfo()
{
    Id = 31,
    Name = "新增的数据",
    CreateTime = DateTime.Now,
    Isdeleted = false,
    SchoolId = 0,
};
//新功能 5.0.6.2+ 
//存在更新 不存在插入 (默认是主键)
db.Storageable(studentInfo).ExecuteCommand();//新版才支持 
studentInfo.Name = "数据已存在就修改";
db.Storageable(studentInfo).ExecuteCommand();//新版才支持


//批量操作---存在更新 不存在插入 
List<StudentInfo> addlist = new List<StudentInfo>();
for (int i = 0; i < 20; i++)
{
    addlist.Add(new StudentInfo()
    {
        Id = i + 1,
        CreateTime = DateTime.Now,
        Isdeleted = false,
        Name = $"名称_{i + 1}",
        SchoolId = i + 1,
    });
}
db.Storageable<StudentInfo>(addlist).ExecuteCommand();
foreach (var item in addlist)
{
    item.Name = $"批量修改";
}
db.Storageable<StudentInfo>(addlist).ExecuteCommand();

2 主键Id是否为0插入否则更新（不验证数据库是否存在）

只更新修改字段

//创建student对象 id 为0 
StudentInfo obj = new StudentInfo()
{
    CreateTime = DateTime.Now,
    Isdeleted = false,
    Name = $"名称_41",
    SchoolId = 41,
};
//id等于0插入否则更新（不验证数据库是否存在）
db.Storageable(obj)
.SplitUpdate(it => it.Item.Id > 0)
.SplitInsert(it => it.Item.Id == 0).ExecuteCommand();

obj.Id = 1;
obj.Name = "修改一下数据";
db.Storageable(obj)
  .SplitUpdate(it => it.Item.Id > 0)
  .SplitInsert(it => Item.Id == 0).ExecuteCommand();

3 更新忽略字段

//修改部分数据
upobj.Name = "Name修改了";
upobj.CreateTime = DateTime.Now.AddYears(10);

//存在更新
StorageableResult<StudentInfo> storageableResult1 = db.Storageable(upobj)
   .ToStorage();
storageableResult1.AsUpdateable.IgnoreColumns(z => z.Name).ExecuteCommand();

4 无主键操作

upobj.CreateTime = DateTime.Now.AddYears(-5);
upobj.Name = "无主键操作";
db.Storageable(upobj)
  .WhereColumns(it => it.Id)//指定一个条件，当然支持多个 new {it.id,it.name}
  .ExecuteCommand();//将数据进行分组 

5 大数据更新

db.Deleteable<StudentInfo>().ExecuteCommand(); //删除所有数据

var addlist = new List<StudentInfo>();
for (int i = 0; i < 100000; i++)
{
    addlist.Add(new StudentInfo()
    {
        Id = i + 1,
        CreateTime = DateTime.Now,
        Isdeleted = false,
        Name = $"名称_{i + 1}",
        SchoolId = i + 1,
    });
}
StorageableResult<StudentInfo> storageableResult = db.
    Storageable<StudentInfo>(addlist).ToStorage();
storageableResult.BulkCopy();

6 更新字典数据

Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("Id", 456789);
dic.Add("SchoolId", 456789);
dic.Add("Name", "字典用法");
dic.Add("CreateTime", DateTime.Now);
dic.Add("Isdeleted", 1);
dictionaryList.Add(dic);

DataTableResult tableResult = db.Storageable(dictionaryList, "StudentInfo")
    .WhereColumns("id")
    .ToStorage();//id作为主键

tableResult.AsInsertable.ExecuteCommand();//如果是自增要添加IgnoreColumns

dictionaryList[0]["Name"] = "修改名称了";
DataTableResult tableResult1 = db.Storageable(dictionaryList, "StudentInfo")
 .WhereColumns("id")
 .ToStorage();//id作为主键
tableResult1.AsUpdateable.ExecuteCommand();

6 分页处理

List<StudentInfo> list = db.Queryable<StudentInfo>().ToList();
//分页处理
db.Utilities.PageEach(list, 2000, pageList =>
{ 
    db.Storageable(pageList).ExecuteCommand();
    //条件列禁止varchar(50)以上，并且是主键或者有索引为佳 
    //也可以用BulkCopy
    //var x= db.Storageable<Order>(data).ToStorage();
    //x.BulkCopy();
    //x.BulkUpdate();  

});