分页大家都做过吧,分页最常见的如
有上下翻页,
有如百度一样翻页
还有如
还有如当当网一样的分页这个下次有机会再列出给大家(朋友给出的)
这篇文章讲到的一个分页,要做成论坛一样的分页格式,都用过CSDN吧,就是像CSDN上翻页样式相似
分页总的说来,大概分为Post分页 与 URL 分页 ,举例来说吧,如大家在做GridView时自带的分页,其实其内含的就是 Post分页的 ,他分页本身URL地址是没有改变的.只是在回传
_doPostBack() 函数,
URL分页,相信大家也用过,静态页面的分页大部分用到的是URL分页,,, 还有就是文章的分页
说多了大家也觉得是多余的,还是看代码强些...........................................
注意我这段代码用到了codeproject网站上的一个组件 ASPnetPagerV2netfx2_0.dll 也是参考codeproject上面例子所做的 我下面的例用到的数据库是 Northwind 数据库
SQL代码段
CREATE PROCEDURE sp_page
@CurrentPage int, @PageSize int,@Field_Info varchar(500),@Table_info varchar(20),@Field_id varchar(10),@intOrder int,@otherwhere varchar(50),@RecordCount int output,@PageCount int output
--@CurrentPage 为显示那一页,@PageSize为每一页显示几行,@Field_info为要显示的字段可以为*,@Table_info为要查询的表或视 图,@field_id为按这个字段排序,@intorder0为升序排1为降序排,@otherwhere为条件,@RecordCount为总行 数,@PageCount为总页数
AS
begin
DECLARE @MinPage int, @MaxPage int
declare @sql varchar(1000)
declare @sqlt nvarchar(300)
declare @order varchar(4)
set @Field_Info = replace(@Field_Info,'''','') --除去@field_info中的'
set @Table_info = replace(@table_info,'''','') --除去@table_info中的'
set @Field_id = replace(@Field_id,'''','') --除去@field_id中的'
set @otherwhere = replace(@otherwhere,'''','''''') --将@otherwhere中的'换成''让SQL语句正确释别'
set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名
IF @PageSize <= 0
begin
set @PageSize = 10
end
else if @PageSize > @RecordCount
begin
set @pageSize = @RecordCount
end
set @pagecount = @RecordCount / @PageSize
if ((@recordcount % @pagesize) != 0) --如果除不尽则加一页
begin
set @PageCount = @RecordCount / @PageSize
set @PageCount = @pagecount + 1
end
else
begin
set @pagecount = @recordcount /@PageSize
end
IF @CurrentPage <= 0
begin
set @CurrentPage = 1
end
else if @CurrentPage > @pagecount
begin
set @currentpage = @pagecount --如果输入页数大于总页数则符最后一页
end
SET @MinPage = (@CurrentPage - 1) * @PageSize + 1
SET @MaxPage = @MinPage + @PageSize - 1
BEGIN
if @intorder = 0
set @order = 'asc'
else
set @order = 'desc'
if @Field_Info like ''
set @field_Info = '*'
if @otherwhere like ''
set @sql = 'select ' + @Field_Info + ' from (select ' + @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage)
else
set @sql = 'select ' + @Field_Info + ' from (select ' + @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ' and ' + @otherwhere
exec(@sql)
END
end
@CurrentPage int, @PageSize int,@Field_Info varchar(500),@Table_info varchar(20),@Field_id varchar(10),@intOrder int,@otherwhere varchar(50),@RecordCount int output,@PageCount int output
--@CurrentPage 为显示那一页,@PageSize为每一页显示几行,@Field_info为要显示的字段可以为*,@Table_info为要查询的表或视 图,@field_id为按这个字段排序,@intorder0为升序排1为降序排,@otherwhere为条件,@RecordCount为总行 数,@PageCount为总页数
AS
begin
DECLARE @MinPage int, @MaxPage int
declare @sql varchar(1000)
declare @sqlt nvarchar(300)
declare @order varchar(4)
set @Field_Info = replace(@Field_Info,'''','') --除去@field_info中的'
set @Table_info = replace(@table_info,'''','') --除去@table_info中的'
set @Field_id = replace(@Field_id,'''','') --除去@field_id中的'
set @otherwhere = replace(@otherwhere,'''','''''') --将@otherwhere中的'换成''让SQL语句正确释别'
set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名
IF @PageSize <= 0
begin
set @PageSize = 10
end
else if @PageSize > @RecordCount
begin
set @pageSize = @RecordCount
end
set @pagecount = @RecordCount / @PageSize
if ((@recordcount % @pagesize) != 0) --如果除不尽则加一页
begin
set @PageCount = @RecordCount / @PageSize
set @PageCount = @pagecount + 1
end
else
begin
set @pagecount = @recordcount /@PageSize
end
IF @CurrentPage <= 0
begin
set @CurrentPage = 1
end
else if @CurrentPage > @pagecount
begin
set @currentpage = @pagecount --如果输入页数大于总页数则符最后一页
end
SET @MinPage = (@CurrentPage - 1) * @PageSize + 1
SET @MaxPage = @MinPage + @PageSize - 1
BEGIN
if @intorder = 0
set @order = 'asc'
else
set @order = 'desc'
if @Field_Info like ''
set @field_Info = '*'
if @otherwhere like ''
set @sql = 'select ' + @Field_Info + ' from (select ' + @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage)
else
set @sql = 'select ' + @Field_Info + ' from (select ' + @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ' and ' + @otherwhere
exec(@sql)
END
end
aspx页面中要引用的分页样式表代码:
.PagerContainerTable
{
border-right: #333333 1px solid;
border-top: #333333 1px solid;
border-left: #333333 1px solid;
color: #d1d1e1;
border-bottom: #333333 1px solid;
background-color: #FFFFFF;
}
.PagerInfoCell
{
padding-right: 6px;
padding-left: 6px;
padding-bottom: 3px;
font: bold 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
color: #f0f1f2;
padding-top: 3px;
white-space: nowrap;
background-color: #990000;
font-weight:normal;
}
.PagerInfoCell:link
{
color: #ffcc66;
text-decoration: none;
}
.PagerInfoCell:visited
{
color: #ffcc66;
text-decoration: none;
}
.PagerCurrentPageCell
{
color: #990000;
background-color: #FFFF99;
}
.PagerOtherPageCells
{
background-color: #f0f1f2;
}
.PagerSSCCells
{
background-color: #cccccc;
}
.PagerHyperlinkStyle
{
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
.PagerHyperlinkStyle:hover
{
color: #000000;
text-decoration: none;
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
.PagerHyperlinkStyle:link
{
color: #000000;
text-decoration: none;
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
.PagerHyperlinkStyle:visited
{
color: #000000;
text-decoration: none;
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
.PagerHyperlinkStyle:active
{
color: #000000;
text-decoration: none;
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
{
border-right: #333333 1px solid;
border-top: #333333 1px solid;
border-left: #333333 1px solid;
color: #d1d1e1;
border-bottom: #333333 1px solid;
background-color: #FFFFFF;
}
.PagerInfoCell
{
padding-right: 6px;
padding-left: 6px;
padding-bottom: 3px;
font: bold 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
color: #f0f1f2;
padding-top: 3px;
white-space: nowrap;
background-color: #990000;
font-weight:normal;
}
.PagerInfoCell:link
{
color: #ffcc66;
text-decoration: none;
}
.PagerInfoCell:visited
{
color: #ffcc66;
text-decoration: none;
}
.PagerCurrentPageCell
{
color: #990000;
background-color: #FFFF99;
}
.PagerOtherPageCells
{
background-color: #f0f1f2;
}
.PagerSSCCells
{
background-color: #cccccc;
}
.PagerHyperlinkStyle
{
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
.PagerHyperlinkStyle:hover
{
color: #000000;
text-decoration: none;
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
.PagerHyperlinkStyle:link
{
color: #000000;
text-decoration: none;
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
.PagerHyperlinkStyle:visited
{
color: #000000;
text-decoration: none;
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
.PagerHyperlinkStyle:active
{
color: #000000;
text-decoration: none;
font: 11px arial, verdana, geneva, lucida, 'lucida grande' , arial, helvetica, sans-serif;
}
aspx页面代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Test_Paging_CSDN.aspx.cs" Inherits="Pageing_Default2" %>
<%@ Register TagPrefix="cc1" Namespace="CutePager" Assembly="ASPnetPagerV2netfx2_0" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
<link href="lightstyle.css" type="text/css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:repeater id="Repeater1" runat="server">
<HeaderTemplate>
<table style="background-color:#ffcc66;" cellpadding="5" cellspacing="0">
<tr>
<th style="70px;">
index</th>
<th style="200px;">
ProductName</th>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table cellpadding="5" cellspacing="0" style="background-color:#f0f1f2;">
<tr>
<td style="70px;" align="center"><%#Eval("RowNumber")%></td>
<td style="200px;"><%#Eval("ProductName")%></td>
</tr>
</table>
</ItemTemplate>
<AlternatingItemTemplate>
<table cellpadding="5" cellspacing="0" style="background-color:#ccccff;">
<tr>
<td style="70px;" align="center"><%#Eval("RowNumber")%></td>
<td style="200px;"><%#Eval("ProductName")%></td>
</tr>
</table>
</AlternatingItemTemplate>
</asp:repeater>
<br />
<cc1:pager id="pager1" runat="server" oncommand="pager_Command" showfirstlast="true" PageSize="10">
</cc1:pager>
</div>
</form>
</body>
</html>
<%@ Register TagPrefix="cc1" Namespace="CutePager" Assembly="ASPnetPagerV2netfx2_0" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
<link href="lightstyle.css" type="text/css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:repeater id="Repeater1" runat="server">
<HeaderTemplate>
<table style="background-color:#ffcc66;" cellpadding="5" cellspacing="0">
<tr>
<th style="70px;">
index</th>
<th style="200px;">
ProductName</th>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table cellpadding="5" cellspacing="0" style="background-color:#f0f1f2;">
<tr>
<td style="70px;" align="center"><%#Eval("RowNumber")%></td>
<td style="200px;"><%#Eval("ProductName")%></td>
</tr>
</table>
</ItemTemplate>
<AlternatingItemTemplate>
<table cellpadding="5" cellspacing="0" style="background-color:#ccccff;">
<tr>
<td style="70px;" align="center"><%#Eval("RowNumber")%></td>
<td style="200px;"><%#Eval("ProductName")%></td>
</tr>
</table>
</AlternatingItemTemplate>
</asp:repeater>
<br />
<cc1:pager id="pager1" runat="server" oncommand="pager_Command" showfirstlast="true" PageSize="10">
</cc1:pager>
</div>
</form>
</body>
</html>
aspx.cs代码
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;
public partial class Pageing_Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
pager1.CurrentIndex = 1;
BindRepeater(1);
}
}
public string strConn = DbHelperSql.connectionString.ToString();
public void pager_Command(object sender, CommandEventArgs e)
{
int currnetPageIndx = Convert.ToInt32(e.CommandArgument);
pager1.CurrentIndex = currnetPageIndx;
BindRepeater(currnetPageIndx);
}
private void BindRepeater(int pageNo)
{
/*
@CurrentPage int, --@CurrentPage为显示那一页
@PageSize int,--@PageSize为每一页显示几行
@Field_Info varchar(500),--@Field_info为要显示的字段可以为*
@Table_info varchar(20),--@Table_info为要查询的表或视图
@Field_id varchar(10),--@field_id为按这个字段排序
@intOrder int,--@intorder0为升序排1为降序排
@otherwhere varchar(50),--@otherwhere为条件
@RecordCount int output,--@RecordCount为总行数
@PageCount int output--@PageCount为总页数
*/
SqlConnection cn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand("dbo.sp_page", cn);
Cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr;
Cmd.Parameters.Add("@CurrentPage", SqlDbType.Int, 4).Value = pageNo;
Cmd.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = pager1.PageSize;
Cmd.Parameters.Add("@Field_Info", SqlDbType.VarChar, 500).Value = "*";
Cmd.Parameters.Add("@Table_info", SqlDbType.VarChar, 20).Value = "Products"; //表名
Cmd.Parameters.Add("@Field_id", SqlDbType.VarChar, 10).Value = "ProductID";
Cmd.Parameters.Add("@intOrder", SqlDbType.Int).Value = 1; //排序
Cmd.Parameters.Add("@otherwhere", SqlDbType.VarChar,50).Value = ""; //条件
Cmd.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output; //总行数
Cmd.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
cn.Open();
dr = Cmd.ExecuteReader();
Repeater1.DataSource = dr;
Repeater1.DataBind();
dr.Close();
cn.Close();
Int32 _totalRecords = Convert.ToInt32(Cmd.Parameters["@RecordCount"].Value);
pager1.ItemCount = _totalRecords;
pager1.PageCount = Convert.ToInt32(Cmd.Parameters["@PageCount"].Value);
}
}
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;
public partial class Pageing_Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
pager1.CurrentIndex = 1;
BindRepeater(1);
}
}
public string strConn = DbHelperSql.connectionString.ToString();
public void pager_Command(object sender, CommandEventArgs e)
{
int currnetPageIndx = Convert.ToInt32(e.CommandArgument);
pager1.CurrentIndex = currnetPageIndx;
BindRepeater(currnetPageIndx);
}
private void BindRepeater(int pageNo)
{
/*
@CurrentPage int, --@CurrentPage为显示那一页
@PageSize int,--@PageSize为每一页显示几行
@Field_Info varchar(500),--@Field_info为要显示的字段可以为*
@Table_info varchar(20),--@Table_info为要查询的表或视图
@Field_id varchar(10),--@field_id为按这个字段排序
@intOrder int,--@intorder0为升序排1为降序排
@otherwhere varchar(50),--@otherwhere为条件
@RecordCount int output,--@RecordCount为总行数
@PageCount int output--@PageCount为总页数
*/
SqlConnection cn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand("dbo.sp_page", cn);
Cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr;
Cmd.Parameters.Add("@CurrentPage", SqlDbType.Int, 4).Value = pageNo;
Cmd.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = pager1.PageSize;
Cmd.Parameters.Add("@Field_Info", SqlDbType.VarChar, 500).Value = "*";
Cmd.Parameters.Add("@Table_info", SqlDbType.VarChar, 20).Value = "Products"; //表名
Cmd.Parameters.Add("@Field_id", SqlDbType.VarChar, 10).Value = "ProductID";
Cmd.Parameters.Add("@intOrder", SqlDbType.Int).Value = 1; //排序
Cmd.Parameters.Add("@otherwhere", SqlDbType.VarChar,50).Value = ""; //条件
Cmd.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output; //总行数
Cmd.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
cn.Open();
dr = Cmd.ExecuteReader();
Repeater1.DataSource = dr;
Repeater1.DataBind();
dr.Close();
cn.Close();
Int32 _totalRecords = Convert.ToInt32(Cmd.Parameters["@RecordCount"].Value);
pager1.ItemCount = _totalRecords;
pager1.PageCount = Convert.ToInt32(Cmd.Parameters["@PageCount"].Value);
}
}
Web.Config文件
//这里你可以改成自已本地数据库测试即可
<connectionStrings>
<add name="NorthwindConnectionString" connectionString="User ID=sa;pwd=sa;Initial Catalog=NORTHWND;Data Source=WANGYONGJUN\SQLEXPRESS" providerName="System.Data.SqlClient"/>
</connectionStrings>
<add name="NorthwindConnectionString" connectionString="User ID=sa;pwd=sa;Initial Catalog=NORTHWND;Data Source=WANGYONGJUN\SQLEXPRESS" providerName="System.Data.SqlClient"/>
</connectionStrings>
好了,这只是一个例子,希望有助于大家的学习,........................................................
代码下载:/Files/accpfriend/Paging4.rar (有什么问题可留言)
没有最好,只有更好。
青华木园