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

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

3 comments:

  1. Venkat, there is a typo in 55 which is different from 54.

    55 if ((totalRows / pageSize) != 0)
    54 if ((totalRows % pageSize) != 0)

    I used 55, which caused me a lot of trouble until i spotted it. Now that I'm using the "%", everything is much better. thanks

    ReplyDelete
    Replies
    1. Thank you very much for bringing this to my attention. I have corrected it now.

      Delete
  2. I added a search filter to my grid, when i search it filters the grid but does not filter the pages

    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.