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;
}
}
}

 

转载于:https://www.cnblogs.com/livexy/archive/2010/07/09/1774618.html