.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_
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：C#抽象类的用法介绍_实用技巧_
                                                                
下一篇：.NET集成ORM框架HiSql_实用技巧_