Repeater自定义分页,使用的存储过程如下
- --取得总记录数
- if exists(select 1 from sys.objects where name = 'GetProductsCount' and type = 'P')
- drop proc GetProductsCount
- go
- CREATE PROCEDURE GetProductsCount
- as
- select count(*) from products
- go
- --使用row_number函数
- --SQL Server 2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关 这个等级可以用来作为每条记录的row index.
- if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
- drop proc GetProductsByPage
- go
- CREATE PROCEDURE GetProductsByPage
- @PageNumber int,
- @PageSize int
- AS
- select ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
- from
- (select row_number() Over (order by productid) as row,ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
- from products) as ProductsWithRowNumber
- where row between (@PageNumber - 1) * @PageSize + 1 and @PageNumber * @PageSize
- go
- --exec GetProductsByPage 1, 10
- --exec GetProductsByPage 5, 10
页面代码如下:
- <asp:linkbutton id="lbtnFirst" runat="server" commandname="First" oncommand="lbtnPage_Command">|<</asp:linkbutton>
- <asp:linkbutton id="lbtnPrevious" runat="server" commandname="Previous" oncommand="lbtnPage_Command"><<</asp:linkbutton>
- <asp:label id="lblMessage" runat="server">
- <asp:linkbutton id="lbtnNext" runat="server" commandname="Next" oncommand="lbtnPage_Command">>></asp:linkbutton>
- <asp:linkbutton id="lbtnLast" runat="server" commandname="Last" oncommand="lbtnPage_Command">>|</asp:linkbutton>
- 转到第<asp:dropdownlist id="dropPage" runat="server" autopostback="True" onselectedindexchanged="dropPage_SelectedIndexChanged"></asp:dropdownlist>页
- </asp:label>
后台代码如下:
- using System;
- using System.Collections;
- using System.Configuration;
- using System.Data;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.HtmlControls;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Data.SqlClient;
- public partial class RepeaterPaging : System.Web.UI.Page
- {
- //每页显示的最多记录的条数
- private int pageSize = 10;
- //当前页号
- private int currentPageNumber;
- //显示数据的总条数
- private static int rowCount;
- //总页数
- private static int pageCount;
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!IsPostBack)
- {
- SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
- SqlCommand cmd = new SqlCommand("GetProductsCount", cn);
- cmd.CommandType = CommandType.StoredProcedure;
- cn.Open();
- rowCount = (int)cmd.ExecuteScalar();
- cn.Close();
- pageCount = (rowCount - 1) / pageSize + 1;
- currentPageNumber = 1;
- for (int i = 1; i <= pageCount; i++)
- {
- dropPage.Items.Add(new ListItem(i.ToString(), i.ToString()));
- }
- dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
- Query();
- }
- }
- protected void sdsProducts_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
- {
- sdsProducts.SelectParameters["PageNumber"].DefaultValue = currentPageNumber.ToString();
- sdsProducts.SelectParameters["PageSize"].DefaultValue = pageSize.ToString();
- }
- protected void sdsProducts_Selected(object sender, SqlDataSourceStatusEventArgs e)
- {
- lblMessage.Text = "共找到" + rowCount + "条记录, 当前第" + currentPageNumber + "/" + pageCount + "页";
- }
- protected void lbtnPage_Command(object sender, CommandEventArgs e)
- {
- switch (e.CommandName)
- {
- case "First":
- currentPageNumber = 1;
- break;
- case "Previous":
- currentPageNumber = (int)ViewState["currentPageNumber"] - 1 > 1 ? (int)ViewState["currentPageNumber"] - 1 : 1;
- break;
- case "Next":
- currentPageNumber = (int)ViewState["currentPageNumber"] + 1 < pageCount ? (int)ViewState["currentPageNumber"] + 1 : pageCount;
- break;
- case "Last":
- currentPageNumber = pageCount;
- break;
- }
- dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
- Query();
- }
- protected void dropPage_SelectedIndexChanged(object sender, EventArgs e)
- {
- currentPageNumber = int.Parse(dropPage.SelectedValue);
- Query();
- }
- private void Query()
- {
- ViewState["currentPageNumber"] = currentPageNumber;
- SetButton(currentPageNumber);
- sdsProducts.Select(DataSourceSelectArguments.Empty);
- }
- private void SetButton(int currentPageNumber)
- {
- lbtnFirst.Enabled = currentPageNumber != 1;
- lbtnPrevious.Enabled = currentPageNumber != 1;
- lbtnNext.Enabled = currentPageNumber != pageCount;
- lbtnLast.Enabled = currentPageNumber != pageCount;
- }
- }
页面效果如下