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

发布时间：2023-07-18 09:00

一、引言

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

.NET使用Hisql实现菜单管理(增删改查)_第1张图片

二、修改增删改查相关代码

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

.NET使用Hisql实现菜单管理(增删改查)_第2张图片

2、修改 SysMenuRepository类中访问数据的代码，将所有操作数据库的地方使用HiSql方式实现。

3、查询业务：获取菜单详情

        /// 
        /// 获取菜单详情
        /// 
        /// 
        /// 
        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();
        }

4、修改业务：编辑菜单

        /// 
        /// 编辑菜单
        /// 
        /// 
        /// 
        public int EditMenu(SysMenu menu)
        {

            //hisql方式
            return ContextHiSql.Update("sys_menu", menu).ExecCommand();
            //sqlsugar方式
            return Context.Updateable(menu).ExecuteCommand();
        }

5、删除业务：删除菜单

        /// 
        /// 删除菜单
        /// 
        /// 
        /// 
        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();
        }

6、新增业务：添加菜单

        /// 
        /// 添加菜单
        /// 
        /// 
        /// 
        public int AddMenu(SysMenu menu)
        {
            var Db = Context;
            menu.Create_time = Db.GetDate();
            
            menu.menuId = IDHelper.GetLongId(); 
            //hisql方式
            return ContextHiSql.Insert("sys_menu", menu).ExecCommand();

            //sqlsugar方式
            return Db.Insertable(menu).ExecuteCommand();
        }

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

其他业务方法见 SysMenuRepository 代码。

SysMenuRepository 代码

using Infrastructure.Attribute;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using HSMB.Model.System.Dto;
using HSMB.Model.System;
using HiSql;
using Infrastructure;

namespace HSMB.Repository.System
{
    /// 
    /// 系统菜单
    /// 
    [AppService(ServiceLifetime = LifeTime.Transient)]
    public class SysMenuRepository : BaseRepository
    {
        /// 
        /// 获取所有菜单（菜单管理）
        /// 
        /// 
        public List SelectMenuList(SysMenu menu)
        {
            //hisql方式
            Filter filters = new Filter();
            filters.AddIf(!string.IsNullOrEmpty(menu.menuName), "menuName", OperType.LIKE, menu.menuName);
            filters.AddIf(!string.IsNullOrEmpty(menu.visible), "visible", OperType.EQ, menu.visible);
            filters.AddIf(!string.IsNullOrEmpty(menu.menuName), "status", OperType.EQ, menu.status);
            return ContextHiSql.Query("sys_menu").Field("*").Where(filters).Sort("parentId", "orderNum").ToList();

            //sqlsugar方式
            return Context.Queryable()
                .WhereIF(!string.IsNullOrEmpty(menu.menuName), it => it.menuName.Contains(menu.menuName))
                .WhereIF(!string.IsNullOrEmpty(menu.visible), it => it.visible == menu.visible)
                .WhereIF(!string.IsNullOrEmpty(menu.status), it => it.status == menu.status)
                .OrderBy(it => new { it.parentId, it.orderNum })
                .ToList();
        }

