学步园

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

页面代码如下:

|  
  
>>>|  
转到第页     

后台代码如下:

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  1 ? (int)ViewState["currentPageNumber"] - 1 : 1;
                break;
            case "Next":
                currentPageNumber = (int)ViewState["currentPageNumber"] + 1 
页面效果如下