一个通用的分页类

结合一个存储过程，将分页做成最简单，请看以下源码

此分页类所操作的存储过程#region 此分页类所操作的存储过程

/**//*********************************************************

* 功能强大，配合以下这个存储过程

* *******************************************************/

/**//*

-- Pager 1,10,0,0, 'EmployeeID>2 and EmployeeID<5 ' , 'Employees','*','LastName',0

CREATE PROCEDURE Pager

@PageIndex int,--索引页 1

@PageSize int,--每页数量2

@RecordCount int out,--总行数3

@PageCount int out,--总页数4

@WhereCondition Nvarchar(1000),--查询条件5

@TableName nvarchar(500),--查询表名6

@SelectStr nvarchar(500) = '*',--查询的列7

@Order nvarchar(500),--排序的列8

@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 9

@Groupby NVarChar(100) = ''

declare @strSQL nvarchar(2000) -- 主语句

declare @strTmp nvarchar(1000) -- 临时变量

declare @strOrder nvarchar(1000) -- 排序类型

if @OrderType != 0

begin

set @strTmp = '<(select min'

set @strOrder = ' order by ' + @Order +' desc'

end

else

begin

set @strTmp = '>(select max'

set @strOrder = ' order by ' + @Order +' asc'

end

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '

+ @TableName + ' where ' + @Order + '' + @strTmp + '(['

+ @Order + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

+ @Order + '] from ' + @TableName + '' + @strOrder + ') as tblTmp)'

+ @Groupby + @strOrder

if @WhereCondition != ''

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '

+ @TableName + ' where ' + @Order + '' + @strTmp + '(['

+ @Order + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

+ @Order + '] from ' + @TableName + ' where (' + @WhereCondition + ') '

+ @strOrder + ') as tblTmp) and (' + @WhereCondition + ') ' + @Groupby + @strOrder

if @PageIndex = 1

begin

set @strTmp = ''

if @WhereCondition != ''

set @strTmp = ' where (' + @WhereCondition + ')'

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '

+ @TableName + '' + @strTmp + ' ' + @Groupby + @strOrder

end

exec (@strSQL)

--print @strSQL

IF @WhereCondition <>''

Begin

SET @strTmp = 'SELECT -1 FROM ' + @TableName + ' Where ' + (@WhereCondition)

End

ELSE

Begin

SET @strTmp = 'SELECT -1 FROM ' + @TableName

End

EXEC SP_EXECUTESQL @strTmp

SET @RecordCount = @@RowCount

-- 获取总页数

-- "CEILING"函数:取得不小于某数的最小整数

SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

*****************************************************************************/

/**//****************************************************************************

* 用法

* ***************************************************************************/

/**//*

Dim ts As String = Request.Form.Item("txtDate")

If (ts = "" Or ts Is Nothing) Then

ts = Request.QueryString("txtDate")

End If

Dim ts2 As String = Request.Form.Item("txtDate2")

If (ts2 = "" Or ts2 Is Nothing) Then

ts2 = Request.QueryString("txtDate2")

End If

Dim ps As String = Request.Form.Item("pageIndex")

If (ps = "" Or ps Is Nothing) Then

ps = Request.QueryString("pageIndex")

End If

Dim t As Integer = 2

Dim p As Integer = 1

If ts Is Nothing Then

ts = ""

End If

If ps Is Nothing Then

ps = ""

End If

If Not (ps = "") Then

p = Integer.Parse(ps)

End If

Dim pager As Pager = New Pager

pager.PageIndex = p

pager.PageSize = 20

pager.PageMode = PageMode.Str

pager.WhereCondition = "TheDate between convert(datetime,'" + ts + "') and convert(datetime,'" + ts2 + "')"

'pager.WhereCondition = " convert(char(10),TheDate,120)= '" + ts + "'"

pager.TableName = "LoadCountlog"

pager.SelectStr = "*"

pager.Order = "ID"

pager.OrderType = False

Dim dt As System.Data.DataTable = pager.GetDatas(p)

myDataGrid.DataSource = dt

myDataGrid.DataBind()

Dim goUrl As String = "WebForm1.aspx?txtDate=" + ts + "&txtDate2=" + ts2

Me.Label3.Text = "共：" + pager.PageCount.ToString + "页，" + pager.RecordCount.ToString() + "条 " + pager.OutPager(pager, goUrl, False) + ""

#endregion

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Text;

namespace solucky

{

/**//// <summary>

/// 分页模式

/// </summary>

public enum PageMode

{

/**//// <summary>

/// 数字分页

/// </summary>

Num =0,

/**//// <summary>

/// 字符分页

/// </summary>

Str =1

}

/**//// <summary>

/// 分页类,能过存储过程进行分页,功能相当强大。

/// </summary>

public class Pager

