Sqlsugar 的使用

1、实体特性
[SugarColumn(IsPrimaryKey = true)] 标识是否为主键
[SugarColumn(IsIdentity = true)] 是否为自增长
[SugarColumn(ColumnName = "id")] 对应数据库表里面的某列
[SugarColumn(IsIgnore = true)] 忽略熟悉，在ORM会过滤掉
[SugarColumn(ColumnDescription = "创建时间")] 描述

[SugarTable("base_student")]
public class Student
{
    /// <summary>
    /// IsPrimaryKey 标识是否为主键
    /// IsIdentity 是否为自增长
    /// </summary>
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true, ColumnName = "id")]
    public string Id { get; set; }
 
    /// <summary>
    /// 定义别名进来Mapping，对应数据库表里面的Remark列
    /// </summary>
    [SugarColumn(ColumnName = "student_name", ColumnDescription = "用户名")]
    public string Name { get; set; }
 
    /// <summary>
    /// IsIgnore 不做数据库操作，true将不会进行查询、添加等操作
    /// </summary>
    [SugarColumn(ColumnName = "created_time", IsIgnore = true, ColumnDescription = "创建时间")]
    public DateTime CreatedTime { get; set; } //这列在ORM会过滤掉
}

2、联合查询（一）

public List<MenuDo> GetListByRoleId(string roleId, string sid)
{
    var result = Context.db.Queryable<RoleMenuDo, MenuDo>((rm, m) => new object[]
    {
        JoinType.Left, rm.MenuId ==m.Id
    })
    .Where((rm, m) => rm.RoleId == roleId && rm.MenuType == 1
    && rm.Status != "D" && rm.SchoolId == sid
    && m.Status != "D" && m.SchoolId == sid)
    .OrderBy((rm, m) => m.Sort, OrderByType.Desc)
    .Select((rm, m) => new
    {
        Id = m.Id,
        MenuName = m.MenuName,
        ControllerName = m.ControllerName,
        ActionName = m.ActionName,
        Url = m.Url,
        ParentId = m.ParentId,
        Sort = m.Sort,
        Icon = m.Icon,
    }).ToList()
    .Select(x => new MenuDo
    {
        Id = x.Id,
        //MenuType = item.MenuType,
        MenuName = x.MenuName,
        ControllerName = x.ControllerName,
        ActionName = x.ActionName,
        Url = x.Url,
        Icon = x.Icon,
        ParentId = x.ParentId,
    }).ToList();
 
    return result;
}

3、联合查询（二）

var result1 = Context.db.Queryable<ClassDo, GradeDo>((c, g) => new object[]
                { JoinType.Left, c.GradeId == g.Id })
                .Where((c, g) => c.SchoolId == sid && c.Status != "D" && g.SchoolId == sid && g.Status != "D")
                .WhereIF(string.IsNullOrEmpty(name), (c, g) =>
                    c.ClassName.Contains(name) || c.ClassCode.Contains(name) || g.GradeName.Contains(name) || g.GradeCode.Contains(name))
                .Select<ClassViewModel>().ToPageList(pageIndex, pageSize, ref total);

4、联合查询 - 简化

public Equipment GetModelByID(string id, string sid)
{
    var result = Context.db.Queryable<Equipment, Dormitory>((e, d) => e.DormitoryId == d.Id)
       .Where((e, d) => e.SchoolId == sid && e.Status != "D" && d.SchoolId == sid && d.Status != "D")
       .Select((e, d) => new Equipment
       {
           Id = e.Id,
           EquipmentCode = e.EquipmentCode,
           EquipmentName = e.EquipmentName,
           Location = e.Location,
           DormitoryId = e.DormitoryId,
           DormitoryName = d.DormitoryName,
           Mac = e.Mac,
           Ip = e.Ip,
           InOutType = e.InOutType,
           Describe = e.Describe,
           Sort = e.Sort,
       }).First();
    return Mapper.Map<Equipment>(result);
}
 
//2表查询
var list5 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id).Select((st,sc)=>new {st.Name,st.Id,schoolName=sc.Name}).ToList();
 
//3表查询
var list6 = db.Queryable<Student, School,School>((st, sc,sc2) => st.SchoolId == sc.Id&&sc.Id==sc2.Id)
    .Select((st, sc,sc2) => new { st.Name, st.Id, schoolName = sc.Name,schoolName2=sc2.Name }).ToList();
 
//3表查询分页
var list7= db.Queryable<Student, School, School>((st, sc, sc2) => st.SchoolId == sc.Id && sc.Id == sc2.Id)
.Select((st, sc, sc2) => new { st.Name, st.Id, schoolName = sc.Name, schoolName2 = sc2.Name }).ToPageList(1,2);

5、WHERE条件查询

var query = Context.SqlQueryable<StudentDto>(sql)
            .Where(x => x.Status != "D")
            .Where(x => grades.Select(g => new { GradeId = g.Id }).JoinAsString(",").Contains(x.GradeId)
                    && classes.Select(c => new { ClassId = c.Id }).JoinAsString(",").Contains(x.ClassId))
            .WhereIF(request.SchoolId.HasValue(), x => x.SchoolId == request.SchoolId)
            .WhereIF(request.ClassId.HasValue(), x => x.ClassId == request.ClassId)
            .WhereIF(request.GradeId.HasValue(), x => x.GradeId == request.GradeId)
            .WhereIF(request.Search.HasValue(), x => x.StudentName == request.Search || x.StudentCode== request.Search)
            .WhereIF(request.NameOrCode.HasValue(),
                        x => x.StudentName.Contains(request.NameOrCode) || x.StudentCode.Contains(request.NameOrCode));

