Custom paging user control for Large amount of data in Gridview

Posted: September 30, 2013 in ASP.NET

Introduction

A scalable web application uses paging techniques to load large data. Paging enables a web-application to efficiently retrieve only the specific rows it needs from the database, and avoid having to pull back dozens, hundreds, or even thousands of results to the web-server. Today I am going to demonstrate how a custom paging can be implemented in asp.net application. It is a very essential approach to use paging technique in applications where lot of data to be loaded from database. Using a good paging technique can lead to significant performance wins within your application (both in terms of latency and throughput), and reduce the load on your database.

Custom Paging in Gridiew

Custom Paging in Gridiew

Background

I’ve seen many times where paging is required and developers gets stuck when there is large amount of data to be displayed in Gridview and other controls. For small amount of data it is ok if we use standard paging of gridview but in case of large data i.e. in millions then it takes lots of time to fetch data from Database. So I’ve decided to build user control for Paging. This controls provides the following features:

  1. First, Next, Last, Previous along with, user can go to any page by putting page no. in Paging TextBox. This feature is not available in traditional Gridview and other third party control, to move on specific page user has to click on next..next..or page no. button every time till the page is reached.
  2. This control will display Current and No. of Total Pages. Ex. Page No. 2 of 23
  3. This control will display starting and ending row no. of current page and Total No. of rows. Ex. Records 11-20 of 1234
  4. User can select Page Size i.e. No. of rows displayed in page i.e. 10, 20, 50, 100
  5. Very Important is Performance. It doesn’t matter how many rows in tables. It will fetch only those rows selected in page size. User has to pass only PageIndex and PageSize only in procedure.
  6. Note: This Paging control is tested on table having more than 1 Million rows. This will be very helpful where it has to display large no. of data in Gridview.

Using the Code

Step 1: Add ASCX page in your Project and name it GridViewPaging.ascx

Step 2: Copy and paste the below style css on GridViewPaging.ascx markup page.

<style type="text/css">
      .navigationButton {
      -webkit-box-shadow: rgba(0,0,0,0.2) 0 1px 0 0;
      -moz-box-shadow: rgba(0,0,0,0.2) 0 1px 0 0;
      box-shadow: rgba(0,0,0,0.2) 0 1px 0 0;
      border-bottom-color: #333;
      border: 1px solid #ffffff;
      background-color: #EAF2D3;
      border-radius: 5px;
      -moz-border-radius: 5px;
      -webkit-border-radius: 5px;
      color: #0d76c3;
      font-family: 'Verdana',Arial,sans-serif;
      font-size: 14px;
      text-shadow: #b2e2f5 0 1px 0;
      padding: 5px;
      cursor: pointer;
 }
.tablePaging {
      font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
      width: 100%;
      border-collapse: collapse;
 }
.tablePaging td {
      font-size: 1em;
      border: 1px solid #ffffff;
      padding: 3px 7px 2px 7px;
      background-color: #b1dbfa;
      font-size: 10pt;
 } 
</style>

Step 3: Copy and paste the below HTML on GridViewPaging.ascx markup page.

