Search ASP.Net GridView with paging using jQuery DataTable TextBox in C# and VB.Net

Hi lingers,

The code will not work for searching all pages record as uses paging, all the rows are not rendered. Only the requested page is rendered. So it will only search the record for the current page.

To resolve the issue you need to rebind the GridView using the data from database based on the search term.

For this you need to modify your procedure to pass the search term as parameter and apply where condition to the columns you want to search.

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Stored Procedure

CREATE PROCEDURE [dbo].[Employees_GetEmployeesPageWise]
	@SearchTerm VARCHAR(100) = '',
	@PageIndex INT = 1,
	@PageSize INT = 20,
	@RecordCount INT OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT  (ROW_NUMBER() OVER(Order By EmployeeID)) AS RowNumber,
			*
	INTO #Results
	FROM [Employees]
	WHERE (FirstName LIKE @SearchTerm + '%' OR @SearchTerm = '')	
	OR (LastName 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 OR @PageIndex = -1	

	DROP TABLE #Results
END

HTML

<script type="text/javascript" src=" https://code.jquery.com/jquery-3.5.1.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetEmployees(parseInt(1));
        var checked = [];
        $('[id*=chkSelect]').on('click', function () {
            if ($(this).is(":checked")) {
                checked.push($(this).closest('tr').find('td').eq(1).html());
            } else {
                checked.pop($(this).closest('tr').find('td').eq(1).html());
            }

            $('[id*=hfCheckedIds]').val(checked.join(','));
        });
    });

    $(document).on("click", '.Pager .page', function () {
        GetEmployees(parseInt($(this).attr('page')));
    });
    var term = '';
    $(document).on("keyup", 'input[type=search]', function () {
        term = $(this).val();
        GetEmployees(parseInt(1));
    });

    $(document).on('click', '.view', function () {
        $('[id*=hfId]').val($(this).closest('tr').find('td').eq(1).html());
    });

    function SearchTerm() {
        return jQuery.trim($("input[type=search]").val());
    };

    var i = 0;
    function GetEmployees(pageIndex) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetEmployees",
            data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                $('[id$=gvEmployees]').prepend($("<thead></thead>").append($('[id$=gvEmployees]').find("tr:first"))).DataTable().destroy();
                var xmlDoc = $.parseXML(response.d);
                var xml = $(xmlDoc);
                var Employees = xml.find("Employees");
                var row = $("[id$=gvEmployees] tbody tr:last-child").eq(0).clone(true);
                $("[id$=gvEmployees] tbody tr").not($("[id$=gvEmployees] tbody tr:first-child")).remove();
                $.each(Employees, function () {
                    $("td", row).eq(1).html($(this).find("EmployeeID").text());
                    $("td", row).eq(2).html($(this).find("FirstName").text());
                    $("td", row).eq(3).html($(this).find("LastName").text());
                    $("td", row).eq(4).html($(this).find("Address").text());
                    $("td", row).eq(5).html($(this).find("City").text());
                    $("td", row).eq(6).html($(this).find("Country").text());
                    $("[id$=gvEmployees]").append(row);
                    row = $("[id$=gvEmployees] tbody tr:last-child").eq(0).clone(true);
                });
                $("[id$=gvEmployees] tbody tr:first-child").remove();
                if (i != 0) {
                    $('[id$=gvEmployees]').DataTable({
                        "paging": false,
                        "info": false
                    });
                } else {
                    $('[id$=gvEmployees]')
                        .prepend($("<thead></thead>").append($('[id$=gvEmployees]').find("tr:first")))
                        .DataTable({
                            "paging": false,
                            "info": false
                        });
                }
                if (term != '') {
                    $('input[type=search]').val(term);
                }
                $('input[type=search]').focus();
                i++;

                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())
                });
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>
<asp:HiddenField ID="hfCheckedIds" runat="server" />
<asp:HiddenField ID="hfId" runat="server" />
<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" Width="100%">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="EmployeeID" HeaderText="Id" />
        <asp:BoundField DataField="FirstName" HeaderText="First Name" />
        <asp:BoundField DataField="LastName" HeaderText="Last Name" />
        <asp:BoundField DataField="Address" HeaderText="Address" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button CssClass="view" Text="Unuseable" ID="Inkview" runat="server"
                    ForeColor="white" BackColor="#FF6600" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<div class="Pager" style="width: 100%"></div>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

private static int PageSize = 1;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDummyRow();
    }
}

private void BindDummyRow()
{
    DataTable dummy = new DataTable();
    dummy.Columns.Add("EmployeeID");
    dummy.Columns.Add("FirstName");
    dummy.Columns.Add("LastName");
    dummy.Columns.Add("Address");
    dummy.Columns.Add("City");
    dummy.Columns.Add("Country");
    dummy.Rows.Add();
    gvEmployees.DataSource = dummy;
    gvEmployees.DataBind();
}

[WebMethod]
public static string GetEmployees(int pageIndex, string searchTerm)
{
    string query = "[Employees_GetEmployeesPageWise]";
    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, "Employees");
                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

Private Shared PageSize As Integer = 1
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindDummyRow()
    End If
End Sub

Private Sub BindDummyRow()
    Dim dummy As DataTable = New DataTable()
    dummy.Columns.Add("EmployeeID")
    dummy.Columns.Add("FirstName")
    dummy.Columns.Add("LastName")
    dummy.Columns.Add("Address")
    dummy.Columns.Add("City")
    dummy.Columns.Add("Country")
    dummy.Rows.Add()
    gvEmployees.DataSource = dummy
    gvEmployees.DataBind()
End Sub

<WebMethod>
Public Shared Function GetEmployees(ByVal pageIndex As Integer, ByVal searchTerm As String) As String
    Dim query As String = "[Employees_GetEmployeesPageWise]"
    Dim cmd As SqlCommand = 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(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Using con As SqlConnection = New SqlConnection(strConnString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using ds As DataSet = New DataSet()
                sda.Fill(ds, "Employees")
                Dim dt As DataTable = 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

Screenshot