存储过程分页主要代码

using System;
using System.Data;
using System.Configuration;
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 _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind();
        }
    }

    int index = 1;//当前页索引
    int size = 5;//每页大小（记录条数）
    protected void Bind()
    {
        //创建连接对象
        SqlConnection con = new SqlConnection("server=.;uid=sa;pwd=;database=northwind");
        //创建数据适配器对象（命令字符串为存储过程pageing，连接对象为con）
        SqlDataAdapter sda = new SqlDataAdapter("paging", con);
        //指定SelectCommand类型为存储过程
        sda.SelectCommand.CommandType = CommandType.StoredProcedure;

        //创建参数数组
        SqlParameter[] parames = new SqlParameter[] {
            new SqlParameter("@index",SqlDbType.Int),
            new SqlParameter("@size",SqlDbType.Int),
            new SqlParameter("@count",SqlDbType.Int)
        };

        //设置每个参数的值以及参数方向
        parames[0].Value = index;
        parames[1].Value = size;
        parames[2].Direction = ParameterDirection.Output;

        //为sda对象的SelectCommand添加参数
        foreach (SqlParameter param in parames)
        {
            sda.SelectCommand.Parameters.Add(param);
        }

        //创建数据集对象ds
        DataSet ds = new DataSet();
        //执行命令,将执行结果填充到数据集对象ds
        sda.Fill(ds);

        //获取被查询表中记录总数
        int count = Convert.ToInt32(sda.SelectCommand.Parameters["@count"].Value);

        //设置总页数（总记录数除以每页大小）
        if (count % size == 0)
        {
            lblTotal.Text = Convert.ToString(count / size);
        }
        else
        {//如果除不断，总页数为商+1
            lblTotal.Text = Convert.ToString(count / size + 1);
        }

        //设置当前页索引
        lblCurrentPage.Text = index.ToString();

        //将数据集ds绑定到GridView,DataList,Repeater
        GridView1.DataSource = ds;
        GridView1.DataBind();

        DataList1.DataSource = ds;
        DataList1.DataBind();

        Repeater1.DataSource = ds;
        Repeater1.DataBind();

        //设置“首页”等连接的可用状态
        SetLinkButtonState();
    }

    //定义方法SetLinkButtonState用来设置“首页”等连接的可用状态
    protected void SetLinkButtonState()
    {
        if (lblTotal.Text == "1")
        {//如果总页数只有一页，则全部设为不可用
            firstPage.Enabled = false;
            previousPage.Enabled = false;
            nextPage.Enabled = false;
            lastPage.Enabled = false;
        }
        else
        {//否则，总页数不止一页
            if (lblCurrentPage.Text == lblTotal.Text)
            {//如果当前页是最后一页，则启用“首页”“上一页”，禁用“末页”“下一页”
                firstPage.Enabled = true;
                previousPage.Enabled = true;
                nextPage.Enabled = false;
                lastPage.Enabled = false;
            }
            else if (lblCurrentPage.Text == "1")
            {//如果当前页是第一页，则禁用“首页”“上一页”，启用“末页”“下一页”
                firstPage.Enabled = false;
                previousPage.Enabled = false;
                nextPage.Enabled = true;
                lastPage.Enabled = true;
            }
            else
            {//如果既不是第一页，又不是最后一页，则启用全部LinkButton
                firstPage.Enabled = true;
                previousPage.Enabled = true;
                nextPage.Enabled = true;
                lastPage.Enabled = true;
            }
        }
    }

    //首页
    protected void firstPage_Click(object sender, EventArgs e)
    {
        index = 1;
        Bind();
    }

    //上一页
    protected void previousPage_Click(object sender, EventArgs e)
    {
        index = int.Parse(lblCurrentPage.Text) - 1;
        Bind();
    }

    //下一页
    protected void nextPage_Click(object sender, EventArgs e)
    {
        index = int.Parse(lblCurrentPage.Text) + 1;
        Bind();
    }

    //末页
    protected void lastPage_Click(object sender, EventArgs e)
    {
        index = int.Parse(lblTotal.Text);
        Bind();
    }
}

======================================================
分页存储过程
======================================================
create procedure paging
(
@index int,--当前页索引
@size int,--每页大小
@count int output--目标表中记录总数
)
as
--创建临时表temp,解决目标表(本例中是products)中主键值可能不连续问题
create table #temp
(
idnew int identity(1,1),
idold int
)
--从目标表中查询出主键值，插入到临时表中
insert into #temp select productid from products
--通过与临时表temp进行内连接，从目标表中查出所需记录
select productid,productname,unitprice from products
inner join #temp on productid=idold
where #temp.idnew>(@index-1)*@size and #temp.idnew<=@index*@size
--给输出参数@count赋值
set @count=(select count(*) from products)