Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Implement custom paging in an 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
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

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;

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

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


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


                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:Repeater ID="repeaterPaging" runat="server">
        <asp:LinkButton ID="pagingLinkButton" runat="server" 
            Text='<%#Eval("Text") %>' 
            CommandArgument='<%# Eval("Value") %>'
            Enabled='<%# Eval("Enabled") %>' 

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

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

1 comment:

  1. How we can paging indexes that has creates for grid view?

    In cases that grid view have many pages, the page style is not clean & beautiful.


It would be great if you can help share these free resources