<table class="tablePaging">
 <tr>
     <td style="width: 15%; text-align: center;">
     <asp:Label ID="lblPageSize" runat="server" Text="Page Size: "></asp:Label>
     <asp:DropDownList ID="PageRowSize" runat="server">
          <asp:ListItem Selected="True">10</asp:ListItem>
          <asp:ListItem>20</asp:ListItem>
          <asp:ListItem>50</asp:ListItem>
          <asp:ListItem>100</asp:ListItem>
    </asp:DropDownList>
 </td>
 <td style="width: 25%; text-align: center;">
     <asp:Label ID="RecordDisplaySummary" runat="server"></asp:Label></td>
 <td style="width: 20%; text-align: center;">
     <asp:Label ID="PageDisplaySummary" runat="server"></asp:Label></td>
 <td style="width: 40%; text-align: center;">
     <asp:Button ID="First" runat="server" Text="<<" Width="45px" OnClick="First_Click" CssClass="navigationButton" />&nbsp;
     <asp:Button ID="Previous" runat="server" Text="<" Width="45px" OnClick="Previous_Click" CssClass="navigationButton" />&nbsp;
     <asp:TextBox ID="SelectedPageNo" runat="server" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" Font-       
     Names="Verdana" Font-Size="Large" OnTextChanged="SelectedPageNo_TextChanged" Width="100px" AutoPostBack="True"   
     MaxLength="8"></asp:TextBox>&nbsp;
     <asp:Button ID="Next" runat="server" Text=">" Width="45px" OnClick="Next_Click" CssClass="navigationButton" />&nbsp;
     <asp:Button ID="Last" runat="server" Text=">>" Width="45px" OnClick="Last_Click" CssClass="navigationButton" />&nbsp; 
 </td>
 </tr>
 <tr id="trErrorMessage" runat="server" visible="false">
     <td colspan="4" style="background-color: #e9e1e1;">
     <asp:Label ID="GridViewPagingError" runat="server" Font-Names="Verdana" Font-Size="9pt" ForeColor="Red"></asp:Label>
     <asp:HiddenField ID="TotalRows" runat="server" Value="0" />
     </td>
 </tr>
</table>

Step 4: Copy and paste the below code in Code behind (server side code) on GridViewPaging.ascx page.

public EventHandler pagingClickArgs;
protected void Page_Load(object sender, EventArgs e)
{
    try
    {
         trErrorMessage.Visible = false;
         if (!IsPostBack)
         {
             SelectedPageNo.Text = "1";
             GetPageDisplaySummary();
         } 
     }
     catch (Exception ex)
     {
        ShowGridViewPagingErrorMessage(ex.Message.ToString());
      }
 }
protected void First_Click(object sender, EventArgs e)
{
    try
    {
         if(!IsValid()) {return;};
         SelectedPageNo.Text = "1";
         GetPageDisplaySummary();
         pagingClickArgs(sender, e);
    }
    catch (Exception ex)
    {
       ShowGridViewPagingErrorMessage(ex.Message.ToString());
    }
 }
protected void Previous_Click(object sender, EventArgs e)
 {
     try
     {
        if (!IsValid()) { return; };
        if (Convert.ToInt32(SelectedPageNo.Text) > 1)
        {
            SelectedPageNo.Text = (Convert.ToInt32(SelectedPageNo.Text) - 1).ToString();
        }
        GetPageDisplaySummary();
        pagingClickArgs(sender, e);
     }
     catch (Exception ex)
     {
         ShowGridViewPagingErrorMessage(ex.Message.ToString());
      }
 }
protected void SelectedPageNo_TextChanged(object sender, EventArgs e)
 {
     try
     {
         if (!IsValid()) { return; };
         int currentPageNo = Convert.ToInt32(SelectedPageNo.Text);
         if (currentPageNo < GetTotalPagesCount())
         {
            SelectedPageNo.Text = (currentPageNo).ToString();
         }
         GetPageDisplaySummary();
         pagingClickArgs(sender, e);
     }
     catch (Exception ex)
     {
        ShowGridViewPagingErrorMessage(ex.Message.ToString());
     }
 }
protected void Next_Click(object sender, EventArgs e)
 {
     try
     {
        if (!IsValid()) { return; };
        int currentPageNo = Convert.ToInt32(SelectedPageNo.Text);
        if (currentPageNo < GetTotalPagesCount())
        {
            SelectedPageNo.Text = (currentPageNo + 1).ToString();
        }
        GetPageDisplaySummary();
        pagingClickArgs(sender, e);
     }
     catch (Exception ex)
     {
          ShowGridViewPagingErrorMessage(ex.Message.ToString());
     }
 }
protected void Last_Click(object sender, EventArgs e)
 {
     try
     {
         if (!IsValid()) { return; };
         SelectedPageNo.Text = GetTotalPagesCount().ToString();
         GetPageDisplaySummary();
         pagingClickArgs(sender, e);
     }
     catch (Exception ex)
     {
         ShowGridViewPagingErrorMessage(ex.Message.ToString());
     }
 }
