Pagination code in ASP.Net

Imagine you have a webpage which is showing rows retrieved from a database table. What do you do to show the table contains hundreds and thousands of rows? You can�t show all those data in a single page. So you may want to split the data into multiple pages. For example, when we do a search in google.com, it doesn�t show all searched results in a single page. It will split the results into multiple pages which look like this:


Previous 1 2 3 4 5 6 7 8 9 10 Next


We can do Pagination in Asp.Net DataGrid easier than other web technologies like PHP, JSP etc. This is because of built in feature for pagination provided by Asp.net Datagrid using its AllowPaging property. But there is a big drawback in it is the performance because it keeps all the records in the memory and when the no of records increases it makes the data retrieval in the web page slower. So use the Datagrid only if it contains a few records. So in the case of pagination in .net it is better to write the code in stored procedure.

ASP.Net provides 3 controls for displaying data. DataGrid, Repeater and the DataList.
Each has its own advantages and disadvantages. DataGrid consists more advanced features than others including built in support for pagination, sorting, Templates etc...

The one drawback in Datagrid is that it is not good for customization. The other 2 ones are more customizable controls. They are providing us total control for formatting the data by customization and flexible templates for data output. But the DataList and Repeater Controls do not support pagination. It doesn�t mean it�s impossible to do pagination with these Controls, but these doesn�t provide built in feature for pagination like DataGrid.

Let us understand how to do pagination using DataGrid first.

The first step to do pagination in DataGrid is setting the AllowPaging property of it to True. Along with the AllowPaging property, there is a PageSize property that specifies how many records to display per page. The default value of this property is 10.

By just setting the AllowPaging property to True and setting the PageSize property, the DataGrid displays only the first PageSize number of rows from the DataSource. When we click on next page the same results will show that is it will redisplay the first page output.


Here is the pagination code in ASP.Net with C#.

Code:

<script language=c# runat=server> using System.Data.SqlClient; using System.Web.Ui.WebControls; private void Page_Load(object sender, System.EventArgs e)       {          // page initialization code                          if(!Page.IsPostBack)          {             bindtable();          }       } private void DataGrid1_Init(object sender, System.EventArgs e)       {          DataGrid1.AllowPaging=true; //setting paging style in a numeric mode like 1 2 3�..          DataGrid1.PagerStyle.Mode=PagerMode.NumericPages;            DataGrid1.PageSize=10;       }       private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)       {          DataGrid1.CurrentPageIndex=e.NewPageIndex;          bindtable();       } public void bindtable()       {          string txt="select * from customer"; //SQL Server Database connection initialization          sqlcon = new sqlConnection(�server=SQLDB;uid=sa;pwd=password;database=Dev�);       sqlDataAdapter sqlcom = new sqlDataAdapter(txt,sqlcon); DataSet ds = new DataSet(); Sqlcom.fill(ds,�customer�);    DataGrid1.DataSource = ds.Tables[�customer�].DefaultView;          DataGrid1.DataBind();       } </script> <form id="frm_Customer" method="post" runat="server">          <asp:datagrid id="DataGrid1" runat="server" Width="613px" ForeColor="Black" CellPadding="2" BackColor="LightGoldenrodYellow" BorderWidth="1px" BorderColor="Tan" AllowPaging="True" PageSize="10">                                         </asp:datagrid          </form>

When one of the paging hyperlinks is clicked, the ASP.NET Web page performs a postback. When the page posts back, the dataGrid�s PageIndexChanged event is raised.
PageIndexChanged is an event of DataGrid for responding to a paging request.
The CurrentPageIndex property is an integer value that shows which page is currently being displayed. Use the e.NewPageIndex to update the DataGrid's CurrentPageIndex, then rebind the data using DataBind() method.

Note
The above code will not work if you use DataReader instead of Dataset. This is mainly because by default, Datagrid requires Datasource property of Dataset to determine how many no of pages there are in the given DataSource.

The above code is useful only the DataGrid contains a few records. In other case, it is better to write the pagination code as stored procedure.

Now let us see how pagination can be done as stored procedure if you are using SQL Server as database using ROW_NUMBER function which uniquely numbers the rows of an sql query.

Code:

CREATE PROC sp_GetCustomersByPaging    @pageSize int,    @startRowNumber int AS SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY OrderID) as RowNumber, CustomerID, CustomerName, UnitPrice FROM Customer) AS Customer WHERE RowNumber BETWEEN @startRowNumber AND (@startRowNumber + @pageSize )

The above stored procedure consists of 2 parameters. pageSize and startRowNumber. The pageSize parameter contains row amount of every page, and startRowNumber is used for storing starting row number for a particular page.

ASP.NET 2.0 provides ObjectDataSource control that already supports custom paging.

The sp_ GetCustomersByPaging procedure will return the only data needed for a particular page. ObjectDataSource needs an object that returns a DataTable, DataSet, or Collection object. So you create 2 methods: GetCustomersByPaging and CountCustomer.

Code:

<script language=c# runat=server> using System.Data.SqlClient; using System.Web.Ui.WebControls; private void Page_Load(object sender, System.EventArgs e)       {          // page initialization code                          if(!Page.IsPostBack)          {             bindtable();          }       } public void bindtable()       {          string txt="select * from customer"; //SQL Server Database connection initialization          sqlcon = new sqlConnection(�server=SQLDB;uid=sa;pwd=password;database=Dev�);       } public DataSet GetCustomersByPaging(int pageSize, int startRowNumber)     {                 SqlCommand cmd = new SqlCommand();         SqlParameter paraPageSize = new SqlParameter("@pageSize", SqlDbType.Int, 4);         cmd.Parameters.Add(paraPageSize);         pararPageSize.Value = pageSize;         SqlParameter paraStartRow = new SqlParameter("@startRowNumber", SqlDbType.Int, 4);         cmd.Parameters.Add(paraStartRow);         paraStartRow.Value = startRowNumber;         cn.Open();         cmd.CommandText = "sp_GetCustomersByPaging";         cmd.CommandType = CommandType.StoredProcedure;         cmd.Connection = cn;         sqlDataReader dr;  dr = cmd.ExecuteReader();  DataSet ds = new DataSet();  ds.Tables.Add(this.GetTable(dr);         return ds;     }     public int CountCustomer()     {           sqlDataReader dr;           sqlCommand sqlcom = new SqlCommand(�SELECT COUNT(OrderID) from Customer�,sqlcon);           Sqlcon.open();           dr = sqlcom.ExecuteReader();           return Convert.ToInt32 (dr(0)(�OrderID�));     } </script>

The GetCustomersByPaging function executes sp_GetCustomersByPaging stored procedure and returns results for every page. The CountCustomer function is needed by ObjectDataSource for passing value as startRowNumber parameter to the method GetCustomersByPaging.

Configure the ObjectDataSource as GetCustomersByPaging as SELECT command.

Code:

<asp:ObjectDataSource ID="objectDataSourceOrders" runat="server" EnablePaging="True"             SelectMethod="GetCustomersByPaging" MaximumRowsParametername=�pageSize� StartRowIndexParameterName =�startRowNumber� SelectCountMethod="CountCustomer"></asp:ObjectDataSource>

Drag the GridView control in your web form and set the AllowPaging property of the GridView to TRUE, PageSize property to 10 and Datasource as the ID of your ObjectDataSource.


Good luck and happy programming to all.