Keywords: DataGrid, stored procedure, pagination from:
http://blog.9cbs.net/yzx110/Archive/2004/08/18/78525.aspx
Summary: In the most intimate project, because a management page to be managed is very large, you must make a pagination display, and you cannot use the DataGrid's built-in paging function, so you can implement it yourself. Let me introduce I used in the project. Paging method.
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, this only selects a small portion and throws most of the way is not deserved. In the most intra-one project, it is very large because a management page must be managed because a management page is very large. Therefore, you must display the display, and you cannot use the built-in paging function of DataGrid, so you can implement it yourself. Here, I will introduce the paging method I used in the project. Of course, the control is still used in DataGrid, because data binding is very convenient ^ _ ^ To ensure that the redundant data is not transmitted, the paging must be implemented when data is read in the database, and the paging operation of the database can be placed in the stored procedure. On a Blog of 9CBS, a million-level data paging is told. Implementation of the stored procedure (http://blog.9cbs.net/wellknow/posts/55167.aspx, his method can be appropriately optimized according to different situations), according to his method, here is a simple SQL statement Implement the stored procedure required here page. Create Procedure ListProduct (@PageIndex Int, - page number @Pagesize Int, - 1 page size @Conditionsql - SQL statement for query criteria) AS ... The specific code is not written (you can refer to the link above) .
Specifically the following SQL statement: SELECT TOP 100 * FROM (select * from product where productid <200000) T WHERE T.productid NOT IN (SELECT TOP 900 productid FROM (select productid from product where productid <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 take it on page 10.
Public DataTable ListProduct (int pageIndex, int pageize) {//ado.net is slightly ^ _ ^.} From the code from the database.
The data read by the above stored procedure is paised in DataGrid, and the dataGrid's allowpaging and allowCustomPaging must be 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 cannot assign a value directly because he is a read-only property. These properties are set to bind data:
ProductGrid.DataSource = listuDuct (1, productgrid.pageSize); // In PageIndex in Page_Load, remember to determine IsPostback, execute these code when ISPostback is false
ProductGrid.databind ();
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
