Days are over when developers had to write complex logic for implementing paging in an ASP page. With DataGrid control in ASP.NET, paging has become far simpler than it was in earlier ASP days. Using DataGrid control we can implement paging in variety of ways. This document discusses various ways in which we can implement the same.
DataGrid control supports two kinds of paging i.e. Default Paging and Custom Paging.
Default Paging:
To display the data in a DataGrid Control, we need to provide a data source and bind the DataGrid with the data source as shown below.
//Declaring DataGrid
protected System.Web.UI.WebControls.DataGrid DataGrid1;
//Binding DataGrid with a Data Source (DataSet in our Case)
DataGrid1.DataSource = dataSet1;
DataGrid1.DataBind();
To enable default paging we need to explicitly set AllowPaging property of DataGrid control to True. Use PageSize property to set the number of records to be displayed per page. PageSize has a default value of 10. DataGrid control has a property CurrentPageIndex which gives the index of current page being displayed i.e. for the first Page DataGrod1.CurrentPageIndex will be zero. Another property PageCount exposes the total number of pages. In other words PageCount indicates the number of pages in DataGrid control to display all the data from the data source. We will make use of above properties to implement paging in our ASPX page. When the user clicks on a page hyperlink on the ASPX web form, postback operation is performed. On postback, PageIndexChanged Event of DataGrid is called. What all we need to do is implement an eventhandler for the PageIndexChanged event as follows.
this.DataGrid1.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.GridPageIndexChanged);
//Implement the EventHandler
private void GridPageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
DataGrid1.CurrentPageIndex = e.NewPageIndex;
//Bind the DataGrid again with the Data Source
DataGrid1.DataSource = dataSet1;
DataGrid1.DataBind();
}
So Paging is implemented in out ASPX page. Isn't it easy then what we use to do in ASP?
Use Mode property of DataGrid Control to set the type of paging i.e. next previous mode or page number mode.
Mode has two options i.e. NextPrev or NumericPages. While using NextPrev Mode, we can use NextPageText and PrevPageText properties to set the text displayed for next and previous link. While using NumericPages Mode, we can use PageButtonCount property to set the number of links displayed i.e. if we set the PageButtonCount property to 5 then at any point of time, link for only 5 pages will be displayed along with ellipsis (...).
Apart from above, we can use properties like BackColor, ForeColor, Font, HorizontalAlign, Position and Width to set the visual display of DataGrid Page.
Note: In order to implement default paging in a DataGrid control, the data source must implement ICollection interface for example DataSet.
Custom Paging:
One problem with Default Paging is, every time we request for a page in the DataGrid, it retrieves complete data from the database. This problem can be a bottle neck if the volume of date we are working with is huge. A genuine suggestion to this problem which comes into mind immediately is to fetch only that data which we need to display on the page. This is what custom paging is all about i.e. fetching only required data from database. To enable Custom paging we need to explicitly set AllowCustomPaging property of DataGrid control to True. Use PageSize property to set the number of records to be displayed per page. We need to set VirtualItemCount property of DataGrid control. This property tells the DataGrid control about the total number of records that we need to page thru.
We can use a simple stored procedure or SQL statement to fetch the total number of records from database i.e.
SELECT COUNT(*) FROM Employee
Now we need to come up with some algorithm for fetching only required number of rows. The same can be implemented in various ways. We can implement the same depending on our application requirements.
Perhaps the easiest case is to fetch the data a single table which has an auto incremental primary key. The SQL statement is shown below.
SELECT * FROM Employee WHERE EmployeeID > (PageSize * (PageNumber - 1)) AND EmployeeID <= (PageSize * (PageNumber))
Note: Here Employee is the table name, PageSize is the number of records need to be displayed per page and PageNumber is the Page Number.
If we don't have any auto incremental primary key then we fetch data from the table after joining the table with a temporary table which has an auto incremental primary key. The SQL statements are shown below.
CREATE TABLE #EMPLOYEEPAGING ( AutoIncPriKey int IDENTITY(1,1), EMPLOYEEID int )
Populate this temporary table using data from the table from which we need to fetch our data (Employee in our case)
INSERT INTO #EMPLOYEEPAGING (EMPLOYEEID)
SELECT EMPLOYEEID FROM EMPLOYEE ORDER BY EMPLOYEEID
Now we can join the temp table and Employee table to fetch the required records as shown below.
SELECT EMPLOYEE.* FROM #EMPLOYEEPAGING, EMPLOYEE WHERE #EMPLOYEEPAGING.EMPLOYEEID = EMPLOYEE.EMPLOYEEID AND AutoIncPriKey > (PageSize * (PageNumber - 1)) AND AutoIncPriKey <= (PageSize * (PageNumber))
Note: Here PageSize is the number of records need to be displayed per page and PageNumber is the Page Number.
There are some other ways also to fetch the required number of records from database i.e. using TOP keyword as shown below
SELECT TOP 10 * FROM EMPLOYEE WHERE EMPLOYEEID > X ORDER BY EMPLOYEEID
Note: Here X is the Employee ID of last record in previous page.
To use above approach, we need to supply the Primary Key of Previous Page when we are inquiring for a next page and vice versa.
Oracle doesn't support TOP, there we can use ROWNUM keyword as shown below
SELECT * FROM EMPLOYEE WHERE ROWNUM BETWEEN 11 and 20 ORDER BY EMPLOYEEID ASC
Above SQL statement will return 10 records starting from 11 to 20.
Once we are able to fetch the required number of records, the rest is very similar to what we have done in case of default paging i.e. calling the PageIndexChanged and setting the CurrentPageIndex to NewPageIndex.
Whether to use default paging or custom paging depends on the application requirement. While default paging is good where amount of data is low, custom paging can be used when we deal with high volume of data. We can also use Caching along with default paging to provide a quicker access for data.