SqlSugar常用增删改操作

一.添加数据

特别说明：

1.特别说明：对于自增长列的表插入数据后，当前自增长列的字段，仍旧为0，但可以通过Insert方法的返回值来获取

SqlSugarClient db = SugarContext.GetInstance();
//插入单条
db.Insert<student>(new student()
{
    sno = "110",
    sname = "张三丰",
    ssex = "男",
    sbirthday = new DateTime(1900, 10, 10),
    @class = "95031"
});

//特别说明：对于自增长列的表插入数据后，当前自增长列的字段，仍旧为0
//但可以通过Insert方法的返回值来获取
UserInfo user1 = new UserInfo()
{
    Name = "ceshi"
};
object idResult = db.Insert<UserInfo>(user1);
Console.WriteLine(user1.ToJsonString());
Console.WriteLine(idResult);

//插入多条
List<student> stuList = new List<student>() {
    new student() {
    sno = "111",
    sname = "张无忌",
    ssex = "男",
    sbirthday = new DateTime(1900, 10, 10),
    @class = "95031"
},
    new student() {
    sno = "112",
    sname = "花无缺",
    ssex = "男",
    sbirthday = new DateTime(1900, 10, 10),
    @class = "95031"
}};
db.InsertRange(stuList);


//设置不插入列,注意，这是全局设置，当执行完对应的操作需要清空
db.DisableInsertColumns = new string[] { "ssex", "sbirthday", "class" };
db.Insert(new student()
{
    sno = "113",
    sname = "小明"
});
db.DisableInsertColumns = null;
Console.WriteLine(db.Queryable<student>().Count());

二、修改数据

//SqlSugar 支持匿名对象方式更新,支持对象部分数据更新
SqlSugarClient db = SugarContext.GetInstance();

//1.匿名对象更新
bool result1 = db.Update<UserInfo>(new { name = "张三丰" }, q => q.UserID == 4);
Console.WriteLine(result1);
//2.批量更新
int[] idList = new int[] { 2, 3, 5 };
db.Update<UserInfo>(new { name = "网名" }, q => idList.Contains(q.UserID));

//3.指定对象更新
bool result3 = db.Update<student>(new student()
{
    sno = "112",
    sname = "王丽华"
});
Console.WriteLine(result3);

//4.指定对象更新，需要先获取在修改
student stu1 = db.Queryable<student>()
    .Where(q => q.sno == "112")
    .First();
stu1.ssex = "女";
stu1.sbirthday = new DateTime(1919, 10, 10);
stu1.@class = "95032";
bool result4 = db.Update(stu1);
Console.WriteLine(result4);

//5.设置不更新列
//db.DisableUpdateColumns ,指定不更新列，执行完修改后，再清空

三、删除数据

SqlSugarClient db = SugarContext.GetInstance();
//1.指定主键删除
bool result1 = db.Delete<UserInfo, int>(4);
Console.WriteLine(result1);
//2.指定条件删除
bool result2 = db.Delete<UserInfo>(q => q.UserID >= 3);
Console.WriteLine(result2);

//3.指定主键批量删除
bool result3 = db.Delete<UserInfo, string>(q => q.Name, new string[] { "张三丰", "王小明" });
Console.WriteLine(result3);
//4.指定非主键批量删除
bool result4 = db.Delete<UserInfo, int>(q => q.UserID, new int[] { 8, 2 });
Console.WriteLine(result4);

//5.指定实体对象删除（必须要有主键）
bool result5 = db.Delete(new UserInfo() { UserID = 13 });
Console.WriteLine(result5);
//6.指定Sql的where的片段删除
bool result6 = db.Delete<UserInfo>("UserID=@UserID", new { UserID = 12 });
Console.WriteLine(result6);