Optimized Paging and Sorting in ASP.NET GridView

Introduction

Paging and sorting are most commonly used features of ASP.NET GridView. And it is very easy to use/implement these features in GridView with small chunk of lines. In this article I am going to demonstrate what are the performance drawbacks of using conventional way to page and sort your GridView and then I will demonstrate 'An Optimized way to implement Paging and Sorting'.

What are conventional steps for Paging and Sorting?

Usually we perform the following steps to enable paging and sorting in our GridView.
1. Set AllowPaging and AllowSorting Properties of GridView to True to enable paging and sorting respectively e.g

1.<asp:GridView ID="GridView1" runat="server" AllowPaging="true" AllowSorting="true" >

2. asp:GridView>

2. Set the PageSize property to mention how many records will be display on each page.
3. Set the SortExpression property of each column. By default each Data Bound columns has the bounded column name as default value for the SortExpression property.
4. Handle PageIndexChanging and Sorting Events of GridView to respond to paging and sorting actions respectively, like so:

<asp:GridView ID="GridView1" runat="server" AllowPaging="true" 
            AllowSorting="true" onpageindexchanging="GridView1_PageIndexChanging" 
            onsorting="GridView1_Sorting"  >  
        asp:GridView>  
 protected void GridView1_PageIndexChanging(object sender,  GridViewPageEventArgs e) 
{  
}  
protected void  GridView1_Sorting(object sender, GridViewSortEventArgs e)  
{  
}

5. Put some logic in the event handlers to do their jobs
5a. In the PageIndexChanging Event Handler method, we usually get the data from database or somewhere from the Cache and rebind our Grid with that data. After rebinding we change the PageIndex property of the GridView to a new page index to display the page that was selected by the user.

protected void GridView1_PageIndexChanging(object  sender, GridViewPageEventArgs e) 
 { 
     GridView1.DataSource = GetData(); // GetData() is a method that will get Data from  Database/Cache/Session to display in Grid. 
     GridView1.DataBind(); 
     GridView1.PageIndex = e.NewPageIndex;  
 }

5b. In the Sorting event handler method, we get the sorted data according to the sort expression from our data source (data source could be database/cache/session etc) and then rebind the Grid to display the sorted records.
And that's it.

Drawbacks

In conventional way of paging and sorting we get complete set of data instead of getting only the portion of data that is required to display on current/requested page. As you can see on each pageIndexChanging call we are getting all the data from our data source and then binding it to the GridView. Ideally we should get only the data that we need to display on the requested page.

Hmmm...Sounds good but HOW??

The question that may arise in your mind could be "It seems good in theory that we should only get the required data, but practically if we bind only one page of data with GridView then it would assume that this is the only data that it needs to display. So how does the GridView even display page numbers and total records count? It is a genuine question, so let's try to answer!

An Optimized Way to implement Paging and Sorting

As in the start of this article, we discuss the conventional 5 steps to implement paging and sorting in ASP.NET GridView . In this solution we will use the first 3 steps as described above, and perform the 4th and 5th steps by ourselves. We will use an ObjectDataSource that will perform these steps for us in an optimized way.

High Level Overview

We will optimize the code on both Database and Presentation layers.
At the Database Level we will write a stored procedure in such a way that it would return only one page of records. The stored procedure takes the page size, page index and a sort expression as input parameters and returns sorted records for a particular page index.
At the Presentation layer, we will use ObjectDataSource’s virtual paging feature to optimize the paging. Virtual paging is not a term defined by Microsoft. I used it by myself because ObjectDataSource exposes some properties and methods that allow us to bind only one page of data with GridView and to define the total number of records in database (not in one page), so that the GridView can extract out the total number of pages that need to be display in the page area of the GridView. In the next sections we will see what these properties and methods are, and how to use them.
If you are not familiar with ObjectDataSource then you should first read some articles on that. Here are some articles:

Implementation Details

Database Layer

We have an employee table in a database with the following schema:

And we wrote the following stored procedure that has two select statements. The first select statement will return the total number of employees in the Employee table and the second dynamic select statement will return the sorted records for one page according to the provided start index, page size, and sortby parameters.

Create PROCEDURE spGetAllEmployee 
    ( 
    @startIndex     int, 
    @pageSize       int, 
    @sortBy     nvarchar(30),  
    @totalEmployees int OUTPUT      
    ) 
AS
    SET NOCOUNT  ON 
 DECLARE
    @sqlStatement nvarchar(max),     
    @upperBound int
  IF @startIndex  < 1 SET @startIndex = 1  
  IF @pageSize < 1 SET @pageSize = 1  
  SET @upperBound =  @startIndex + @pageSize 
 Select @totalEmployees=Count(*) From Employee 
  SET @sqlStatement =  ' SELECT E.EmployeeID, E.EmployeeCode, E.Name,  E.Department, E.Salary 
                FROM ( 
                      SELECT   ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, * 
                      FROM     Employee 
                     ) AS E  
                WHERE  rowNumber >= ' + CONVERT(varchar(9),  @startIndex) + ' AND 
                       rowNumber  <  ' + CONVERT(varchar(9),  @upperBound) 
  exec (@sqlStatement)

One thing that I want to explain in the above stored procedure is the ROW_NUMBER() function that makes it possible for us to select only one page of data. The ROW_NUMBER() method is included in the 2005 release of TSQL. It actually adds an integer column in the selected record set, that contains the record number for each record . It seems very simple but in fact it's very helpful as we are going to perform nested quires. As we did in our stored procedure, in the nested query we select all employees records sorted by the provided sort expression, and add a row number for each record using the ROW_NUMBER() method. In the outer query we filter the result rows by using lower and upper bound indexes so that we return only the rows between lower and upper bounds.

Data Access layer

In the Data Access Layer we will write a class that will be responsible to call the spGetAllEmployee sorted procedure to get employee records and return the employee list to the business logic layer. To avoid increasing the complexity and length of the article I am only posting the code that is used to fetch the records from the database. I am not posting any helper code/classes; however the complete code is available for download.

See full details: http://dotnetslackers.com/articles/gridview/Optimized-Paging-and-Sorting-in-ASP-NET-GridView.aspx

Search This Blog

Asp.net and c#