Repeater自定义分页(1)

Repeater自定义分页，使用的存储过程如下

--取得总记录数if exists(select 1 from sys.objects where name = 'GetProductsCount' and type = 'P')drop proc GetProductsCountgoCREATE PROCEDURE GetProductsCountasselect count(*) from productsgo--使用row_number函数--SQL Server 2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关 这个等级可以用来作为每条记录的row index.if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')drop proc GetProductsByPagegoCREATE PROCEDURE GetProductsByPage@PageNumberint,@PageSizeintASselect ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinuedfrom(select row_number() Over (order by productid) as row,ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinuedfrom products) as ProductsWithRowNumberwhere row between (@PageNumber - 1) * @PageSize + 1 and @PageNumber * @PageSizego--exec GetProductsByPage 1, 10--exec GetProductsByPage 5, 10

页面代码如下:

|<  <<  >>  >|  转到第页     

后台代码如下:

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;    }}

页面效果如下