.NET ORM框架HiSql实战-第二章-使用Hisql实现菜单管理(增删改查) ...

嚴華 · 2022-8-22 15:10:37

一、引言
上一篇.NET ORM框架HiSql实战-第一章-集成HiSql 已经完成了Hisql的引入，本节就把项目中的菜单管理改成hisql的方式实现。
菜单管理界面如图：

二、修改增删改查相关代码
1、在 BaseRepository 仓储基类中添加 hisql访问对象：HiSqlClient。这样所有继承了BaseRepository的业务仓储类都可以使用HiSqlClient操作数据库。本节中的菜单管理用到的仓储对象就是：SysMenuRepository

2、修改 SysMenuRepository类中访问数据的代码，将所有操作数据库的地方使用HiSql方式实现。
3、查询业务：获取菜单详情

/// <summary>
/// 获取菜单详情
/// </summary>
/// <param name="menuId"></param>
/// <returns></returns>
public SysMenu SelectMenuById(int menuId)
{
//hisql方式
return ContextHiSql.HiSql(@"select * from sys_menu where menuId = @menuId ", new { menuId = menuId })
.ToList<SysMenu>().FirstOrDefault();
//sqlsuar方式
return Context.Queryable<SysMenu>().Where(it => it.menuId == menuId).Single();
}

复制代码

4、修改业务：编辑菜单

/// <summary>
/// 编辑菜单
/// </summary>
/// <param name="menu"></param>
/// <returns></returns>
public int EditMenu(SysMenu menu)
{
//hisql方式
return ContextHiSql.Update("sys_menu", menu).ExecCommand();
//sqlsugar方式
return Context.Updateable(menu).ExecuteCommand();
}

复制代码

5、删除业务：删除菜单

/// <summary>
/// 删除菜单
/// </summary>
/// <param name="menuId"></param>
/// <returns></returns>
public int DeleteMenuById(long menuId)
{
//hisql方式
return ContextHiSql.Delete("sys_menu").Where(new Filter { { "menuId", OperType.EQ, menuId } }).ExecCommand();
//sqlsugar方式
return Context.Deleteable<SysMenu>().Where(it => it.menuId == menuId).ExecuteCommand();
}

复制代码

6、新增业务：添加菜单

/// <summary>
/// 添加菜单
/// </summary>
/// <param name="menu"></param>
/// <returns></returns>
public int AddMenu(SysMenu menu)
{
var Db = Context;
menu.Create_time = Db.GetDate();
menu.menuId = IDHelper.GetLongId();
//hisql方式
return ContextHiSql.Insert("sys_menu", menu).ExecCommand();
//sqlsugar方式
return Db.Insertable(menu).ExecuteCommand();
}

复制代码

Tip:此处使用雪花ID，其实HiSql自带相关方法。如 HiSql.Snowflake.NextId(); 以及业务根据业务实际情况自定义编号的模块，后面再集成到项目中来。自定义编号
其他业务方法见 SysMenuRepository 代码。
SysMenuRepository 代码

