SQl语句进行分页
SQL语句进行分页主要是应用Entity FrameWork的SqlQuery()传入SQL语句进行查询时分页。
效果展示。
页面代码展示，显示是用Repeater控件进行动态显示
<%@ Page Title="" Language="C#" MasterPageFile="~/HoTai/HoTaiGuanLi.Master" AutoEventWireup="true" CodeBehind="HoTaiYingYYM.aspx.cs" Inherits="OLMS.HoTai.HoTaiYingYYM" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<link href="../Assets/css/input.css" rel="stylesheet" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<div class="pure-form">
<fieldset>
<legend>后台管理<i class="fa fa-angle-double-right"></i>类型管理
<asp:Button ID="Button1" CssClass=" pure-button pure-button-primary tools-button" runat="server" Text="添加音乐类型" OnClick="Button1_Click" />
</legend>
</fieldset>
</div>
<div>
<table class="table table-striped">
<thead>
<tr>
<th scope="col">编号</th>
<th scope="col">应用名称</th>
<th scope="col">类型名称</th>
<th scope="col">歌手名称</th>
<th scope="col">歌手价格</th>
<th scope="col">操作</th>
</tr>
</thead>
<tbody>
<asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand">
<ItemTemplate>
<tr>
<th scope="row"><%# Eval("AlbumId") %></th> //表的id
<td><%# Eval("Title") %></td>
<td><%# Eval("Genres.Name") %></td>
<td><%# Eval("Artists.Name") %></td>
<td><%# Eval("Price") %></td>
<td>
<asp:LinkButton ID="LinkButton1" CssClass="btn btn-primary" CommandArgument='<%# Eval("AlbumId") %>' CommandName="dianjia" runat="server">编辑</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" CssClass="btn btn-light" CommandArgument='<%# Eval("AlbumId") %>' CommandName="delete" runat="server">删除</asp:LinkButton></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</tbody>
</table>
</div>
<div>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:Button ID="Button2" runat="server" Text="首页" OnClick="Button2_Click" />
<asp:Button ID="Button3" runat="server" Text="上一页" OnClick="Button3_Click" />
<asp:Button ID="Button4" runat="server" Text="下一页" OnClick="Button4_Click" />
<asp:Button ID="Button5" runat="server" Text="末页" OnClick="Button5_Click" />
</div>
</asp:Content>
后台事件代码
using OLMS.DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace OLMS.HoTai
{
public partial class HoTaiYingYYM : System.Web.UI.Page
{
static int x = 0;
static int i = 1;
static int zyes;
static int ztian;
static int tianos = 5;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//刷新
Select();
}
}
private void Select()
{
using (OLMSDBEntities db = new OLMSDBEntities())
{
string sql = $"select * from Albums order by AlbumId offset {x} rows fetch next 5 rows only";
Repeater1.DataSource = db.Albums.SqlQuery(sql).ToList();
Repeater1.DataBind();
var fy = db.Albums.ToList();
ztian = fy.Count;
zyes = ztian / tianos;
Label1.Text = $"每页5条/共{ztian}条 第{i}页/共{zyes + 1}页";
}
}
protected void Button2_Click(object sender, EventArgs e)
{
//首页
x = 0;
i = 1;
Select();
}
protected void Button4_Click(object sender, EventArgs e)
{
//下一页
x += 5;
i++;
if (i >= zyes)
{
x = zyes * 5;
i = zyes + 1;
}
if (i >= (zyes + 1))
{
string strUrl = "<script>alert('已到末页');</script>";
Response.Write(strUrl);
}
else
{
Select();
}
}
protected void Button3_Click(object sender, EventArgs e)
{
//上一页
x -= 5;
i--;
if (x < 0)
{
x = 0;
i = 1;
}
if (x <= 0)
{
string strUrl = "<script>alert('已到第一页');</script>";
Response.Write(strUrl);
}
else
{
Select();
}
}
protected void Button5_Click(object sender, EventArgs e)
{
//末页
x = zyes * 5;
i = zyes + 1;
Select();
}
}
}
Skip().Take()进行分页
Skip().Take()进行分页是应用Entity FrameWork的Skip(起始行).Take(每页多少行)方法来进行分页
页面结构和上面是一样的，后台事件代码有区别
using OLMS.DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace OLMS.HoTai
{
public partial class HoTaiYingYYM : System.Web.UI.Page
{
static int i = 1; //起始页数
static int zyes; //总条数
static int ztian;//总页数
static int tianos = 5; //每页多少条
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//刷新
Select();
}
}
private void Select()
{
using (OLMSDBEntities db = new OLMSDBEntities())
{
var fy = db.Albums.ToList();
ztian = fy.Count;
zyes = ztian / tianos;
//起始行数 每页多少条
Repeater1.DataSource = db.Albums.ToList().Skip((i - 1) * tianos).Take(tianos).ToList();
Repeater1.DataBind();
Label1.Text = $"每页{tianos}条/共{fy.Count}条 第{i}页/共{(fy.Count / 5) + 1}页";
}
}
protected void Button2_Click(object sender, EventArgs e)
{
i = 1;
Select();
}
protected void Button4_Click(object sender, EventArgs e)
{
i++;
if (i >= zyes)
{
i = zyes + 1;
}
if (i >= (zyes + 1))
{
string strUrl = "<script>alert('已到末页');</script>";
Response.Write(strUrl);
}
else
{
Select();
}
}
protected void Button3_Click(object sender, EventArgs e)
{
i--;
if (i < 0)
{
i = 1;
}
if (i <= 0)
{
string strUrl = "<script>alert('已到第一页');</script>";
Response.Write(strUrl);
}
else
{
Select();
}
}
protected void Button5_Click(object sender, EventArgs e)
{
i = zyes + 1;
Select();
}
}
}
总结
Skip().Take()进行分页方法和SQl语句进行分页总体代码差不太多，按具体需要来进行选择。
