Shulou(Shulou.com)05/31 Report--

GridView how to customize the paging stored procedures, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1. Why not use the default paging function of GridView first of all, why not use the default paging function of GridView? the GridView control doesn't really know how to get a new page, it just asks the bound data source control to return the row that fits the specified page, and paging is finally done by the data source control. When we use SqlDataSource or use the above code to deal with paging. Each time the page is requested or posted back, all records that match the SELECT statement are read and stored in an internal DataSet, but only the number of records appropriate to the current page size is displayed. In other words, it is possible to return 1000000 records using the select statement, while only 10 records are displayed per postback. If caching on SqlDataSource is enabled, things will be better by setting EnableCaching to true. In this case, we only need to access the database server once, and the entire dataset is loaded only once and stored in the ASP.NET cache for a specified period of time. As long as the data remains cached, any pages displayed will not have to access the database server again. However, there may be a large amount of data stored in memory, in other words, the pressure on the Web server is greatly increased. Therefore, if you want to use SqlDataSource to get small data, GridView's built-in automatic pagination may be efficient enough, but it is not suitable for large amounts of data. two。 Four stored procedures for paging (paging + sorting version please refer to other articles in Blog) in most cases we use stored procedures for paging. Today we are free to summarize four ways of paging GridView using stored procedures (using the Top keyword, temporary table, respectively. Temporary table variables and SQL Server 2005 newly added Row_Number () function) subsequent articles will also involve GridView controls using ObjectDataSource custom paging + sorting, Repeater control custom paging + sorting, interested friends can refer to. The copy code is as follows: if exists (select 1 from sys.objects where name = & apos;GetProductsCount' and type = & apos;P') drop proc GetProductsCount go CREATE PROCEDURE GetProductsCount as select count (*) from products go-- 1. Use Top if exists (select 1 from sys.objects where name = & apos;GetProductsByPage' and type = & apos;P') drop proc GetProductsByPage go CREATE PROCEDURE GetProductsByPage @ PageNumber int, @ PageSize int AS declare @ sql nvarchar (4000) set @ sql = & apos;select top & apos; + Convert (varchar, @ PageSize) + & apos; * from products where productid not in (select top & apos; + Convert (varchar, (@ PageNumber-1) * @ PageSize) + & apos; productid from products) & apos Exec sp_executesql @ sql go-- exec GetProductsByPage 1,10-- exec GetProductsByPage 5,10-- 2. Use the temporary table if exists (select 1 from sys.objects where name = & apos;GetProductsByPage' and type = & apos;P&apos) ) drop proc GetProductsByPage go CREATE PROCEDURE GetProductsByPage @ PageNumber int, @ PageSize int AS-- create temporary table 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)-- fill in temporary table INSERT INTO # TempProducts (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, UnitsOnOrder 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 (@ PageNumber-1) * @ PageSize SET ROWCOUNT 0 GO-exec GetProductsByPage 1,10-exec GetProductsByPage 5,10-- 4. Using the new feature of the row_number function-SQL Server 2005, it can arrange records in a certain order, and each record is related to a level that can be used as a row index for each record. If exists (select 1 from sys.objects where name = & apos;GetProductsByPage' and type = & apos;P&apos ) 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. The application copy code in GridView is as follows: Paging | | go to page to copy code as follows: Paging | | go to page to copy code as follows: 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 {/ / maximum number of records displayed per page private int pageSize = 10; / / current page number private int currentPageNumber; / / total number of displayed data private static int rowCount; / / total number of pages 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 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 {/ / maximum number of records displayed per page private int pageSize = 10; / / current page number private int currentPageNumber / / display the total number of private static int rowCount; (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 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]

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.