Asp. NET enables efficient paging with custom paging functionality and stored procedures in the DataGrid

Key Words:DataGrid, stored procedures, paging

From :Http://blog.csdn.net/yzx110/archive/2004/08/18/78525.aspx

Summary:In the most advanced project because a management page to manage the amount of data is very large, it must be paginated, and can not use the DataGrid's built-in paging capabilities, so you implement pagination. Here's a description of the paging method I used in my project.

Asp. NET in the DataGrid has built-in paging capabilities, but its default paging efficiency is very low, especially when the volume of data is very large, it is almost impossible to use its built-in paging function, because it will all the data from the database read out and then paging, This is a small part of the selection and lose most of the method is not to be taken.

In the most advanced project because a management page to manage the amount of data is very large, it must be paginated, and can not use the DataGrid's built-in paging capabilities, so you implement pagination. Here's a description of the paging method I used in my project.

Of course, the display control is still in the DataGrid, because data binding is convenient ^_^.

To ensure that no redundant data is transferred, pagination must be implemented when data is read in the database, and the paging operations of the database can be placed in stored procedures. Read a blog csdn a millions data paging of the implementation of the stored procedures (http://blog.csdn.net/wellknow/posts/55167.aspx, his method can be based on the appropriate optimization of different circumstances), according to his method , where a simple SQL statement is implemented to implement the stored procedures required for paging here.

CREATE PROCEDURE Listproduct

(

@PageIndex int,--the number of pages required after pagination

@PageSize int,--the size of a page

@ConditionSQL – SQL statement for query criteria

)

As ... The specific code is not written (you can refer to the link above).

<?xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/>

The specific SQL statements are as follows:

Select Top * FROM (SELECT * from product where productid<200000) T where T.productid does not

(select Top 900 ProductID from (select ProductID to Product where productid<200000) T1 ORDER by t1.productid ASC) ORDER BY ProductID ASC

This statement removes the productid<200000 (total 200,000) from the total product (300,000), pages 100 of each page, and then removes page 10th.

Public DataTable listproduct (int pageIndex, int pageSize)

{

The code that ado.net the data from the database skips the ^_^.

}

The data read from the above stored procedure is paginated inside the DataGrid, and the AllowPaging and allowcustompaging of the DataGrid must be set to True

protected System.Web.UI.WebControls.DataGrid Productgrid;

Productgrid.allowpaging = true;

Productgrid.allowcustompaging = true;

Then set the size of the page you want to display

productgrid.pagesize = 100; Display on the basis of actual data when displayed.

After setting a page size, if you want the DataGrid to actually separate the number of pages, you must also set the

Productgrid.virtualitemcount = Getproductcount (); The Getproductcount () function is to obtain the number of products to meet the conditions, where the conditions are productid<200000. After setting this property, the number of pages in this DataGrid is

Virtualitemcount/pagesize, which is the value of PageCount. You cannot assign a value directly to PageCount because he is a read-only property.

These properties are set and then bound to the data:

Productgrid.datasource = listproduct (1, productgrid.pagesize); In Page_Load pageindex is 1, remember to Judge IsPostBack, execute the code when IsPostBack is False

Productgrid.databind ();

This way the data is bound to see pages with pagination. But not really paging. To implement true paging, you must also implement the following functionality.

Handling the PageIndexChanged event for a DataGrid (event when a user selects a new page)

private void Productgrid_pageindexchanged (object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

{

If you are using 1 to represent the 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 with a page number of 3 on the DataGrid, then E. NewPageIndex is 2), otherwise direct with E. NewPageIndex on it.

Productgrid.datasource = Listproduct (e.newpageindex+1, productgrid.pagesize); Reading new data from a database

Productgrid.databind ();

Sets the current page ordinal value and does not become if it is not set, which can cause misunderstanding to the user that all pages have the same data.

Productgrid.currentpageindex =e.newpageindex;

}

If you have dealt with the ItemCommand event of the DataGrid, you must precede the ItemCommand event handling code with this code:

if (E.item.itemtype = = Listitemtype.pager)

{

Return

}

Because when the PageIndexChanged event is fired, that is, when the user chooses another page, it fires the ItemCommand event, and if you do not, you may experience unexpected situations (if you really need it, you can do the above code, but it's best to actually test it).

After the whole process is finished, browsing the page again, feel the speed is really much faster.

Operating Environment:

WinXP Pro SP1, SQL Server Watts,. Net Framework 1.1