Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET

Optimized Paging and Sorting using Object Data Source

Rate me:
Please Sign up or sign in to vote.
4.90/5 (28 votes)
7 Sep 2009CPL9 min read 208.9K   4.1K   75   40
An artcile on End to End solution to optimized paging and sorting in ASP.NET GridView using Object Data Source.
Title:       Optimized Paging and Sorting using Object Data Source
Author:      Muhammad Akhtar Shiekh 
Email:       akhhttar@gmail.com
Member ID:   2243665
Language:    C# 2.0
Platform:    Windows, .NET
Technology:  ASP.NET
Level:       Intermediate
Description: An article on optimized paging and sorting in GridView.
Section      ASP.NET
SubSection   GridView
License:     CPL

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 do 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
    ASP.NET
    <asp:GridView ID="GridView1" runat="server" AllowPaging="true" AllowSorting="true"  >
     </asp:GridView>
    
  2. Set PageSize property to mention how many records will be display on each page.
  3. Set SortExpression property of each column. By default each DataBound columns has bounded column name as default value for SortExpression property.
  4. Handle PageIndexChanging and Sorting Events of GridView to respond Paging and sorting actions respectively e.g
    ASP.NET
    <asp:GridView ID="GridView1" runat="server" AllowPaging="true"
                AllowSorting="true" onpageindexchanging="GridView1_PageIndexChanging"
                onsorting="GridView1_Sorting"  >
            </asp:GridView>
    
    C#
     protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
    
    
    }
    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
    
    }
    
  5. Put Some Logic in Event Handler to do their jobs

    a. In PageIndexChanging Event Handler method, we usually get the data from database or somewhere from Cache and rebind our Grid with that data. And after rebinding we change PageIndex property of GridView to new page index to display the page that was selected by user.
    C#
     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;
    }
    
    b. In 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 sorted records.
    C#
     protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        GridView1.DataSource = GetData(e.SortExpression); // GetData(string sortBy) is a method that will get Data from Database/Cache/Session to display in Grid.
    
        GridView1.DataBind();
    }
    
And that’s it.

Drawbacks

In conventional way of paging and sorting we get complete set of data instead of getting part of data that is required to display on current/requested page. As you can see that on each pageIndexChanging call, we are getting all the data from our data source and then bind it to GridView. Ideally we should get only that data that is required to display on 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 required data, but practically if we bind only one page data with GridView then it would assume that this is the only data that it needs to display, so how GridView even display page numbers and total records count? It is a genuine question, so let’s try to answer this question!!!

An Optimized Way of Paging and Sorting

As in the start of this article, we discuss conventional 5 steps to implement paging and sorting in ASP.NET GridView. In this purposed solution we will be using first 3 steps AS IT IS and instead of performing 4th and 5th steps by our self, we will use ObjectDataSource that will perform these steps for us in optimized way.

High Level Overview

We will optimize the code on both Database and Presentation layer

At the Database Level we will write a stored procedure in such a way that it would return only one page records. That stored procedure take Page Size, Page Index and Sort Expression as input parameters and return sorted record for 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 expose some properties and method that allow us to bind only one page data with GridView and to define total number of records in database (not in one page), so that GridView can extract out total number of pages that needs to be display in page area of 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 I would recommend that you should first read some article on that. Here are some articles

Implementation Details:

Database Layer:

We have an employee table in database with following schema

TableSchema.GIF

and we wrote a following stored procedure that has two select statements. First select statement will return the total number of employee in Employee table and second dynamic select statement will return one page sorted record according to the provided start index, page size, and sortby by parameters

SQL
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 above stored procedure is ROW_NUMBER() function that make it possible for us to select only one page data. ROW_NUMBER() method is included in 2005 release of TSQL, it actually add an integer column in selected record set, that contain the record number for each record . It seems very simple but infact it very helpful while we are going to perform nested queries. As we did in our stored procedure, in nested query we select all employees record sorted by the provided sort expression and add a row number for each record using ROW_NUMBER() method, and in outer query we filter the resulted rows by using lower and upper bound indexes so that we return only those rows which lies in lower and upper bounds.