private int GetTotalPagesCount()
 {
      try
      {
            int totalPages = Convert.ToInt32(TotalRows.Value) / Convert.ToInt32(PageRowSize.SelectedValue); 
            // total page item to be displyed
            int pageItemRemain = Convert.ToInt32(TotalRows.Value) % Convert.ToInt32(PageRowSize.SelectedValue); 
            // remaing no of pages
           if (pageItemRemain > 0)// set total No of pages
           {
              totalPages = totalPages + 1;
           }
           else
           {
              totalPages = totalPages + 0;
           }
           return totalPages;
        }
        catch (Exception ex)
        {
              throw ex;
         }
 }
private void GetPageDisplaySummary()
 {
 try
 {
    PageDisplaySummary.Text = "Page " + SelectedPageNo.Text.ToString() + " of " + GetTotalPagesCount().ToString();
    int startRow = (Convert.ToInt32(PageRowSize.SelectedValue) * (Convert.ToInt32(SelectedPageNo.Text) - 1)) + 1;
    int endRow = Convert.ToInt32(PageRowSize.SelectedValue) * Convert.ToInt32(SelectedPageNo.Text);
    int totalRecords = Convert.ToInt32(TotalRows.Value);
    if (totalRecords >= endRow)
    {
                RecordDisplaySummary.Text = "Records: " + startRow.ToString() + " - " + endRow.ToString() + " of " + totalRecords.ToString();
    }
    else
    {
          RecordDisplaySummary.Text = "Records: " + startRow.ToString() + " - " + totalRecords.ToString() + " of " + totalRecords.ToString();
    }
 }
 catch (Exception ex)
 {
 throw ex;
 }
 }
private bool IsValid()
 {
    try
    {
       if (String.IsNullOrEmpty(SelectedPageNo.Text.Trim()) || (SelectedPageNo.Text == "0"))
       {
          SelectedPageNo.Text = "1";
          return false;
       }
      else if (!IsNumeric(SelectedPageNo.Text))
      {
         ShowGridViewPagingErrorMessage("Please Insert Valid Page No.");
         return false;
      }
      else
      {
         return true;
      }
  }
  catch (FormatException)
  {
    return false;
  }
 }
private bool IsNumeric(string PageNo)
 {
    try
    {
       int i = Convert.ToInt32(PageNo);
       return true;
     }
    catch (FormatException)
    {
       return false;
    }
 }
private void ShowGridViewPagingErrorMessage(string msg)
 {
    trErrorMessage.Visible = true;
    GridViewPagingError.Text = "Error: " + msg;
 }

Step 5: Create table with the column name shown in below Procedure and Create below procedure in your SQL database.

——————————————————————————————-

Create table and name it EmployeeList

CREATE TABLE [dbo].[EmployeeList]

(
[EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](200) NOT NULL,
[LastName] [varchar](200) NOT NULL,
[Department] [varchar](200) NOT NULL,
[Experience] [decimal](18, 0) NOT NULL,
[Salary] [decimal](18, 0) NOT NULL,
CONSTRAINT [PK_EmployeeList] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)
)

Run below query to insert 1 Million test rows into above table

This will take some time to insert data. Later on you can do indexing etc. whatever you required

DECLARE @i bigint = 0
WHILE (@i<100000)
BEGIN
SET @i= @i+1;
INSERT INTO EmployeeList1(FirstName,LastName,Department,Experience,Salary)
VALUES(‘FirstName_’ + convert(varchar,@i), ‘LastName_’ + convert(varchar,@i),’Department_’ + convert(varchar,@i),@i,@i)
END

——————————————————————————————-

CREATE procedure [dbo].[CustomPaging]

