欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

三层+存储过程实现分页

程序员文章站 2023-11-01 17:29:16
前台设计: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind=&qu...

前台设计:

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

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

<html xmlns="https://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">
    <p>
        <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>
    </p>
    </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;
        int pageindex = 1;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["pageindex"] = 1;
                LadaData();
                GetListPageindex();
               
            }
        }

        private void GetListPageindex()
        {
            BLL.T_News1 bnews = new BLL.T_News1();
            int totalcount = bnews.GetRecordCount("");
            if (totalcount % pagesize == 0)
            {
                ViewState["lastpageindex"] = totalcount / pagesize;
            }
            else
            {
                ViewState["lastpageindex"] = totalcount / pagesize + 1;
            }
        }

        private void LadaData()
        {
            BLL.T_News1 bnews = new BLL.T_News1();
            DataTable dt = bnews.GetListDataTable(pagesize, Convert.ToInt32(ViewState["pageindex"]));
            this.GridView1.DataSource = dt;
            this.GridView1.DataBind();
        }

        //第一页
        protected void btnFirst_Click(object sender, EventArgs e)
        {
            ViewState["pageindex"] = 1;
            LadaData();
        }

        //上一页
        protected void btnPre_Click(object sender, EventArgs e)
        {
            int pageindex = Convert.ToInt32(ViewState["pageindex"]);
            if (pagesize>1)
            {
                pageindex--;
                ViewState["pageindex"] = pageindex;
                LadaData();
            }
        }

        //下一页
        protected void btnNext_Click(object sender, EventArgs e)
        {
            int pageindex = Convert.ToInt32(ViewState["pageindex"]);
            if (pageindex<Convert.ToInt32(ViewState["lastpageindex"]))
            {
                pageindex++;
                ViewState["pageindex"] = pageindex;
                LadaData();
            }
        }

        //最后一页
        protected void btnLast_Click(object sender, EventArgs e)
        {
            ViewState["pageindex"] = ViewState["lastpageindex"];
            LadaData();
        }
       
        //跳转页面
        protected void btnSkip_Click(object sender, EventArgs e)
        {
            int result;
            if (int.TryParse(txtPagination.Text, out result) == true)
            {
                ViewState["pageindex"] = txtPagination.Text.Trim();
                LadaData();
            }
            else
            {
                txtPagination.Text = "请输入合法的数字";
            }
        }
    }
}