首页 > 数据库 >SqlSugar 基础知识

SqlSugar 基础知识

时间:2024-09-30 18:44:45浏览次数:8  
标签:SchoolId Context db 基础知识 && new Id 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、事务
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

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

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

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

 



 

标签:SchoolId,Context,db,基础知识,&&,new,Id,SqlSugar
From: https://www.cnblogs.com/lgx5/p/18442322

相关文章

  • Shell脚本基础知识-初步版
    本文是笔者研究生期间在阅读《Linux命令行与shell脚本编程大全》之后总结出来的一些重点知识的记录,在此重新整理输出。以便在给上个帖子涉及到的相关知识点进行一下讲解,帮助自己复习shell脚本的首行规范化应该是#!/bin/bash#functiondescription其中第一行必须如此,#后......
  • Postgre基础知识整理
    逻辑结构在一个实例下,允许存在多个互相独立的数据库,在一个数据库下,允许存在多个schema(schema并非完全独立,如,可存在跨越schema的外键),一个schema可以建立对象。大小写和单双引号不使用双引号时:PostgreSQL会将标识符(如表名、列名等)转换为小写。双引号:用于引用标识......
  • 变压器磁芯的基础知识介绍
     一、Core磁芯磁芯具有高磁导率,高的磁导率意味着导磁能力很强,也可以说磁芯对产生磁通的阻力很小。磁芯使得变压器的初级绕组可以较小的励磁电流产生较大的磁感应强度(又叫磁通密度或磁力线密度)。磁芯为磁力线提供了“比较顺畅的通路”,使得大多数的磁力线被约束在磁芯内,使得磁......
  • C++入门基础知识90(实例)——实例15【求两数的最大公约数】
    成长路上不孤单......
  • C++入门基础知识89(实例)——实例14【创建各类三角形图案】
    成长路上不孤单......
  • FFmpeg 初学者需要掌握的基础知识和实用技能。每个部分可以深入讲解,提供具体的命令示
    FFmpeg初级使用教程大纲1. FFmpeg简介什么是FFmpegFFmpeg的主要功能安装FFmpeg2. 基本命令格式FFmpeg的基本命令结构输入与输出文件的指定常用选项的介绍3. 常用命令示例转换视频格式示例:将MP4转换为AVI提取音频示例:从视频中提取音频压缩视......
  • 软件测试基础知识(自用)
    1.软件1.1软件的定义软件是运行在硬件上,一段指令的集合1.2软件的分类系统软件:系统自带的软件应用软件:移动端(APP)软件电脑端(PC)软件平板电脑端(pad)软件前端C/SPCexe--- 指Windows操作系统下的可执行文件APPIPA--- 指iOS应用程序包文件,扩展名为.ipa,包含了iOS应......
  • 【C++基础知识——如何判断是注入依赖还是赋值?】
    问题template<typenameType,typenameEventlist,typenameEventlist,typenameRejectEventList,typenameQueuedEventHandler>classParallelProcedureQueueManager{explicitParallelProcedureQueueManager(Type&equipment,con......
  • Pandas -----------------------基础知识(五)
    索引和列操作函数缺失值索引和列操作#1加载数据#1.1从链家租房数据集中获取天通苑租房区域的所有数据存储在df2中#1.2从df2中获取价格列存储在df2_price对象importpandasaspddf=pd.read_csv('/root/pandas_code_ling/data/b_LJdata.csv')df2=df[df['区......
  • shell基础知识
    shell基础知识前言Shell是一个应用程序,它连接了用户和Linux内核,让用户能够更加高效、安全、低成本地使用Linux内核,这就是Shell的本质。然而Shell本身并不是内核的一部分,它只是站在内核的基础上编写的一个应用程序,但是Shell也有着它的特殊性,就是开机立马启动,并呈现在......