{

private int pageIndex = 0;

private int recordCount = 0;

private int pageSize = 20;

private int pageCount = 0;

private int rowCount = 0;

private string tableName = "";

private string whereCondition = "1=1";

private string selectStr = "*";

private string order = "";

private string procedure ="pager";

private bool orderType = true;

private PageMode pageMode =PageMode.Num;

private string sqlConnectionString = ConfigurationSettings.AppSettings["database"];

private string databaseOwner = "dbo";

数据连接#region 数据连接

/**//// <summary>

/// 数据连接字符串

/// </summary>

private string SqlConnectionString

{

get

{

return this.sqlConnectionString;

}

set

{

this.sqlConnectionString=value;

}

/**//// <summary>

///获取连接实例

/// </summary>

/// <returns></returns>

private SqlConnection GetSqlConnectionString()

{

try

{

return new SqlConnection(SqlConnectionString);

}

catch

{

throw new Exception("SQL Connection String is invalid.");

}

/**//// <summary>

/// 数据对象所有者

/// </summary>

private string DatabaseOwner

{

get

{

return this.databaseOwner;

}

set{

this.databaseOwner=value;

}

#endregion

public Pager()

{

// TODO: 在此处添加构造函数逻辑

//Enum.Parse(tyo

}

public Pager(string connstr )

{

if (connstr!=null)

this.SqlConnectionString=connstr;

}

#region

/**//// <summary>

/// 所要操作的存储过程名称,已有默认的分页存储过程

/// </summary>

public string Procedure

{

get{

return this.procedure ;

}

set {

if (value==null || value.Length <=0)

{

this.procedure="pager";

}

else

{

this.procedure=value;

}

/**//// <summary>

/// 当前所要显示的页面数

/// </summary>

public int PageIndex

{

get

{

return this.pageIndex;

}

set

{

this.pageIndex = value;

}

/**//// <summary>

/// 总的页面数

/// </summary>

public int PageCount

{

get

{

return this.pageCount;

}

set

{

this.pageCount = value;

}

/**//// <summary>

/// 总行数

/// </summary>

public int RecordCount

{

get

{

return this.recordCount;

}

set

{

this.recordCount = value;

}

/**//// <summary>

/// 每页条数

/// </summary>

public int PageSize

{

get

{

return this.pageSize;

}

set

{

this.pageSize = value;

}

/**//// <summary>

/// 表名称

/// </summary>

public string TableName

{

get

{

return tableName;

}

set

{

this.tableName = value;

}

/**//// <summary>

/// 条件查询

/// </summary>

public string WhereCondition

{

get

{

return whereCondition;

}

set

{

whereCondition = value;

}

/**//// <summary>

/// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号

/// </summary>

public string SelectStr

{

get

{

return selectStr;

}

set

{

selectStr = value;

}

/**//// <summary>

/// 排序的列

/// </summary>

public string Order

{

get

{

return order;

}

set

{

order = value;

}

/**//// <summary>

/// 排序类型 true:asc false:desc

/// </summary>

public bool OrderType

{

get

{

return orderType;

}

set

{

orderType = value;

}

/**//// <summary>

/// 分页模式

/// </summary>

public PageMode PageMode

{

get

{

return this.pageMode;

}

set

{

this.pageMode = value;

}

/**//// <summary>

/// 得到当前返回的数量

/// </summary>

public int RowCount

{

get

{

return this.rowCount;

}

private string groupby;

public string Groupby

{

get

{

return this.groupby;

}

set

{

this.groupby = value;

}

#endregion

/**//// <summary>

/// 分页查寻结果

/// </summary>

public DataTable GetDatas(int pageIndex)

{

this.pageIndex = pageIndex;

Pager pager = this;

//pager.pageIndex = pageIndex;

DataTable returnTb = Pagination(ref pager).Tables[0];

this.rowCount = returnTb.Rows.Count;

return returnTb;

}

/**//// <summary>

/// 分页操作存储过程函数

/// </summary>

/// <param name="pager"></param>

/// <returns></returns>

private DataSet Pagination(ref Pager pager)

{

using ( SqlConnection myConnection = GetSqlConnectionString() )

{

SqlDataAdapter myCommand = new SqlDataAdapter(pager.databaseOwner + "."+pager.Procedure, myConnection);

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parameterPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);

parameterPageIndex.Value = pager.PageIndex;

myCommand.SelectCommand.Parameters.Add(parameterPageIndex);

SqlParameter parameterPageSize = new SqlParameter("@PageSize", SqlDbType.Int);

parameterPageSize.Value = pager.PageSize;

myCommand.SelectCommand.Parameters.Add(parameterPageSize);

SqlParameter parameterRecordCount = new SqlParameter("@RecordCount", SqlDbType.Int);

parameterRecordCount.Value = 0;

parameterRecordCount.Direction = ParameterDirection.InputOutput;

myCommand.SelectCommand.Parameters.Add(parameterRecordCount);

SqlParameter parameterPageCount = new SqlParameter("@PageCount", SqlDbType.Int);

parameterPageCount.Value = 0;

parameterPageCount.Direction = ParameterDirection.InputOutput;

myCommand.SelectCommand.Parameters.Add(parameterPageCount);

SqlParameter parameterWhereCondition = new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500);

parameterWhereCondition.Value = pager.WhereCondition;

myCommand.SelectCommand.Parameters.Add(parameterWhereCondition);

SqlParameter parameterTableName = new SqlParameter("@TableName", SqlDbType.NVarChar,500);

parameterTableName.Value = pager.TableName;

myCommand.SelectCommand.Parameters.Add(parameterTableName);

SqlParameter parameterOrder = new SqlParameter("@Order", SqlDbType.NVarChar,500);

parameterOrder.Value = pager.Order;

myCommand.SelectCommand.Parameters.Add(parameterOrder);

SqlParameter parameterSelectStr = new SqlParameter("@SelectStr", SqlDbType.NVarChar,500);

parameterSelectStr.Value = pager.SelectStr;

myCommand.SelectCommand.Parameters.Add(parameterSelectStr);

SqlParameter parameterGroupby = new SqlParameter("@Groupby", SqlDbType.NVarChar, 100);

parameterGroupby.Value = pager.Groupby;

myCommand.SelectCommand.Parameters.Add(parameterGroupby);

SqlParameter parameterOrderType = new SqlParameter("@OrderType", SqlDbType.Bit);

parameterOrderType.Value = pager.OrderType==false?0:1;

myCommand.SelectCommand.Parameters.Add(parameterOrderType);

DataSet returnDS = new DataSet();

//SqlDataAdapter sqlDA = myCommand.crnew SqlDataAdapter(myCommand);

myCommand.Fill(returnDS);

pager.PageCount = (int)parameterPageCount.Value;

pager.RecordCount = (int)parameterRecordCount.Value;

return returnDS;

}

生成分页#region 生成分页

/**//// <summary>

/// 生成分页格式

/// </summary>

/// <param name="pager"></param>

/// <param name="url"></param>

/// <param name="isBr"></param>

/// <returns></returns>

public string OutPager(Pager pager,string url,bool isBr)

{

StringBuilder returnOurWml;

if(isBr)

{

returnOurWml= new StringBuilder("["+ pager.PageCount.ToString() + "页," + pager.RecordCount.ToString() +"条] ");

}

else

{

returnOurWml = new StringBuilder();

}

if (pager.PageMode == PageMode.Num)

{

//分页每行显示的数量

int pagersCount = 10;

int pagers = 0;

int startInt = 1;

int endInt = pager.PageCount;

int i = 1;

string endStr = "";

if (pager.PageCount>pagersCount)

{

//double k = ;

pagers = pager.PageIndex / pagersCount;

if (pagers == 0)

{

pagers = 1;

}

else if((pager.PageIndex % pagersCount)!=0)

{

pagers +=1;

}

endInt = pagers * pagersCount;

if (pager.PageIndex <= endInt)

{

startInt = endInt +1 - pagersCount;

if (startInt <1)

{

startInt = 1;

}

//显示数量不足时pagersCount

if (endInt>=pager.PageCount)

{

endInt = pager.PageCount;

}

else

{

//if (pager.PageIndex)

endStr = " <a href=\"";

endStr += url + "&pageIndex=" + (endInt + 1).ToString() + "\" title='第"+ (endInt + 1).ToString()+"页'>";

endStr += ">>";

endStr += "</a> ";

}

if (pagers > 1)

{

returnOurWml.Append(" <a href=\"");

returnOurWml.Append(url + "&pageIndex=" + (startInt - 1).ToString() + "\" title='第"+ (startInt - 1).ToString()+"页'>");

returnOurWml.Append("<<");

returnOurWml.Append("</a> ");

}

for (i = startInt; i<=endInt;i++)

{

if (i!=pager.PageIndex)

{

returnOurWml.Append(" <a href=\"");

returnOurWml.Append(url + "&pageIndex=" + i.ToString() + "\" title='第"+ i.ToString()+"页'>");

returnOurWml.Append("["+i.ToString() + "]");

returnOurWml.Append("</a> ");

}

else

{

returnOurWml.Append(""+ i.ToString() + "");

}

returnOurWml.Append(endStr);

return returnOurWml.Append(" ").ToString();

}

else

{

if ( pager.PageIndex > 1)

{

returnOurWml.Append(" <a href=\"");

returnOurWml.Append(url + "&pageIndex=" + (pager.PageIndex -1).ToString() + "\">");

returnOurWml.Append("上一页");

returnOurWml.Append("</a> ");

}

if (pager.PageIndex < pager.PageCount)

{

returnOurWml.Append(pager.PageIndex.ToString());

returnOurWml.Append(" <a href=\"");

returnOurWml.Append(url + "&pageIndex=" + (pager.PageIndex +1).ToString() + "\">");

returnOurWml.Append("下一页");

returnOurWml.Append("</a> ");

}

return returnOurWml.Append(" ").ToString();

}

#endregion

}

http://www.cnblogs.com/solucky/archive/2006/09/20/509741.html