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