Between ... AND ...

db.Queryable<Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();

6、Insert 新增 批量插入

var insertData = new course() { };//测试参数
var insertArray = new course[] { insertData };
courseDb.Insert(insertData);//插入
courseDb.InsertRange(insertArray);//批量插入
var id = courseDb.InsertReturnIdentity(insertData);//插入返回自增列
courseDb.AsInsertable(insertData).ExecuteCommand();//我们可以转成 Insertable实现复杂插入
var insertObjs = new List<Student>(); //批量插入
var s9 = db.Insertable(insertObjs.ToArray()).ExecuteCommand();
//注意 ： SqlSever 建表语句带有Wtih(设置)，如果设置不合理，可能会引起慢，把With删掉就会很快

7、Update 修改

var updateData = new course() {  };//测试参数
var updateArray = new course[] { updateData };//测试参数
courseDb.Update(updateData);//根据实体更新
courseDb.UpdateRange(updateArray);//批量更新
// 只更新Name列和CreateTime列，其它列不更新，条件id=1
//courseDb.Update(it => new course() { Name = "a", CreateTime = DateTime.Now }, it => it.id==1);
courseDb.AsUpdateable(updateData).ExecuteCommand();

8、删除

var deldata = new course() {  };//测试参数
courseDb.Delete(deldata);//根据实体删除
courseDb.DeleteById(1);//根据主键删除
courseDb.DeleteById(new int[] { 1,2});//根据主键数组删除
courseDb.Delete(it=>1==2);//根据条件删除
courseDb.AsDeleteable().Where(it=>1==2).ExecuteCommand();//转成Deleteable实现复杂的操作

9、MySQL 存储过程

-- ----------------------------
-- Procedure structure for delete_data
-- ----------------------------
DROP PROCEDURE IF EXISTS `delete_data`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `delete_data`(in sid VARCHAR(36))
BEGIN
    DELETE FROM base_grade WHERE school_id=sid;
    DELETE FROM sys_user_info WHERE school_id=sid AND (mobile IS NULL OR mobile != 'admin');
    SET @adminId = '';
    SELECT @adminId := id FROM sys_user_info WHERE school_id=sid AND mobile = 'admin';
    DELETE FROM sys_user_role WHERE school_id=sid and user_id != @adminId;
END;;
DELIMITER ;

10、事务
1、无数据返回只返回状态

var result = Context.db.Ado.UseTran(() =>
{
    #region
    //Context.db.Deleteable<GradeDo>(x => x.SchoolId == grades[0].SchoolId).ExecuteCommand();
    //Context.db.Deleteable<ClassDo>(x => x.SchoolId == grades[0].SchoolId).ExecuteCommand();
    #endregion
 
    #region 执行存储过程删除
    Context.db.Ado.UseStoredProcedure().GetDataTable("proc_data_delete", new { sid = grades[0].SchoolId });
    #endregion
 
    if (grades != null && grades.Count > 0)
        Context.db.Insertable(grades.ToArray()).ExecuteCommand();
 
    if (classes != null && classes.Count > 0)
        Context.db.Insertable(classes.ToArray()).ExecuteCommand();
});
// result.ErrorException
// result.IsSuccess

2、返回数据并且返回状态

var result2 = db.Ado.UseTran<List<Student>>(() =>
{
    return db.Queryable<Student>().ToList();
});
// result.ErrorException
// result.IsSuccess
// result.Data

3、使用try catch来处理事务，用这种方式事务一定要加try catch回滚不然会锁表，在操作就卡死

try
{
    Context.db.Ado.BeginTran();
 
    #region 方式1
    //Context.db.Deleteable<GradeDo>(x => x.SchoolId == grades[0].SchoolId).ExecuteCommand();
    //Context.db.Deleteable<ClassDo>(x => x.SchoolId == grades[0].SchoolId).ExecuteCommand();
    #endregion
 
    #region 执行存储过程删除
    Context.db.Ado.UseStoredProcedure().GetDataTable("proc_data_delete", new { sid = grades[0].SchoolId });
    #endregion
 
    if (grades != null && grades.Count > 0)
        Context.db.Insertable(grades.ToArray()).ExecuteCommand();
 
    if (classes != null && classes.Count > 0)
        Context.db.Insertable(classes.ToArray()).ExecuteCommand();
 
    if (dormitorys != null && dormitorys.Count > 0)
        Context.db.Insertable(dormitorys.ToArray()).ExecuteCommand();
 
    Context.db.Ado.CommitTran();
}
catch (Exception)
{
    Context.db.Ado.RollbackTran();
    throw;
}
return true;

11、排序

db.Queryable<teacher>().OrderBy("sort desc")
 
db.Queryable<teacher>()
.OrderBy(it => it.Id) //asc
.OrderBy(it => it.Name, OrderByType.Desc) //desc
.ToList()