GridView自定义分页的四种存储过程-每日运维

1. 为什么不使用GridView的默认分页功能首先要说说为什么不用GridView的默认的分页功能，GridView控件并非真正知道如何获得一个新页面，它只是请求绑定的数据源控件返回适合规定页面的

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 runat="server"> <div> <asp:LinkButton runat="server" CommandName="First" OnCommand="lbtnPage_Command">|<</asp:LinkButton> <asp:LinkButton runat="server" CommandName="Previous" OnCommand="lbtnPage_Command"><<</asp:LinkButton> <asp:Label runat="server" /> <asp:LinkButton runat="server" CommandName="Next" OnCommand="lbtnPage_Command">>></asp:LinkButton> <asp:LinkButton runat="server" CommandName="Last" OnCommand="lbtnPage_Command">>|</asp:LinkButton> 转到第<asp:DropDownList runat="server" AutoPostBack="True" OnSelectedIndexChanged="dropPage_SelectedIndexChanged"></asp:DropDownList>页 <asp:GridView 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 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 runat="server"> <div> <asp:LinkButton runat="server" CommandName="First" OnCommand="lbtnPage_Command">|<</asp:LinkButton> <asp:LinkButton runat="server" CommandName="Previous" OnCommand="lbtnPage_Command"><<</asp:LinkButton> <asp:Label runat="server" /> <asp:LinkButton runat="server" CommandName="Next" OnCommand="lbtnPage_Command">>></asp:LinkButton> <asp:LinkButton runat="server" CommandName="Last" OnCommand="lbtnPage_Command">>|</asp:LinkButton> 转到第<asp:DropDownList runat="server" AutoPostBack="True" OnSelectedIndexChanged="dropPage_SelectedIndexChanged"></asp:DropDownList>页 <asp:GridView 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 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.分页效果图:

GridView自定义分页的四种存储过程

相关文章

发布评论取消回复

三掌柜

百度搜索：蓝易云 – CentOS7编译生成openssl和openssh RPM包教程

【Rocky 9】Step by Step 安装 Rocky Linux 9.2 系统

蔚来汽车 x TiDB丨单表超 20 亿条数据，从 MySQL 到 TiDB 的迁移思考与实践