oracle 通用分页存储过程 asp.net,将以前写的sql2分查找法通用分页存储过程算法 改成.net类实现_asp.net技巧...

using System;

namespace CountryPark.DAL

…{

/**//**//**

/// PageList 的摘要说明。

///

public sealed class PageList

…{

static PageList()

…{

}

/**//**//**

/// 分页查询数据记录总数获取

///

/// —-要显示的表或多个表的连接

/// —-主表的主键

/// —-查询条件,不需where

/// —-是否添加查询字段的 DISTINCT 默认0不添加/1添加

///

public static string getPageListCounts(string _tbName, string _ID, string _strCondition, int _Dist)

…{

//—存放取得查询结果总数的查询语句

//—对含有DISTINCT的查询进行SQL构造

//—对含有DISTINCT的总数查询进行SQL构造

string strTmp=””, SqlSelect=””, SqlCounts=””;

if (_Dist == 0)

…{

SqlSelect = “SELECT “;

SqlCounts = “COUNT(*)”;

}

else

…{

SqlSelect = “SELECT DISTINCT “;

SqlCounts = “COUNT(DISTINCT “+ _ID +”)”;

}

if (_strCondition == string.Empty)

…{

strTmp = SqlSelect +” @Counts=”+ SqlCounts +” FROM “+ _tbName;

}

else

…{

strTmp = SqlSelect +” @Counts=”+ SqlCounts +” FROM “+ ” WHERE (1=1) “+ _strCondition;

}

return strTmp;

}

/**//**//**

/// 获取分页数据查询SQL

///

/// —-要显示的表或多个表的连接

/// —-要显示的字段列表

/// —-每页显示的记录个数

/// —-要显示那一页的记录

/// —-查询结果分页后的总页数

/// —-查询到的记录数

/// —-排序字段列表或条件(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)–程序传参如： SortA Asc,SortB Desc,SortC )

/// —-排序方法，0为升序，1为降序

/// —-查询条件,不需where

/// —-主表的主键

/// —-是否添加查询字段的 DISTINCT 默认0不添加/1添加

///

public static string getPageListSql(string _tbName, string _fldName, int _PageSize, int _Page, out int _PageCount, int _Counts, string _fldSort, int _Sort, string _strCondition, string _ID, int _Dist)

…{

string strTmp=””; //—strTmp用于返回的SQL语句

string SqlSelect=””, strSortType=””, strFSortType=””;

if (_Dist == 0)

…{

SqlSelect = “SELECT “;

}

else

…{

SqlSelect = “SELECT DISTINCT “;

}

if (_Sort == 0)

…{

strFSortType = ” ASC”;

strSortType = ” DESC”;

}

else

…{

strFSortType = ” DESC”;

strSortType = ” ASC”;

}

//            —-取得查询结果总数量—–

int tmpCounts = 1;

if (_Counts != 0)

…{

tmpCounts = _Counts;

}

//          –取得分页总数

_PageCount = (tmpCounts + _PageSize – 1)/_PageSize;

//    /**//**当前页大于总页数 取最后一页**/

if (_Page > _PageCount)

…{

_Page = _PageCount;

}

if (_Page <= 0)

…{

_Page = 1;

}

//          –/*—–数据分页2分处理——-*/

int pageIndex = tmpCounts/_PageSize;

int lastCount = tmpCounts%_PageSize;

if (lastCount > 0)

…{

pageIndex = pageIndex + 1;

}

else

…{

lastCount = _PageSize;

}

if (_strCondition == string.Empty) // –没有设置显示条件

…{

if (pageIndex < 2 || _Page <= (pageIndex/2 + pageIndex%2))  //–前半部分数据处理

…{

if (_Page == 1)

…{

strTmp = SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +” ORDER BY “+ _fldSort +” “+ strFSortType;

}

else

…{

strTmp = SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +” WHERE “+ _ID +”

” ORDER BY “+ _fldSort +” “+ strFSortType +”) AS TBMinID) ORDER BY “+ _fldSort +” “+ strFSortType;

}

}

else

