.NET ORM框架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完成了 菜单的新增、编辑、删除、查看详情。