An Optimized Way of Paging and Sorting

An Optimized Way of Paging and Sorting

 

What are conventional way 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: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: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)
       {
           
   // Implement some paging logic here
       }
       protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
       {
   
   // Implement sone sorting logic here

   }

Problem:-

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.

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?



Implementation Details:

Database Layer:

We have an employee table in database with following schema

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

Collapse | Copy Code

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 .

Collapse | Copy Code

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

 

Collapse | Copy Code

<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

Collapse | Copy Code

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.

you can find this on code project.