ASP.NET下的B/S模式分页的3种方式:前端分页,后台分页,数据库分页

2023-10-03 22:54:58

本文仅介绍ASP.NET下的分页功能,涉及到的技术有C#,html,css,javascript,jquery,SQLServer

分页一般有3种方式,前端分页,后端分页,数据库分页,本文会一一说明

1.前端分页
前端分页主要的分页逻辑均在前端实现,后台只提供数据,页面初始化时,将数据赋予前端定义好的变量即可,格式为json,下面给出各端实现逻辑

数据库:

CREATE TABLE t_user(
	us_id INT IDENTITY(1,1) PRIMARY KEY,
	us_name VARCHAR(100),
	us_sex VARCHAR(2),
	us_age INT,
	us_phone VARCHAR(20),
	us_address VARCHAR(300)
)

INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小明','男',25,'13111111111','上海市浦东新区1号区')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小红','女',23,'13222222222','上海市青浦区1号区')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小李','女',22,'13333333333','上海市青浦区2号区')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小哄','女',21,'13111112222','上海市青浦区3号区')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小玉','女',13,'13222222222','上海市青浦区5号区')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小红','女',23,'13222222222','上海市浦东新区2号区')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小小','男',33,'13224422222','上海市男区3号区')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小王','男',43,'13224422222','北京')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小华','男',13,'13224423221','湖北')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小三','女',23,'13224422222','湖南')
INSERT INTO t_user(us_name,us_sex,us_age,us_phone,us_address)
VALUES('小贝','女',22,'13224422222','湖南二区')

后端:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace WebAppTest
{
    public partial class WebFormTest : System.Web.UI.Page
    {
        public string testData;

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection("Server=.;user=sa;pwd=guobei;database=testDB");
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM t_user", sqlConnection);
            SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
            DataTable dt = new DataTable();
            sda.Fill(dt);

            testData = table2json(dt);
        }

        public static string table2json(DataTable dt)
        {
            StringBuilder sb = new StringBuilder("[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append("{");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (j == dt.Columns.Count - 1)
                    {
                        sb.Append("\"" + dt.Columns[j].Caption + "\":\"" + dt.Rows[i][j].ToString() + "\"");
                    }
                    else
                    {
                        sb.Append("\"" + dt.Columns[j].Caption + "\":\"" + dt.Rows[i][j].ToString() + "\",");
                    }

                }
                if (i == dt.Rows.Count - 1)
                {
                    sb.Append("}");
                }
                else
                {
                    sb.Append("},");
                }
            }
            sb.Append("]");
            return sb.ToString();
        }
    }
}

前端:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebFormTest.aspx.cs" Inherits="WebAppTest.WebFormTest" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <style type="text/css">


    </style>
</head>
<body>
    <table id="testTable">
        <tr>
            <td>序号</td>
            <td>姓名</td>
            <td>性别</td>
            <td>年龄</td>
            <td>电话</td>
            <td>地址</td>
        </tr>
    </table>

    <div>
        第<span id="currentPage">1</span>页/总<span id="totalPage">1</span>页|总<span id="totalCount">0</span>条 &nbsp;&nbsp;&nbsp;&nbsp;
        <a href="javascript:void(0);" onclick="pageTo('top')">首页</a> &nbsp;&nbsp;
        <a href="javascript:void(0);" onclick="pageTo('next')">下一页</a>&nbsp;&nbsp;
        <a href="javascript:void(0);" onclick="pageTo('pre')">上一页</a>&nbsp;&nbsp;
        <a href="javascript:void(0);" onclick="pageTo('bottom')">末页</a>&nbsp;&nbsp;
        <input id="page" name="page" value="" style="width:50px;"/>
        <a href="javascript:void(0);" onclick="pageTo('assign')">确定</a>&nbsp;&nbsp;
    </div>
</body>

