HTML Table Paging and Searching using jQuery AJAX and JSON in C# and VB.Net

Last Reply one month ago By pandeyism

Posted one month ago

Dharmendr Sir 

you solved my query 

HTML Table Paging using jQuery AJAX and JSON in C# and VB.Net

but can you tell me if i want to implement searching 

how to do this 

but searching is sever side 

Posted one month ago Modified on one month ago

Hi SajidHussa,

Refer below sample.

Database

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

 Download Northwind Database

SQL

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

 HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Html Table Paging</title>
    <style type="text/css">
        .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;
        }
    </style>
    <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" src="http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js"></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, name) {
            var obj = {};
            obj.pageIndex = $.trim(pageIndex);
            obj.name = $.trim(SearchTerm());
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetCustomers",
                data: JSON.stringify(obj),
                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("CustomerID").text());
                    $("td", row).eq(1).html($(this).find("ContactName").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());
                });
            } 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>
</head>
<body>
    <form id="form1" runat="server">
    <center>
        Name :
        <input type="text" name="name" id="txtSearch" class="ContactName" />
        <table id="gvCustomers">
            <tr>
                <th>CustomerID</th>
                <th>Contact Name</th>
                <th>City</th>
            </tr>
            <tr>
                <td></td>
                <td></td>
                <td></td>
            </tr>
        </table>
        <br />
        <div class="Pager"></div>
    </center>
    </form>
</body>
</html>

Namespaces

C#

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

VB.Net

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

Code

C#

private static int PageSize = 10;
[WebMethod]
public static string GetCustomers(int pageIndex, string name)
{
    string query = "[GetCustomers_Pager]";
    SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@SearchTerm", name);
    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

Private Shared PageSize As Integer = 10
<WebMethod()>
Public Shared Function GetCustomers(ByVal pageIndex As Integer, ByVal name As String) As String
    Dim query As String = "[GetCustomers_Pager]"
    Dim cmd As SqlCommand = New SqlCommand(query)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@SearchTerm", name)
    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, "Customers")
                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