一个通用的分页类
- 作者：互联网
- 时间：2009-07-01 15:55:12
结合一个存储过程，将分页做成最简单，请看以下源码
此分页类所操作的存储过程#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 + ' 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)
GO
*****************************************************************************/
/**//****************************************************************************
*
* 用法
*
* ***************************************************************************/
/**//*
Dim ts As String = Re***st.Form.Item("txtDate")
If (ts = "" Or ts Is Nothing) Then
ts = Re***st.QueryString("txtDate")
End If
Dim ts2 As String = Re***st.Form.Item("txtDate2")
If (ts2 = "" Or ts2 Is Nothing) Then
ts2 = Re***st.QueryString("txtDate2")
End If
Dim ps As String = Re***st.Form.Item("pageIndex")
If (ps = "" Or ps Is Nothing) Then
ps = Re***st.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 = In***er.Parse(ps)
End If
Dim pager As Pager = New Pager
pa***.PageIndex = p
pa***.PageSize = 20
pa***.PageMode = Pa***ode.Str
pa***.WhereCondition = "TheDate between convert(datetime,'" + ts + "') and convert(datetime,'" + ts2 + "')"
'pa***.WhereCondition = " convert(char(10),TheDate,120)= '" + ts + "'"
pa***.TableName = "LoadCountlog"
pa***.SelectStr = "*"
pa***.Order = "ID"
pa***.OrderType = False
Dim dt As Sy***m.Data.DataTable = pa***.GetDatas(p)
my***aGrid.DataSource = dt
my***aGrid.DataBind()
Dim goUrl As String = "We***rm1.aspx?txtDate=" + ts + "&txtDate2=" + ts2
Me***bel3.Text = "共：" + pa***.PageCount.ToString + "页，" + pa***.RecordCount.ToString() + "条 " + pa***.OutPager(pager, goUrl, False) + ""
*/
#endregion
using System;
using Sy***m.Data;
using Sy***m.Data.SqlClient;
using Sy***m.Configuration;
using Sy***m.Collections;
using Sy***m.Text;
namespace solucky
{
/**////
/// 分页模式
///
public enum PageMode
{
/**////
/// 数字分页
///
Num =0,
/**////
/// 字符分页
///
Str =1
}
/**////
/// 分页类,能过存储过程进行分页,功能相当强大。
///
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 =P***Mode.Num;
private string sqlConnectionString = Co***gurationSettings.AppSettings["database"];
private string databaseOwner = "dbo";
数据连接#region 数据连接
/**////
/// 数据连接字符串
///
private string SqlConnectionString
{
get
{
return th***sqlConnectionString;
}
set
{
th***sqlConnectionString=value;
}
}
/**////
///获取连接实例
///
///
private SqlConnection GetSqlConnectionString()
{
try
{
return new SqlConnection(SqlConnectionString);
}
catch
{
throw new Exception("SQL Connection String is invalid.");
}
}
/**////
/// 数据对象所有者
///
private string DatabaseOwner
{
get
{
return th***databaseOwner;
}
set{
th***databaseOwner=value;
}
}
#endregion
public Pager()
{
//
// TODO: 在此处添加构造函数逻辑
//
//***m.Parse(tyo
}
public Pager(string connstr )
{
if (connstr!=null)
th***SqlConnectionString=connstr;
}
#region
/**////
/// 所要操作的存储过程名称,已有默认的分页存储过程
///
public string Procedure
{
get{
return th***procedure ;
}
set {
if (value==null || va***.Length <=0)
{
th***procedure="pager";
}
else
{
th***procedure=value;
}
}
}
/**////
/// 当前所要显示的页面数
///
public int PageIndex
{
get
{
return th***pageIndex;
}
set
{
th***pageIndex = value;
}
}
/**////
/// 总的页面数
///
public int PageCount
{
get
{
return th***pageCount;
}
set
{
th***pageCount = value;
}
}
/**////
/// 总行数
///
public int RecordCount
{
get
{
return th***recordCount;
}
set
{
th***recordCount = value;
}
}
/**////
/// 每页条数
///
public int PageSize
{
get
{
return th***pageSize;
}
set
{
th***pageSize = value;
}
}
/**////
/// 表名称
///
public string TableName
{
get
{
return tableName;
}
set
{
th***tableName ; = value;
}
}
/**////
/// 条件查询
///
public string WhereCondition
{
get
{
return whereCondition;
}
set
{
whereCondition = value;
}
}
/**////
/// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
///
public string SelectStr
{
get
{
return selectStr;
}
set
{
selectStr = value;
}
}
/**////
/// 排序的列
///
public string Order
{
get
{
return order;
}
set
{
order = value;
}
}
/**////
/// 排序类型 true:asc false:desc
///
public bool OrderType
{
get
{
return orderType;
}
set
{
orderType = value;
}
}
/**////
/// 分页模式
///
public PageMode PageMode
{
get
{
return th***pageMode;
}
set
{
th***pageMode = value;
}
}
/**////
/// 得到当前返回的数量
///
public int RowCount
{
get
{
return th***rowCount;
}
}
private string groupby;
public string Groupby
{
get
{
return th***groupby;
}
set
{
th***groupby = value;
}
}
#endregion
/**////
/// 分页查寻结果
///
public DataTable GetDatas(int pageIndex)
{
th***pageIndex = pageIndex;
Pager pager = this;
//***er.pageIndex ; = pageIndex;
DataTable returnTb = Pagination(ref pager).Tables[0];
th***rowCount = re***nTb.Rows.Count;
return returnTb;
}
/**////
/// 分页操作存储过程函数
///
///
///
private DataSet Pagination(ref Pager pager)
{
using ( SqlConnection myConnection = GetSqlConnectionString() )
{
SqlDataAdapter myCommand = new SqlDataAdapter(pa***.databaseOwner + "."+pa***.Procedure, myConnection);
my***mand.SelectCommand.CommandType ; = Co***ndType.StoredProcedure;
SqlParameter parameterPageIndex = new SqlParameter("@PageIndex", Sq***Type.Int);
pa***eterPageIndex.Value ; = pa***.PageIndex;
my***mand.SelectCommand.Parameters.Add(parameterPageIndex);
SqlParameter parameterPageSize = new SqlParameter("@PageSize", Sq***Type.Int);
pa***eterPageSize.Value ; = pa***.PageSize;
my***mand.SelectCommand.Parameters.Add(parameterPageSize);
SqlParameter parameterRecordCount = new SqlParameter("@RecordCount", Sq***Type.Int);
pa***eterRecordCount.Value ; = 0;
pa***eterRecordCount.Direction ; = Pa***eterDirection.InputOutput;
my***mand.SelectCommand.Parameters.Add(parameterRecordCount);
SqlParameter parameterPageCount = new SqlParameter("@PageCount", Sq***Type.Int);
pa***eterPageCount.Value ; = 0;
pa***eterPageCount.Direction ; = Pa***eterDirection.InputOutput;
my***mand.SelectCommand.Parameters.Add(parameterPageCount);
SqlParameter parameterWhereCondition = new SqlParameter("@WhereCondition", Sq***Type.NVarChar,500);
pa***eterWhereCondition.Value ; = pa***.WhereCondition;
my***mand.SelectCommand.Parameters.Add(parameterWhereCondition);
SqlParameter parameterTableName = new SqlParameter("@TableName", Sq***Type.NVarChar,500);
pa***eterTableName.Value ; = pa***.TableName;
my***mand.SelectCommand.Parameters.Add(parameterTableName);
SqlParameter parameterOrder = new SqlParameter("@Order", Sq***Type.NVarChar,500);
pa***eterOrder.Value ; = pa***.Order;
my***mand.SelectCommand.Parameters.Add(parameterOrder);
SqlParameter parameterSelectStr = new SqlParameter("@SelectStr", Sq***Type.NVarChar,500);
pa***eterSelectStr.Value ; = pa***.SelectStr;
my***mand.SelectCommand.Parameters.Add(parameterSelectStr);
SqlParameter parameterGroupby = new SqlParameter("@Groupby", Sq***Type.NVarChar, 100);
pa***eterGroupby.Value ; = pa***.Groupby;
my***mand.SelectCommand.Parameters.Add(parameterGroupby);
SqlParameter parameterOrderType = new SqlParameter("@OrderType", Sq***Type.Bit);
pa***eterOrderType.Value ; = pa***.OrderType==false?0:1;
my***mand.SelectCommand.Parameters.Add(parameterOrderType);
DataSet returnDS = new DataSet();
//SqlDataAdapter sqlDA = my***mand.crnew SqlDataAdapter(myCommand);
my***mand.Fill(returnDS);
pa***.PageCount ; = (int)pa***eterPageCount.Value;
pa***.RecordCount ; = (int)pa***eterRecordCount.Value;
return returnDS;
}
}
生成分页#region 生成分页
/**////
/// 生成分页格式
///
///
///
///
///
public string OutPager(Pager pager,string url,bool isBr)
{
StringBuilder returnOurWml;
if(isBr)
{
returnOurWml= new StringBuilder("["+ pa***.PageCount.ToString() + "页," + pa***.RecordCount.ToString() +"条]
");
}
else
{
returnOurWml = new StringBuilder();
}
if (pa***.PageMode == Pa***ode.Num)
{
//分页每行显示的数量
int pagersCount = 10;
int pagers = 0;
int startInt = 1;
int endInt = pa***.PageCount;
int i = 1;
string endStr = "";
if (pa***.PageCount>pagersCount)
{
//double k = ;
pagers = pa***.PageIndex / pagersCount;
if (pagers == 0)
{
pagers = 1;
}
else if((pa***.PageIndex % pagersCount)!=0)
{
pagers +=1;
}
endInt = pagers * pagersCount;
if (pa***.PageIndex <= endInt)
{
startInt = endInt +1 - pagersCount;
if (startInt <1)
{
startInt = 1;
}
}
//显示数量不足时pagersCount
if (endInt>=pa***.PageCount)
{
endInt = pa***.PageCount;
}
else
{
//if (pa***.PageIndex)
endStr = " endStr += url + "&pageIndex=" + (endInt + 1).ToString() + "" title='第"+ (endInt + 1).ToString()+"页'>";
endStr += ">>";
endStr += " ";
}
if (pagers > 1)
{
re***nOurWml.Append(" re***nOurWml.Append(url + "&pageIndex=" + (startInt - 1).ToString() + "" title='第"+ (startInt - 1).ToString()+"页'>");
re***nOurWml.Append("<<");
re***nOurWml.Append(" ");
}
}
for (i = startInt; i<=endInt;i++)
{
if (i!=pa***.PageIndex)
{
re***nOurWml.Append(" re***nOurWml.Append(url + "&pageIndex=" + i.***tring() + "" title='第"+ i.***tring()+"页'>");
re***nOurWml.Append("["+i.***tring() + "]");
re***nOurWml.Append(" ");
}
else
{
re***nOurWml.Append(""+ i.***tring() + "");
}
}
re***nOurWml.Append(endStr);
return re***nOurWml.Append("
").ToString();
}
else
{
if ( pa***.PageIndex > 1)
{
re***nOurWml.Append(" re***nOurWml.Append(url + "&pageIndex=" + (pa***.PageIndex -1).ToString() + "">");
re***nOurWml.Append("上一页");
re***nOurWml.Append(" ");
}
if (pa***.PageIndex < pa***.PageCount)
{
re***nOurWml.Append(pa***.PageIndex.ToString());
re***nOurWml.Append(" re***nOurWml.Append(url + "&pageIndex=" + (pa***.PageIndex +1).ToString() + "">");
re***nOurWml.Append("下一页");
re***nOurWml.Append(" ");
}
return re***nOurWml.Append("
").ToString();
}
}
#endregion
}
}
http://www.cnblogs.com/solucky/archive/2006/09/20/509741.html
上一篇： 在asp.net页面中使用异步读取
下一篇： ASP.NET 2.0“插件”说
相关阅读
最新文章
- 打造通用ASP.NET数据分页控件 2009-07-01
- 一个ASP.NET中使用的MessageBox类 2009-06-30
- 最简单的asp.net分页 2009-07-01
- Asp.net url分页的用户控件 2009-06-30
- ASP.NET组件DataGrid的分页实用方法 2009-07-01
- ASP通用模板类 2009-06-25
- 关于asp.net HttpUtility.UrlDecode解码问题 2013-03-21
- ASP.NET 网站中的共享代码文件夹 2011-04-26
- asp.net 用户控件和自定义控件总结 2011-04-26
- asp.net 跑马灯 怎样从文件夹里读取图片,怎样实现？ 2010-07-12
- Asp.Net 4.0 SEO增强之 UrlRouting 2010-05-26
- ASP.NET 4.0开发更加简便 2010-02-21