ASP.NET
In use
DataGrid
Custom paging function and stored procedure to achieve efficient paging
DataGrid in ASP.NET has built-in paging features, but its default paging mode is very low, especially when the amount of data is large, it is almost impossible to use its built-in paging function because it will Read all the data from the database and then make a paging, which only selects a small portion and throwing most of the way is not deserved.
In the most intimate project, because a management page you want to manage is very large, you must make a pagination display, and you cannot use the built-in paging function of DataGrid, so you can implement a paging. Let's take a look at the paging method I used in the project. .
Of course, the control is still used with DataGrid, because the data binding is very convenient ^ _ ^.
To ensure that the redundant data is not transmitted, you must implement paging when data is read in the database, and the paging operation of the database can be placed during the stored procedure. On a blog of 9CBS, a million-level data pagination is taught. Realization of the process (http://blog.9cbs.net/wellknow/posts/55167.aspx, his method can be appropriately optimized according to different situations), according to his method, here is implemented here to implement a simple SQL statement The stored procedure required for page here.
CREATE Procedure ListProduct
(
@PageIndex Int, - Sequence number of page after paging
@PageSize Int, - A page size
@Conditionsql - SQL statement for query conditions
)
AS ... The specific code is not written (you can refer to the link above).
The specific SQL statement is as follows:
SELECT TOP 100 * from (Select * from Product Where ProductID <200000) T Where T.ProductId Not in
(Select Top 900 Productid WHERE PROMTID <200000) T1 ORDER BY T1.PRODUCTID ASC) Order by ProductID ASC
This statement takes the productID <200000 ($ 200,000) from the total commodity (300,000), then press the size pagination per page and then taken on page 10.
Public DataTable ListProduct (int pageIndex, int pageize)
{
//Ado.net is slightly ^ _ ^ from the code of the data from the database.
}
The data read by the above stored procedure is paised in DataGrid, and the dataGrid's allowpaging and allows are set to True.
Protected system.web.ui.webcontrols.datagrid productgrid;
ProductGrid.Allowpaging = true;
ProductGrid.AllowCustomPaging = true;
Then set the size of a page to display to display
ProductGrid.pageSize = 100; // According to the actual data, it is displayed.
Set up a page size, if you want to make DataGrid's number of pages, you must also set
ProductGrid.VirtualItemcount = getProductCount (); // getProductCount () function is to get the number of products that meet the condition, where the condition is ProductID <200000. After setting this property, then the number of pages of this DataGrid is
VirtualItemCount / PageSize, that is, the value of PageCount. You can't assign it directly because he is a read-only property.
These attributes are set to bind the data:
ProductGrid.DataSource = ListProduct (1, ProductGrid.pageSize); // In PageIndex in Page_Load, remember to determine IsPostback, execute these code productGrid.Database when ISPostback is False;
After such data binding, you can see a page with paging mode. But you can't really paginize. To achieve true paging, you must implement the following features.
Handle DataGrid's PageIndexchanged event (Events to handle a user new page)
Private void productgrid_pageIndexchanged (Object Source, System.Web.ui.WebControls.DataGridPageChangeDeventargs E)
{
/ / If you use 1 to represent the serial number of the first page in the stored procedure paging function, then you must use E.NewpageIndex 1 as PageIndex (if you select a page number 3 page number 3, then E.NewpageIndex is 2) Otherwise, use E.NewpageIndex directly.
ProductGrid.DataSource = listProduct (E.NewpageIndex 1, ProductGrid.pageSize); // Read new data from the database
ProductGrid.databind ();
/ / Set the current PAGE number value, if it is not set, it will not become, which will cause misunderstandings to the user, as the data of all pages is the same.
ProductGrid.currentPageIndex = E.NewpageIndex;
}
If you handle the event of DataGrid Itemmand, you must add these code in front of the ItemMand event handling code:
IF (E.Item.itemType == ListItemType.pager)
{
Return;
}
Because when the pageindexchanged event is inspired, it is the user choice. If you don't have this, if you don't have this, you may have some unexpected situations (if you really need it, you can also do the above code, But it is best to test it.).
After the entire process is completed, browse the page again, feel that the speed is really much.
Operating environment:
WinXP Pro SP1, SQLServer 2000, .NET Framework 1.1
