.NET使用Hisql实现菜单管理(增删改查)

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

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

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

        /// 
 /// 添加菜单 /// 
 ///  ///  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(); }

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(); } /// 
 /// 编辑菜单 /// 
 ///  ///  public int EditMenu(SysMenu menu) { //hisql方式 return ContextHiSql.Update("sys_menu", menu).ExecCommand(); //sqlsugar方式 return Context.Updateable(menu).ExecuteCommand(); } /// 
 /// 删除菜单 /// 
 ///  ///  public int DeleteMenuById(long menuId) { //hisql方式 return ContextHiSql.Delete("sys_menu").Where(new Filter { { "menuId", OperType.EQ, menuId } }).ExecCommand(); //sqlsugar方式 return Context.Deleteable().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 代码 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