通用权限底层研究：强大的分页功能

         /// <summary>
        /// 获取系统运行日志数据
        /// </summary>
        /// <returns></returns>
        [AjaxRequestAttribute]
        public ActionResult GetSystemLog(SYSTEM_LOG4NETEntity logEntity, Pager pager, string sort, string direction, string startTime, string endTime)
        {
            Hashtable result = new Hashtable();
            DataTable dt = new DataTable();
            List<KeyValuePair<string, object>> dbParameters = new List<KeyValuePair<string, object>>();
            List<string> listWhere = new List<string>();
            string conditions = string.Empty;
            //时间查询条件
            DateTime timeStart, timeEnd;
            //默认查7天
            if (!DateTime.TryParse(startTime, out timeStart))
            {
                timeStart = DateTime.Now.AddDays(-7);
            }
            if (!DateTime.TryParse(endTime, out timeEnd))
            {
                timeEnd = DateTime.Now.AddDays(1);
            }
            listWhere.Add(SYSTEM_LOG4NETEntity.FieldLOG_DATE + " >= " + dbHelper.GetParameter("startTime") + " AND " + SYSTEM_LOG4NETEntity.FieldLOG_DATE + " <= " + dbHelper.GetParameter("endTime"));
            dbParameters.Add(new KeyValuePair<string, object>(dbHelper.GetParameter("startTime"), timeStart));
            dbParameters.Add(new KeyValuePair<string, object>(dbHelper.GetParameter("endTime"), timeEnd));
            //按IP地址查询
            if (!string.IsNullOrWhiteSpace(logEntity.IP))
            {
                listWhere.Add(SYSTEM_LOG4NETEntity.FieldIP + " = " + dbHelper.GetParameter("FieldIP"));
                dbParameters.Add(new KeyValuePair<string, object>(dbHelper.GetParameter("FieldIP"), logEntity.IP.Trim()));
            }
            //按标题查询
            if (!string.IsNullOrWhiteSpace(logEntity.LOG_TITLE))
            {
                if (logEntity.LOG_TITLE.IndexOf("%", StringComparison.Ordinal) < 0)
                {
                    logEntity.LOG_TITLE = string.Format("%{0}%", logEntity.LOG_TITLE).ToUpper().Trim();
                }
                listWhere.Add("Upper(" + SYSTEM_LOG4NETEntity.FieldLOG_TITLE + ") like " + dbHelper.GetParameter("FieldLOG_TITLE"));
                dbParameters.Add(new KeyValuePair<string, object>(dbHelper.GetParameter("FieldLOG_TITLE"), logEntity.LOG_TITLE));
            }
            //按systemcode查询
            if (!string.IsNullOrWhiteSpace(logEntity.SYSTEMCODE))
            {
                listWhere.Add(SYSTEM_LOG4NETEntity.FieldSYSTEMCODE + " = " + dbHelper.GetParameter("FieldSYSTEMCODE"));
                dbParameters.Add(new KeyValuePair<string, object>(dbHelper.GetParameter("FieldSYSTEMCODE"), logEntity.SYSTEMCODE.Trim()));
            }
            //按真实姓名查询
            if (!string.IsNullOrWhiteSpace(logEntity.REALNAME))
            {
                listWhere.Add(SYSTEM_LOG4NETEntity.FieldREALNAME + " = " + dbHelper.GetParameter("FieldREALNAME"));
                dbParameters.Add(new KeyValuePair<string, object>(dbHelper.GetParameter("FieldREALNAME"), logEntity.REALNAME.Trim()));
            }
            //按方法名称查询
            if (!string.IsNullOrWhiteSpace(logEntity.FUNCTIONNAME))
            {
                listWhere.Add(SYSTEM_LOG4NETEntity.FieldFUNCTIONNAME + " = " + dbHelper.GetParameter("FieldFUNCTIONNAME"));
                dbParameters.Add(new KeyValuePair<string, object>(dbHelper.GetParameter("FieldFUNCTIONNAME"), logEntity.FUNCTIONNAME.Trim()));
            }
            //查询条件
            if (listWhere.Count > 0)
            {
                conditions = string.Join(" and ", listWhere.ToArray());
            }
            string tableName = "SYSTEM_LOG4NET";
            string selectField = " LOG_ID,LOG_DATE,SYSTEMCODE,LOG_LEVEL,LOG_TITLE,IP,REALNAME ";
            int totalRows = 0;
            string orderBy = sort + " " + direction;
            try
            {
                dt = DbLogic.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pager.pageNo, pager.pageSize, conditions, dbHelper.MakeParameters(dbParameters), orderBy);
                pager.totalPages = totalRows;
                result.Add("status", "true");
                result.Add("message", "成功获取数据。");
                result.Add("pager.pageNo", pager.pageNo);
                result.Add("rows", dt);
                result.Add("pager.totalRows", pager.totalPages);
                result.Add("sort", sort);
                result.Add("direction", direction);
            }
            catch (Exception ex)
            {
                result.Add("status", "false");
                result.Add("message", "系统异常：" + ex.Message);
                result.Add("pager.pageNo", pager.pageNo);
                result.Add("rows", dt);
                result.Add("pager.totalRows", pager.totalPages);
                result.Add("sort", sort);
                result.Add("direction", direction);
                LogHelper.OracleWarn(OperateContext.Current.UserInfo, "获取系统运行日志异常", ex.Message, "GetSystemLog", typeof(SystemLogController), ex);
            }
            result.Add("span", (DateTime.Now - begin).TotalMilliseconds);
            return Json(result);

        }

