一、定义分页类及存储过程：
using System;
using System.Collections.Generic;
using System.Text;
/**//// <summary>
/// .Net通用分页类(存储过程分页版,可以选择页码的显示样式,且有中英选择)
/// 作者:启程 www.letwego.cn
/// 可用于任意用途,请保留作者信息，谢谢！
/// </summary>
namespace letwego.cn
{
public class PageStore
{
初始构造#region 初始构造
//PageStore()
//{
//}
#endregion
字段#region 字段
/**//// <summary>
/// 每页记录数
/// </summary>
private int _PageSize = 20;
/**//// <summary>
/// 需要获取第几页的数据,从 1 开始
/// </summary>
private int _PageIndex = 1;
/**//// <summary>
/// 总页数
/// </summary>
private int _PageCounts = 0;
/**//// <summary>
/// 总记录数
/// </summary>
private int _Counts = 0;
/**//// <summary>
/// 首页 显示样式
/// </summary>
private string _FirstStr = "";
/**//// <summary>
/// 上一页 显示样式
/// </summary>
private string _PrevStr = "";
/**//// <summary>
/// 下一页 显示样式
/// </summary>
private string _NextStr = "";
/**//// <summary>
/// 尾页 显示样式
/// </summary>
private string _LastStr = "";
/**//// <summary>
/// 跳转 的url链接
/// </summary>
private string _TurnUrlStr = "";
/**//// <summary>
/// 跳转的url链接的参数前面不要加问号和与号
/// </summary>
private string _Options = "";
private string strCountww = ""; //共N条信息
private string strPageww = ""; //第N页/共N页
private string strTurnww; //跳转控件
#endregion
属性#region 属性
/**//// <summary>
/// 每页记录数
/// </summary>
public int PageSize
{
get { return _PageSize; }
set { _PageSize = value; }
}
/**//// <summary>
/// 需要获取第几页的数据,从 1 开始
/// </summary>
public int PageIndex
{
get { return _PageIndex; }
set { _PageIndex = value; }
}
/**//// <summary>
/// 总页数
/// </summary>
public int PageCounts
{
get { return _PageCounts; }
}
/**//// <summary>
/// 总记录数
/// </summary>
public int Counts
{
get { return _Counts; }
set { _Counts = value; }
}
/**//// <summary>
/// 首页 显示样式
/// </summary>
public string FirstStr
{
get { return _FirstStr; }
set { _FirstStr = value; }
}
/**//// <summary>
/// 上一页 显示样式
/// </summary>
public string PrevStr
{
get { return _PrevStr; }
set { _PrevStr = value; }
}
/**//// <summary>
/// 下一页 显示样式
/// </summary>
public string NextStr
{
get { return _NextStr; }
set { _NextStr = value; }
}
/**//// <summary>
/// 尾页 显示样式
/// </summary>
public string LastStr
{
get { return _LastStr; }
set { _LastStr = value; }
}
/**//// <summary>
/// 跳转 的url链接
/// </summary>
public string TurnUrlStr
{
get { return _TurnUrlStr; }
set { _TurnUrlStr = value; }
}
/**//// <summary>
/// 跳转的url链接的参数前面不要加问号和与号
/// </summary>
public string Options
{
get { return _Options; }
set { _Options = value; }
}
#endregion
返回分页后的页码显示#region 返回分页后的页码显示
/**//// <summary>
/// 返回分页后的页码显示
/// </summary>
/// <param name="bolCount">是否显示 共N条信息</param>
/// <param name="bolPage">是否显示 第N页/共N页</param>
/// <param name="bolFirst">是否显示 首页</param>
/// <param name="bolLast">是否显示 尾页</param>
/// <param name="bolTurn">是否显示 跳转控件</param>
/// <param name="IsChinese">是否 用中文显示</param>
/// <param name="intStyle">样式选择</param>
/// <param name="intShowNum">每页显示多少个数字</param>
/// <returns>返回分页后的页码显示</returns>
public string GetShowPageStr(bool bolCount, bool bolPage, bool bolFirst, bool bolLast, bool bolTurn, bool IsChinese, int intStyle, int intShowNum)
{
string strPageShowww = "";
string _FirstStr2 = "";
string _PrevStr2 = "";
string _NextStr2 = "";
string _LastStr2 = "";
公共处理#region 公共处理
//总页数
_PageCounts = (_Counts + _PageSize - 1) / _PageSize;
//超出最小页码
if (_PageIndex < 1)
{
_PageIndex = 1;
}
//超出最大页码
if (_PageIndex > _PageCounts)
{
_PageIndex = _PageCounts;
}
if (IsChinese)//中文分页
{
//跳转
strTurnww = "<input value='" + _PageIndex.ToString() + "' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='跳转' onclick=""javascript:window.location.href='" + _TurnUrlStr + "?Page=' + document.getElementById('txtPageGo').value + '" + "&" + Options + "'"">";
//共N条信息
strCountww = "共 " + _Counts.ToString() + " 条信息";
//第N页/共N页
strPageww = "第" + _PageIndex.ToString() + "页/共" + _PageCounts.ToString() + "页";
//处理页码显示样式
if (intStyle == 1)
{
if (_FirstStr == "")
{
_FirstStr = "首页";
}
if (_PrevStr == "")
{
_PrevStr = "上一页";
}
if (_NextStr == "")
{
_NextStr = "下一页";
}
if (_LastStr == "")
{
_LastStr = "尾页";
}
}
else
{
if (_FirstStr == "")
{
_FirstStr = " << ";
}
if (_PrevStr == "")
{
_PrevStr = " < ";
}
if (_NextStr == "")
{
_NextStr = " > ";
}
if (_LastStr == "")
{
_LastStr = " >> ";
}
}
}
else//英文文分页
{
//跳转
strTurnww = "<input value='" + _PageIndex.ToString() + "' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='Goto' onclick=""javascript:window.location.href='" + _TurnUrlStr + "?Page=' + document.getElementById('txtPageGo').value + '" + "&" + Options + "'"">";
//共N条信息
strCountww = "Total " + _Counts.ToString() + " Infos";
//第N页/共N页
strPageww = " " + _PageIndex.ToString() + "/" + _PageCounts.ToString() + " ";
//处理页码显示样式
if (intStyle == 1)
{
if (_FirstStr == "")
{
_FirstStr = " First ";
}
if (_PrevStr == "")
{
_PrevStr = " Previous ";
}
if (_NextStr == "")
{
_NextStr = " Next ";
}
if (_LastStr == "")
{
_LastStr = " Last ";
}
}
else
{
if (_FirstStr == "")
{
_FirstStr = " << ";
}
if (_PrevStr == "")
{
_PrevStr = " < ";
}
if (_NextStr == "")
{
_NextStr = " > ";
}
if (_LastStr == "")
{
_LastStr = " >> ";
}
}
}
#endregion
//没有记录
if (_Counts <= 0)
{
strPageShowww = strCountww;
}
//有记录
else
{
//只有一页
if (_PageCounts <= 1)
{
strPageShowww = strCountww + " " + strPageww;
}
//不止一页
else
{
页码链接处理#region 页码链接处理
//第一页
if (_PageIndex == 1)
{
_FirstStr2 = _FirstStr;
_PrevStr2 = _PrevStr;
}
else
{
_FirstStr2 = "<a href=""" + _TurnUrlStr + "?Page=1" + "&" + _Options + """>" + _FirstStr + "</a>";
_PrevStr2 = "<a href=""" + _TurnUrlStr + "?Page=" + Convert.ToString(_PageIndex - 1) + "&" + _Options + """>" + _PrevStr + "</a>";
}
//最后一页
if (_PageIndex == _PageCounts)
{
_NextStr2 = _NextStr;
_LastStr2 = _LastStr;
}
else
{
_NextStr2 = "<a href=""" + _TurnUrlStr + "?Page=" + Convert.ToString(_PageIndex + 1) + "&" + _Options + """>" + _NextStr + "</a>";
_LastStr2 = "<a href=""" + _TurnUrlStr + "?Page=" + _PageCounts + "&" + _Options + """>" + _LastStr + "</a>";
}
//----处理显示页码-----------
if (bolCount == true)//共N条信息
{
strPageShowww = strPageShowww + " " + strCountww;
}
if (bolPage == true)//第N页/共N页
{
strPageShowww = strPageShowww + " " + strPageww;
}
if (bolFirst == true) //首页
{
strPageShowww = strPageShowww + " " + _FirstStr2;
}
strPageShowww = strPageShowww + "{0}";//上一页
strPageShowww = strPageShowww + "{1}{2}";//下一页
if (bolLast == true)//尾页
{
strPageShowww = strPageShowww + " " + _LastStr2;
}
if (bolTurn == true)//跳转控件
{
strPageShowww = strPageShowww + " " + strTurnww;
}
#endregion
样式一: 共X条信息 第N页/共M页 首页 上一页 下一页 尾页 跳转#region 样式一: 共X条信息 第N页/共M页 首页 上一页 下一页 尾页 跳转
if (intStyle == 1)
{
strPageShowww = strPageShowww.Replace("{0}", " " + _PrevStr2);//上一页
strPageShowww = strPageShowww.Replace("{1}", " " + _NextStr2);//下一页
strPageShowww = strPageShowww.Replace("{2}", "");//
}
#endregion
样式二: 共X条信息 第N页/共M页 首页 1 2 3 尾页 跳转#region 样式二: 共X条信息 第N页/共M页 首页 1 2 3 尾页 跳转
if (intStyle == 2)
{
int PageTemp = 0;
string strPageNum = "";
string strTempNow = "";
//当页码超过最后一批该显示
if (_PageIndex > _PageCounts - intShowNum + 1)
{
PageTemp = _PageCounts < intShowNum ? 0 : _PageCounts - intShowNum;
for (int i = 1; i <= intShowNum; i++)
{
if (i > _PageCounts) break;
strTempNow = Convert.ToString(PageTemp + i);
//当前页不显示超链接
if( PageIndex == PageTemp + i)
{
strPageNum = strPageNum + "<b>" + strTempNow + "</b> ";
}
else
{
strPageNum = strPageNum + "<a href=""" + _TurnUrlStr + "?Page=" + strTempNow + "&" + _Options + """>" + strTempNow + "</a> ";
}
}
}
else
{
for (int i = 0; i < intShowNum; i++)
{
strTempNow = Convert.ToString(PageIndex + i);
//当前页不显示超链接
if (i == 0)
{
strPageNum = strPageNum + "<b>" + strTempNow + "</b> ";
}
else
{
strPageNum = strPageNum + "<a href=""" + _TurnUrlStr + "?Page=" + strTempNow + "&" + _Options + """>" + strTempNow + "</a> ";
}
}
}
//
strPageShowww = strPageShowww.Replace("{0}", " " + _PrevStr2);//上一页
strPageShowww = strPageShowww.Replace("{1}", " " + strPageNum);//显示数字
strPageShowww = strPageShowww.Replace("{2}", " " + _NextStr2);//下一页
}
#endregion
}
}
return strPageShowww;
}
#endregion
}
调用实例#region 调用实例
/**//*
protected void BindData()
{
int MyPage;
string strPage = Request["page"];
if (strPage == null || strPage == "")
{
MyPage = 1;
}
else
{
MyPage = Convert.ToInt32(strPage);
}
PageStore MyPageStore = new PageStore();
MyPageStore.Counts = 100;//总记录数
MyPageStore.PageIndex = MyPage;//当前要显示的页码
MyPageStore.PageSize = 2;//每页显示记录数
MyPageStore.TurnUrlStr = "WebPageStore.aspx";//要跳转的页面(当前页)
MyPageStore.Options = "Options=List";//页面所带参数
this.Label1.Text = MyPageStore.GetShowPageStr(true, true, true, true, true, true, 2, 4);
}
*/
#endregion
简要说明#region 简要说明
/**//*
.Net通用分页类(存储过程分页版,可以选择页码的显示样式,且有中英选择).
大概思路是:主要是利用存储过程在数据库进行分页,
所以在这个类里面不涉及到数据的处理,只进行页码的显示格式处理,
配合SQL2005 的 ROW_NUMBER () 功能,能够达到更好的效果.
目前一共4种样式,4种样式里还可以设置参数,把页码显示调整到最简,只剩下 上一页 下一页,
且 上一页 下一页 等文字可以自定义,用图片也可以,只是要把字符串拼成图片的Html
效果图:http://images.cnblogs.com/cnblogs_com/84ww/128905/r_PageStore.gif
*/
#endregion
附 存储过程例子#region 附 存储过程例子
/**//*
CREATE PROCEDURE dbo.sp_userinfoList
@strOptions varchar(200) = NULL,
@PageSize int = 20,
@PageIndex int = 1,
@Counts int = 0 OUTPUT
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @PageUp int
DECLARE @PageDown int
--处理页大小
IF @PageSize = -1 SET @PageSize = 20
--获得总记录数
SELECT @Counts = COUNT(1) FROM userinfo
--当前页的第一条记录RowID
SET @PageDown = @PageSize * @PageIndex + 1
--当前页的最后一条记录RowID
SET @PageUp = @PageSize * (@PageIndex + 1)
-- 信息列表
IF @strOptions='LIST' BEGIN
SELECT
id,
user,
password,
datetime,
content
FROM
(
SELECT * ,
PageTableRowID = ROW_NUMBER () OVER (ORDER BY id DESC)
FROM userinfo
) AS PageTableList
WHERE PageTableRowID BETWEEN @PageDown AND @PageUp
END
*/
#endregion
}
二、调用分页：
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using PageList;
public partial class ProductList : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//一、获取页数及每页显示的记录数
int MyPage = 1;
string strPage = Request["page"];
if (!string.IsNullOrEmpty(strPage))
{
MyPage = Convert.ToInt32(strPage);
}
int ContentSize = 30;
//二、获取当前页内容
int totalContent = 0;//记录总数
string returnTxt = string.Empty;//返回值
DataTable dt = new DataTable();
string con = System.Configuration.ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
SqlConnection conn = new SqlConnection(con);
SqlCommand comm = new SqlCommand("sc_dt_PubPageList", conn); //建立SqlCommand对象
comm.CommandType = CommandType.StoredProcedure; //设置SqlCommand对象执行类型为存储过程
comm.Parameters.Add("@TableName", SqlDbType.VarChar, 200); //向Parameters参数列表添加参数
comm.Parameters.Add("@FieldList", SqlDbType.VarChar, 2000);
comm.Parameters.Add("@PrimaryKey", SqlDbType.VarChar, 100);
comm.Parameters.Add("@where", SqlDbType.VarChar, 2000);
comm.Parameters.Add("@Order", SqlDbType.VarChar, 1000);
comm.Parameters.Add("@SortType", SqlDbType.Int);
comm.Parameters.Add("@RecorderCount", SqlDbType.Int);
comm.Parameters.Add("@PageSize", SqlDbType.Int);
comm.Parameters.Add("@PageIndex", SqlDbType.Int);
comm.Parameters.Add("@TotalCount", SqlDbType.Int);
comm.Parameters.Add("@TotalPageCount", SqlDbType.Int);
comm.Parameters["@TotalCount"].Direction = ParameterDirection.Output; //设置参数的输出类型
comm.Parameters["@TotalPageCount"].Direction = ParameterDirection.Output; //设置参数的输出类型
comm.Parameters["@TableName"].Value = "newsc_product";//表名
comm.Parameters["@FieldList"].Value = "productid,name,lsprice,vipprice,jf,adddate,p_pic";//显示列名，如果是全部字段则为*
comm.Parameters["@PrimaryKey"].Value = "productid";//单一主键或唯一值键
comm.Parameters["@where"].Value = "";//查询条件 不含'where'字符，如id>10 and len(userid)>9
comm.Parameters["@Order"].Value = "adddate desc";//排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
comm.Parameters["@SortType"].Value = 2;//排序规则 1:正序asc 2:倒序desc 3:多列排序方法
comm.Parameters["@RecorderCount"].Value = 0;//记录总数 0:会返回总记录
comm.Parameters["@PageSize"].Value = ContentSize;//每页输出的记录数
comm.Parameters["@PageIndex"].Value = MyPage;//当前页数
conn.Open();
SqlDataReader sdr = comm.ExecuteReader();
dt = ConvertDataReaderToDataTable(sdr);
totalContent = Convert.ToInt32(comm.Parameters["@TotalCount"].Value);
sdr.Close();
//循环输出内容
returnTxt = "<table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\">\r\n";
if (dt.Rows.Count > 0)
{
returnTxt += "<tr><td height=\"30\" width=\"100\" class=\"tabtitle\">产品编号</td><td width=\"150\" class=\"tabtitle\">产品名称</td><td width=\"80\" class=\"tabtitle\">商超价</td><td width=\"80\" class=\"tabtitle\">VIP价</td>";
returnTxt += "<td width=\"80\" class=\"tabtitle\">消费积分</td><td width=\"100\" class=\"tabtitle\">添加时间</td><td width=\"50\" class=\"tabtitle\">图片</td><td class=\"tabtitle\">操作</td></tr>";
for (int i = 0; i < dt.Rows.Count; i++)
{
returnTxt += "<tr>";
returnTxt += "<td height=\"25\" class=\"center\">" + dt.Rows[i][0].ToString() + "</td>";
returnTxt += "<td height=\"25\" class=\"center\">" + dt.Rows[i][1].ToString() + "</td>";
returnTxt += "<td height=\"25\" class=\"center\">" + dt.Rows[i][2].ToString() + "</td>";
returnTxt += "<td height=\"25\" class=\"center\">" + dt.Rows[i][3].ToString() + "</td>";
returnTxt += "<td height=\"25\" class=\"center\">" + dt.Rows[i][4].ToString() + "</td>";
returnTxt += "<td height=\"25\" class=\"center\">" + Convert.ToDateTime(dt.Rows[i][5]).ToString("yyyy-MM-dd") + "</td>";
if (dt.Rows[i][6].ToString() == "http://www.myg2b.com/images/product/nopic.jpg")
{
returnTxt += "<td height=\"25\" class=\"center\"><span class=\"red\">无</span></td>";
}
else
{
returnTxt += "<td height=\"25\" class=\"center\"><span class=\"green\">有</span></td>";
}
returnTxt += "<td height=\"25\" class=\"center\"><a href=\"UpdateProduct.aspx?ProID=" + dt.Rows[i][0].ToString() + "&RePage=" + MyPage + "\">产品详情</a> <span>|</span> <a href=\"UpdatePicture.aspx?ProID=" + dt.Rows[i][0].ToString() + "&RePage=" + MyPage + "\">上传图片</a></td>";
returnTxt += "</tr>";
}
}
else
{
returnTxt += "<tr><td height=\"50\" algin=\"center\">暂时还没有记录！</td></tr>";
}
returnTxt += "</table>";
TabList.Text = returnTxt;
conn.Close();
//三、获取分页栏
PageStore MyPageStore = new PageStore();
MyPageStore.Counts = totalContent;//总记录数
MyPageStore.PageIndex = MyPage;//当前要显示的页码
MyPageStore.PageSize = ContentSize;//每页显示记录数
MyPageStore.TurnUrlStr = "ProductList.aspx";//要跳转的页面(当前页)
MyPageStore.Options = "Option=List";//页面所带参数
this.pageNav.Text = MyPageStore.GetShowPageStr(true, true, true, true, true, true, 2, 5);
}
#region 将DataReader 转为 DataTable
/// <summary>
/// 将DataReader 转为 DataTable
/// </summary>
/// <param name="DataReader">DataReader</param>
public static DataTable ConvertDataReaderToDataTable(SqlDataReader reader)
{
try
{
DataTable objDataTable = new DataTable();
int intFieldCount = reader.FieldCount;
for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
{
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
}
objDataTable.BeginLoadData();
object[] objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
return objDataTable;
}
catch (Exception ex)
{
throw new Exception("转换DataReader为DataTable出错!", ex);
}
}
#endregion
}
