Implement custom paging in an asp.net gridview that uses objectdatasource - Part 52

Suggested Videos 
Part 49 - How to include sort arrows when sorting an asp.net gridview control
Part 50 - Implement paging in an asp.net gridview that uses sqldatasource
Part 51 - Implement default paging gridview that uses objectdatasource



In this video we will discuss about implementing custom paging in a gridview control that uses objectdatasource control. 

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Create a stored procedure to retrieve only the required number of rows.
Create proc spGetEmployees
@StartIndex int,
@MaximumRows int
as
Begin
Set @StartIndex = @StartIndex + 1

Select EmployeeId, Name, Gender, City from
(Select ROW_NUMBER() over (order by EmployeeId) as RowNumber, EmployeeId, Name, Gender, City
from tblEmployee) Employees
Where RowNumber >= @StartIndex and RowNumber < (@StartIndex + @MaximumRows)
End

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". To implement custom paging the following 2 methods are required in EmployeeDataAccessLayer.
GetEmployees(int startRowIndex, int maximumRows) - This method is responsible for retrieving the set of required rows. The 2 parameters startRowIndex and maximumRows are used to calculate the correct set of required rows.
GetTotalCount() - This method is responsible for returning the total numbers of rows in the underlying table. This method is used by the gridview control to calculate the exact number of pages to display, in the pager area of the gridview control.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
    
    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetEmployees(int startRowIndex, int maximumRows)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetEmployees", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramStartIndex = new SqlParameter();
                paramStartIndex.ParameterName = "@StartIndex";
                paramStartIndex.Value = startRowIndex;
                cmd.Parameters.Add(paramStartIndex);

                SqlParameter paramMaximumRows = new SqlParameter();
                paramMaximumRows.ParameterName = "@MaximumRows";
                paramMaximumRows.Value = maximumRows;
                cmd.Parameters.Add(paramMaximumRows);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }
            return listEmployees;
        }

        public static int GetTotalCount()
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select Count(*) from tblEmployee", con);

                con.Open();
                return (int)cmd.ExecuteScalar();
            }
        }
    }
}

Step 3: Build the solution.

Step 4: Drag and drop a gridview, and an objectdatasource control on webform1.aspx

Step 5: Configure ObjectDataSource1 control to retrieve data from Demo.EmployeeDataAccessLayer business object, using GetEmployees() method. On "Define Parameters" screen, click finish.

Step 6: Associate "ObjectDataSource1" with "GridView1" control, and select "Enable Paging" checkbox. Set PageSize property to 3.

Step 7: At this point, flip webform1.aspx to HTML source mode and delete "SelectParameters" section from "ObjectDataSource1" control. Set the following properties of ObjectDataSource control.
MaximumRowsParameterName="maximumRows"
StartRowIndexParameterName="startRowIndex"
SelectCountMethod="GetTotalCount"
EnablePaging="true"

At this point the HTML of "WebForm1.aspx" should be as shown below.
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
    AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" 
    PageSize="3">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" 
            SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name" 
            SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" 
            SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City" 
            SortExpression="City" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
    SelectMethod="GetEmployees" 
    TypeName="Demo.EmployeeDataAccessLayer"
    MaximumRowsParameterName="maximumRows"
    StartRowIndexParameterName="startRowIndex"
    SelectCountMethod="GetTotalCount"
    EnablePaging="true">
</asp:ObjectDataSource>

What is the difference between default paging and custom paging?
This is a very common asp.net interview question. With default paging all the rows are retrieved from the database every time we navigate to a different page. For example, if there are 1000 rows in database table, and if the page size is set 10, the gridview control displays just 10 rows at a time, but all the 1000 rows will be retrieved. When we navigate to page 2, the second set of 10 records get displayed in the gridview control, but again all the 1000 rows will be retrieved. Obviously this leads to a lot of unnecessary network traffic between database and web servers and is bad for performance.

With custom paging we only retrieve the subset of rows that will be displayed in the gridview control. For example, if there are 1000 rows in database table, and if the page size is set 10, with custom paging we retrieve just those 10 records. So, custom paging is much better for performance over default paging. When compared with default paging, custom paging is relatively complex to implement.