        /// 
        /// 根据用户查询系统菜单列表
        /// 
        /// 
        /// 用户id
        /// 
        public List SelectMenuListByUserId(SysMenu sysMenu, long userId)
        {
            //hisql方式
            Filter filters = new Filter();
            filters.Add("userRole.UserId", OperType.EQ, userId);
            filters.AddIf(!string.IsNullOrEmpty(sysMenu.menuName), "menu.menuName", OperType.LIKE, sysMenu.menuName);
            filters.AddIf(!string.IsNullOrEmpty(sysMenu.visible), "menu.visible", OperType.EQ, sysMenu.visible);
            filters.AddIf(!string.IsNullOrEmpty(sysMenu.menuName), "menu.status", OperType.EQ, sysMenu.status);

            return ContextHiSql.HiSql(@"select menu.* from sys_menu as  menu 
                    join sys_role_menu as roleMenu on menu.menuId = roleMenu.menuId 
                    join sys_user_role as userRole on userRole.Role_id = roleMenu.Role_id
                    join sys_role as role on role.RoleId = userRole.RoleId
                 order by menu.parentId, menu.orderNum
                ").Where(filters).ToList(); //
            //sqlsugar方式
            return Context.Queryable((menu, roleMenu, userRole, role) => new JoinQueryInfos(
                 SqlSugar.JoinType.Left, menu.menuId == roleMenu.Menu_id,
                 SqlSugar.JoinType.Left, roleMenu.Role_id == userRole.RoleId,
                 SqlSugar.JoinType.Left, userRole.RoleId == role.RoleId
                 ))
                .Where((menu, roleMenu, userRole, role) => userRole.UserId == userId)
                .WhereIF(!string.IsNullOrEmpty(sysMenu.menuName), (menu, roleMenu, userRole, role) => menu.menuName.Contains(sysMenu.menuName))
                .WhereIF(!string.IsNullOrEmpty(sysMenu.visible), (menu, roleMenu, userRole, role) => menu.visible == sysMenu.visible)
                .WhereIF(!string.IsNullOrEmpty(sysMenu.status), (menu, roleMenu, userRole, role) => menu.status == sysMenu.status)
                .OrderBy((menu, roleMenu, userRole, role) => new { menu.parentId, menu.orderNum })
                .Select((menu, roleMenu, userRole, role) => menu).ToList();
        }

        #region 左侧菜单树

        /// 
        /// 管理员获取左侧菜单树
        /// 
        /// 
        public List SelectMenuTreeAll()
        {
            var menuTypes = new string[] { "M", "C" };
            //hisql方式
            return ContextHiSql.HiSql("select * from sys_menu where status = @status and menuType in(@menuType)", new { status = "0", menuType = menuTypes })
                .Sort("parentId", "orderNum")
                .ToList();
            //sqlsugar方式
            return Context.Queryable()
                .Where(f => f.status == "0" && menuTypes.Contains(f.menuType))
                .OrderBy(it => new { it.parentId, it.orderNum }).ToList();
        }

        /// 
        /// 根据用户角色获取左侧菜单树
        /// 
        /// 
        /// 
        public List SelectMenuTreeByRoleIds(List roleIds)
        {
            var menuTypes = new List() { "M", "C"};
            //hisql方式
            return ContextHiSql.HiSql(@"select menu.* from sys_menu as menu join  sys_role_menu as roleMenu on  menu.menuId = roleMenu.Menu_id
                    where roleMenu.Role_id in (@roleIds) and menu.menuType in(@menuType)", new { roleIds = (List)roleIds, menuType = menuTypes })
                .Sort("parentId", "orderNum")
                .ToList();
            //sqlsugar方式
            return Context.Queryable((menu, roleMenu) => new JoinQueryInfos(
                 SqlSugar.JoinType.Left, menu.menuId == roleMenu.Menu_id
                 ))
                .Where((menu, roleMenu) => roleIds.Contains(((int)roleMenu.Role_id)) && menuTypes.Contains(menu.menuType) && menu.status == "0")
                .OrderBy((menu, roleMenu) => new { menu.parentId, menu.orderNum })
                .Select((menu, roleMenu) => menu).ToList();
        }

        #endregion

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

        /// 
        /// 添加菜单
        /// 
        /// 
        /// 
        public int AddMenu(SysMenu menu)
        {
            var Db = Context;
            menu.Create_time = Db.GetDate();
            
            menu.menuId = IDHelper.GetLongId(); // 此处使用雪花ID，其实HiSql自带相关方法。如 HiSql.Snowflake.NextId();
            //hisql方式
            return ContextHiSql.Insert("sys_menu", menu).ExecCommand();

            //sqlsugar方式
            return Db.Insertable(menu).ExecuteCommand();
        }

        /// 
        /// 编辑菜单
        /// 
        /// 
        /// 
        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($"select * from {pager.TableName}").Where(sqlWhere).AddParameters(parameters);
            if (pager.OrderBy.IsNotEmpty())
            {
                query = query.OrderBy(pager.OrderBy);
            }
            var table = query.ToDataTablePage(pager.PageNum, pager.PageSize, ref TotalPageNum, ref TotalNum);

            pager.TotalPageNum = TotalPageNum;
            pager.TotalNum = TotalNum;
            return table;
        }
        public PagedInfo QueryableToDataTablePage(PagerInfo pager)
        {
            var table = QueryableToDataTable(pager);
            PagedInfo pagedInfo = new PagedInfo();
            pagedInfo.PageIndex = pager.PageNum;
            pagedInfo.PageSize = pager.PageSize;
            pagedInfo.TotalPage = pager.TotalNum;
            pagedInfo.TotalCount = pager.TotalPageNum;
            pagedInfo.Result = table;  // Enumerable.ToList< DataRow >(table);
            return pagedInfo;
        }

        

        private string buildSearchFilter(PagerInfo pager, out List parameters)
        {
            parameters = new List();
            if (pager.QueryConditions == null || pager.QueryConditions.Count() == 0)
            {
                return pager.Where;
            }
            StringBuilder sqlWhere = new StringBuilder(pager.QueryConditions.Count() + 1);
            sqlWhere.Append(" 1 = 1 ");
         
            int i = 0;
            foreach (PageQueryCondition pageQuery in pager.QueryConditions)
            {
                i++;
                string field = pageQuery.FieldName;
                var _value = pageQuery.Values;

                string startValue = null;
                if (_value != null)
                {
                    startValue = _value.ElementAtOrDefault(0);
                }
                if(startValue == null)
                    continue;

                if (string.IsNullOrEmpty(startValue) && (pageQuery.Mode != QueryConditionMode.Equal && pageQuery.Mode != QueryConditionMode.NotEqual && pageQuery.Mode != QueryConditionMode.Between && pageQuery.Mode != QueryConditionMode.BetweenAndDate))
                    continue;
                if (!string.IsNullOrEmpty(startValue))
                {
                    startValue = startValue.Replace("'", "''");
                }
                switch (pageQuery.Mode)
                {
                    case QueryConditionMode.In:
                        {
                            for (int q  = 0; q < _value.Count; q++)
                            {
                                _value[q] = _value[q].Replace("'", "''");
                            }
                          
                            sqlWhere.Append(string.Format(" and [{0}] in ('"+ string.Join("','", _value) + "')", field, field + i.ToString()));
                            break;
                        }
                    case QueryConditionMode.Equal:
                        {
                            if (startValue == null)
                            {
                                sqlWhere.Append(string.Format(" and [{0}] is null", field));
                                break;
                            }
                            else
                            {
                                sqlWhere.Append(string.Format(" and [{0}]=@{1}", field, field + i.ToString()));
                                parameters.Add(new SugarParameter(field + i.ToString(), startValue));
                                break;
                            }

                        }
                    case QueryConditionMode.NotEqual:
                        {
                            if (startValue == null)
                            {
                                sqlWhere.Append(string.Format(" and [{0}] is not null", field));
                                break;
                            }
                            else
                            {

                                sqlWhere.Append(string.Format(" and [{0}] <> @{1}", field, field + i.ToString()));
                                parameters.Add(new SugarParameter(field + i.ToString(), startValue));
                                break;
                            }
                        }
                    case QueryConditionMode.Greater:
                        {
                            sqlWhere.Append(string.Format(" and [{0}]>@{1}", field, field + i.ToString()));
                            parameters.Add(new SugarParameter(field + i.ToString(), startValue));
                            break;
                        }
                    case QueryConditionMode.GreaterEqual:
                        {
                            sqlWhere.Append(string.Format(" and [{0}]>=@{1}", field, field + i.ToString()));
                            parameters.Add(new SugarParameter(field + i.ToString(), startValue));
                            break;
                        }
                    case QueryConditionMode.Less:
                        {
                            sqlWhere.Append(string.Format(" and [{0}]<@{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();
        }
        /// 
        /// 判断值的数据类型
        /// 
        /// 
        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
    {
        /// 
        /// 读取列表
        /// 
        /// 
        /// 
        /// 
        /// 
        public static PagedInfo ToPage(this ISugarQueryable source, PagerInfo parm)
        {
            var page = new PagedInfo();
            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 ToPageDataTable(this ISugarQueryable source, PagerInfo parm)
        {
            var page = new PagedInfo();
            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完成了 菜单的新增、编辑、删除、查看详情。
 
 
.NET使用Hisql实现菜单管理(增删改查)_第3张图片
 
 
.NET使用Hisql实现菜单管理(增删改查)_第4张图片
 
 
.NET使用Hisql实现菜单管理(增删改查)_第5张图片
 
 
到此这篇关于.NET使用Hisql实现菜单管理的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持脚本之家。
 
                
            
            

                
相关推荐


                                
李沐基于Pytorch的深度学习笔记（8）-基础优化算法（附代码）

                                
客观评价 增长趋势比 vite 还猛的 TailwindCSS

                                
C 语言与指针 ， 指针部分笔试题目及解析

                                
【中秋系列】这款秘制Python月饼游戏，拿走不谢~

                                
人工智能如何快速入门？这10个开源AI项目真太香！

                                
2021数学建模C题详细思路，代码，论文，参考文献

                                
springcloud Ribbon详解

                                
Python 绘制混淆矩阵

                                
机器学习 基于IRIS鸢尾花数据集进行聚类分析

                                
内网渗透之Windows反弹shell（一）

                                
基于matlab的语音信号处理

                                
零基础YOLOv5的详细使用教程

                                
oracle常用的字符串函数,【转载】oracle 常用的几个处理字符串函数

                                
卷积神经网络--卷积层和池化层

                                
centos7 puppeteer Error: 安装失败

                                
动漫人物也能变“真人”？PaddleGAN帮你找到“撕漫”的TA

                                
简单理解B树和B+树

                                
SparkStreaming-----SparkStreaming教程

                                
Grafana 中文入门教程 | 构建你的第一个仪表盘

                                
猿创征文｜Aixos的引入与基本使用

                            

        
    


            

                

                    ItVuer - 免责声明 - 关于我们 - 联系我们
                

                
本网站信息来源于互联网，如有侵权请联系：561261067@qq.com

                
桂ICP备16001015号