需要用到的sql语句
第一条SQL：查询总数，返回一个数字（总记录数）
select count(*) from 表名第二条SQL：分页语句
select top 每页数据条数 * from 表名 where id not in (select top ((当前页数-1)*每页数据条数) id from 表名)二．编码
1.1 编写SQL脚本
create database Paging
go
use Paging
go
create table Catgory
(
id int primary key identity,
name varchar(20),
)
go
create table Article
(
id int primary key identity,
title nvarchar(20),
author nvarchar(10),
price float,
cid int foreign key references Catgory(id)
)
go
insert into Catgory values
('学术'),('八卦'),('娱乐')
go
insert into Article values
('文章1','张三',11,1),
('文章2','张三',12,1),
('文章3','张三',13,3),
('文章4','张三',14,1),
('文章5','张三',15,2),
('文章6','张三',16,1),
('文章7','张三',17,3),
('文章8','张三',18,1),
('文章9','张三',19,1),
('文章10','张三',20,2),
('文章11','张三',21,2),
('文章12','张三',22,3),
('文章13','张三',23,1),
('文章14','张三',24,3),
('文章15','张三',25,3),
('文章16','张三',26,1),
('文章17','张三',27,1),
('文章18','张三',28,2),
('文章19','张三',29,3),
('文章20','张三',30,2)
go
select * from Article
select * from Catgory
-- select top 每页数据条数 * from 表名 where id not in ( select top ((当前页数-1)*每页数据条数) id from 表名)
select top 5 * from Article where id not in (select top ((2-1)* 5) id from Article)1.创建工程，添加bootstrap模板应用
构建 _Layout.cshtml
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>分页查询案例</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
</head>
<body>
<div class="container body-content">
@RenderBody()
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>
2.使用布局页
3.导入模型
4.编写控制器
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace Demo1.Controllers
{
public class PageController : Controller
{
// GET: Pag
public ActionResult Index(int pageIndex = 1, int pageSize = 3)
{
using (PagingEntities db = new PagingEntities())
{
var articles = db.Article.ToList();
var res = db.Article.Include("Catgory")
.OrderBy(p => p.id)
.Skip((pageIndex - 1) * pageSize) //跳过前n个结果，返回剩余的结果。
.Take(pageSize) //从查询结果中提取前n个结果
.ToList();
ViewBag.pageIndex = pageIndex;
ViewBag.pageSize = pageSize;
//计算总数
ViewBag.totalRows = articles.Count;
//计算共有多少页
ViewBag.totalPage = Math.Ceiling(articles.Count * 1.0 / pageSize);
return View(res);
}
}
}
}
4.编写页面代码
<div>
<table class="table table-striped">
<thead>
<tr style="background-color:burlywood">
<td>编号</td>
<td>名称</td>
<td>作者</td>
<td>价格</td>
<td>类型</td>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.id</td>
<td>@item.title</td>
<td>@item.author</td>
<td>@item.price</td>
<td>@item.Catgory.name</td>
</tr>
}
</tbody>
</table>
<div>
<nav aria-label="Page navigation example" style="display:flex;justify-content:space-between;">
<ul class="pagination">
<li class="page-item"><a class="page-link" href="javascript:page(1)">首页</a></li>
<li class="page-item"><a class="page-link" href="javascript:page(@(ViewBag.pageIndex-1))">上一页</a></li>
<li class="page-item"><a class="page-link" href="#">@ViewBag.pageIndex</a></li>
<li class="page-item"><a class="page-link" href="javascript:page(@(ViewBag.pageIndex+1))">下一页</a></li>
<li class="page-item"><a class="page-link" href="javascript:page(@ViewBag.totalPage)">尾页</a></li>
</ul>
<div>
共 @ViewBag.totalRows 条数据，
每页显示
<select id="pageSize" onchange="page()">
<option value="2">2</option>
<option value="5">5</option>
<option value="10">10</option>
<option value="15">15</option>
<option value="20">20</option>
</select>
条数据,
前往第 <input type="number" id="txtPageIndex" name="name" value="@ViewBag.pageIndex" style="width:50px;" />页
<button id="go" class="btn">GO</button>
</div>
</nav>
</div>
</div>
@section scripts{
<script>
$(function () {
$("#pageSize").val(@ViewBag.pageSize)
})
function page(pageIndex) {
if (pageIndex < 1) {
alert("已经是第一页");
return;
}
if (pageIndex > @ViewBag.totalPage) {
alert("没有下一下页");
return;
}
let pageSize=$("#pageSize").val()
window.location.href = "/Page/index?pageIndex=" + pageIndex+"&pageSize="+pageSize
}
$("#go").click(function () {
let pageIndex = $("#txtPageIndex").val();
page(pageIndex)
})
</script>
}