多条件搜索分页的实现

　　对教材列表进行展示的时候，可以有条件的筛选结果，前台用到的是EasyUI DataGrid,后台接受查询参数，返回结果。

     这里只写成后台，数据访问层中实现的关键代码。

 /// <summary>
        /// 默认下获取分页数据
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每一页的行数</param>
        /// <returns>BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime</returns>
        public DataTable GetPagedModel(int pageIndex, int pageSize) 
        {
            string strOrder = "order by AddDate DESC";
            string strSQL = string.Format(@"select * from
                (select BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime,ROW_NUMBER() over({0}) as num from ViewBookInfo where IsDel=0) as tb 
                where num between {1}*({2}-1)+1 and {1}*{2} {0} ", strOrder, pageSize, pageIndex);

            return  SQLHelper.GetDataTable(strSQL);
        }

        /// <summary>
        /// 含参数的查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="paras">BookName、ClassName、DeptID、DeptPID、Category、[State]</param>
        /// <returns>BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime</returns>
        public DataTable GetPagedModel(int pageIndex, int pageSize, Hashtable ht) 
        {
            string strCondition;
            List<SqlParameter> parasList;
            string sOrder=" order by AddDate DESC";

            //获取参数
            GetSearchCondition(ht,out strCondition,out parasList);

            string strSQL = string.Format(@"select * from (select BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime,ROW_NUMBER() over({0}) as num from ViewBookInfo where IsDel=0 {3}) as tb 
                where num between {1}*({2}-1)+1 and {1}*{2} {0} ", sOrder , pageSize, pageIndex,strCondition);

            return SQLHelper.GetDataTable(strSQL,parasList.ToArray());

        }

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="BookID"></param>
        /// <returns></returns>
        public DataTable GetDetail(int BookID) 
        {
            string strSQL = "select ClassTarget,BookFeature from BookInfo where BookID="+BookID;

            return SQLHelper.GetDataTable(strSQL);
        }
        /// <summary>
        /// 含参数记录的条数
        /// </summary>
        /// <param name="ht">BookName、ClassName、DeptID、DeptPID、Category、[State]</param>
        /// <returns></returns>
        public int GetRecordCount(Hashtable ht) 
        {
            string strCondition;
            List<SqlParameter> parasList;

            //获取参数
            GetSearchCondition(ht, out strCondition, out parasList);

            string strSQL = string.Format("select count(0) from ViewBookInfo where IsDel=0 {0} ", strCondition);

            return Convert.ToInt32(SQLHelper.ExecuteScalar(strSQL, parasList.ToArray()));
        }

        /// <summary>
        /// 装配查询参数,获取查询要的字符串，和参数集合
        /// </summary>
        /// <param name="ht">BookName、ClassName、DeptID、DeptPID、Category、[State]</param>
        /// <param name="sOrder"></param>
        /// <param name="paras"></param>
        private void GetSearchCondition(Hashtable ht, out string contString, out List<SqlParameter> paras) 
        {
            StringBuilder sOrder = new StringBuilder();
            paras=new List<SqlParameter>();

            if (ht.Count == 0) 
            {
                contString = "";
                return;
            }

            object oBookName = ht["BookName"];
            object oClassName = ht["ClassName"];
            object oDeptID = ht["DeptID"];
            object oDeptPID = ht["DeptPID"];
            object oCategory = ht["Category"];
            object oState = ht["State"];

            //书名
            if (oBookName != null && !string.IsNullOrEmpty(oBookName.ToString()))
            {
                sOrder.Append(" and BookName like @BookName or  BookPY like @BookName");
                paras.Add(new SqlParameter("@BookName",oBookName.ToString()+"%"));
            }
            //课程名
            if(oClassName!=null&&!string.IsNullOrEmpty(oClassName.ToString()))
            {
                sOrder.Append(" and  ClassName like @ClassName ");
                paras.Add(new SqlParameter("@ClassName",oClassName.ToString()+"%"));
            }
            //部门ID
            if (oDeptID != null && !string.IsNullOrEmpty(oDeptID.ToString())) 
            {
                sOrder.Append(" and DeptID=@DeptID ");
                paras.Add(new SqlParameter("@DeptID",Convert.ToInt32(oDeptID)));
            }

            //上级部门PID
            if (oDeptPID != null && !string.IsNullOrEmpty(oDeptPID.ToString())) 
            {
                sOrder.Append(" and DeptPID=@DeptPID ");
                paras.Add(new SqlParameter("@DeptPID",Convert.ToInt32(oDeptPID)));
            }

            //种类Category
            if (oCategory != null && !string.IsNullOrEmpty(oCategory.ToString())) 
            {
                sOrder.Append(" and Category=@Category ");
                paras.Add(new SqlParameter("@Category",oCategory.ToString()));
            }

            //状态State
            if (oState != null && !string.IsNullOrEmpty(oState.ToString())) 
            {
                sOrder.Append(" and [State]=@State ");
                paras.Add(new SqlParameter("@State",oState.ToString()));
            }

            contString = sOrder.ToString();
            
        }

　　条件查询中Hashtable存储的是筛选的字段和其对应的值。