Custom paging and sorting in gridview - Part 55

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



For explanation of the code in this article, please watch the video by clicking on the image at the bottom of this page.

In this video we will discuss about implementing custom sorting and paging in an asp.net gridview control that does not use any data source controls. Please watch Part 54 before proceeding with this video.



Step 1: Create a stored procedure to retrieve employees sorted 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,
@SortExpression nvarchar(50),
@SortDirection nvarchar(50),
@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
case when @SortExpression = 'EmployeeId' and @SortDirection = 'ASC' 
then EmployeeId end asc, 
case when @SortExpression = 'EmployeeId' and @SortDirection = 'DESC' 
 then EmployeeId end desc,
case when @SortExpression = 'Name' and @SortDirection = 'ASC' 
 then Name end asc, 
case when @SortExpression = 'Name' and @SortDirection = 'DESC' 
 then Name end desc,
case when @SortExpression = 'Gender' and @SortDirection = 'ASC' 
 then Gender end asc, 
case when @SortExpression = 'Gender' and @SortDirection = 'DESC' 
 then Gender end desc,
case when @SortExpression = 'City' and @SortDirection = 'ASC' 
 then City end asc, 
case when @SortExpression = 'City' and @SortDirection = 'DESC' 
 then City end desc
) 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, 
            string sortExpression, string sortDirection, 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 paramSortExpression = new SqlParameter();
                paramSortExpression.ParameterName = "@SortExpression";
                paramSortExpression.Value = sortExpression;
                cmd.Parameters.Add(paramSortExpression);

                SqlParameter paramSortDirection = new SqlParameter();
                paramSortDirection.ParameterName = "@SortDirection";
                paramSortDirection.Value = sortDirection;
                cmd.Parameters.Add(paramSortDirection);

                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" AllowSorting="true"
    CurrentSortField="EmployeeId" CurrentSortDirection="ASC"
    AllowPaging="true" PageSize="3"
    OnSorting="GridView1_Sorting">
</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
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                int totalRows = 0;
                GridView1.DataSource = EmployeeDataAccessLayer.
                    GetEmployees(GridView1.PageIndex, GridView1.PageSize, 
                    GridView1.Attributes["CurrentSortField"], 
                    GridView1.Attributes["CurrentSortDirection"], out totalRows);
                GridView1.DataBind();

                DatabindRepeater(GridView1.PageIndex, 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, 
                GridView1.Attributes["CurrentSortField"], 
                GridView1.Attributes["CurrentSortDirection"], 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();
        }
        private void SortGridview(GridView gridView, GridViewSortEventArgs e, 
            out SortDirection sortDirection, out string sortField)
        {
            sortField = e.SortExpression;
            sortDirection = e.SortDirection;

            if (gridView.Attributes["CurrentSortField"] != null && 
                gridView.Attributes["CurrentSortDirection"] != null)
            {
                if (sortField == gridView.Attributes["CurrentSortField"])
                {
                    if (gridView.Attributes["CurrentSortDirection"] == "ASC")
                    {
                        sortDirection = SortDirection.Descending;
                    }
                    else
                    {
                        sortDirection = SortDirection.Ascending;
                    }
                }

                gridView.Attributes["CurrentSortField"] = sortField;
                gridView.Attributes["CurrentSortDirection"] = 
                    (sortDirection == SortDirection.Ascending ? "ASC" : "DESC");
            }
        }
        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            SortDirection sortDirection = SortDirection.Ascending;
            string sortField = string.Empty;

            SortGridview(GridView1, e, out sortDirection, out sortField);
            string strSortDirection = 
                sortDirection == SortDirection.Ascending ? "ASC" : "DESC";

            int totalRows = 0;
            GridView1.DataSource = EmployeeDataAccessLayer.
                GetEmployees(GridView1.PageIndex, GridView1.PageSize, 
                e.SortExpression, strSortDirection, out totalRows);
            GridView1.DataBind();
            DatabindRepeater(GridView1.PageIndex, GridView1.PageSize, totalRows);
        }
    }
}