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

一、引言
上一篇.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{    /// <summary>    /// 系统菜单    /// </summary>    [AppService(ServiceLifetime = LifeTime.Transient)]    public class SysMenuRepository : BaseRepository<SysMenu>    {        /// <summary>        /// 获取所有菜单（菜单管理）        /// </summary>        /// <returns></returns>        public List<SysMenu> 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<SysMenu>();            //sqlsugar方式            return Context.Queryable<SysMenu>()                .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();        }        /// <summary>        /// 根据用户查询系统菜单列表        /// </summary>        /// <param name="sysMenu"></param>        /// <param name="userId">用户id</param>        /// <returns></returns>        public List<SysMenu> 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<SysMenu>(); //            //sqlsugar方式            return Context.Queryable<SysMenu, SysRoleMenu, SysUserRole, SysRole>((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 左侧菜单树        /// <summary>        /// 管理员获取左侧菜单树        /// </summary>        /// <returns></returns>        public List<SysMenu> 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<SysMenu>();            //sqlsugar方式            return Context.Queryable<SysMenu>()                .Where(f => f.status == "0" && menuTypes.Contains(f.menuType))                .OrderBy(it => new { it.parentId, it.orderNum }).ToList();        }        /// <summary>        /// 根据用户角色获取左侧菜单树        /// </summary>        /// <param name="userId"></param>        /// <returns></returns>        public List<SysMenu> SelectMenuTreeByRoleIds(List<long> roleIds)        {            var menuTypes = new List<string>() { "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<long>)roleIds, menuType = menuTypes })                .Sort("parentId", "orderNum")                .ToList<SysMenu>();            //sqlsugar方式            return Context.Queryable<SysMenu, SysRoleMenu>((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        /// <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();            //sqlsugar方式            return Context.Queryable<SysMenu>().Where(it => it.menuId == menuId).Single();        }        /// <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(); // 此处使用雪花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();        }        /// <summary>        /// 菜单排序        /// </summary>        /// <param name="menuDto">菜单Dto</param>        /// <returns></returns>        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;        }        /// <summary>        /// 查询菜单权限        /// </summary>        /// <param name="userId"></param>        /// <returns></returns>        public List<SysMenu> SelectMenuPermsByUserId(long userId)        {            //var ta =  ContextHiSql.Query("sys_role").Field("*").ToList<SysMenu>().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<SysMenu>(); //            //sqlsugar方式            return Context.Queryable<SysMenu, SysRoleMenu, SysUserRole, SysRole>((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();        }        /// <summary>        /// 校验菜单名称是否唯一        /// </summary>        /// <param name="menu"></param>        /// <returns></returns>        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<SysMenu>().FirstOrDefault();            //sqlsugar方式            return Context.Queryable<SysMenu>()                .Where(it => it.menuName == menu.menuName && it.parentId == menu.parentId).Single();        }        /// <summary>        /// 是否存在菜单子节点        /// </summary>        /// <param name="menuId"></param>        /// <returns></returns>        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<SysMenu>().Where(it => it.parentId == menuId).Count();        }        #region RoleMenu        /// <summary>        /// 查询菜单使用数量        /// </summary>        /// <param name="menuId"></param>        /// <returns></returns>        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<SysRoleMenu>().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{    /// <summary>    ///     /// </summary>    /// <typeparam name="T"></typeparam>    public class BaseRepository<T> : IBaseRepository<T> where T : class, new()    {        public ISqlSugarClient Context;        public HiSqlClient ContextHiSql;        public ICacheManagerBase cacheManager;        public BaseRepository( string configId = "0")        {            this.cacheManager = AutofacCore.GetFromFac<ICacheManagerBase>();            //hisql方式            this.ContextHiSql = AutofacCore.GetFromFac<HiSqlClient>();                       //sqlsugar 方式            Context = DbTransient.Sugar.GetConnection(configId);//根据类传入的ConfigId自动选择   Context = DbScoped.SqlSugarScope.GetConnection(configId);          }        #region add        /// <summary>        /// 插入指定列使用        /// </summary>        /// <param name="parm"></param>        /// <param name="iClumns"></param>        /// <param name="ignoreNull"></param>        /// <returns></returns>        public int Add(T parm, Expression<Func<T, object>> iClumns = null, bool ignoreNull = true)        {            return Context.Insertable(parm).InsertColumns(iClumns).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand();        }        /// <summary>        /// 插入实体        /// </summary>        /// <param name="t"></param>        /// <param name="IgnoreNullColumn">默认忽略null列</param>        /// <returns></returns>        public int Add(T t)        {            return Context.Insertable(t).ExecuteCommand();        }        public int Insert(List<T> t)        {            return Context.Insertable(t).ExecuteCommand();        }        public long InsertReturnBigIdentity(T t)        {            return Context.Insertable(t).ExecuteReturnBigIdentity();        }                #endregion add        #region update                /// <summary>        ///        /// </summary>        /// <param name="entity"></param>        /// <param name="list"></param>        /// <param name="isNull">默认为true</param>        /// <returns></returns>        public bool Update(T entity, List<string> list = null, bool isNull = true)        {            if (list == null)            {                list = new List<string>()            {                "Create_By",                "Create_time"            };            }            return Context.Updateable(entity).IgnoreColumns(isNull).IgnoreColumns(list.ToArray()).ExecuteCommand() > 0;        }        public bool Update(Expression<Func<T, bool>> where, Expression<Func<T, T>> columns)        {            return Context.Updateable<T>().SetColumns(columns).Where(where).RemoveDataCache().ExecuteCommand() > 0;        }        #endregion update        public DbResult<bool> UseTran(Action action)        {            var result = Context.Ado.UseTran(() => action());            return result;        }        public DbResult<bool> 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        /// <summary>        /// 删除表达式        /// </summary>        /// <param name="expression"></param>        /// <returns></returns>        public int Delete(Expression<Func<T, bool>> expression)        {            return Context.Deleteable<T>().Where(expression).ExecuteCommand();        }        /// <summary>        /// 批量删除        /// </summary>        /// <param name="obj"></param>        /// <returns></returns>        public int Delete(object[] obj)        {            return Context.Deleteable<T>().In(obj).ExecuteCommand();        }        public int Delete(object id)        {            return Context.Deleteable<T>(id).ExecuteCommand();        }        public bool DeleteTable()        {            return Context.Deleteable<T>().ExecuteCommand() > 0;        }        #endregion delete        #region query        public bool Any(Expression<Func<T, bool>> expression)        {            return Context.Queryable<T>().Where(expression).Any();        }        public ISugarQueryable<T> Queryable()        {            return Context.Queryable<T>();        }        public List<T> GetList(Expression<Func<T, bool>> expression)        {            return Context.Queryable<T>().Where(expression).ToList();        }        public Task<List<T>> GetListAsync(Expression<Func<T, bool>> expression)        {            return Context.Queryable<T>().Where(expression).ToListAsync();        }               public List<T> SqlQueryToList(string sql, object obj = null)        {            return Context.Ado.SqlQuery<T>(sql, obj);        }        /// <summary>        /// 获得一条数据        /// </summary>        /// <param name="where">Expression<Func<T, bool>></param>        /// <returns></returns>        public T GetFirst(Expression<Func<T, bool>> where)        {            return Context.Queryable<T>().Where(where).First();        }        /// <summary>        /// 根据主值查询单条数据        /// </summary>        /// <param name="pkValue">主键值</param>        /// <returns>泛型实体</returns>        public T GetId(object pkValue)        {            return Context.Queryable<T>().InSingle(pkValue);        }        /// <summary>        /// 根据条件查询分页数据        /// </summary>        /// <param name="where"></param>        /// <param name="parm"></param>        /// <returns></returns>        public PagedInfo<T> GetPages(Expression<Func<T, bool>> where, PagerInfo parm)        {            var source = Context.Queryable<T>().Where(where);            return source.ToPage(parm);        }        public PagedInfo<T> GetPages(Expression<Func<T, bool>> where, PagerInfo parm, Expression<Func<T, object>> order, string orderEnum = "Asc")        {            var source = Context.Queryable<T>().Where(where).OrderByIF(orderEnum == "Asc", order, OrderByType.Asc).OrderByIF(orderEnum == "Desc", order, OrderByType.Desc);            return source.ToPage(parm);        }        /// <summary>        /// 查询所有数据(无分页,请慎用)        /// </summary>        /// <returns></returns>        public virtual List<T> GetAll(bool useCache = false, int cacheSecond = 3600)        {            if (useCache)            {                var cacheData = this.cacheManager.Get<List<T>>(typeof(T).FullName, (ct) => {                    var data = Context.Queryable<T>().ToList();                    return data;                }, TimeSpan.FromSeconds(cacheSecond));                if (typeof(SysUser).FullName == typeof(T).FullName)                {                                   }                return cacheData;            }            return Context.Queryable<T>().WithCacheIF(useCache, cacheSecond).ToList();        }        public int Count(Expression<Func<T, bool>> where)        {            return Context.Queryable<T>().Count(where);        }        #endregion query        /// <summary>        /// 此方法不带output返回值        /// var list = new List<SugarParameter>();        /// list.Add(new SugarParameter(ParaName, ParaValue)); input        /// </summary>        /// <param name="procedureName"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public DataTable UseStoredProcedureToDataTable(string procedureName, List<SugarParameter> parameters)        {            return Context.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters);        }        public DataSet UseStoredProcedureToDataSet(string procedureName, List<SugarParameter> 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<SugarParameter>());        }        /// <summary>        /// 带output返回值        /// var list = new List<SugarParameter>();        /// list.Add(new SugarParameter(ParaName, ParaValue, true));  output        /// list.Add(new SugarParameter(ParaName, ParaValue)); input        /// </summary>        /// <param name="procedureName"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public (DataTable, List<SugarParameter>) UseStoredProcedureToTuple(string procedureName, List<SugarParameter> parameters)        {            var result = (Context.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters), parameters);            return result;        }                public DataTable QueryableToDataTable(PagerInfo pager)        {            int TotalPageNum = 0;            int TotalNum = 0;            List<SugarParameter> parameters = new List<SugarParameter>();            string sqlWhere = buildSearchFilter(pager, out parameters);            var query = Context.SqlQueryable<object>($"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<SugarParameter> parameters)        {            parameters = new List<SugarParameter>();            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}]<@{1}", field, field + i.ToString()));                            parameters.Add(new SugarParameter(field + i.ToString(), startValue));                            break;                        }                    case QueryConditionMode.LessEqual:                        {                            sqlWhere.Append(string.Format(" and [{0}]<=@{1}", field, field + i.ToString()));                            parameters.Add(new SugarParameter(field + i.ToString(), startValue));                            break;                        }                    case QueryConditionMode.Like:                        {                            sqlWhere.Append(string.Format(" and CHARINDEX(@{1},[{0}])>0 ", field, field + i.ToString()));                            parameters.Add(new SugarParameter(field + i.ToString(), startValue));                            break;                        }                    case QueryConditionMode.Between:                        {                            var endValue = pageQuery.Values.ElementAtOrDefault(1);                            if (string.IsNullOrEmpty(startValue) && string.IsNullOrEmpty(endValue)) break;                            endValue = endValue.Replace("'", "''");                            dataType(startValue, endValue);                            sqlWhere.Append(string.Format(" and ([{0}] between @{1}1 and @{2}2) ", field, field + i.ToString(), field + i.ToString()));                            parameters.Add(new SugarParameter(string.Format("@{0}1", field + i.ToString()), startValue));                            parameters.Add(new SugarParameter(string.Format("@{0}2", field + i.ToString()), endValue));                            break;                        }                    case QueryConditionMode.BetweenAndDate:                        {                            var endValue = pageQuery.Values.ElementAtOrDefault(1);                            if (endValue == null)                                endValue = "";                            if (!startValue.IsEmpty() && startValue.IndexOf(" - ") > -1)                            {                                string splitStr = " - ";                                string _startValue = startValue;                                startValue = _startValue.Substring(0, _startValue.IndexOf(splitStr));                                endValue = _startValue.Substring(_startValue.IndexOf(splitStr) + splitStr.Length);                            }                            if (string.IsNullOrEmpty(startValue) && string.IsNullOrEmpty(endValue)) break;                            endValue = endValue.Replace("'", "''");                            DateTime outDateTime;                            if (DateTime.TryParse(startValue, out outDateTime))                            {                                startValue = outDateTime.ToString("yyyy-MM-dd 00:00:00");                            }                            else                            {                                startValue = "1900-01-01 00:00:00";                            }                            if (DateTime.TryParse(endValue, out outDateTime))                            {                                endValue = outDateTime.ToString("yyyy-MM-dd 23:59:59");                            }                            else                            {                                endValue = "2099-01-01 23:59:59";                            }                            sqlWhere.Append(string.Format(" and ([{0}] between @{1}1 and @{2}2) ", field, field + i.ToString(), field + i.ToString()));                            parameters.Add(new SugarParameter(string.Format("@{0}1", field + i.ToString()), startValue));                            parameters.Add(new SugarParameter(string.Format("@{0}2", field + i.ToString()), endValue));                            break;                        }                }            }            return pager.Where.IsEmpty()? sqlWhere.ToString():pager.Where+ " and "+ sqlWhere.ToString();        }        /// <summary>        /// 判断值的数据类型        /// </summary>        /// <returns></returns>        private void dataType(string startValue, string endValue)        {            DateTime outDateTime;            if (DateTime.TryParse(startValue, out outDateTime) || DateTime.TryParse(endValue, out outDateTime))            {                startValue = string.IsNullOrEmpty(startValue) ? "1900-01-01 00:00:00" : startValue;                endValue = string.IsNullOrEmpty(startValue) ? "2050-01-01 23:59:59" : startValue;            }            double outi = 0;            if (double.TryParse(startValue, out outi) || double.TryParse(endValue, out outi))            {                startValue = string.IsNullOrEmpty(startValue) ? int.MinValue.ToString() : startValue;                endValue = string.IsNullOrEmpty(startValue) ? int.MinValue.ToString() : startValue;            }        }    }    public static class QueryableExtension    {        /// <summary>        /// 读取列表        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="source"></param>        /// <param name="parm"></param>        /// <returns></returns>        public static PagedInfo<T> ToPage<T>(this ISugarQueryable<T> source, PagerInfo parm)        {            var page = new PagedInfo<T>();            var total = source.Count();            page.TotalCount = total;            page.PageSize = parm.PageSize;            page.PageIndex = parm.PageNum;            page.Result = source.ToPageList(parm.PageNum, parm.PageSize);            return page;        }        public static PagedInfo<DataTable> ToPageDataTable<T>(this ISugarQueryable<T> source, PagerInfo parm)        {            var page = new PagedInfo<DataTable>();            var total = source.Count();            page.TotalCount = total;            page.PageSize = parm.PageSize;            page.PageIndex = parm.PageNum;            page.DataTable = source.ToDataTablePage(parm.PageNum, parm.PageSize);            return page;        }    }}

到此，菜单管理模块通过hisql完成了 菜单的新增、编辑、删除、查看详情。