IntroductionPaging 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.
AllowSortingProperties of GridView to True to enable paging and sorting respectively e.g
PageSizeproperty to mention how many records will be display on each page.
3. Set the
SortExpressionproperty of each column. By default each Data Bound columns has the bounded column name as default value for the
SortingEvents of GridView to respond to paging and sorting actions respectively, like so:
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
5a. In the
PageIndexChangingEvent 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
PageIndexproperty of the GridView to a new page index to display the page that was selected by the user.
sender, GridViewPageEventArgs e)
GridView1.DataSource = GetData();
// GetData() is a method that will get Data from Database/Cache/Session to display in Grid.
GridView1.PageIndex = e.NewPageIndex;
Sortingevent 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.
DrawbacksIn 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
pageIndexChangingcall 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 SortingAs 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
ObjectDataSourcethat will perform these steps for us in an optimized way.
High Level OverviewWe 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’svirtual paging feature to optimize the paging. Virtual paging is not a term defined by Microsoft. I used it by myself because
ObjectDataSourceexposes 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
ObjectDataSourcethen you should first read some articles on that. Here are some articles:
Database LayerWe have an employee table in a database with the following schema:
And we wrote the following stored procedure that has two
selectstatements. The first select statement will return the total number of employees in the Employee table and the second dynamic
selectstatement will return the sorted records for one page according to the provided start index, page size, and sortby parameters.
IF @startIndex < 1
@startIndex = 1
IF @pageSize < 1
@pageSize = 1
@upperBound = @startIndex + @pageSize
' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
SELECT ROW_NUMBER() OVER(ORDER BY '
+ @sortBy +
') AS rowNumber, *
) AS E
WHERE rowNumber >= '
(9), @startIndex) +
rowNumber < '
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 layerIn the Data Access Layer we will write a class that will be responsible to call the
spGetAllEmployeesorted 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