dt = DbLogic.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pager.pageNo, pager.pageSize, conditions, dbHelper.MakeParameters(dbParameters), orderBy);

        /// <summary>
        /// 获取分页数据
        /// 增加最大输出数据限制
        /// </summary>
        /// <param name="dbHelper">数据库连接</param>
        /// <param name="tableName">数据来源表名</param>
        /// <param name="selectField">选择字段</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示多少条</param>
        /// <param name="conditions">查询条件</param>
        /// <param name="dbParameters">查询参数</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="currentIndex"></param>
        /// <param name="maxOutPut">最大记录输出数量</param>
        /// <returns>数据表</returns>
        public static DataTable GetDataTableByPage(IDbHelper dbHelper, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy, string currentIndex = null,int? maxOutPut=null)
        {
            string sqlStart = ((pageIndex - 1) * pageSize).ToString();
            string sqlEnd = (pageIndex * pageSize).ToString();
            if (currentIndex == null)
            {
                currentIndex = string.Empty;
            }
            if (!string.IsNullOrEmpty(conditions))
            {
                conditions = "WHERE " + conditions;
            }
            string sqlQuery = string.Empty;

            if (dbHelper.CurrentDbType == CurrentDbType.Oracle)
            {
                if (!string.IsNullOrWhiteSpace(orderBy))
                {
                    orderBy = " ORDER BY " + orderBy;
                }
                sqlQuery = string.Format("SELECT * FROM (SELECT ROWNUM RN, H.* FROM ((SELECT " + currentIndex + " " + selectField + " FROM {0} {1} {2} )H)) Z WHERE Z.RN <={3} AND Z.RN >{4} "
    , tableName, conditions, orderBy, sqlEnd, sqlStart);
                if (maxOutPut!=null)
                {
                    sqlQuery += " AND ROWNUM<= " + maxOutPut;
                }
            }
            else if (dbHelper.CurrentDbType == CurrentDbType.SqlServer)
            {
                sqlQuery = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS RowIndex, " + selectField + " FROM {1} {2}) AS PageTable WHERE RowIndex <={3} AND RowIndex >{4} "
                    , orderBy, tableName, conditions, sqlEnd, sqlStart);
            }
            else if (dbHelper.CurrentDbType == CurrentDbType.MySql
                || dbHelper.CurrentDbType == CurrentDbType.SQLite)
            {
                sqlQuery = string.Format("SELECT {0} FROM {1} {2} ORDER BY {3} LIMIT {4}, {5}", selectField, tableName, conditions, orderBy, sqlStart, pageSize);
            }

            var dt = new DataTable(tableName);
            if (dbParameters != null && dbParameters.Length > 0)
            {
                dt = dbHelper.Fill(sqlQuery, dbParameters);
            }
            else
            {
                dt = dbHelper.Fill(sqlQuery);
            }
            return dt;
        }