效果：
SQL-存储过程（Paging）：
- ROW_NUMBER() over(order by MessageDateTime desc) 其中的 MessageDateTime desc 代表的是按照时间，倒序排列。
USE [DB_Message]
GO
/****** Object: StoredProcedure [dbo].[Paging] Script Date: 2015/9/3 11:53:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: HF_Ultrastrong
-- Create date: 2015-8-28 19:31:59
-- Description: 分页
-- =============================================
ALTER PROCEDURE [dbo].[Paging]
@DisplayCountPage int,
@CurrentPage int
AS
BEGIN
declare @StartPage int
declare @EndPage int
set @StartPage=(@CurrentPage-1)*@DisplayCountPage
set @EndPage=@CurrentPage*@DisplayCountPage
select * from
(select ROW_NUMBER() over(order by MessageDateTime desc) as rownum, MessageID, MessageName, MessageIP, MessageContent, MessageDateTime, MessageHeadImage from Tb_Message) as a
where a.rownum > @StartPage and a.rownum <= @EndPage
END
前端代码：
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Score</th>
</tr>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<tr>
<td>
<%#Eval("ID") %>
</td>
<td>
<%#Eval("Name") %>
</td>
<td>
<%#Eval("Score") %>
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<asp:HyperLink ID="HyperLink1" runat="server">HyperLink</asp:HyperLink>
</div>
</form>
</body>
后端代码：
protected void Page_Load(object sender, EventArgs e)
{
// 当前页，默认加载时，当前页为1
int CurrentPage = 1;
// 每一页显示数据量
int DisplayCountPage = 5;
// 总记录数
int TotalPage = Convert.ToInt32(SQLHelper.ExcuteScalar("select Count(*) from Tb_Message", CommandType.Text));
if (TotalPage < DisplayCountPage)
{
this.HyperLink1.Visible = false;
}
//判断是否传递参数
if (Request.QueryString["page"] != null)
{
if ((!IsNumber(Request.QueryString["page"].ToString())) || Convert.ToInt32(Request.QueryString["page"].ToString()) > TotalPage)
{
Response.Write("<script>alert('页码不正确！');history.back()</script>");
}
else
{
CurrentPage = Convert.ToInt32(Request.QueryString["page"].ToString());
}
}
//参数化数据
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@DisplayCountPage", DisplayCountPage),
new SqlParameter("@CurrentPage", CurrentPage),
};
//绑定数据
this.Repeater1.DataSource = SQLHelper.ExecuteTable("Paging", paras, CommandType.StoredProcedure);
this.Repeater1.DataBind();
//显示上下页按钮
this.HyperLink1.Text = get_pagenation(DisplayCountPage, TotalPage, CurrentPage, "Message.aspx?page={0}", "_top");
}
//==========================================分页类=======================================//
#region 分页内容
/// <summary>
/// 分页内容
/// </summary>
/// <param name="size">页面大小</param>
/// <param name="count">页面数量</param>
/// <param name="currendIndex">当前页</param>
/// <param name="pattern">url模式：demo.aspx?page={0}</param>
/// <param name="target">窗口模式</param>
/// <returns></returns>
public static string get_pagenation(int size, int count, int currendIndex, string pattern, string target)
{
//1>打开窗口目标
target = string.IsNullOrEmpty(target) ? "_top" : target;
//2>总页数
int pageCount = count / size;
pageCount = pageCount * size == count ? pageCount : pageCount + 1;
//3>分页内容
StringBuilder strHtml = new StringBuilder();
strHtml.Append("<span class='pagenation'>");
#region 首部处理
if (currendIndex > 1)
{
strHtml.AppendFormat("<a href='Message.aspx?page=1' target='{0}'>[首页]</a>", target);
strHtml.AppendFormat("<a href='{0}' target='{1}'>[上一页]</a>", string.Format(pattern, currendIndex - 1), target);
}
else
{
strHtml.Append("<span class='disabled'>[首页]</span> <span class='disabled'>[上一页]</span>");
}
#endregion
#region 中间部分
int i = 1;
int right = (currendIndex + 4) > pageCount ? pageCount : currendIndex + 4;
if (currendIndex > 6)
{
i = currendIndex - 5;
}
else
{
right = pageCount >= 10 ? 10 : pageCount;
}
for (; i <= right; i++)
{
if (i == currendIndex)
{
strHtml.AppendFormat("<font class='current'>{0}</font>", i);
strHtml.AppendLine();
continue;
}
strHtml.AppendFormat("<a href='{0}' target='{1}'>[{2}]</a>", string.Format(pattern, i), target, i);
strHtml.AppendLine();
}
#endregion
#region 尾部处理
if (currendIndex == pageCount)
{
strHtml.Append("<span class='disabled'>[下一页]</span><span class='disabled'>[末页]</span>");
strHtml.AppendFormat("总共({0})页", pageCount);
}
else
{
strHtml.AppendFormat("<a href='{0}' target='{1}'>[下一页]</a>", string.Format(pattern, currendIndex + 1), target);
strHtml.AppendFormat("<a href='{0}' target='{1}'>[末页]</a>", string.Format(pattern, pageCount), target);
strHtml.AppendFormat(" <label>总共({0})页</label>", pageCount);
}
#endregion
strHtml.Append("</span>");
return strHtml.ToString();
}
#endregion
//======================================================================================//
#region 判断是否为数字
/// <summary>
/// 判断是否为数字
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public bool IsNumber(string value)
{
Regex r = new Regex(@"^d+(.)?d*$");
if (r.IsMatch(value))
return true;
else
return false;
}
#endregion
================================================================================
最终效果：
前端代码：
<%@ Page Title="" Language="C#" MasterPageFile="~/Manager/Manager.Master" AutoEventWireup="true" CodeBehind="DisplayBookInfo.aspx.cs" Inherits="Shop.Manager.WebForm4" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<div class="config-items">
<div class="config-title">
<h1><i class="icon-font">�</i>添加图书分类</h1>
</div>
<div class="result-content">
<table class="result-tab" style=" 100%">
<tr>
<th>图书编号</th>
<th>图书名称</th>
<th>作者名称</th>
<th>出版社</th>
<th>出版时间</th>
<th>ISBN</th>
<th>操作</th>
</tr>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<tr>
<td>
<a><%#Eval("BookID")%></a>
</td>
<td>
<a><%#Eval("BookName")%></a>
</td>
<td>
<a><%#Eval("AuthorName")%></a>
</td>
<td>
<a><%#Eval("Publishing")%></a>
</td>
<td>
<a><%#Eval("PublishingTime")%></a>
</td>
<td>
<a><%#Eval("ISBN")%></a>
</td>
<td>
<a href="../Manager/BookDetaileInfoAndUpdate.aspx?id=<%#Eval("BookID")%>&Is=true">详细信息</a>
<a href="../Manager/BookDetaileInfoAndUpdate.aspx?id=<%#Eval("BookID")%>&Is=false">| 修改</a>
<asp:LinkButton ID="LnkDeleteCategory" runat="server" CssClass="link-del" CommandArgument='<%#Eval("BookID")%>' OnClientClick="return confirm('确定要删除吗?')" OnClick="LnkDeleteCategory_Click">| 删除</asp:LinkButton>
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<div class="list-page">
<!--分页-->
<asp:LinkButton ID="FirstPage" runat="server" OnClick="FirstPage_Click">首页</asp:LinkButton>
<asp:LinkButton ID="PreviousPage" runat="server" OnClick="PreviousPage_Click">上一页</asp:LinkButton>
<asp:Label ID="CurrentPage" runat="server" Text="Label">1</asp:Label>
/
<asp:Label ID="TotalPage" runat="server" Text="Label">0</asp:Label>页
<asp:LinkButton ID="NextPage" runat="server" OnClick="NextPage_Click">下一页</asp:LinkButton>
<asp:LinkButton ID="EndPage" runat="server" OnClick="EndPage_Click">尾页</asp:LinkButton>
</div>
</div>
</div>
</asp:Content>
后台代码：
using System;
using System.Web.UI.WebControls;
using BLL;
using Tools;
namespace Shop.Manager
{
public partial class WebForm4 : System.Web.UI.Page
{
BookInfoBLL bookinfobll = new BookInfoBLL();
AlbumBLL albumbll = new AlbumBLL();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//绑定图书信息
DisplayTotalPages();//显示总计多少页
Show();//初始化显示第一页，默认当前为第一页
State();//初始化导航按钮的使用状态
}
}
#region 分页
/// <summary>
/// 按照哪一个字段进行排序（在进行查询记录时）
/// </summary>
public string OderbyField = "BookID";
/// <summary>
/// 查询字段（中间以逗号隔开，开头不加逗号，末尾不加逗号）
/// </summary>
public string QueryField = "[BookID],[BookName],[AuthorName],[Publishing],[PublishingTime],[ISBN]";
/// <summary>
/// 表名
/// </summary>
public string TableName = "Tb_BookInfo";
/// <summary>
/// 每一页显示数据量
/// </summary>
public int CountPage = 5;
/// <summary>
/// 首页
/// </summary>
protected void FirstPage_Click(object sender, EventArgs e)
{
this.CurrentPage.Text = "1";
Show();
State();
}
/// <summary>
/// 上一页
/// </summary>
protected void PreviousPage_Click(object sender, EventArgs e)
{
this.CurrentPage.Text = (Convert.ToInt32(this.CurrentPage.Text) - 1).ToString();
Show();
State();
}
/// <summary>
/// 下一页
/// </summary>
protected void NextPage_Click(object sender, EventArgs e)
{
this.CurrentPage.Text = (Convert.ToInt32(this.CurrentPage.Text) + 1).ToString();
Show();
State();
}
/// <summary>
/// 尾页
/// </summary>
protected void EndPage_Click(object sender, EventArgs e)
{
this.CurrentPage.Text = this.TotalPage.Text;
Show();
State();
}
/// <summary>
/// 显示总计多少页
/// </summary>
public void DisplayTotalPages()
{
string text = "select count(*) from " + TableName + "";
int page = Convert.ToInt32(SelectDataSource.ExcuteScalar(text));//获取总条数
this.TotalPage.Text = (Math.Ceiling(((page * 1.0 / CountPage)))).ToString();
}
/// <summary>
/// 状态设置
/// </summary>
public void State()
{
if (this.CurrentPage.Text == "1")//如果当前页为第一页，则前一页和首页按钮禁用
{
this.FirstPage.Enabled = false;
this.PreviousPage.Enabled = false;
this.EndPage.Enabled = true;
this.NextPage.Enabled = true;
}
if (this.CurrentPage.Text == this.TotalPage.Text)//如果当前页码等于总页码，则后一页和尾页按钮禁用
{
this.FirstPage.Enabled = true;
this.PreviousPage.Enabled = true;
this.EndPage.Enabled = false;
this.NextPage.Enabled = false;
}
if (this.CurrentPage.Text == "1" && this.TotalPage.Text == "1")//当前页码与总页码都等于1时，（记录数小于要显示的条数）
{
this.FirstPage.Enabled = false;
this.PreviousPage.Enabled = false;
this.EndPage.Enabled = false;
this.NextPage.Enabled = false;
}
if (Convert.ToInt32(this.CurrentPage.Text) > 1 && Convert.ToInt32(this.CurrentPage.Text) < Convert.ToInt32(this.TotalPage.Text))//如果当前也在首页和尾页之间则四个按钮均可用
{
this.FirstPage.Enabled = true;
this.PreviousPage.Enabled = true;
this.EndPage.Enabled = true;
this.NextPage.Enabled = true;
}
}
/// <summary>
/// 显示数据，绑定数据
/// </summary>
public void Show()
{
string sql = @"select * from (select ROW_NUMBER() over(order by " + OderbyField + ") as rownum, " + QueryField + " from " + TableName + ") as a where a.rownum > '" + (Convert.ToInt32(this.CurrentPage.Text) - 1) * CountPage + "' and a.rownum <='" + Convert.ToInt32(this.CurrentPage.Text) * CountPage + "' order by a.rownum ASC";
//根据上面的sql语句给定Repeater控件数据源
this.Repeater1.DataSource = SelectDataSource.DataSource(sql);
this.Repeater1.DataBind();
}
#endregion
}
}