Data Access layer:

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

C#
public List<EmployeeInfo> GetAllEmployee(int startIndex, int pageSize, string sortBy,ref int totalEmployees)        {
            IDbConnection connection=null ;
            IDbCommand selectCommand=null ;
            List<EmployeeInfo> employeeInfoList;
            DataSet employeeDS = new DataSet();
            IDbDataAdapter dataAdapter = DataObjectFactory.CreateDataAdapter();
            try
            {
                using (connection = DataObjectFactory.CreateConnectionObject())
                {
                    using (selectCommand = DataObjectFactory.CreateStoredProcedureCommand(connection, "spGetAllEmployee"))
                    {

                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@startIndex", SqlDbType.Int, startIndex));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@pageSize", SqlDbType.Int, pageSize));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@sortBy", SqlDbType.NVarChar, 30, sortBy));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@totalEmployees", SqlDbType.Int, 0));
                        ((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Direction = ParameterDirection.Output;

                        if (connection.State != ConnectionState.Open)
                            connection.Open();

                        dataAdapter.SelectCommand = selectCommand;

                        dataAdapter.Fill(employeeDS);

                        totalEmployees = Convert.ToInt32(((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Value);
                    }
                }
            }
            catch (SqlException ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                DALException mineException = new DALException();
                mineException.ConnectingString = connection.ConnectionString;
                mineException.StoredProcedureName = selectCommand.CommandText;
                mineException.Source = "GetAllEmployee";
                throw mineException;
                
            }

            employeeInfoList =  ConvertorUtility.ConvertToEmployeeInfoCollection(employeeDS);

            return employeeInfoList;
           
        }

One thing you might notice that last parameter totalEmployee of GetAllEmployee is by reference, it is because of the optimization, we don’t want to perform two seprate database calls to get employees records and total count. Because of the same reason we are returning both things (employee data and total count) from one stored procedure. In presentation layer it will be more clearer to you that what was the necessity of this approach and how it will be helping us.

Business Logic Layer:

Business logic layer will only be calling Data Access layer code to fetch the records. Source code is available in download.

Presentation Layer:

As we discusses earlier in the artcile that we will use the virtual paging feature of ObjectDataSource. Object Data Source exposes some interesting properties which is used for virtual paging purpose. To get benefits from these properties you should set EnablePaging property of ObjectDataSource to True. These properties are

  • StartRowIndexParameterName: Specify the parameter name of Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when user changes the page index. For example, if Grid View page size is 10 and user click page number 3 to view 3rd page then the ObjectDataSource will calculate the value of StartRowIndexParameter by using ( page Size * page Index) formula , in this case it will be 10 * 3= 30. Default value of this property is startRowIndex, which means if we don’t mention any value for this property then Select method should have startRowIndex parameter.
  • MaximumRowsParameterName: Specify the parameter name of Select method of ObjectDataSurce’s bounded Type. ObjectDataSource will pass the value to this parameter when user changes the page Index. ObjectDataSource source get its value from GridView’s PageSize property. Default value of this property is maximumRow. which means if we don’t mention any value for this property then Select method should have maximumRow parameter.
  • SelectCountMethod: Specify the method name of ObjectDataSource’s Type. This method will be called by ObjectDataSource to get the total number of records in database. This count will help ObjectDataSource to create virtual paging in the Grid. For example, if GridView page size is 10, and SelectCountMethod return 100 as total number of employees in database then ObjectDataSource will display 10 page indexes in GridView page areas ( However we didn’t get all 100 records from data base)
  • SortParameterName: Specify the parameter name of Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when user click on any column header to sort the data according to that column. ObjectDataSource fetch the value from SortExpression property of particular GirdView column

Now if you see all these three properties togather then you will understand that SelectCountMethod will use to display virtual page indexes in GridView and on each page index change we will use values of StartRowIndexParameterName and MaximumRowsParameterName parameters to query database to fetch desired page data only.

As you know that we already written stored procedures, data access and business logic code which accpet these parameters and return the only one page sorted data. So it's time to use them by passing these parameters values to them.

It’s enough in theory, now let’s talk something in C# and ASPX,

