In this article I will explain how to display large amount of data in GridView with Search functionality in ASP.Net by making use of Paging using Stored Procedures and jQuery AJAX.
By combining the power of SQL Server and jQuery we can efficiently do paging through large amounts of data.
 
Database
For this article I have used Microsoft’s Northwind database. You can download it using the link provided below.
 
HTML Markup
Below is the HTML Markup. It has an ASP.Net GridView, a TextBox for entering search term and an HTML DIV control where the Pager will be populated for pagination.
Search:
<asp:TextBox ID="txtSearch" runat="server" />
<hr />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name"
            ItemStyle-CssClass="ContactName" />
        <asp:BoundField HeaderStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
        <asp:BoundField HeaderStyle-Width="150px" DataField="City" HeaderText="City" />
    </Columns>
</asp:GridView>
<br />
<div class="Pager">
</div>
 
 
Namespaces
You will need to import the following namespaces
C#
using System.Data;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
 
VB.Net
Imports System.Data
Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient
 
Populating GridView Dummy with Dummy Data
In Page Load event of the page I am populating the GridView with dummy data so that we can use its HTML Table structure to populate data using jQuery.
I have created a variable PageSize which will decide the number of records to be displayed per page in GridView
Note: The dummy DataTable being populated has the same columns which will be returned from the SQL Query
C#
private static int PageSize = 5;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDummyRow();
    }
}
 
private void BindDummyRow()
{
    DataTable dummy = new DataTable();
    dummy.Columns.Add("CustomerID");
    dummy.Columns.Add("ContactName");
    dummy.Columns.Add("City");
    dummy.Rows.Add();
    gvCustomers.DataSource = dummy;
    gvCustomers.DataBind();
}
 
VB.Net
Private Shared PageSize As Integer = 5
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindDummyRow()
    End If
End Sub
 
Private Sub BindDummyRow()
    Dim dummy As New DataTable()
    dummy.Columns.Add("CustomerID")
    dummy.Columns.Add("ContactName")
    dummy.Columns.Add("City")
    dummy.Rows.Add()
    gvCustomers.DataSource = dummy
    gvCustomers.DataBind()
End Sub
 
 
Implement Search and Pagination using SQL Server Stored Procedure
The following stored procedure which will perform search and pagination on the table records and return per page data.
CREATE PROCEDURE [dbo].[GetCustomers_Pager]
       @SearchTerm VARCHAR(100) = ''
      ,@PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[City]
      INTO #Results
      FROM [Customers]
      WHERE [ContactName] LIKE @SearchTerm + '%' OR @SearchTerm = ''
      SELECT @RecordCount = COUNT(*)
      FROM #Results
          
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    
      DROP TABLE #Results
END
 
 
 
WebMethod to handle jQuery AJAX calls
Below is the Web Method which is being called by the jQuery AJAX function which populates the GridView with data and also does Pagination.
The SearchTerm and PageIndex are passed as parameter from Client side, while the PageSize we get from the static variable we have declared. Based on these parameters we can fetch the records Page wise.
The records fetched along with the Total Record Count are sent to Client Side as XML string.  
Note: Total Record Count is necessary to populate pager.
C#
[WebMethod]
public static string GetCustomers(string searchTerm, int pageIndex)
{
    string query = "[GetCustomers_Pager]";
    SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    cmd.Parameters.AddWithValue("@PageSize", PageSize);
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
    return GetData(cmd, pageIndex).GetXml();
}
 
private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
    string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds, "Customers");
                DataTable dt = new DataTable("Pager");
                dt.Columns.Add("PageIndex");
                dt.Columns.Add("PageSize");
                dt.Columns.Add("RecordCount");
                dt.Rows.Add();
                dt.Rows[0]["PageIndex"] = pageIndex;
                dt.Rows[0]["PageSize"] = PageSize;
                dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                ds.Tables.Add(dt);
                return ds;
            }
        }
    }
}
 
VB.Net
<WebMethod()> _
Public Shared Function GetCustomers(searchTerm As String, pageIndex As Integer) As String
    Dim query As String = "[GetCustomers_Pager]"
    Dim cmd As New SqlCommand(query)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
    cmd.Parameters.AddWithValue("@PageSize", PageSize)
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
    Return GetData(cmd, pageIndex).GetXml()
End Function
 
