Gridview paginated data with search and sort functionality in asp.net

Most of the times we need to use GridView control to show tabular data in our asp.net application. We simply write procedure to search the records and bind them with GridView and show 10 to 20 records per page. Have you ever thought that why you are fetching all the records and show only 10 to 20 records, why cannot fetch only those records which we have to show on the page, means 20 records if page size is 20 or 50 if page size is 50.

Suppose we have millions of records in our table and we are fetching all records and showing only 20 records, just imagine the page speed, here we are going to discuss how to get only page size records which will work faster, never matter how many records we have in our table.

Here is the page we are going to create with paginated data, searching, sorting on columns and paging functionality, we will use GridView control and ObjectDataSource with Ajax update panel.

alt text

Let’s start with stored procedures; we will need two procedures one for page records and one for number of records count.

If we don't want sorting functionality on columns then our procedure will be as follows (We will see complete procedure with sorting functionality latter)

CREATE PROC [dbo].[Customer_Search]
     @City VARCHAR(50) 
   , @PinCode VARCHAR(6) 
   , @StartRowIndex INT
   , @MaximumRows INT 
   , @SortExpression VARCHAR(100)
AS
WITH Cust AS
(
   SELECT ROW_NUMBER() OVER (ORDER BY FirstName) AS [RowNo]
    , CustomerID
    FROM Customers
    WHERE City Like '%' + @City + '%'
      AND Zip Like '%' + @PinCode + '%'
)
SELECT Cust.[CustomerID]
, [FirstName]
, [MidleName]
, [LastName]
, [Address1]
, [Address2]
, [City]
, [State] 
, [Country]
, [Zip]
FROM Customers
   INNER JOIN Cust ON Cust.CustomerID = Customers.CustomerID
Where Cust.RowNo Between   @startRowIndex + 1 AND @startRowIndex +@MaximumRows 
ORDER BY Cust.RowNo

See we used WITH Cust AS to get row number and customer Id which will always be fast and further we used it as a table to join and only get page data with the help of row number see the second part of procedure. Copy the same procedure and create a count one to get the total number of records, we are not going to change the parameters here even we are not using then in count procedure see this:

CREATE PROC [dbo].[Customer_SearchCount]
     @City      VARCHAR(50) 
   , @PinCode    VARCHAR(6)
   , @StartRowIndex     INT
   , @MaximumRows      INT
   , @SortExpression VARCHAR(100)
AS    
   SELECT COUNT(CustomerID)
    FROM Customers
    WHERE City Like '%' + @City + '%'
        AND Zip Like '%' + @PinCode + '%'

We completed our procedure part, now let's create methods to get data from database, I wrote two methods in my CustomerRepository class, here they are:

// Method to get page data
public static DataTable Customer_Search(String City, String Zip
        , Int32 StartRowIndex, Int32 MaximumRows, string SortExpression)
{
   using (SqlConnection conn = new
       SqlConnection(ConfigurationManager.ConnectionStrings["appConnection"].ToString()))
  {
       SqlCommand CMD = new SqlCommand("Customer_Search", conn);
       CMD.CommandType = CommandType.StoredProcedure;

        CMD.Parameters.AddWithValue("@City", City ?? "");
        CMD.Parameters.AddWithValue("@PinCode", Zip ?? "");
        CMD.Parameters.AddWithValue("@StartRowIndex", StartRowIndex);
        CMD.Parameters.AddWithValue("@MaximumRows", MaximumRows);
        CMD.Parameters.AddWithValue("@SortExpression", SortExpression);

        if (conn.State == ConnectionState.Closed) conn.Open();
        SqlDataReader dr = CMD.ExecuteReader(CommandBehavior.CloseConnection);
        DataTable dtab = new DataTable("CR Report");
        dtab.Load(dr);
        return dtab;
   }
}

// Method to get count of records
public static Int32 Customer_SearchCount(String City, String Zip
    , Int32 StartRowIndex, Int32 MaximumRows, string SortExpression)
{
   using (SqlConnection conn = new 
     SqlConnection(ConfigurationManager.ConnectionStrings["appConnection"].ToString()))
     {
          SqlCommand CMD = new SqlCommand("Customer_SearchCount", conn);
         CMD.CommandType = CommandType.StoredProcedure;

         CMD.Parameters.AddWithValue("@City", City ?? "");
         CMD.Parameters.AddWithValue("@PinCode", Zip ?? "");
         CMD.Parameters.AddWithValue("@StartRowIndex", StartRowIndex);
         CMD.Parameters.AddWithValue("@MaximumRows", MaximumRows);
         CMD.Parameters.AddWithValue("@SortExpression", SortExpression);

         if (conn.State == ConnectionState.Closed) conn.Open();
         var recordCount = CMD.ExecuteScalar();

         return Convert.ToInt32(recordCount);
    }
}

