Implement custom paging in an asp.net gridview without using datasource controls - Part 54

Suggested Videos 
Part 51 - Implement default paging gridview that uses objectdatasource
Part 52 - Implement custom paging in gridview that uses objectdatasource
Part 53 - Default paging in gridview without using datasource controls



In this video we will discuss about implementing custom paging in a gridview control that does not use any data source controls.

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 employees by page index and page size. This procedure also has an output parameter to return the total number of rows in the database table.
Create proc spGetEmployees_by_PageIndex_and_PageSize
@PageIndex int,  
@PageSize int,
@TotalRows int output
as  
Begin  
Declare @StartRowIndex int
Declare @EndRowIndex int

Set @StartRowIndex = (@PageIndex * @PageSize) + 1;
    Set @EndRowIndex = (@PageIndex + 1) * @PageSize;

Select EmployeeId, Name, Gender, City from  
(Select ROW_NUMBER() over (order by EmployeeId) as RowNumber, EmployeeId, Name, Gender, City  
from tblEmployee) Employees  
Where RowNumber >= @StartRowIndex and RowNumber <= @EndRowIndex

Select @TotalRows = COUNT(*) from tblEmployee
End

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". 
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 pageIndex, int pageSize, out int totalRows)
        {
            List<Employee> listEmployees = new List<Employee>();

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

                SqlParameter paramStartIndex = new SqlParameter();
                paramStartIndex.ParameterName = "@PageIndex";
                paramStartIndex.Value = pageIndex;
                cmd.Parameters.Add(paramStartIndex);

                SqlParameter paramMaximumRows = new SqlParameter();
                paramMaximumRows.ParameterName = "@PageSize";
                paramMaximumRows.Value = pageSize;
                cmd.Parameters.Add(paramMaximumRows);

                SqlParameter paramOutputTotalRows = new SqlParameter();
                paramOutputTotalRows.ParameterName = "@TotalRows";
                paramOutputTotalRows.Direction = ParameterDirection.Output;
                paramOutputTotalRows.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(paramOutputTotalRows);

                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);
                }

                rdr.Close();
                totalRows = (int)cmd.Parameters["@TotalRows"].Value;
                
            }
            return listEmployees;
        }
    }
}

Step 3: Copy and paste the following HTML on WebForm1.aspx
<asp:GridView ID="GridView1" runat="server" 
    AllowPaging="true" PageSize="3">
</asp:GridView>
<asp:Repeater ID="repeaterPaging" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="pagingLinkButton" runat="server" 
            Text='<%#Eval("Text") %>' 
            CommandArgument='<%# Eval("Value") %>'
            Enabled='<%# Eval("Enabled") %>' 
            OnClick="linkButton_Click">
        </asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>

Step 4: Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        int totalRows = 0;
        GridView1.DataSource = EmployeeDataAccessLayer.
            GetEmployees(0, GridView1.PageSize, out totalRows);
        GridView1.DataBind();

        DatabindRepeater(0, GridView1.PageSize, totalRows);
    }
}
protected void linkButton_Click(object sender, EventArgs e)
{
    int totalRows = 0;
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    pageIndex -= 1;
    GridView1.PageIndex = pageIndex;
    GridView1.DataSource = EmployeeDataAccessLayer.
        GetEmployees(pageIndex, GridView1.PageSize, out totalRows);
    GridView1.DataBind();
    DatabindRepeater(pageIndex, GridView1.PageSize, totalRows);
}
private void DatabindRepeater(int pageIndex, int pageSize, int totalRows)
{
    int totalPages = totalRows / pageSize;
    if ((totalRows % pageSize) != 0)
    {
        totalPages += 1;
    }

    List<ListItem> pages = new List<ListItem>();
    if (totalPages > 1)
    {
        for (int i = 1; i <= totalPages; i++)
        {
            pages.Add(new ListItem(i.ToString(), i.ToString(), i != (pageIndex + 1)));
        }
    }
    repeaterPaging.DataSource = pages;
    repeaterPaging.DataBind();
}