存储过程实现分页查询

以学生信息表为例
一、创建分页存储过程
create  proc    Proc_FindStudentSplitPage

--------------------------声明参数----------------
@PageSize int, --每页记录数  输入参数
@CurPage int, --当前是第几页 输入参数
@TotalPage int out, --总页数 输出参数
@TotalCount int out--总记录数 输出参数
as
-------------------定义变量-------------------------------------------
declare @StrSql varchar(200) --要执行的SQL语句
--计算总页数和总记录数
select @TotalCount = count(*) from student
if (@TotalCount % @PageSize = 0) --------总记录数正好按每页记录的个数年分均不多不少
   set @TotalPage = @TotalCount / @PageSize
else                  --------------------记录数分不均，不够一页，或者最后一页不满
   set @TotalPage = @TotalCount / @PageSize + 1

--构造分页查询语句 以主键为标准
set @StrSql =  'select top ' + cast(@PageSize as varchar)
set @StrSql =  @StrSql + ' * from student where stuno not in'
set @StrSql = @StrSql + ' (select top '+ cast(@PageSize * (@CurPage-1) as varchar)+' stuno from student)'
--打印执行Sql语句
print @StrSql
exec(@StrSql)--执行，注意()

print '总页数:' + cast(@TotalPage as varchar)
print '总记录数:' + cast(@TotalCount as varchar) 

二、在工程中应用
1、创建实体类
using System;
using System.Collections.Generic;
using System.Text;

namespace StudentAccessLib
{
    /// <summary>
    /// 实体类，代表学生
    /// </summary>
    public class StudentVO
    {
        private string _stuNo;
        private string _stuName;
        private DateTime _stuAge;
        private string _stuSex;

        public string StuSex
        {
            get { return _stuSex; }
            set { _stuSex = value; }
        }
 
        public DateTime StuAge
        {
            get { return _stuAge; }
            set { _stuAge = value; }
        }
 

        public string StuName
        {
            get { return _stuName; }
            set { _stuName = value; }
        }
 

        public string StuNo
        {
            get { return _stuNo; }
            set { _stuNo = value; }
        }
 
    }
}

2、数据访问对象
using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace StudentAccessLib
{
    public class StudentAccess
    {

  ///
        ///分页查询学生信息
        /// </summary>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="curPage">当前要显示的页</param>
        /// <param name="totalPage">总页数</param>
        /// <param name="totalCount">总记录数</param>
        public ArrayList FindAllStudentByPage(int pageSize, int curPage, out int totalPage, out int totalCount)
        {
            totalPage = 0;
            totalCount = 0;
            ArrayList stuList = new ArrayList();
            try
            {
                //创建Command对象
                SqlCommand comm = new SqlCommand();
                comm.Connection = InitConnect();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = "Proc_FindStudentSplitPage";
                //构建参数
                SqlParameter param = new SqlParameter("@PageSize",pageSize);
                comm.Parameters.Add(param);
                param = new SqlParameter("@CurPage", curPage);
                comm .Parameters .Add (param);
                param = new SqlParameter("@TotalPage", SqlDbType.Int);
                param.Direction = ParameterDirection.Output;
                comm.Parameters.Add(param);
                param = new SqlParameter("@TotalCount",SqlDbType .Int);
                param.Direction = ParameterDirection.Output;
                comm.Parameters.Add(param);
                //构建SqlDataReader对象
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string stuNo = dr.GetString(0);
                    string stuName = dr.GetString(1);
                    string stuSex = dr.GetString(2);
                    DateTime stuAge = dr.GetDateTime(3);
                    //实例学生对象
                    StudentVO stu = new StudentVO();
                    stu.StuNo = stuNo;
                    stu.StuName = stuName;
                    stu.StuSex = stuSex;
                    stu.StuAge = stuAge;
                    //把学生对象添加到stuList集合中
                    stuList.Add(stu);
                }
                dr.Close();
                comm.Dispose();
                comm.Connection.Close();
    
                totalPage = Int32.Parse(comm.Parameters[2].Value.ToString());
                totalCount = Int32.Parse(comm.Parameters[3].Value.ToString());

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            //返回stuList集合
            return stuList;
        }
  }
}
3、在页面中添加datalist控件,页面文件如下：

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
      </div>
        <asp:DataList ID="DataList1" runat="server" OnItemDataBound="DataList1_ItemDataBound">
        <ItemTemplate><table >
<tr><td><asp:Label ID="aa" text='<%# DataBinder.Eval (Container.DataItem, "StuNo") %>' runat ="server"  /></td>
<td><asp:Label ID="Label1" text='<%# DataBinder.Eval (Container.DataItem, "StuNo") %>' runat ="server"  /></td>
<td><asp:Label ID="Label2" text='<%# DataBinder.Eval (Container.DataItem, "StuNo") %>' runat ="server"  /></td>
<td><asp:Label ID="Label3" text='<%# DataBinder.Eval (Container.DataItem, "StuNo") %>' runat ="server"  /></td>
</tr>
<asp:DataList ID ="dlScore" runat ="server" >
<ItemTemplate >
<table>
<tr>
<td><%# Eval("id")%></td>
<td><%# Eval("suName")%></td>
<td><%# Eval("sutNo")%></td>
<td><%# Eval("sorce")%></td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
        　 </table></ItemTemplate>
        </asp:DataList>&nbsp;
        <asp:Button ID="btnFirst" runat="server" OnClick="btnFirst_Click" Text="首页" />
        <asp:Button ID="btnPreced" runat="server" OnClick="btnPreced_Click" Text="上一页" />
        <asp:Button ID="btnNext" runat="server" OnClick="btnNext_Click" Text="下一页" />
        <asp:Button ID="btnLast" runat="server" OnClick="btnLast_Click" Text="尾页" />
        共<asp:Label ID="lblRecod" runat="server" Text="Label"></asp:Label>条 当前
        <asp:Label ID="lblpage" runat="server" Text="Label"></asp:Label>页 共<asp:Label ID="lblTotalPage"
            runat="server" Text="Label"></asp:Label>页
    </form>
</body>
</html>
4、cs后台代码

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.IO;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{
        int currPage = 1;   
        int totalPage = 0;
        int totalCount = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bindStudent();
            this.btnPreced .Enabled = false;
        }
    }

    private void bindStudent()
    {
       
        StudentAccessLib.StudentAccess stuAccess = new StudentAccessLib.StudentAccess();

        DataList1.DataSource = stuAccess.FindAllStudentByPage(5,currPage, out totalPage, out totalCount);
        DataList1.DataKeyField = "stuNo";
        DataList1.DataBind();
    
        this.lblpage.Text = currPage.ToString();
        this.lblRecod.Text = totalCount.ToString();
        this.lblTotalPage.Text = totalPage.ToString();
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        currPage =1;
        bindStudent();
        this.btnPreced.Enabled = false;
       
    }
    protected void btnPreced_Click(object sender, EventArgs e)
    {
        currPage = Convert.ToInt32(lblpage.Text) - 1;
        bindStudent();
        if (currPage ==1)
        {
           this.btnPreced .Enabled = false;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        currPage = Convert.ToInt32(lblpage.Text) + 1;
        bindStudent();
        if (currPage == Convert.ToInt32(this.lblTotalPage.Text ))
        {
            btnNext.Enabled = false;
            this.btnPreced.Enabled = true;
        }
    }
    protected void btnLast_Click(object sender, EventArgs e)
    {
        currPage = Convert.ToInt32(this.lblTotalPage .Text);
        bindStudent();
        btnNext.Enabled = false;
        this.btnPreced.Enabled = true;
    }

}