<script src="Scripts/jquery-3.3.1.js"></script>
<script type="text/javascript">
    var pageCount = 5;  //每页显示数量
    var totalPage;      //总页数
    var totalCount;     //总条数
    //后台给的数据
    var testData = eval("(" + '<% =testData%>' + ")");

    window.onload = function () {
        //根据给定的数据初始化总页数和总条数
        totalCount = testData.length;
        totalPage = Math.ceil(totalCount / pageCount);
        //将后台给定的数据testData填充至testTable中
        $("#totalPage").text(totalPage);
        $("#totalCount").text(totalCount);

        dataFill();
    }

    function pageTo(operator) {
        if (operator == "top") {
            $("#currentPage").text("1");
            dataFill();
        }
        else if (operator == "next") {
            if ($("#currentPage").text() != $("#totalPage").text()) {
                $("#currentPage").text(parseInt($("#currentPage").text()) + 1);
                dataFill();
            }
        }
        else if (operator == "pre") {
            if ($("#currentPage").text() != "1") {
                $("#currentPage").text(parseInt($("#currentPage").text()) - 1);
                dataFill();
            }
        }
        else if (operator == "bottom") {
            $("#currentPage").text($("#totalPage").text());
            dataFill();
        }
        else if (operator == "assign") {
            if (parseFloat($("#page").val()).toString() != "NaN") {
                if (parseInt($("#page").val()) < 1) {
                    $("#currentPage").text("1");
                }
                else if (parseInt($("#page").val()) > parseInt($("#totalPage").text())) {
                    $("#currentPage").text($("#totalPage").text());
                }
                else {
                    $("#currentPage").text($("#page").val());
                }
                dataFill();
            }
            
        }
        
    }

    function dataFill() {
        //去掉除第一个tr后面的所有tr元素
        $("#testTable tr:gt(0)").remove();

        //填充的行
        var dataContent = "";

        //根据当前页确定需要从第几位下标开始取数据,目前设定的每页显示3条
        //则第一页时,pageNow为0
        //第二页时,pageNow为3
        var pageNow = (parseInt($("#currentPage").text()) - 1) * pageCount;

        //计数器,循环中使用,执行到每页定义的条数时跳出
        var count = 0;

        for (var i = pageNow; i < testData.length; i++) {
            dataContent += "<tr>";
            dataContent += "<td>" + testData[i].us_id + "</td>";
            dataContent += "<td>" + testData[i].us_name + "</td>";
            dataContent += "<td>" + testData[i].us_sex + "</td>";
            dataContent += "<td>" + testData[i].us_age + "</td>";
            dataContent += "<td>" + testData[i].us_phone + "</td>";
            dataContent += "<td>" + testData[i].us_address + "</td>";
            dataContent += "</tr>";
            
            if (count == pageCount - 1) {
                break;
            }

            count += 1;
        }
        
        $("#testTable").append(dataContent);

    }
</script>

</html>

效果展示:

以上为前端分页功能,是没有做封装的,有需要的小伙伴可以自行做封装,方便到一行代码即可实现分页

2.后台分页
后台分页功能是将分页逻辑放置后台实现,同样,数据库只提供数据,数据库不动

后台

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace WebAppTest
{
    public partial class WebFormTest : System.Web.UI.Page
    {
        //当前页
        public int currentPage;
        //每页显示数
        public int pageCount = 5;
        //总页数
        public int totalPage;
        //总条数
        public int totalCount;
        public DataTable dt= new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                currentPage = Convert.ToInt32(Request["currentPage"]);
            }
            else
            {
                currentPage = 1;
            }

            SqlConnection sqlConnection = new SqlConnection("Server=.;user=sa;pwd=guobei;database=testDB");
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT * FROM t_user", sqlConnection);
            SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
            sda.Fill(dt);

            //初始化分页需要的数据
            //总条数
            totalCount = dt.Rows.Count;     
            //总页数
            totalPage = Convert.ToInt32(Math.Ceiling(totalCount * 1.0 / pageCount));

            
        }

        public void showTable()
        {
            //计数器,用于控制循环次数
            int count = 0;
            //Response.Write(currentPage - 1);
            //Response.End();
            for (int i = (currentPage - 1) * pageCount; i < dt.Rows.Count && i>=0; i++)
            {
                Response.Write("<tr>");
                Response.Write("<td>" + dt.Rows[i]["us_id"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_name"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_sex"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_age"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_phone"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_address"].ToString() + "</td>");
                Response.Write("</tr>");

                if (count++ == pageCount - 1)
                {
                    break;
                }
            }
        }

        public void showPage()
        {

            Response.Write("第"+ currentPage + "页/总"+totalPage+"页|总"+totalCount+"条&nbsp;&nbsp;&nbsp;&nbsp;");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo(1)'>首页</a> &nbsp;&nbsp;");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo("+ (currentPage + 1>totalPage? totalPage: currentPage + 1) + ")'>下一页</a> &nbsp;&nbsp;");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo(" + (currentPage - 1 < 1 ? 1 : currentPage - 1) + ")'>上一页</a> &nbsp;&nbsp;");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo(" + totalPage + ")'>末页</a> &nbsp;&nbsp;");
            Response.Write("<input type='number' id='page' name='page' value='' style='width: 50px;' />");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo($(\"#page\").val())'>确定</a>&nbsp;&nbsp;");
        }
    }
}

前端

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebFormTest.aspx.cs" Inherits="WebAppTest.WebFormTest" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <style type="text/css">


    </style>
