SqlSugar 基础知识_sugarcolumn

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);

0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
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 ;

//执行存储过程删除
var del = Context.Ado.UseStoredProcedure().GetDataTable("delete_data", new { sid = grades[0].SchoolId });

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()

0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、public IHttpActionResult GetAllGrade()
0、