GridView自定义分页的四种存储过程
发布时间:2024-01-27 09:52:25
1. 为什么不使用GridView的默认分页功能
首先要说说为什么不用GridView的默认的分页功能,GridView控件并非真正知道如何获得一个新页面,它只是请求绑定的数据源控件返回适合规定页面的行,分页最终是由数据源控件完成。当我们使用SqlDataSource或使用以上的代码处理分页时。每次这个页面被请求或者回发时,所有和这个SELECT语句匹配的记录都被读取并存储到一个内部的DataSet中,但只显示适合当前页面大小的记录数。也就是说有可能使用Select语句返回1000000条记录,而每次回发只显示10条记录。如果启用了SqlDataSource上的缓存,通过把EnableCaching设置为true,则情况会更好一些。在这种情况下,我们只须访问一次数据库服务器,整个数据集只加载一次,并在指定的期限内存储在ASP.NET缓存中。只要数据保持缓存状态,显示任何页面将无须再次访问数据库服务器。然而,可能有大量数据存储在内存中,换而言之,Web服务器的压力大大的增加了。因此,如果要使用SqlDataSource来获取较小的数据时,GridView内建的自动分页可能足够高效了,但对于大数据量来说是不合适的。
2. 分页的四种存储过程(分页+排序的版本请参考Blog里其他文章)
在大多数情况下我们使用存储过程来进行分页,今天有空总结了一下使用存储过程对GridView进行分页的4种写法(分别是使用Top关键字,临时表,临时表变量和SQL Server 2005 新加的Row_Number()函数)
后续的文章中还将涉及GridView控件使用ObjectDataSource自定义分页 + 排序,Repeater控件自定义分页 + 排序,有兴趣的朋友可以参考。
if exists(select 1 from sys.objects where name = 'GetProductsCount' and type = 'P')
drop proc GetProductsCount
go
CREATE PROCEDURE GetProductsCount
as
select count(*) from products
go
--1.使用Top
if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
drop proc GetProductsByPage
go
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
declare @sql nvarchar(4000)
set @sql = 'select top ' + Convert(varchar, @PageSize)
+ ' * from products where productid not in (select top ' + Convert(varchar, (@PageNumber - 1) * @PageSize) + ' productid from products)'
exec sp_executesql @sql
go
--exec GetProductsByPage 1, 10
--exec GetProductsByPage 5, 10
--2.使用临时表
if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
drop proc GetProductsByPage
go
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
-- 创建临时表
CREATE TABLE #TempProducts
(
ID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName varchar(40) ,
SupplierID int,
CategoryID int,
QuantityPerUnit nvarchar(20),
UnitPrice money,
UnitsInStock smallint,
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit
)
-- 填充临时表
INSERT INTO #TempProducts
(ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
DECLARE @FromID int
DECLARE @ToID int
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM #TempProducts
WHERE ID >= @FromID AND ID <= @ToID
go
--exec GetProductsByPage 1, 10
--exec GetProductsByPage 5, 10
--3.使用表变量
/*
为要分页的数据创建一个table变量,这个table变量里有一个作为主健的IDENTITY列.这样需要分页的每条记录在table变量里就和一个row index(通过IDENTITY列)关联起来了.一旦table变量产生,连接数据库表的SELECT语句就被执行,获取需要的记录.SET ROWCOUNT用来限制放到table变量里的记录的数量.
当SET ROWCOUNT的值指定为PageNumber * PageSize时,这个方法的效率取决于被请求的页数.对于比较前面的页来说– 比如开始几页的数据– 这种方法非常有效. 但是对接近尾部的页来说,这种方法的效率和默认分页时差不多
*/
if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
drop proc GetProductsByPage
go
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
DECLARE @TempProducts TABLE
(
ID int IDENTITY,
productid int
)
DECLARE @maxRows int
SET @maxRows = @PageNumber * @PageSize
--在返回指定的行数之后停止处理查询
SET ROWCOUNT @maxRows
INSERT INTO @TempProducts (productid)
SELECT productid
FROM products
ORDER BY productid
SET ROWCOUNT @PageSize
SELECT p.*
FROM @TempProducts t INNER JOIN products p
ON t.productid = p.productid
WHERE ID > (@PageNumber - 1) * @PageSize
SET ROWCOUNT 0
GO
--exec GetProductsByPage 1, 10
--exec GetProductsByPage 5, 10
--4.使用row_number函数
--SQL Server 2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关 这个等级可以用来作为每条记录的row index.
if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
drop proc GetProductsByPage
go
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
select ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
from
(select row_number() Over (order by productid) as row,ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
from products) as ProductsWithRowNumber
where row between (@PageNumber - 1) * @PageSize + 1 and @PageNumber * @PageSize
go
--exec GetProductsByPage 1, 10
--exec GetProductsByPage 5, 10
3. 在GridView中的应用
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewPaging.aspx.cs" Inherits="GridViewPaging" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Paging</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:LinkButton id="lbtnFirst" runat="server" CommandName="First" OnCommand="lbtnPage_Command">|<</asp:LinkButton>
<asp:LinkButton id="lbtnPrevious" runat="server" CommandName="Previous" OnCommand="lbtnPage_Command"><<</asp:LinkButton>
<asp:Label id="lblMessage" runat="server" />
<asp:LinkButton id="lbtnNext" runat="server" CommandName="Next" OnCommand="lbtnPage_Command">>></asp:LinkButton>
<asp:LinkButton id="lbtnLast" runat="server" CommandName="Last" OnCommand="lbtnPage_Command">>|</asp:LinkButton>
转到第<asp:DropDownList ID="dropPage" runat="server" AutoPostBack="True" OnSelectedIndexChanged="dropPage_SelectedIndexChanged"></asp:DropDownList>页
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False" ReadOnly="True" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName" />
<asp:BoundField DataField="SupplierID" HeaderText="SupplierID" />
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" />
<asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" />
<asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" />
<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True" ProviderName="System.Data.SqlClient" SelectCommand="GetProductsByPage" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSource1_Selecting" OnSelected="SqlDataSource1_Selected">
<SelectParameters>
<asp:Parameter Name="PageNumber" Type="Int32" />
<asp:Parameter Name="PageSize" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewPaging.aspx.cs" Inherits="GridViewPaging" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Paging</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:LinkButton id="lbtnFirst" runat="server" CommandName="First" OnCommand="lbtnPage_Command">|<</asp:LinkButton>
<asp:LinkButton id="lbtnPrevious" runat="server" CommandName="Previous" OnCommand="lbtnPage_Command"><<</asp:LinkButton>
<asp:Label id="lblMessage" runat="server" />
<asp:LinkButton id="lbtnNext" runat="server" CommandName="Next" OnCommand="lbtnPage_Command">>></asp:LinkButton>
<asp:LinkButton id="lbtnLast" runat="server" CommandName="Last" OnCommand="lbtnPage_Command">>|</asp:LinkButton>
转到第<asp:DropDownList ID="dropPage" runat="server" AutoPostBack="True" OnSelectedIndexChanged="dropPage_SelectedIndexChanged"></asp:DropDownList>页
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False" ReadOnly="True" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName" />
<asp:BoundField DataField="SupplierID" HeaderText="SupplierID" />
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" />
<asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" />
<asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" />
<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True" ProviderName="System.Data.SqlClient" SelectCommand="GetProductsByPage" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSource1_Selecting" OnSelected="SqlDataSource1_Selected">
<SelectParameters>
<asp:Parameter Name="PageNumber" Type="Int32" />
<asp:Parameter Name="PageSize" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class GridViewPaging : System.Web.UI.Page
{
//每页显示的最多记录的条数
private int pageSize = 10;
//当前页号
private int currentPageNumber;
//显示数据的总条数
private static int rowCount;
//总页数
private static int pageCount;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("GetProductsCount", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
rowCount = (int)cmd.ExecuteScalar();
cn.Close();
pageCount = (rowCount - 1) / pageSize + 1;
currentPageNumber = 1;
ViewState["currentPageNumber"] = currentPageNumber;
lbtnPrevious.Enabled = false;
lbtnFirst.Enabled = false;
for (int i = 1; i <= pageCount; i++)
{
dropPage.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
}
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
SqlDataSource1.SelectParameters["PageNumber"].DefaultValue = currentPageNumber.ToString();
SqlDataSource1.SelectParameters["PageSize"].DefaultValue = pageSize.ToString();
}
protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
lblMessage.Text = "共找到" + rowCount + "条记录, 当前第" + currentPageNumber + "/" + pageCount + "页";
}
protected void lbtnPage_Command(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case "First":
currentPageNumber = 1;
break;
case "Previous":
currentPageNumber = (int)ViewState["currentPageNumber"] - 1 > 1 ? (int)ViewState["currentPageNumber"] - 1 : 1;
break;
case "Next":
currentPageNumber = (int)ViewState["currentPageNumber"] + 1 < pageCount ? (int)ViewState["currentPageNumber"] + 1 : pageCount;
break;
case "Last":
currentPageNumber = pageCount;
break;
}
dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
ViewState["currentPageNumber"] = currentPageNumber;
SetButton(currentPageNumber);
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
private void SetButton(int currentPageNumber)
{
lbtnFirst.Enabled = currentPageNumber != 1;
lbtnPrevious.Enabled = currentPageNumber != 1;
lbtnNext.Enabled = currentPageNumber != pageCount;
lbtnLast.Enabled = currentPageNumber != pageCount;
}
protected void dropPage_SelectedIndexChanged(object sender, EventArgs e)
{
currentPageNumber = int.Parse(dropPage.SelectedValue);
ViewState["currentPageNumber"] = currentPageNumber;
SetButton(currentPageNumber);
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
}
[/code]
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class GridViewPaging : System.Web.UI.Page
{
//每页显示的最多记录的条数
private int pageSize = 10;
//当前页号
private int currentPageNumber;
//显示数据的总条数
private static int rowCount;
//总页数
private static int pageCount;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("GetProductsCount", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
rowCount = (int)cmd.ExecuteScalar();
cn.Close();
pageCount = (rowCount - 1) / pageSize + 1;
currentPageNumber = 1;
ViewState["currentPageNumber"] = currentPageNumber;
lbtnPrevious.Enabled = false;
lbtnFirst.Enabled = false;
for (int i = 1; i <= pageCount; i++)
{
dropPage.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
}
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
SqlDataSource1.SelectParameters["PageNumber"].DefaultValue = currentPageNumber.ToString();
SqlDataSource1.SelectParameters["PageSize"].DefaultValue = pageSize.ToString();
}
protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
lblMessage.Text = "共找到" + rowCount + "条记录, 当前第" + currentPageNumber + "/" + pageCount + "页";
}
protected void lbtnPage_Command(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case "First":
currentPageNumber = 1;
break;
case "Previous":
currentPageNumber = (int)ViewState["currentPageNumber"] - 1 > 1 ? (int)ViewState["currentPageNumber"] - 1 : 1;
break;
case "Next":
currentPageNumber = (int)ViewState["currentPageNumber"] + 1 < pageCount ? (int)ViewState["currentPageNumber"] + 1 : pageCount;
break;
case "Last":
currentPageNumber = pageCount;
break;
}
dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
ViewState["currentPageNumber"] = currentPageNumber;
SetButton(currentPageNumber);
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
private void SetButton(int currentPageNumber)
{
lbtnFirst.Enabled = currentPageNumber != 1;
lbtnPrevious.Enabled = currentPageNumber != 1;
lbtnNext.Enabled = currentPageNumber != pageCount;
lbtnLast.Enabled = currentPageNumber != pageCount;
}
protected void dropPage_SelectedIndexChanged(object sender, EventArgs e)
{
currentPageNumber = int.Parse(dropPage.SelectedValue);
ViewState["currentPageNumber"] = currentPageNumber;
SetButton(currentPageNumber);
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
}
[/code]
4.分页效果图:
猜你喜欢
- 1 用mysql客户端登入 2 选择数据库 mysql>use test 3 查询当前数据库有哪些存储过程 mysql>show
- 1. 正则表达式基础1.1. 简单介绍正则表达式并不是Python的一部分。正则表达式是用于处理字符串的强大工具,拥有自己独特的语法以及一个
- 除了使用xlrd库或者xlwt库进行对excel表格的操作读与写,而且pandas库同样支持excel的操作;且pandas操作更加简介方便
- 作者:xiaolanLin声明 :本文版权归作者和博客园共有,来源网址:https://www.cnblogs.com/xiaolan-Li
- 引言最近刚刚用python写完了一个解析protobuf文件的简单编译器,深感ply实现词法分析和语法分析的简洁方便。乘着余热未过,头脑清醒
- 开篇:测试过程中,对于多参数参数多值的情况进行测试用例组织,之前一直使用【正交分析法】进行用例组织,说白了就是把每个参数的所有值分别和其他参
- 在曾经的 淘宝UED 招聘 中有这样一道题目:“使用纯CSS实现未知尺寸的图片(但高宽都小于200px)在200px的正方形容器中水平和垂直
- 1. 背景最近在爬取某个站点时,发现在POST数据时,使用的数据格式是request payload,有别于之前常见的 POST数据格式(F
- 一.思路1.整体思路2.代码思路思路很简单,就是用python发送请求,提取响应体中的状态码加以判断,最后保存到本地txt文本中,以实现网站
- 一、Tornado简介Tornado是一个使用Python编写的高效、非阻塞的web服务器软件和网络库。它被设计用于处理数以千计的并发连接,
- 这篇文章主要介绍了python接口自动化如何封装获取常量的类,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需
- javascript版 俄罗斯方块(Russian box)小游戏,喜欢的朋友可以玩玩。对源代码感兴趣的朋友也可以研究一下。玩法介绍:可以输
- 本文主要是对flask中的before_request与after_request用法做一个简单的分析,具体实例和介绍如下。使用before
- 最终效果展示实现思路在绘图区域插入一个嵌入图,嵌入图与原图的绘画保持一致,通过限制嵌入图的x轴和y轴的显示范围,达到缩放的效果,并在原图上绘
- 如下所示:from kafka import KafkaClientfrom kafka.producer import SimplePro
- 在HTML中,常见的URL有多种表示方式:相对URL: example.php demo
- 当Python中用到双重for循环设计的时候我一般会使用循环的嵌套,但是在Python中其实还存在另一种技巧——for复合语句。简单写一个小
- 增强的toggleClass()1.toggleClass也可以和addClass一样,用空格分隔多个class名称了。.toggleCla
- 灵感来源之前在B站看到一个有意思的视频:【B站】【亦】终极云游戏!五千人同开一辆车,复现经典群体智慧实验大家可以看看,很有意思。up主通过代
- xhtml+css页面制作过程中问题的解决方案,说是解决方案应该有点过了,充其量只不过是给刚刚开始学标准页面制作的朋友们的一些小建议,如果讲