using Infrastructure.Attribute;using SqlSugar;using System;using System.Collections.Generic;using System.Linq;using HSMB.Model.System.Dto;using HSMB.Model.System;using HiSql;using Infrastructure;namespace HSMB.Repository.System{ /// /// 系统菜单 /// [AppService(ServiceLifetime = LifeTime.Transient)] public class SysMenuRepository : BaseRepository { /// /// 获取所有菜单（菜单管理） /// /// public List SelectMenuList(SysMenu menu) { //hisql方式 Filter filters = new Filter(); filters.AddIf(!string.IsNullOrEmpty(menu.menuName), "menuName", OperType.LIKE, menu.menuName); filters.AddIf(!string.IsNullOrEmpty(menu.visible), "visible", OperType.EQ, menu.visible); filters.AddIf(!string.IsNullOrEmpty(menu.menuName), "status", OperType.EQ, menu.status); return ContextHiSql.Query("sys_menu").Field("*").Where(filters).Sort("parentId", "orderNum").ToList(); //sqlsugar方式 return Context.Queryable() .WhereIF(!string.IsNullOrEmpty(menu.menuName), it => it.menuName.Contains(menu.menuName)) .WhereIF(!string.IsNullOrEmpty(menu.visible), it => it.visible == menu.visible) .WhereIF(!string.IsNullOrEmpty(menu.status), it => it.status == menu.status) .OrderBy(it => new { it.parentId, it.orderNum }) .ToList(); } /// /// 根据用户查询系统菜单列表 /// /// /// 用户id /// public List SelectMenuListByUserId(SysMenu sysMenu, long userId) { //hisql方式 Filter filters = new Filter(); filters.Add("userRole.UserId", OperType.EQ, userId); filters.AddIf(!string.IsNullOrEmpty(sysMenu.menuName), "menu.menuName", OperType.LIKE, sysMenu.menuName); filters.AddIf(!string.IsNullOrEmpty(sysMenu.visible), "menu.visible", OperType.EQ, sysMenu.visible); filters.AddIf(!string.IsNullOrEmpty(sysMenu.menuName), "menu.status", OperType.EQ, sysMenu.status); return ContextHiSql.HiSql(@"select menu.* from sys_menu as menu join sys_role_menu as roleMenu on menu.menuId = roleMenu.menuId join sys_user_role as userRole on userRole.Role_id = roleMenu.Role_id join sys_role as role on role.RoleId = userRole.RoleId order by menu.parentId, menu.orderNum ").Where(filters).ToList(); // //sqlsugar方式 return Context.Queryable((menu, roleMenu, userRole, role) => new JoinQueryInfos( SqlSugar.JoinType.Left, menu.menuId == roleMenu.Menu_id, SqlSugar.JoinType.Left, roleMenu.Role_id == userRole.RoleId, SqlSugar.JoinType.Left, userRole.RoleId == role.RoleId )) .Where((menu, roleMenu, userRole, role) => userRole.UserId == userId) .WhereIF(!string.IsNullOrEmpty(sysMenu.menuName), (menu, roleMenu, userRole, role) => menu.menuName.Contains(sysMenu.menuName)) .WhereIF(!string.IsNullOrEmpty(sysMenu.visible), (menu, roleMenu, userRole, role) => menu.visible == sysMenu.visible) .WhereIF(!string.IsNullOrEmpty(sysMenu.status), (menu, roleMenu, userRole, role) => menu.status == sysMenu.status) .OrderBy((menu, roleMenu, userRole, role) => new { menu.parentId, menu.orderNum }) .Select((menu, roleMenu, userRole, role) => menu).ToList(); } #region 左侧菜单树 /// /// 管理员获取左侧菜单树 /// /// public List SelectMenuTreeAll() { var menuTypes = new string[] { "M", "C" }; //hisql方式 return ContextHiSql.HiSql("select * from sys_menu where status = @status and menuType in(@menuType)", new { status = "0", menuType = menuTypes }) .Sort("parentId", "orderNum") .ToList(); //sqlsugar方式 return Context.Queryable() .Where(f => f.status == "0" && menuTypes.Contains(f.menuType)) .OrderBy(it => new { it.parentId, it.orderNum }).ToList(); } /// /// 根据用户角色获取左侧菜单树 /// /// /// public List SelectMenuTreeByRoleIds(List roleIds) { var menuTypes = new List() { "M", "C"}; //hisql方式 return ContextHiSql.HiSql(@"select menu.* from sys_menu as menu join sys_role_menu as roleMenu on menu.menuId = roleMenu.Menu_id where roleMenu.Role_id in (@roleIds) and menu.menuType in(@menuType)", new { roleIds = (List)roleIds, menuType = menuTypes }) .Sort("parentId", "orderNum") .ToList(); //sqlsugar方式 return Context.Queryable((menu, roleMenu) => new JoinQueryInfos( SqlSugar.JoinType.Left, menu.menuId == roleMenu.Menu_id )) .Where((menu, roleMenu) => roleIds.Contains(((int)roleMenu.Role_id)) && menuTypes.Contains(menu.menuType) && menu.status == "0") .OrderBy((menu, roleMenu) => new { menu.parentId, menu.orderNum }) .Select((menu, roleMenu) => menu).ToList(); } #endregion /// /// 获取菜单详情 /// /// /// public SysMenu SelectMenuById(int menuId) { //hisql方式 return ContextHiSql.HiSql(@"select * from sys_menu where menuId = @menuId ", new { menuId = menuId }) .ToList().FirstOrDefault(); //sqlsugar方式 return Context.Queryable().Where(it => it.menuId == menuId).Single(); } /// /// 添加菜单 /// /// /// public int AddMenu(SysMenu menu) { var Db = Context; menu.Create_time = Db.GetDate(); menu.menuId = IDHelper.GetLongId(); // 此处使用雪花ID，其实HiSql自带相关方法。如 HiSql.Snowflake.NextId(); //hisql方式 return ContextHiSql.Insert("sys_menu", menu).ExecCommand(); //sqlsugar方式 return Db.Insertable(menu).ExecuteCommand(); } /// <summary>
/// 编辑菜单
/// </summary>
/// <param name="menu"></param>
/// <returns></returns>
public int EditMenu(SysMenu menu)
{
//hisql方式
return ContextHiSql.Update("sys_menu", menu).ExecCommand();
//sqlsugar方式
return Context.Updateable(menu).ExecuteCommand();
} /// <summary>
/// 删除菜单
/// </summary>
/// <param name="menuId"></param>
/// <returns></returns>
public int DeleteMenuById(long menuId)
{
//hisql方式
return ContextHiSql.Delete("sys_menu").Where(new Filter { { "menuId", OperType.EQ, menuId } }).ExecCommand();
//sqlsugar方式
return Context.Deleteable<SysMenu>().Where(it => it.menuId == menuId).ExecuteCommand();
} /// /// 菜单排序 /// /// 菜单Dto /// public int ChangeSortMenu(MenuDto menuDto) { //hisql方式 return ContextHiSql.Update("sys_menu", new SysMenu() { menuId = menuDto.MenuId, orderNum = menuDto.orderNum }).Only("orderNum").ExecCommand(); //sqlsugar方式 var result = Context.Updateable(new SysMenu() { menuId = menuDto.MenuId, orderNum = menuDto.orderNum }) .UpdateColumns(it => new { it.orderNum }).ExecuteCommand(); return result; } /// /// 查询菜单权限 /// /// /// public List SelectMenuPermsByUserId(long userId) { //var ta = ContextHiSql.Query("sys_role").Field("*").ToList().FirstOrDefault(); //hisql方式 Filter filters = new Filter(); filters.Add("menu.status", OperType.EQ, 0); filters.Add("role.status", OperType.EQ, 0); filters.Add("userRole.user_id", OperType.EQ, userId); string aa = @"select menu.* from sys_menu as menu join sys_role_menu as roleMenu on menu.menuId = roleMenu.menu_id join sys_user_role as userRole on userRole.Role_id = roleMenu.Role_id join sys_role as role on role.RoleId = userRole.role_id order by menu.parentId, menu.orderNum "; return ContextHiSql.HiSql(@"select menu.* from sys_menu as menu join sys_role_menu as roleMenu on menu.menuId = roleMenu.menu_id join sys_user_role as userRole on userRole.Role_id = roleMenu.Role_id join sys_role as role on role.RoleId = userRole.role_id order by menu.parentId, menu.orderNum ").Where(filters).ToList(); // //sqlsugar方式 return Context.Queryable((m, rm, ur, r) => new JoinQueryInfos( SqlSugar.JoinType.Left, m.menuId == rm.Menu_id, SqlSugar.JoinType.Left, rm.Role_id == ur.RoleId, SqlSugar.JoinType.Left, ur.RoleId == r.RoleId )) //.Distinct() .Where((m, rm, ur, r) => m.status == "0" && r.Status == "0" && ur.UserId == userId) .Select((m, rm, ur, r) => m).ToList(); } /// /// 校验菜单名称是否唯一 /// /// /// public SysMenu CheckMenuNameUnique(SysMenu menu) { //hisql方式 Filter filters = new Filter(); filters.Add("menuName", OperType.EQ, menu.menuName); filters.Add("parentId", OperType.EQ, menu.parentId); return ContextHiSql.Query("sys_menu").Field("*").Where(filters).ToList().FirstOrDefault(); //sqlsugar方式 return Context.Queryable() .Where(it => it.menuName == menu.menuName && it.parentId == menu.parentId).Single(); } /// /// 是否存在菜单子节点 /// /// /// public int HasChildByMenuId(long menuId) { //hisql方式 Filter filters = new Filter(); filters.Add("parentId", OperType.EQ, menuId); return int.Parse(ContextHiSql.Query("sys_menu").Field("count(*) as Cnt").Where(filters).ToTable().Rows[0][0].ToString()); //sqlsugar方式 return Context.Queryable().Where(it => it.parentId == menuId).Count(); } #region RoleMenu /// /// 查询菜单使用数量 /// /// /// public int CheckMenuExistRole(long menuId) { //hisql方式 Filter filters = new Filter(); filters.Add("Menu_id", OperType.EQ, menuId); return int.Parse(ContextHiSql.Query("sys_role_menu").Field("count(*) as Cnt").Where(filters).ToTable().Rows[0][0].ToString()); //sqlsugar方式 return Context.Queryable().Where(it => it.Menu_id == menuId).Count(); } #endregion }}

复制代码

仓储基类 BaseRepository.cs 代码
[code]using H.Cache;using Infrastructure;using Infrastructure.Extensions;using Infrastructure.Model;using SqlSugar;using SqlSugar.IOC;using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Linq.Expressions;using System.Text;using System.Threading.Tasks;using HSMB.Model;using HSMB.Model.System;using HiSql;namespace HSMB.Repository{ /// /// /// /// public class BaseRepository : IBaseRepository where T : class, new() { public ISqlSugarClient Context; public HiSqlClient ContextHiSql; public ICacheManagerBase cacheManager; public BaseRepository( string configId = "0") { this.cacheManager = AutofacCore.GetFromFac(); //hisql方式 this.ContextHiSql = AutofacCore.GetFromFac(); //sqlsugar 方式 Context = DbTransient.Sugar.GetConnection(configId);//根据类传入的ConfigId自动选择 Context = DbScoped.SqlSugarScope.GetConnection(configId); } #region add /// /// 插入指定列使用 /// /// /// /// /// public int Add(T parm, Expression iClumns = null, bool ignoreNull = true) { return Context.Insertable(parm).InsertColumns(iClumns).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand(); } /// /// 插入实体 /// /// /// 默认忽略null列 /// public int Add(T t) { return Context.Insertable(t).ExecuteCommand(); } public int Insert(List t) { return Context.Insertable(t).ExecuteCommand(); } public long InsertReturnBigIdentity(T t) { return Context.Insertable(t).ExecuteReturnBigIdentity(); } #endregion add #region update /// /// /// /// /// /// 默认为true /// public bool Update(T entity, List list = null, bool isNull = true) { if (list == null) { list = new List() { "Create_By", "Create_time" }; } return Context.Updateable(entity).IgnoreColumns(isNull).IgnoreColumns(list.ToArray()).ExecuteCommand() > 0; } public bool Update(Expression where, Expression columns) { return Context.Updateable().SetColumns(columns).Where(where).RemoveDataCache().ExecuteCommand() > 0; } #endregion update public DbResult UseTran(Action action) { var result = Context.Ado.UseTran(() => action()); return result; } public DbResult UseTran(SqlSugarClient client, Action action) { var result = client.Ado.UseTran(() => action()); return result; } public bool UseTran2(Action action) { var result = Context.Ado.UseTran(() => action()); return result.IsSuccess; } #region delete /// /// 删除表达式 /// /// /// public int Delete(Expression expression) { return Context.Deleteable().Where(expression).ExecuteCommand(); } /// /// 批量删除 /// /// /// public int Delete(object[] obj) { return Context.Deleteable().In(obj).ExecuteCommand(); } public int Delete(object id) { return Context.Deleteable(id).ExecuteCommand(); } public bool DeleteTable() { return Context.Deleteable().ExecuteCommand() > 0; } #endregion delete #region query public bool Any(Expression expression) { return Context.Queryable().Where(expression).Any(); } public ISugarQueryable Queryable() { return Context.Queryable(); } public List GetList(Expression expression) { return Context.Queryable().Where(expression).ToList(); } public Task GetListAsync(Expression expression) { return Context.Queryable().Where(expression).ToListAsync(); } public List SqlQueryToList(string sql, object obj = null) { return Context.Ado.SqlQuery(sql, obj); } /// /// 获得一条数据 /// /// Expression /// public T GetFirst(Expression where) { return Context.Queryable().Where(where).First(); } /// /// 根据主值查询单条数据 /// /// 主键值 /// 泛型实体 public T GetId(object pkValue) { return Context.Queryable().InSingle(pkValue); } /// /// 根据条件查询分页数据 /// /// /// /// public PagedInfo GetPages(Expression where, PagerInfo parm) { var source = Context.Queryable().Where(where); return source.ToPage(parm); } public PagedInfo GetPages(Expression where, PagerInfo parm, Expression order, string orderEnum = "Asc") { var source = Context.Queryable().Where(where).OrderByIF(orderEnum == "Asc", order, OrderByType.Asc).OrderByIF(orderEnum == "Desc", order, OrderByType.Desc); return source.ToPage(parm); } /// /// 查询所有数据(无分页,请慎用) /// /// public virtual List GetAll(bool useCache = false, int cacheSecond = 3600) { if (useCache) { var cacheData = this.cacheManager.Get(typeof(T).FullName, (ct) => { var data = Context.Queryable().ToList(); return data; }, TimeSpan.FromSeconds(cacheSecond)); if (typeof(SysUser).FullName == typeof(T).FullName) { } return cacheData; } return Context.Queryable().WithCacheIF(useCache, cacheSecond).ToList(); } public int Count(Expression where) { return Context.Queryable().Count(where); } #endregion query /// /// 此方法不带output返回值 /// var list = new List(); /// list.Add(new SugarParameter(ParaName, ParaValue)); input /// /// /// /// public DataTable UseStoredProcedureToDataTable(string procedureName, List parameters) { return Context.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters); } public DataSet UseStoredProcedureToDataSet(string procedureName, List parameters) { var dataResult = new DataSet(); string sql = $"exec {procedureName} "; foreach (var key in parameters) { if (!key.Value.IsEmpty()) { sql = sql + (sql.IndexOf("@") > -1 ? ", " : " ") + $" {key.ParameterName} = {key.ParameterName}"; } } return Context.Ado.GetDataSetAll(sql, parameters); return Context.Ado.UseStoredProcedure().GetDataSetAll(procedureName, parameters); } public int ExecSql(string sql) { return Context.Ado.ExecuteCommand(sql,new List()); } /// /// 带output返回值 /// var list = new List(); /// list.Add(new SugarParameter(ParaName, ParaValue, true)); output /// list.Add(new SugarParameter(ParaName, ParaValue)); input /// /// /// /// public (DataTable, List) UseStoredProcedureToTuple(string procedureName, List parameters) { var result = (Context.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters), parameters); return result; } public DataTable QueryableToDataTable(PagerInfo pager) { int TotalPageNum = 0; int TotalNum = 0; List parameters = new List(); string sqlWhere = buildSearchFilter(pager, out parameters); var query = Context.SqlQueryable($"select * from {pager.TableName}").Where(sqlWhere).AddParameters(parameters); if (pager.OrderBy.IsNotEmpty()) { query = query.OrderBy(pager.OrderBy); } var table = query.ToDataTablePage(pager.PageNum, pager.PageSize, ref TotalPageNum, ref TotalNum); pager.TotalPageNum = TotalPageNum; pager.TotalNum = TotalNum; return table; } public PagedInfo QueryableToDataTablePage(PagerInfo pager) { var table = QueryableToDataTable(pager); PagedInfo pagedInfo = new PagedInfo(); pagedInfo.PageIndex = pager.PageNum; pagedInfo.PageSize = pager.PageSize; pagedInfo.TotalPage = pager.TotalNum; pagedInfo.TotalCount = pager.TotalPageNum; pagedInfo.Result = table; // Enumerable.ToList< DataRow >(table); return pagedInfo; } private string buildSearchFilter(PagerInfo pager, out List parameters) { parameters = new List(); if (pager.QueryConditions == null || pager.QueryConditions.Count() == 0) { return pager.Where; } StringBuilder sqlWhere = new StringBuilder(pager.QueryConditions.Count() + 1); sqlWhere.Append(" 1 = 1 "); int i = 0; foreach (PageQueryCondition pageQuery in pager.QueryConditions) { i++; string field = pageQuery.FieldName; var _value = pageQuery.Values; string startValue = null; if (_value != null) { startValue = _value.ElementAtOrDefault(0); } if(startValue == null) continue; if (string.IsNullOrEmpty(startValue) && (pageQuery.Mode != QueryConditionMode.Equal && pageQuery.Mode != QueryConditionMode.NotEqual && pageQuery.Mode != QueryConditionMode.Between && pageQuery.Mode != QueryConditionMode.BetweenAndDate)) continue; if (!string.IsNullOrEmpty(startValue)) { startValue = startValue.Replace("'", "''"); } switch (pageQuery.Mode) { case QueryConditionMode.In: { for (int q = 0; q < _value.Count; q++) { _value[q] = _value[q].Replace("'", "''"); } sqlWhere.Append(string.Format(" and [{0}] in ('"+ string.Join("','", _value) + "')", field, field + i.ToString())); break; } case QueryConditionMode.Equal: { if (startValue == null) { sqlWhere.Append(string.Format(" and [{0}] is null", field)); break; } else { sqlWhere.Append(string.Format(" and [{0}]=@{1}", field, field + i.ToString())); parameters.Add(new SugarParameter(field + i.ToString(), startValue)); break; } } case QueryConditionMode.NotEqual: { if (startValue == null) { sqlWhere.Append(string.Format(" and [{0}] is not null", field)); break; } else { sqlWhere.Append(string.Format(" and [{0}] @{1}", field, field + i.ToString())); parameters.Add(new SugarParameter(field + i.ToString(), startValue)); break; } } case QueryConditionMode.Greater: { sqlWhere.Append(string.Format(" and [{0}]>@{1}", field, field + i.ToString())); parameters.Add(new SugarParameter(field + i.ToString(), startValue)); break; } case QueryConditionMode.GreaterEqual: { sqlWhere.Append(string.Format(" and [{0}]>=@{1}", field, field + i.ToString())); parameters.Add(new SugarParameter(field + i.ToString(), startValue)); break; } case QueryConditionMode.Less: { sqlWhere.Append(string.Format(" and [{0}]

		自动登录	找回密码
密码			立即注册

.NET ORM框架HiSql实战-第二章-使用Hisql实现菜单管理(增删改查) ...

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云