三层+存储过程实现分页

前台设计：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="paging.aspx.cs" Inherits="五二一练习.paging" %>

<!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>
    <script src="js/Jquery1.7.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(function () {
            $('#txtPagination').focus(function () {
                $(this).val("");
            })
        })
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            Height="336px" Width="685px">
            <Columns>
                <asp:BoundField DataField="Id" HeaderText="编号" />
                <asp:BoundField DataField="NewsTitle" HeaderText="标题" />
                <asp:BoundField DataField="NewsContent" HeaderText="内容" />
                <asp:BoundField DataField="CreateTime"
                    DataFormatString="{0:yyyy-MM-dd hh:mm:ss}" HeaderText="发布时间" />
            </Columns>
        </asp:GridView>
        <asp:LinkButton ID="btnFirst" runat="server" onclick="btnFirst_Click">第一页</asp:LinkButton>
        <asp:LinkButton
            ID="btnPre" runat="server" onclick="btnPre_Click">上一页</asp:LinkButton>
        <asp:LinkButton ID="btnNext"
                runat="server" onclick="btnNext_Click">下一页</asp:LinkButton>
        <asp:LinkButton ID="btnLast" runat="server" onclick="btnLast_Click">最后一页</asp:LinkButton><asp:TextBox
                    ID="txtPagination" runat="server"></asp:TextBox>
        <asp:LinkButton ID="btnSkip" runat="server" onclick="btnSkip_Click">GO</asp:LinkButton>
    </div>
    </form>
</body>
</html>

首先在数据库创建存储过程

create proc usp_role_GetDateByPageIndex
@pageSize int,
@pageIndex int
as
begin
select * from
(
select *,ROW_NUMBER() over(order by role_id) as rownumber from role) as tbl
where tbl.rownumber between (@pageSize*(@pageIndex-1)+1) and @pageIndex*@pageSize
end

exec usp_role_GetDateByPageIndex 5,3

在项目中添加BLL,DAL,DataAccess,MODEL层

在DAL中写一个方法：

        //自己写的方法，分页获取数据列表
        public DataTable GetListDataTable(int PageSize, int PageIndex)
        {
            SqlParameter[] parameters = {

     new SqlParameter("@PageSize", SqlDbType.Int),
     new SqlParameter("@PageIndex", SqlDbType.Int)
     };

            parameters[0].Value = PageSize;
            parameters[1].Value = PageIndex;
            return DbHelperSQL.RunProcedureDataTable("usp_role_GetDateByPageIndex", parameters);
        }

在BLL中调用GetListDataTable：

        public DataTable GetListDataTable(int pagesize, int pageindex)
        {
            return dal.GetListDataTable(pagesize, pageindex);
        }

在DbHelper中添加RunProcedureDataTable方法：

        public static DataTable RunProcedureDataTable(string stroreProcName, IDataParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataTable dt = new DataTable();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, stroreProcName, parameters);
                sqlDA.Fill(dt);
                connection.Close();
                return dt;
            }
        }

然后在后台调用即可：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace 练习
{
    public partial class paging : System.Web.UI.Page
    {
        int pagesize = 10;
    &nb

补充：Web开发 , ASP.Net ,