ASPX: Markup of GridView and ObjectDataSource


ASP.NET
<asp:GridView ID="GridView1" DataKeyNames="EmployeeID" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        CellPadding="4" DataSourceID="ObjectDataSource1" ForeColor="#333333" GridLines="None" AllowSorting="True" PageSize="5" >
        <RowStyle BackColor="#EFF3FB" />
        <Columns>
            <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="true" SortExpression="EmployeeID" />
            <asp:BoundField DataField="EmployeeCode" HeaderText="EmployeeCode" SortExpression="EmployeeCode" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
            <asp:BoundField DataField="Salary" HeaderText="Salary" SortExpression="Salary" />
        </Columns>
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllEmployees" EnablePaging="true"
        TypeName="EmployeeData" StartRowIndexParameterName="startIndex" MaximumRowsParameterName="pageSize" SortParameterName="sortBy" SelectCountMethod="GetTotalEmployeesCount" >
    </asp:ObjectDataSource>

EmployeeData.cs: A class which is bound to ObjectDataSource

C#
public class EmployeeData
{

    private static int employeesCount;
    public EmployeeData()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    [DataObjectMethod(DataObjectMethodType.Select)]
    public static List<EmployeeInfo> GetAllEmployees(int startIndex, int pageSize, string sortBy)
    {
        EmployeeBLL objEmployeeBLL = new EmployeeBLL();
        List<EmployeeInfo> result;
        int totalEmployees=0;

        if (string.IsNullOrEmpty(sortBy))
            sortBy = "EmployeeID";

        result = objEmployeeBLL.GetAllEmployee(startIndex, pageSize, sortBy, ref totalEmployees);
        employeesCount = totalEmployees;
        return result;

    }

    public static int GetTotalEmployeesCount()
    {
        return employeesCount;
    }


}

Nothing special here to explain because we are calling already explained code to get one page sorted data by passing ObjectDataSource provided values. But one thing that I want to explain from optimization point of view, We are getting employeesCount in GetAllEmployees() method, and in GetTotalEmployeesCount() we only return that value, instead of fetching the count from database. What people usually do is , they send seprate database call to fetch the count which is an unnecessary step because we can write our stored procedure and data acess and business logic layer in such a way in which we can get the actual data and total count in one database call.

Comparison

I did comparison between conventional and optimized implementation of paging and sorting and there is a HUGE difference between them, Here are the results

One noticeable thing from this performance table is, as we are increasing the data up-optimized implementation is taking more time to fetch the records from database while there is no noticeable difference in optimized way.

No. of Records Optimized Paging ( in seconds)Un-optimized Paging  ( in seconds)
500000 0.21 07.41
1000000 0.28 17.27
2000000 0.32 36.28
3000000 0.33 54.03

No. of Records Optimized Sorting ( in seconds)Un-optimized Sorting  ( in seconds)
500000 01.61 10.14
1000000 03.13 23.22
2000000 09:38 47.39
3000000 13.12 70:25

* Paging time is calculated on GridView page index changed event.
* Sorting time is calculated on GridView sorting event.

Summary

In this article we learned an End to End solution to optimized paging and sorting in ASP.NET GridView. In Database Layer we used ROWNUMBER() method which help us to select only one page data. And In Presentation layer we used ObjectDataSource Virtual paging feature by using its properties StartRowIndexParameterName, MaximumRowsParameterName, SelectCountMethod,SortParameterName. Hope it helps.

Feedback

You feedback will really help me to be better, so please leave your comments, suggestions. You can also send me any query at akhhttar at gmail dot com.

Happy Programming!!!

License

This article, along with any associated source code and files, is licensed under The Common Public License Version 1.0 (CPL)