Our coding part is done, time to manage page and GridView control's HTML

<table border="0" cellpadding="0" cellspacing="0" width="100%">
    <tr>
        <td><b>City</b></td>
        <td><asp:TextBox ID="txtCity" runat="server" /></td>
        <td><b>Zip/Pin Code</b></td>
        <td><asp:TextBox ID="txtZip" runat="server" /></td>
        <td><asp:Button ID="btnSearch" runat="server" Text="Search" 
                onclick="btnSearch_Click" /></td>
    </tr>
</table>
<br />
<asp:GridView ID="gvCustomer" runat="server" AutoGenerateColumns="false" 
    Width="100%"
    PageSize="10"
    AllowPaging="true"
    AllowSorting="true"
    OnSorting="gvCustomer_Sorting">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" 
             SortExpression="CustomerID" />
        <asp:BoundField DataField="FirstName" HeaderText="First Name" 
             SortExpression="FirstName" />
        <asp:BoundField DataField="LastName" HeaderText="Last Name" 
             SortExpression="LastName" />
        <asp:TemplateField HeaderText="Address">
            <ItemTemplate>
                <%# String.Format("{0}, {1}", Eval("Address1"), Eval("Address2") ) %>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
        <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
        <asp:BoundField DataField="Country" HeaderText="Country" 
             SortExpression="Country" />
        <asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
    </Columns>
</asp:GridView>

Now come to code behind and adjust different events:

protected void Page_Load(object sender, EventArgs e)   
{
   if (!IsPostBack)
   {
      ViewState["SortDirection"] = "ASC";
      ViewState["SortExpression"] = "FirstName";
      BindGridView(0);
   }
}

Method to bind the asp.net GridView, we used here ObjectDataSource from code behind which you can use it from HTML as well

private void BindGridView(Int32 currentPage)
{
  ObjectDataSource odsCustomer = new ObjectDataSource();
  odsCustomer.TypeName = "WebApplication1.CustomerRepository";
  odsCustomer.SelectMethod = "Customer_Search";
  odsCustomer.SelectCountMethod = "Customer_SearchCount";
  odsCustomer.EnablePaging = true;

  odsCustomer.SelectParameters.Add("City", DbType.String, txtCity.Text.Trim());
  odsCustomer.SelectParameters.Add("Zip", DbType.String, txtZip.Text.Trim());
  odsCustomer.SelectParameters.Add("StartRowIndex", DbType.Int32,
           gvCustomer.PageIndex.ToString());
  odsCustomer.SelectParameters.Add("MaximumRows", DbType.Int32, 
           gvCustomer.PageSize.ToString());
  odsCustomer.SelectParameters.Add("SortExpression", 
          DbType.String, String.Format("{0} {1}",    
  ViewState["SortExpression"], ViewState["SortDirection"]));

  gvCustomer.DataSource = odsCustomer;
  gvCustomer.DataBind();
}

Now add the button click event:

protected void btnSearch_Click(object sender, EventArgs e)
{
   BindGridView(0);
}

Time to adjust sorting functionality of grid view, we already used sort expression in our gridview HTML

protected void gvCustomer_Sorting(object sender, GridViewSortEventArgs e)
{
    if(ViewState["SortDirection"] == null)
       ViewState["SortDirection"] = "ASC";
    else
       ViewState["SortDirection"] =
          Convert.ToString(ViewState["SortDirection"]) == "ASC"? "DESC": "ASC";

    ViewState["SortExpression"] = e.SortExpression;

    BindGridView(gvCustomer.PageIndex);
}

In our procedure we removed the sorting functionality in beginning so let's complete our procedure now to sort records by columns, we will make change only in WITH Cust AS part, see this:

CREATE PROC [dbo].[Customer_Search]
     @City VARCHAR(50) 
   , @PinCode VARCHAR(6) 
   , @StartRowIndex INT
   , @MaximumRows INT 
   , @SortExpression VARCHAR(100)
