Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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.

1 comment:

  1. Hi Venkat,
    if I understand correctly, the custom paging can be useful to speed up the processing time when the web server and database server are on two different machines and the unnecessary network traffic is time consuming, isn't it? And what about using the cache? Can it help us?
    I was inspired by your lesson #131 of ASP.NET series (Cache dependency on sql server database table) and I set up another gridview management using the cache. Advantages in this approach, IMHO, are more than one:
    (1) we need not to implement the custom paging: the default sorting and paging management of ObjectDataSource control is used instead, (2) till the database table is unchanged the cache is used by ObjectDataSource, hence, (3) data retrieving is quick also in case the database & web server are on the same machine, because we need not to prepare the new dataset every time, picking data from database, we just use cached dataset, (4) when the database server is unavailable we can still navigate thru the gridview control and sort it as we like.
    So, below, I report changes to do:
    Aspx page design mode: select Gridview1 control, click on upper right arrow -> Edit columns. Select many Boundfields as you like and for each of them set their properties Headertext, SortExpression and DataField. Example: "FirstName" column should be set like this: HeaderText = First Name, SortExpression = FirstName, DataField = FirstName. (By the way, the SortExpression property can contain more then one field separated by comma.)
    Then select Enable paging & Enable sorting.
    Add the new label control "lblStatus" on web form.
    The Select method of the ObjectDataSource1 control should be changed to the EmployeeDataAccessLayer.LoadCachedEmployees()
    and the data access layer class should be modified like this:

    // adding a few more namespaces
    using System.Web;
    using System.Web.Caching;

    public class EmployeeDataAccessLayer
    {
    public static DataSet LoadCachedEmployees()
    {
    DataSet ds;

    if (HttpContext.Current.Cache["Employees"] == null) {
    ds = EmployeeDataAccessLayer.GetDatasetEmployees();
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    SqlCacheDependencyAdmin.EnableNotifications(CS);
    SqlCacheDependencyAdmin.EnableTableForNotifications(CS, "tblEmployee");
    SqlCacheDependency sqlDependency = new SqlCacheDependency("Sample", "tblEmployee");
    HttpContext.Current.Cache.Insert("Employees", ds, sqlDependency);
    HttpContext.Current.Session["Employees"] = "Data retrieved from Database @ " + DateTime.Now.ToString();
    } else {
    ds = (DataSet)HttpContext.Current.Cache["Employees"];
    HttpContext.Current.Session["Employees"] = "Data retrieved from cache @ " + DateTime.Now.ToString();
    }

    return ds;
    }

    public static DataSet GetDatasetEmployees()
    {
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    SqlConnection con = new SqlConnection(CS);
    SqlDataAdapter da = new SqlDataAdapter("select * from tblEmployee", con);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
    }
    }

    Eventually, in codebehind code, we have to add an event method for lblStatus control. (Unfortunately lblStatus_PreRender event is fired BEFORE the execution of LoadCachedEmployees(), hence the lblStatus displayed message will always concern the previous stage of loading the grid...)

    protected void lblStatus_PreRender(object sender, EventArgs e)
    {
    if (Session["Employees"] != null)
    lblStatus.Text = Session["Employees"].ToString();
    }

    That's all!

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.