Written By
Software Developer Imanami Corporation
Pakistan Pakistan
I am Microsoft Certified Technology Specialist for Web Application Development. I have 4 year experience of Web and Distributed application development.I have considerable experience developing client / server software for major corporate clients using the Windows operating systems and .NET platform ( ASP.NET, C# , VB.NET).I have single and multi-threaded code development experience, as well as experience developing database and enterprise level distributed applications.

Comments and Discussions

 
QuestionCustom Query Pin
Angel Blandón26-Jun-13 11:29
Angel Blandón26-Jun-13 11:29 
GeneralMy vote of 5 Pin
Sajjad Ashraf22-Apr-13 16:45
Sajjad Ashraf22-Apr-13 16:45 
GeneralMy vote of 3 Pin
Ehsan yazdani rad14-Mar-13 15:33
Ehsan yazdani rad14-Mar-13 15:33 
GeneralMy vote of 5 Pin
Abhijoy_D12-Feb-13 20:23
Abhijoy_D12-Feb-13 20:23 
GeneralMy vote of 5 Pin
yetrus6-Feb-13 1:56
yetrus6-Feb-13 1:56 
QuestionPossible to filter using this method? Pin
leobaby1-Sep-12 12:27
leobaby1-Sep-12 12:27 
QuestionPaging result wrong Pin
kamran pervaiz23-Mar-12 0:00
kamran pervaiz23-Mar-12 0:00 
QuestionBad idea to bring count in the same SP Pin
MeetingAttender4-Jan-12 11:40
MeetingAttender4-Jan-12 11:40 
GeneralMy vote of 5 Pin
Member 417885010-Oct-11 20:13
Member 417885010-Oct-11 20:13 
GeneralMy vote of 5 Pin
shanawazway17-Sep-10 19:48
shanawazway17-Sep-10 19:48 
NewsGood Artical But,sql server 2000 && dynamic stored procedure Pin
Amr M. K.16-Sep-09 20:40
Amr M. K.16-Sep-09 20:40 
GeneralRe: Good Artical But,sql server 2000 && dynamic stored procedure Pin
Muhammad Akhtar Shiekh17-Sep-09 23:36
Muhammad Akhtar Shiekh17-Sep-09 23:36 
GeneralRe: Good Artical But,sql server 2000 && dynamic stored procedure Pin
Amr M. K.19-Sep-09 11:09
Amr M. K.19-Sep-09 11:09 
Generalfew improvements and alternatives [modified] Pin
vrajaraman14-Sep-09 15:55
vrajaraman14-Sep-09 15:55 
GeneralRe: few improvements and alternatives Pin
Muhammad Akhtar Shiekh17-Sep-09 23:26
Muhammad Akhtar Shiekh17-Sep-09 23:26 
Generaluseful article but you should have examined more methods Pin
arviy8-Sep-09 6:29
arviy8-Sep-09 6:29 
GeneralRe: useful article but you should have examined more methods Pin
Muhammad Akhtar Shiekh8-Sep-09 18:11
Muhammad Akhtar Shiekh8-Sep-09 18:11 
GeneralRe: useful article but you should have examined more methods Pin
Erakis29-Sep-09 17:39
Erakis29-Sep-09 17:39 
GeneralRe: useful article but you should have examined more methods Pin
Muhammad Akhtar Shiekh29-Sep-09 23:33
Muhammad Akhtar Shiekh29-Sep-09 23:33 
GeneralWhy use a DataSet Pin
Dmytro Lapshyn8-Sep-09 0:33
Dmytro Lapshyn8-Sep-09 0:33 
In your GetAllEmployee method, you retrieve the employee data into a DataSet only to convert the DataSet to a collection later. Wouldn't it be more efficient to use a DataReader instead and populate the collection right from the DataReader?
GeneralRe: Why use a DataSet Pin
Muhammad Akhtar Shiekh8-Sep-09 1:57
Muhammad Akhtar Shiekh8-Sep-09 1:57 
GeneralRe: Why use a DataSet Pin
Dmytro Lapshyn8-Sep-09 3:10
Dmytro Lapshyn8-Sep-09 3:10 
GeneralRe: Why use a DataSet Pin
zhaojicheng8-Sep-09 6:32
zhaojicheng8-Sep-09 6:32 
GeneralRe: Why use a DataSet Pin
Dmytro Lapshyn8-Sep-09 6:38
Dmytro Lapshyn8-Sep-09 6:38 
GeneralRe: Why use a DataSet Pin
zhaojicheng8-Sep-09 22:08
zhaojicheng8-Sep-09 22:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.