AS
WITH Cust AS
(
   SELECT ROW_NUMBER() OVER (ORDER BY 
      CASE WHEN @SortExpression = 'CustomerID ASC'  THEN  CustomerID END ASC,
      CASE WHEN @SortExpression = 'CustomerID DESC' THEN  CustomerID END DESC,

      CASE WHEN @SortExpression = 'FirstName ASC'  THEN  FirstName END ASC,
      CASE WHEN @SortExpression = 'FirstName DESC' THEN  FirstName END DESC,

      CASE WHEN @SortExpression = 'MidleName ASC'  THEN  MidleName END ASC,
      CASE WHEN @SortExpression = 'MidleName DESC' THEN  MidleName END DESC,

      CASE WHEN @SortExpression = 'LastName ASC'  THEN  FirstName END ASC,
      CASE WHEN @SortExpression = 'LastName DESC' THEN  FirstName END DESC,

      CASE WHEN @SortExpression = 'Address1 ASC'  THEN  FirstName END ASC,
      CASE WHEN @SortExpression = 'Address1 DESC' THEN  FirstName END DESC,

      CASE WHEN @SortExpression = 'City ASC'  THEN  City END ASC,
      CASE WHEN @SortExpression = 'City DESC' THEN  City END DESC,

      CASE WHEN @SortExpression = 'State ASC'  THEN  State END ASC,     
      CASE WHEN @SortExpression = 'State DESC' THEN  State END DESC,

      CASE WHEN @SortExpression = 'Country ASC'  THEN  Country END ASC,
      CASE WHEN @SortExpression = 'Country DESC' THEN  Country END DESC,

      CASE WHEN @SortExpression = 'Zip ASC'  THEN  Zip END ASC,
      CASE WHEN @SortExpression = 'Zip DESC' THEN  Zip END DESC 
     ) AS [RowNo]
    , CustomerID
    FROM Customers
    WHERE City Like '%' + @City + '%'
      AND Zip Like '%' + @PinCode + '%'
)
SELECT Cust.[CustomerID]
, [FirstName]
, [MidleName]
, [LastName]
, [Address1]
, [Address2]
, [City]
, [State] 
, [Country]
, [Zip]
FROM Customers
   INNER JOIN Cust ON Cust.CustomerID = Customers.CustomerID
Where Cust.RowNo Between   @startRowIndex + 1 AND @startRowIndex +@MaximumRows 
ORDER BY Cust.RowNo

It's complete now, I know it became long but I try to provide every thing which we need for paginated data. It is really helpful for table where we have tons of records and want to show only 20 t0 50 or 100 records on a page, if we will use our normal procedure and pagination really those page will take lot's of time to render if internet speed is slow because entire records need to travel from database to client machine.

Ali Adravi Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
  • asp.net
  • gridview
By Ali Adravi On 16 Feb, 13  Viewed: 8,576

Other blogs you may like

Readonly textbox postback issues and solutions

In many cases we need to use read only text box so user cannot edit the value but readonly textbox will lost its value after postback. Let’s say you have a read only text box to set date by using ajax calendar so after post back date will be lost, other case might be you are setting some value in... By Ali Adravi   On 24 Apr 2013  Viewed: 4,306

Call code behind method from JavaScript in asp.net

There are ways to call a web service method JavaScript, for more detail you can see [how to retrieve data from database using JavaScript in asp.net][1], but is there any way to call a normal method from JavaScript? And the answer is No; show how we can call a code behind method from JavaScript,... By Jonathan King   On 08 Apr 2013  Viewed: 12,512

ASP.Net 4.5 new feature Model Binding

A nice feature with ASP.Net is the model binding, it reduced our code and effort to bind our well know controls like GridView, DataList, Repeater etc. So let’s see how we can bind our old controls in new and easy way. Let’s see how we bind the our grid before 4.5 1. We write the code to get... By Mike .Net   On 17 Jan 2013  Viewed: 3,217

Upload multiple image in multiple size with progress bar in asp.net

In asp.net there is not control to select multiple files and upload them once with progress bar, so we will use a small third party DLL to achieve this functionality. We will use Flajaxian FileUploader, you can download it from [http://www.flajaxian.com][1] We will create three different images... By Hamden   On 12 Jul 2012  Viewed: 6,571

Check/Uncheck all checkboxes in asp.net by javascript

I was searching for select/deselect all checkboxes code into a gridview. I found most of them only provide to select and deselet all the checkboxes if any of the checkbox is unselected into grid the main checkbox is not affecting. I further try to search some useful code but could not found any... By Ali Adravi   On 10 Jul 2012  Viewed: 5,454