ASP.NET中分页的实现

实现环境:SQL Server 2005 Express + VWD 2008 Express Edition
说明：关系数据库及表的详细情况请参看我的上一篇文章《实例讲解用.NET技术将Excel表格中的数据导入到特定的数据库中》。
第一步:存储过程GetRecordFromPage
代码:

ALTER PROCEDURE GetRecordFromPage 
	@PageIndex int,
	@PageSize int,
	@RecordCount int out,
	@PageCount int out
AS
BEGIN
	select @RecordCount=Count(*) from UserInfo
	select @PageCount=ceiling(@RecordCount/@PageSize)
	select * from
	(select u.Id,u.UserName,u.UserAddress,u.UserTelephone,c.Model,u.InstallationDate,u.Amount,u.AccessoriesModel,u.InstallationName,u.Notes,Row_Number() over (order by u.Id) as RowNumber from UserInfo u inner join Category c on u.Caid=c.Id) as temp_table 
	 where temp_table.RowNumber>(@PageIndex*@PageSize) and temp_table.RowNumber<=((@PageIndex+1)*@PageSize) 	
END

说明:该存储教程中用了两个输入参数@PageIndex和@PageSize,用以表示页码和每页显示的数据记录条数，注意PageIndex是实际显示的页码数值减1；两个输出参数@RecordCount和@PageCount,用以表示表中总的记录数和可以分的页码数量。
原理：将两个有关联的表Category和UserInfo中的数据选择出来，并根据行数添加一字段RowNumber放到一临时表temp_table中,之后再从该临时表中查找指定页码的记录。
第二步:在DAL层添加方法：
(1)RecordPage方法,功能:传入页码和每页要显示的记录数，返回所需要的记录
代码:

        public DataTable RecordPage(int pageIndex, int pageSize)
        {
            DataTable dt = new DataTable();
            SqlDataReader sdr = null;
            string [email protected]"server=PC2009080519VDZ\SQLEXPRESS;database=yuajiasys;uid=sa;pwd=123456";
            SqlConnection conn=new SqlConnection(connStr);
            conn.Open();
            SqlCommand cmd=new SqlCommand("GetRecordFromPage",conn);
            cmd.CommandType=CommandType.StoredProcedure;
            cmd.Parameters.Add("@PageIndex",SqlDbType.Int);
            cmd.Parameters["@PageIndex"].Value = pageIndex;
            cmd.Parameters.Add("@PageSize",SqlDbType.Int);
            cmd.Parameters["@PageSize"].Value = pageSize;
            cmd.Parameters.Add("@RecordCount",SqlDbType.Int);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@PageCount", SqlDbType.Int);
            cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
            sdr = cmd.ExecuteReader();
            dt.Load(sdr);
            sdr.Close();
            conn.Close();
            return dt;
        }

(2)RecordPageCount方法,功能:返回总的页数。
代码:

        public int RecordPageCount(int pageIndex, int pageSize)
        {
            int recordCount = 0;
            int pageCount = 0;
            DataTable dt = new DataTable();
            SqlDataReader sdr = null;
            string connStr = @"server=PC2009080519VDZ\SQLEXPRESS;database=yuajiasys;uid=sa;pwd=123456";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand("GetRecordFromPage", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int);
            cmd.Parameters["@PageIndex"].Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int);
            cmd.Parameters["@PageSize"].Value = pageSize;
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@PageCount", SqlDbType.Int);
            cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
            sdr = cmd.ExecuteReader();
            dt.Load(sdr);
            sdr.Close();
            conn.Close();
            recordCount = (int)cmd.Parameters["@RecordCount"].Value;
            pageCount = (int)cmd.Parameters["@PageCount"].Value;
            return pageCount;
        }

第三步:在网页中调用:
当然，首先要在BLL层写相应的RecordPage()和RecordPageCount()方法代码。
(1)前台:

    ......
    <table>
        <tr><th>ID号</th><th>姓名</th><th>地址</th><th>电话</th><th>机型</th><th>安装日期</th><th>RowNumber</th></tr>
        <asp:Repeater ID="repuserinfo" runat="server">
            <ItemTemplate>
                <tr>
                    <td><%# Eval("Id") %></td>
                    <td><%# Eval("UserName") %></td>
                    <td><%# Eval("UserAddress") %></td>
                    <td><%# Eval("UserTelephone") %></td>
                    <td><%# Eval("Model") %></td>
                    <td><%# Eval("InstallationDate") %></td>
                    <td><%# Eval("RowNumber") %></td>
                </tr>
            </ItemTemplate>
        </asp:Repeater>
    </table>
    <p id="pginfo">
        第<asp:Label ID="lbpgIndex" runat="server" Text=""></asp:Label>页
        共<asp:Label ID="lbpgCount" runat="server" Text=""></asp:Label>页 
        转到第<asp:TextBox ID="txttopgIndex" CssClass="pginput" runat="server"></asp:TextBox>页
        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="txttopgIndex" runat="server" ErrorMessage="请输入页码" Text="*" ValidationGroup="pginfo"></asp:RequiredFieldValidator>
        <asp:Button ID="btntopgIndex" runat="server" Text="Go" onclick="btntopgIndex_Click" ValidationGroup="pginfo" />
        <asp:RegularExpressionValidator ID="RegularExpressionValidator1" ControlToValidate="txttopgIndex" ValidationExpression="\d{1}[0-9]|\d{1}" runat="server" ErrorMessage="请输入数值" ValidationGroup="pginfo"></asp:RegularExpressionValidator>
        <asp:ValidationSummary ID="ValidationSummary1" runat="server" ShowMessageBox="true" ShowSummary="false" ValidationGroup="pginfo" />
    </p>
   ......

(2)后台:

    ......
    private int pgSize = 100;
    protected void Page_Load(object sender, EventArgs e)
    {
        int pgCount = new UserInfoManager().RecordPageCount(1, pgSize);
        DataTable dt = new DataTable();
        dt = new UserInfoManager().RecordPage(pgCount, pgSize);
        repuserinfo.DataSource = dt;
        repuserinfo.DataBind();
        lbpgIndex.Text = Convert.ToString(pgCount+1);
        lbpgCount.Text = Convert.ToString(pgCount+1);
    }
    protected void btntopgIndex_Click(object sender, EventArgs e)
    {
        int pgIndex = int.Parse(txttopgIndex.Text);
        int pgCount = new UserInfoManager().RecordPageCount(1, pgSize);
        DataTable dt = new DataTable();
        pgIndex -= 1;
        dt = new UserInfoManager().RecordPage(pgIndex, pgSize);
        repuserinfo.DataSource = dt;
        repuserinfo.DataBind();
        lbpgIndex.Text = Convert.ToString(pgIndex+1);
        lbpgCount.Text = Convert.ToString(pgCount+1);
        txttopgIndex.Text = "";
    }
    ......