</head>
<body>
    <form id="frmShow" name="frmShow" runat="server">
        <input type="hidden" id="currentPage" name="currentPage" value="<%=Request["currentPage"] %>" />
        <table id="testTable">
            <tr>
                <td>序号</td>
                <td>姓名</td>
                <td>性别</td>
                <td>年龄</td>
                <td>电话</td>
                <td>地址</td>
            </tr>
            <%showTable(); %>
        </table>

        <div>
            <%showPage(); %>
        </div>
    </form>
</body>

<script src="Scripts/jquery-3.3.1.js"></script>
<script type="text/javascript">
    function pageTo(currentPage) {
        $("#currentPage").val(currentPage);
        $("#frmShow").submit();
    }
</script>

</html>

效果展示

以上为后端分页功能,同样可以封装后调用,方便实现功能,数据库分页与后台分页类似,只需要调整查询的sql即可,后台仅做数据遍历,前端逻辑不变,下面给出实现

3.数据库分页
后台

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace WebAppTest
{
    public partial class WebFormTest : System.Web.UI.Page
    {
        //当前页
        public int currentPage;
        //每页显示数
        public int pageCount = 5;
        //总页数
        public int totalPage;
        //总条数
        public int totalCount;
        public DataTable dt= new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                currentPage = Convert.ToInt32(Request["currentPage"]);
            }
            else
            {
                currentPage = 1;
            }

            SqlConnection sqlConnection = new SqlConnection("Server=.;user=sa;pwd=guobei;database=testDB");
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(1) FROM t_user", sqlConnection);
            SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
            sda.Fill(dt);

            //初始化分页需要的数据
            //总条数
            totalCount = Convert.ToInt32(dt.Rows[0][0]);     
            //总页数
            totalPage = Convert.ToInt32(Math.Ceiling(totalCount * 1.0 / pageCount));

            string strSQL = @"
                SELECT * FROM (
                    SELECT ROW_NUMBER() OVER(ORDER BY us_id) AS [index],* FROM t_user
                ) a
                WHERE a.[index] BETWEEN {0} AND {1}
            ";
            strSQL = String.Format(strSQL, (currentPage - 1) * pageCount+1, (currentPage - 1) * pageCount + pageCount);
            Response.Write(strSQL);
            sqlCommand = new SqlCommand(strSQL, sqlConnection);
            sda = new SqlDataAdapter(sqlCommand);
            sda.Fill(dt);
        }

        public void showTable()
        {
            for (int i = 0; i < dt.Rows.Count && i>=0; i++)
            {
                Response.Write("<tr>");
                Response.Write("<td>" + dt.Rows[i]["us_id"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_name"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_sex"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_age"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_phone"].ToString() + "</td>");
                Response.Write("<td>" + dt.Rows[i]["us_address"].ToString() + "</td>");
                Response.Write("</tr>");
            }
        }

        public void showPage()
        {

            Response.Write("第"+ currentPage + "页/总"+totalPage+"页|总"+totalCount+"条&nbsp;&nbsp;&nbsp;&nbsp;");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo(1)'>首页</a> &nbsp;&nbsp;");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo("+ (currentPage + 1>totalPage? totalPage: currentPage + 1) + ")'>下一页</a> &nbsp;&nbsp;");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo(" + (currentPage - 1 < 1 ? 1 : currentPage - 1) + ")'>上一页</a> &nbsp;&nbsp;");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo(" + totalPage + ")'>末页</a> &nbsp;&nbsp;");
            Response.Write("<input type='number' id='page' name='page' value='' style='width: 50px;' />");
            Response.Write("<a href='javascript: void(0);' οnclick='pageTo($(\"#page\").val())'>确定</a>&nbsp;&nbsp;");
        }
    }
}

效果和后台分页是一样的,就不重复展示了,以上就是分页的3种方式,下面谈一下各种分页的好处和坏处以及应用场景

1.前端分页
坏处:若数据较多,第一次进入页面加载时较慢,期间若数据出现变更不会立刻反馈到前端
好处:因为不向后台提交请求,所以翻页会很流畅
应用:若数据量在万级以下,且基本不可能会大量增加的情况下可考虑使用前端分页,比如展示一些网站的配置信息等

2.后台分页
坏处:由于翻页会提交请求,所以翻页时用户体验可能会有不同程度的不流畅感,这取决于用户的网络环境,
好处:可以将查出的数据放入内存,不必每次翻页都去查数据库,生命周期为页级,即离开该页面则释放内存

3:数据库分页
数据库分页其实是最方便的,代码可读性可维护性也是最好的,支持的数据量跟前端分页也不是一个量级的,应用也是最广泛的

码农公寓

相关文章