Paging and Sorting Optimized with ASP.Net GridView

Lets start by creating an Employee table

CREATE TABLE [dbo].[Employee](
   [EmpID] [int] NOT NULL,
   [Name] [varchar](50) NULL,
   [Department] [varchar](50) NULL,
   [Salary] [decimal](18, 0) NULL
) ON [PRIMARY]

GO

For optimization, we need a stored procedure at database level, which will return one page of data. The following stored procedure is the bottom line of optimization

Create PROCEDURE spGetAllEmployee
(
   @startIndex     int,
   @pageSize       int,
   @sortBy     nvarchar(30),
   @totalEmployees int OUTPUT    
)
AS
   SET NOCOUNT ON
   DECLARE @sqlStatement nvarchar(max)
   DECLARE @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)

The stored procedure accepts page index, page size and sort expression. It returns the sorted data for that page index. The procedure contains two SELECT statements. The first one will find out the total employee count while the second statement is dynamic, which will return the sorted records of one page according to the provided page index and page size. If you look into the second SELECT statement you can see the use of ROW_NUMBER() function, which is an addition to SQL Server 2005 and above. What it will do is, it will add an additional column to the record set and places a record number for each row. In the outer query we will filter the result rows by using lower bound and upper bound indexes so that we return only the rows between lower and upper bounds.

Now take a look at the aspx page

<asp:GridView ID="GridView1" DataKeyNames="EmpID" 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="EmpID" HeaderText="EmpID" 
ReadOnly="true" SortExpression="EmpID" />           
           <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="WebApplication1.EmployeeData" 
StartRowIndexParameterName="startIndex" MaximumRowsParameterName="pageSize"
   SortParameterName="sortBy" SelectCountMethod="GetTotalEmployeesCount" >   
   </asp:ObjectDataSource>

We will be using the ObjectDataSource, which allow Virtual Paging. For achieving this we will be using the following property of ObjectDataSource

StartRowIndexParameterName - Specifies the parameter name of the Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when the user changes the page index. For example, if Grid View page size is 10 and the user clicks page number 3 to view the 3rd page, then the ObjectDataSource will calculate the value of StartRowIndexParameter by using the (page Size * page Index) formula. In this case it will be 10 * 3= 30. Default value of this property is startRowIndex, which means that if we don't mention any value for this property then the Select method should have the startRowIndex parameter.

MaximumRowsParameterName - Specifies the parameter name of the Select method of ObjectDataSurce’s bounded Type. ObjectDataSource will pass the value to this parameter when the user changes the page Index. ObjectDataSource source gets its value from GridView's PageSize property. Default value of this property is maximumRow, which means that if we don't set any value for this property then the Select method should have the maximumRow parameter.

SelectCountMethod - Specifies the method name of ObjectDataSource’s Type. This method will be called by ObjectDataSource to get the total number of records in the database. This count will help ObjectDataSource to create virtual paging in the Grid. For example, if GridView page size is 10, and SelectCountMethod returns 100 as total number of employees in the database then ObjectDataSource will display 10 page indexes in the GridView's pager area, even if we didn't get all the 100 records from the database.

SortParameterName - Specifies the parameter name of the Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when the user clicks on any column header to sort the data according to that column. ObjectDataSource fetches the value from the SortExpression property of the particular GridView column.

public class EmployeeData
   {
       private static int employeesCount;

       public EmployeeData()
       {
           //
           // TODO: Add constructor logic here
           //
       }

       [DataObjectMethod(DataObjectMethodType.Select)]
       public static List<Employee> GetAllEmployees(int startIndex,
int pageSize, string sortBy)
       {
           List<Employee> result;
           int totalEmployees = 0;
           if (string.IsNullOrEmpty(sortBy))
               sortBy = "EmpID";
           result = GetAllEmployee(startIndex, pageSize, sortBy, 
ref totalEmployees);
           employeesCount = totalEmployees;
           return result;
       }

       public static int GetTotalEmployeesCount()
       {
           return employeesCount;
       }

       public static List<Employee> GetAllEmployee(int startIndex, 
int pageSize, string sortBy, ref int totalEmployees)
       {
           System.Data.SqlClient.SqlConnection connection;
           System.Data.SqlClient.SqlCommand selectCommand = null;
           List<Employee> employeeList = new List<Employee>();

           try
           {
               using (connection = new
System.Data.SqlClient.SqlConnection(
"Data Source=.;Initial Catalog=deptStore;Integrated Security=SSPI;"))
               {
                   if (connection.State != ConnectionState.Open)
                       connection.Open();

                   using (selectCommand = new System.Data.SqlClient.SqlCommand())
                   {
                       selectCommand.CommandText = "dbo.spGetAllEmployee";
                       selectCommand.Connection = connection;
                       selectCommand.CommandType = CommandType.StoredProcedure;

                       selectCommand.Parameters.Add(new 
SqlParameter("@startIndex", SqlDbType.Int));
                       selectCommand.Parameters[0].Value = startIndex;
                       selectCommand.Parameters.Add(new 
SqlParameter("@pageSize", SqlDbType.Int));
                       selectCommand.Parameters[1].Value = pageSize;
                       selectCommand.Parameters.Add(new 
SqlParameter("@sortBy", SqlDbType.VarChar, 30));
                       selectCommand.Parameters[2].Value = sortBy;
                       selectCommand.Parameters.Add(new
SqlParameter("@totalEmployees", SqlDbType.Int));
                       selectCommand.Parameters[3].Value = totalEmployees;
                       ((System.Data.SqlClient.SqlParameter)
selectCommand.Parameters["@totalEmployees"]).Direction = ParameterDirection.Output;
                       SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                       DataSet empDS = new DataSet();
                       adapter.Fill(empDS);
                       totalEmployees = Convert.ToInt32(((
System.Data.SqlClient.SqlParameter)
selectCommand.Parameters["@totalEmployees"]).Value);

                       for (int index = 0; index < empDS.Tables[0].Rows.Count;
index++)
                       {
                           Employee emp = new Employee()
                           {
                               EmpID = Convert.ToInt32(
empDS.Tables[0].Rows[index][0].ToString()),
                               Name = empDS.Tables[0].Rows[index][1].ToString(),
                               Department =
empDS.Tables[0].Rows[index][2].ToString(),
                               Salary = Convert.ToInt32(
empDS.Tables[0].Rows[index][3].ToString())
                           };

                           employeeList.Add(emp);
                       }
                   }
               }
           }
           catch (System.Data.SqlClient.SqlException ex)
           {
               throw ex;
           }
           return employeeList;
       }
   }

   public class Employee
   {
       public int EmpID { get; set; }
       public string Name { get; set; }
       public string Department { get; set; }
       public int Salary { get; set; }
   }