本文仅介绍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>条
<a href="javascript:void(0);" onclick="pageTo('top')">首页</a>
<a href="javascript:void(0);" onclick="pageTo('next')">下一页</a>
<a href="javascript:void(0);" onclick="pageTo('pre')">上一页</a>
<a href="javascript:void(0);" onclick="pageTo('bottom')">末页</a>
<input id="page" name="page" value="" style="width:50px;"/>
<a href="javascript:void(0);" onclick="pageTo('assign')">确定</a>
</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+"条 ");
Response.Write("<a href='javascript: void(0);' οnclick='pageTo(1)'>首页</a> ");
Response.Write("<a href='javascript: void(0);' οnclick='pageTo("+ (currentPage + 1>totalPage? totalPage: currentPage + 1) + ")'>下一页</a> ");
Response.Write("<a href='javascript: void(0);' οnclick='pageTo(" + (currentPage - 1 < 1 ? 1 : currentPage - 1) + ")'>上一页</a> ");
Response.Write("<a href='javascript: void(0);' οnclick='pageTo(" + totalPage + ")'>末页</a> ");
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> ");
}
}
}
前端
<%@ 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+"条 ");
Response.Write("<a href='javascript: void(0);' οnclick='pageTo(1)'>首页</a> ");
Response.Write("<a href='javascript: void(0);' οnclick='pageTo("+ (currentPage + 1>totalPage? totalPage: currentPage + 1) + ")'>下一页</a> ");
Response.Write("<a href='javascript: void(0);' οnclick='pageTo(" + (currentPage - 1 < 1 ? 1 : currentPage - 1) + ")'>上一页</a> ");
Response.Write("<a href='javascript: void(0);' οnclick='pageTo(" + totalPage + ")'>末页</a> ");
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> ");
}
}
}
效果和后台分页是一样的,就不重复展示了,以上就是分页的3种方式,下面谈一下各种分页的好处和坏处以及应用场景
1.前端分页
坏处:若数据较多,第一次进入页面加载时较慢,期间若数据出现变更不会立刻反馈到前端
好处:因为不向后台提交请求,所以翻页会很流畅
应用:若数据量在万级以下,且基本不可能会大量增加的情况下可考虑使用前端分页,比如展示一些网站的配置信息等
2.后台分页
坏处:由于翻页会提交请求,所以翻页时用户体验可能会有不同程度的不流畅感,这取决于用户的网络环境,
好处:可以将查出的数据放入内存,不必每次翻页都去查数据库,生命周期为页级,即离开该页面则释放内存
3:数据库分页
数据库分页其实是最方便的,代码可读性可维护性也是最好的,支持的数据量跟前端分页也不是一个量级的,应用也是最广泛的
扫一扫
158
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