…{

_Page = pageIndex – _Page + 1; //后半部分数据处理

if (_Page <= 1) //–最后一页数据显示

…{

strTmp = SqlSelect +” * FROM (“+ SqlSelect +” TOP “+ lastCount +” “+ _fldName +” FROM “+ _tbName +” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TempTB”+ ” ORDER BY “+ _fldSort +” “+ strFSortType;

}

else

…{

strTmp = SqlSelect +” * FROM (“+ SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +

” WHERE “+ _ID +” >(SELECT MAX(“+ _ID +”) FROM(“+ SqlSelect +” TOP “+ (_PageSize*(_Page-2)+lastCount) +” “+ _ID +” FROM “+ _tbName +

” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TBMaxID) ORDER BY “+ _fldSort +” “+ strSortType +”) AS TempTB ORDER BY “+ _fldSort +” “+ strFSortType;

}

}

}

else // –有查询条件

…{

if (pageIndex < 2 || _Page <=(pageIndex/2 + pageIndex%2))//–前半部分数据处理

…{

if (_Page == 1)

…{

strTmp = SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +”WHERE 1=1 “+ _strCondition +” ORDER BY “+ _fldSort +” “+ strFSortType;

}

else

…{

strTmp = SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +

” WHERE “+ _ID +”

” WHERE 1=1 “+ _strCondition +” ORDER BY “+ _fldSort +” “+ strFSortType +”) AS TBMaxID) “+ _strCondition +

” ORDER BY “+ _fldSort +” “+ strFSortType;

}

}

else //–后半部分数据处理

…{

_Page = pageIndex-_Page+1;

if (_Page <= 1) //–最后一页数据显示

…{

strTmp = SqlSelect +” * FROM (“+ SqlSelect +” TOP “+ lastCount +” “+ _fldName +” FROM “+ _tbName +

” WHERE 1=1 “+ _strCondition +” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TempTB ORDER BY “+ _fldSort +” “+ strFSortType;

}

else

…{

strTmp = SqlSelect +” * FROM (“+ SqlSelect +” TOP “+ _PageSize +” “+ _fldName +” FROM “+ _tbName +

” WHERE “+ _ID +” >(SELECT MAX(“+ _ID +”) FROM(“+ SqlSelect +” TOP “+ (_PageSize*(_Page-2)+ lastCount) +” “+ _ID +” FROM “+ _tbName +

” WHERE 1=1 “+ _strCondition +” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TBMaxID) “+ _strCondition +

” ORDER BY “+ _fldSort +” “+ strSortType +”) AS TempTB ORDER BY “+ _fldSort +” “+ strFSortType;

}

}

}

return strTmp;

}

}

}

–以上代码是针对之前写的TOP MAX模式的分页存储过程修改

–以上分页算法对SQL SERVER 和 ACCESS同样有效

参见:http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.html

//调用函数例子

public IList getParkDataList(string key, int curPage, out int pageCount, int pageSize, int Counts)

…{

IList list = new ArrayList();

string SECLECT_FIELD = “T_Park.ParkID, T_Park.ParkTitle, T_Park.ParkLetter, T_ParkArea.AreaName, T_ParkType.ParkTypeName “;

string SECLECT_TABLE = “T_ParkType INNER JOIN (T_ParkArea INNER JOIN T_Park ON T_ParkArea.ParkAreaID = T_Park.ParkAreaID) ON T_ParkType.ParkTypeID = T_Park.ParkTypeID”;

string SECLECT_CONDITION = string.Empty;

if (key != string.Empty)

…{

SECLECT_CONDITION = ” AND T_Park.ParkTitle like %”+ key +”%”;

}

string SELECT_ID = “ParkID”;

string SELECT_FLDSORT = “ParkID”;

int SELECT_SORT = 1;

int SELECT_DIST = 0;

string SQL = PageList.getPageListSql(SECLECT_TABLE, SECLECT_FIELD, pageSize, curPage, out pageCount, Counts, SELECT_FLDSORT, SELECT_SORT, SECLECT_CONDITION, SELECT_ID, SELECT_DIST);

//string strCondition;

OleDb db = new OleDb();

ParkBE park;

using(OleDbDataReader dr = (OleDbDataReader)db.ExecuteReader(CommonFun.GetConnectionString(), CommandType.Text, SQL))

…{

while (dr.Read())

…{

park = new ParkBE();

park.ParkID = Convert.ToInt32(dr[0]);

park.ParkTitle = dr[1].ToString();

park.ParkLetter = dr[2].ToString();

park.ParkAreaName = dr[3].ToString();

park.ParkTypeName = dr[4].ToString();

list.Add(park);

}

}

return list;

}

http://blog.csdn.net/todaywlq/archive/2007/01/29/1497418.aspx