一個通用的分頁類

結合一個儲存過程，將分頁做成最簡單，請看以下原始碼

此分頁類別所操作的預存程序#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) = ''
AS

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 + ' 其中 ' + @Order + '' + @strTmp + '(['
+ @Order + ']) 從 (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @Order + '] 從 ' + @TableName + '' + @strOrder + ') as tblTmp)'
+ @Groupby + @strOrder

if @WhereCondition != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '
+ @TableName + ' 其中 ' + @Order + '' + @strTmp + '(['
+ @Order + ']) 從 (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @Order + '] 從 ' + @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)
GO
************************************************** ***************************/
/**//********************************************* *******************************
*
* 用法
*
* ************************************************* **************************/
/**//*
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() + "條<strong>" + pager.OutPager(pager, goUrl, False) + "< /strong>"
*/
#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() +"條]<br/>");
}
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 + "&amp;pageIndex=" + (endInt + 1).ToString() + "" title='第"+ (endInt + 1).ToString()+"頁'>";
endStr += "&gt;&gt;";
endStr += "</a> ";
}

if (pagers > 1)
{
returnOurWml.Append(" <a href="");
returnOurWml.Append(url + "&amp;pageIndex=" + (startInt - 1).ToString() + "" title='第"+ (startInt - 1).ToString()+"頁'>");
returnOurWml.Append("&lt;&lt;");
returnOurWml.Append("</a> ");
}
}

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

if (i!=pager.PageIndex)
{
returnOurWml.Append(" <a href="");
returnOurWml.Append(url + "&amp;pageIndex=" + i.ToString() + "" title='第"+ i.ToString()+"頁'>");
returnOurWml.Append("["+i.ToString() + "]");
returnOurWml.Append("</a> ");
}
else
{
returnOurWml.Append("<u>"+ i.ToString() + "</u>");
}
}

returnOurWml.Append(endStr);

return returnOurWml.Append("<br/>").ToString();
}
else
{
if ( pager.PageIndex > 1)
{
returnOurWml.Append(" <a href="");
returnOurWml.Append(url + "&amp;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 + "&amp;pageIndex=" + (pager.PageIndex +1).ToString() + "">");
returnOurWml.Append("下一頁");
returnOurWml.Append("</a> ");
}
return returnOurWml.Append("<br/>").ToString();
}
}

#endregion
}
}

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