@pageIndex int = 0,
@pageSize int = 0
As
BEGIN
 SET NOCOUNT ON;

 /* Set Starting Row Index and Ending Row Index */
 -------------------------------------------------
 declare @startRowIndex int = 0
 SET @startRowIndex = (@pageIndex * @pageSize)+ 1

 declare @endRowIndex int = 0
 SET @endRowIndex = (@startRowIndex + @pageSize - 1)

 /* recordCount required for paging in front end */
 declare @recordCount int = 0 
 SET @recordCount=0
 -------------------------------------------------

 Select @recordCount=count(*) from EmployeeList A WITH (NOLOCK)

 Select EmployeeID, FirstName, LastName, Department, Experience, Salary, tRecordCount
 From 
 (
     Select A.EmployeeID, A.FirstName, A.LastName, A.Department, A.Experience, A.Salary,
     ROW_NUMBER() OVER(ORDER BY A.EmployeeID) as RowNum, @recordCount as tRecordCount
     from EmployeeList A WITH (NOLOCK)
 ) as temp 
 WHERE RowNum BETWEEN @startRowIndex AND @endRowIndex

END

Step 6: Add new ASPX page in your project and name it CustomPaging.aspx

Step 7: Copy and paste below code on  CustomPaging.aspx Markup.

Add below code in top or if you drag and drop GridViewPaging control on this page it will be created automatically

<%@ Register Src="~/GridViewPaging.ascx" TagPrefix="uc1" TagName="GridViewPaging" %>
<form id="form1" runat="server">
   <div>
       <asp:GridView ID="GridView1" runat="server" CellPadding="4" Font-Names="Verdana" Font-Size="9pt" Width="100%" AllowPaging="True" ForeColor="#333333" GridLines="Both"></asp:GridView>
       <uc1:GridViewPaging runat="server" id="GridViewPagingControl" />
   </div>
 </form>

Step 8: Copy and paste below code on CustomPaging.aspx code behind.

 protected void Page_Load(object sender, EventArgs e)
 { 
    //---------------------------------------------------------------------
    //Step 1: Bind Grid On Page Load
    //---------------------------------------------------------------------
    if (!Page.IsPostBack)
    {
        dataBind(); 
    }
    //---------------------------------------------------------------------
    //Step 2: Event Handler for First, Previous, Next and Last Button Click
    //---------------------------------------------------------------------
     GridViewPagingControl.pagingClickArgs += new EventHandler(Paging_Click);

 }

 //--------------------------------------------------------------------------------------------------------
 // Step 3: Add Below Function which gets call on User Controls First, Previous, Next and Last Button Click
 //--------------------------------------------------------------------------------------------------------
 private void Paging_Click(object sender, EventArgs e )
 {
     GridView1.PageSize = Convert.ToInt32(((DropDownList)GridViewPagingControl.FindControl("PageRowSize")).SelectedValue); 
     GridView1.PageIndex = Convert.ToInt32(((TextBox)GridViewPagingControl.FindControl("SelectedPageNo")).Text) - 1;
     dataBind();
}
private void dataBind()
 {
    SqlConnection connSql = new SqlConnection("Server=SERVERNAME;Database=DATABASENAME;uid=USERID;pwd=PASSWORD");
    DataSet ds = new DataSet();
    SqlCommand cmdSql = new SqlCommand();
    cmdSql.Connection = connSql; 
    cmdSql.CommandType = System.Data.CommandType.StoredProcedure;
    cmdSql.CommandText = "CustomPaging"; 

    //---------------------------------------------------------------------------------
    //Step 4: Pass two Parameter @pageIndex and @pageSize to SQL Procedure
    //---------------------------------------------------------------------------------
    cmdSql.Parameters.Add(new SqlParameter("@pageIndex", GridView1.PageIndex));
    cmdSql.Parameters.Add(new SqlParameter("@pageSize", GridView1.PageSize));
    connSql.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmdSql); 
    da.Fill(ds);

    if (ds.Tables[0].Rows.Count != 0)
    {
    //---------------------------------------------------------------------------------
    //Step 5: Store No. of Total Rows in Hidden Field which is situated in User Control
    //---------------------------------------------------------------------------------
((HiddenField)GridViewPagingControl.FindControl("TotalRows")).Value = ds.Tables[0].Rows[0]["tRecordCount"].ToString();
    }

    GridView1.DataSource = ds;
    GridView1.DataBind();
    connSql.Close(); 
 }

Advertisements
Comments
  1. Lakshmipathy G says:

    Thanks it helped a lot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s