SQL分页ROW_NUMBER() OVER/TOP MAX/TOP MIN存贮过程调用方法

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Linq;


namespace Pub.Class {

    public class PagerProc {

        private static int cacheSeconds = 1440;

        public static IList<TResult> ToList<TResult>(int pageIndex,int pageSize,string tableName,string pk,string fieldList,string sortList, bool isDescSort,string where,bool isDist,out int totalRecords, bool useCache) where TResult : class,new() {
            if (string.IsNullOrEmpty(fieldList)) fieldList = "*";
            totalRecords = 0; IList<TResult> list = null;
            string cacheNameKey = string.Empty; string cacheRecordsKey = string.Empty; 
            if (useCache) {
                string cacheName = "{2}Cache_SelectPageList_{0}_{1}_{3}_{4}_{5}_{6}_{7}_{8}";
                string cacheRecords = "{2}Cache_RecordsSelectPageList_{0}_{1}_{3}_{4}_{5}_{6}_{7}_{8}";
                cacheNameKey = string.Format(cacheName, pageIndex, pageSize, tableName, pk, fieldList, sortList, isDescSort, where, isDist);
                cacheRecordsKey = string.Format(cacheRecords, pageIndex, pageSize, tableName, pk, fieldList, sortList, isDescSort, where, isDist);
                list = (IList<TResult>)Cache2.Get(cacheNameKey);
                if (list != null) { totalRecords = (int)Cache2.Get(cacheRecordsKey); return list; }
            }

            DbParameter[] dbParams = {
                Data.MakeInParam("@page", (DbType)SqlDbType.Int, 4, pageIndex),
                Data.MakeInParam("@pageSize", (DbType)SqlDbType.Int, 4, pageSize),
                Data.MakeInParam("@tblName", (DbType)SqlDbType.NVarChar, 2000, tableName),
                Data.MakeInParam("@ID", (DbType)SqlDbType.NVarChar, 150, pk),
                Data.MakeInParam("@fldName", (DbType)SqlDbType.NVarChar, 500, fieldList),
                Data.MakeInParam("@fldSort", (DbType)SqlDbType.NVarChar, 200, sortList),
                Data.MakeInParam("@Sort", (DbType)SqlDbType.Bit, 1, isDescSort),
                Data.MakeInParam("@strCondition", (DbType)SqlDbType.NVarChar, 1000, where),
                Data.MakeInParam("@Dist", (DbType)SqlDbType.Bit, 1, isDist),
                Data.MakeOutParam("@Counts", (DbType)SqlDbType.Int, totalRecords),
            };
            list = Data.GetDbDataReader("getPagerTopMAX", dbParams).ToList<TResult>();
            if (list == null) return null;
            totalRecords = (int)dbParams[9].Value;

            if (useCache) {
                Cache2.Insert(cacheNameKey, list, cacheSeconds);
                Cache2.Insert(cacheRecordsKey, totalRecords, cacheSeconds);
            }
            return list;
        }

        public static IList<TResult> ToList<TResult>(int pageIndex,int pageSize,string tableName,string pk,string fieldList,string where,out int totalRecords, bool useCache) where TResult : class,new() {
            return ToList<TResult>(pageIndex, pageSize, tableName, pk, fieldList, pk, true, where, false, out totalRecords, useCache);
        }

        public static IList<TResult> ToList<TResult>(int pageIndex,int pageSize,string tableName,string pk,string fieldList,string where,out int totalRecords) where TResult : class,new() {
            return ToList<TResult>(pageIndex, pageSize, tableName, pk, fieldList, where, out totalRecords, false);
        }

        public static IList<TResult> ToList<TResult>(int pageIndex,int pageSize,string tableName, string fieldList,string where,string groupBy,string orderBy,out int totalRecords, bool useCache) where TResult : class,new() {
            if (string.IsNullOrEmpty(fieldList)) fieldList = "*";
            totalRecords = 0; IList<TResult> list = null;
            string cacheNameKey = string.Empty; string cacheRecordsKey = string.Empty; 
            if (useCache) {
                string cacheName = "{2}Cache_SelectPageList_{0}_{1}_{3}_{4}_{5}_{6}";
                string cacheRecords = "{2}Cache_RecordsSelectPageList_{0}_{1}_{3}_{4}_{5}_{6}";
                cacheNameKey = string.Format(cacheName, pageIndex, pageSize, tableName, fieldList, where, groupBy, orderBy);
                cacheRecordsKey = string.Format(cacheRecords, pageIndex, pageSize, tableName, fieldList, where, groupBy, orderBy);
                list = (IList<TResult>)Cache2.Get(cacheNameKey);
                if (list != null) { totalRecords = (int)Cache2.Get(cacheRecordsKey); return list; }
            }

            DbParameter[] dbParams = {
                Data.MakeInParam("@PageIndex", (DbType)SqlDbType.Int, 4, pageIndex),
                Data.MakeInParam("@PageSize", (DbType)SqlDbType.Int, 4, pageSize),
                Data.MakeInParam("@Tables", (DbType)SqlDbType.NVarChar, 1000, tableName),
                Data.MakeInParam("@Fields", (DbType)SqlDbType.NVarChar, 2000, fieldList),
                Data.MakeInParam("@Where", (DbType)SqlDbType.NVarChar, 2000, where),
                Data.MakeInParam("@GroupBy", (DbType)SqlDbType.NVarChar, 2000, groupBy),
                Data.MakeInParam("@OrderBy", (DbType)SqlDbType.NVarChar, 1000, orderBy),
                Data.MakeOutParam("@ReturnCount", (DbType)SqlDbType.Int, totalRecords),
            };
            list = Data.GetDbDataReader("getPagerROWOVER", dbParams).ToList<TResult>();
            if (list == null) return null;
            totalRecords = (int)dbParams[7].Value;

            if (useCache) {
                Cache2.Insert(cacheNameKey, list, cacheSeconds);
                Cache2.Insert(cacheRecordsKey, totalRecords, cacheSeconds);
            }
            return list;
        }
    }
}