Private Shared Function GetData(cmd As SqlCommand, pageIndex As Integer) As DataSet
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Using con As New SqlConnection(strConnString)
        Using sda As New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using ds As New DataSet()
                sda.Fill(ds, "Customers")
                Dim dt As New DataTable("Pager")
                dt.Columns.Add("PageIndex")
                dt.Columns.Add("PageSize")
                dt.Columns.Add("RecordCount")
                dt.Rows.Add()
                dt.Rows(0)("PageIndex") = pageIndex
                dt.Rows(0)("PageSize") = PageSize
                dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
                ds.Tables.Add(dt)
                Return ds
            End Using
        End Using
    End Using
End Function
 
 
Client Side Implementation
Below is the complete Client Side Implementation, here on load event of jQuery the GridView is populated with PageIndex 1 using the method GetCustomers which populates the ASP.Net GridView which was initially populated with dummy data.
Using the Record Count, the pager is populated inside the HTML DIV using the ASPSnippets_Pager jQuery Plugin.
A jQuery Keyup event handler has been attached to the Search TextBox which will trigger an AJAX call and will filter the GridView with the matched records.
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetCustomers(1);
    });
    $("[id*=txtSearch]").live("keyup", function () {
        GetCustomers(parseInt(1));
    });
    $(".Pager .page").live("click", function () {
        GetCustomers(parseInt($(this).attr('page')));
    });
    function SearchTerm() {
        return jQuery.trim($("[id*=txtSearch]").val());
    };
    function GetCustomers(pageIndex) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetCustomers",
            data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: OnSuccess,
            failure: function (response) {
                alert(response.d);
            },
            error: function (response) {
                alert(response.d);
            }
        });
    }
    var row;
    function OnSuccess(response) {
        var xmlDoc = $.parseXML(response.d);
        var xml = $(xmlDoc);
        var customers = xml.find("Customers");
        if (row == null) {
            row = $("[id*=gvCustomers] tr:last-child").clone(true);
        }
        $("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
        if (customers.length > 0) {
            $.each(customers, function () {
                var customer = $(this);
                $("td", row).eq(0).html($(this).find("ContactName").text());
                $("td", row).eq(1).html($(this).find("CustomerID").text());
                $("td", row).eq(2).html($(this).find("City").text());
                $("[id*=gvCustomers]").append(row);
                row = $("[id*=gvCustomers] tr:last-child").clone(true);
            });
            var pager = xml.find("Pager");
            $(".Pager").ASPSnippets_Pager({
                ActiveCssClass: "current",
                PagerCssClass: "pager",
                PageIndex: parseInt(pager.find("PageIndex").text()),
                PageSize: parseInt(pager.find("PageSize").text()),
                RecordCount: parseInt(pager.find("RecordCount").text())
            });
 
            $(".ContactName").each(function () {
                var searchPattern = new RegExp('(' + SearchTerm() + ')', 'ig');
                $(this).html($(this).text().replace(searchPattern, "<span class = 'highlight'>" + SearchTerm() + "</span>"));
            });
        } else {
            var empty_row = row.clone(true);
            $("td:first-child", empty_row).attr("colspan", $("td", row).length);
            $("td:first-child", empty_row).attr("align", "center");
            $("td:first-child", empty_row).html("No records found for the search criteria.");
            $("td", empty_row).not($("td:first-child", empty_row)).remove();
            $("[id*=gvCustomers]").append(empty_row);
        }
    };
</script>
 
 
CSS
Below is the necessary CSS styles which you need to put on your page.
<style type="text/css">
    body
    {
        font-family: Arial;
        font-size: 10pt;
    }
    table
    {
        border: 1px solid #ccc;
    }
    table th
    {
        background-color: #F7F7F7;
        color: #333;
        font-weight: bold;
    }
    table th, table td
    {
        padding: 5px;
        border-color: #ccc;
    }
    .Pager span
    {
        color: #333;
        background-color: #F7F7F7;
        font-weight: bold;
        text-align: center;
        display: inline-block;
        width: 20px;
        margin-right: 3px;
        line-height: 150%;
        border: 1px solid #ccc;
    }
    .Pager a
    {
        text-align: center;
        display: inline-block;
        width: 20px;
        border: 1px solid #ccc;
        color: #fff;
        color: #333;
        margin-right: 3px;
        line-height: 150%;
        text-decoration: none;
    }
    .highlight
    {
        background-color: #FFFFAF;
    }
</style>
 
 
Screenshots
GridView displaying matched records.
Display Large Amount of Data in GridView with Search functionality in ASP.Net using jQuery
GridView when no records are found.
Display Large Amount of Data in GridView with Search functionality in ASP.Net using jQuery
Demo
 
Downloads