Implement HTML Table Sorting with Stored Procedure using jQuery AJAX and JSON in C# and VB.Net

Last Reply 3 months ago By dharmendr

Posted 3 months ago

How to implementing sorting on thead th in html table in this example 

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

Posted 3 months ago Modified on 3 months ago

Hi SajidHussa,

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


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

Download Northwind Database


CREATE PROCEDURE [dbo].[GetCustomersPageWise_Sort]
    @SearchTerm VARCHAR(100) = ''
    ,@PageIndex INT = 1
    ,@PageSize INT = 10
    ,@SortDirection VARCHAR(4) = 'ASC'
    ,@SortColumn VARCHAR(40) = ''
    ,@RecordCount INT OUTPUT
                CASE WHEN @SortColumn = 'CustomerID' AND @SortDirection = 'ASC' THEN CustomerID END ASC,
                CASE WHEN @SortColumn = 'CustomerID' AND @SortDirection = 'DESC' THEN CustomerID END DESC,
                CASE WHEN @SortColumn = 'CompanyName' AND @SortDirection = 'ASC' THEN CompanyName END ASC,
                CASE WHEN @SortColumn = 'CompanyName' AND @SortDirection = 'DESC' THEN CompanyName END DESC,
                CASE WHEN @SortColumn = 'ContactName' AND @SortDirection = 'ASC' THEN ContactName END ASC,
                CASE WHEN @SortColumn = 'ContactName' AND @SortDirection = 'DESC' THEN ContactName END DESC,
                CASE WHEN @SortColumn = 'ContactTitle' AND @SortDirection = 'ASC' THEN ContactTitle END ASC,
                CASE WHEN @SortColumn = 'ContactTitle' AND @SortDirection = 'DESC' THEN ContactTitle END DESC,
                CASE WHEN @SortColumn = 'Address' AND @SortDirection = 'ASC' THEN Address END ASC,
                CASE WHEN @SortColumn = 'Address' AND @SortDirection = 'DESC' THEN Address END DESC,
                CASE WHEN @SortColumn = 'City' AND @SortDirection = 'ASC' THEN City END ASC,
                CASE WHEN @SortColumn = 'City' AND @SortDirection = 'DESC' THEN City END DESC,
                CASE WHEN @SortColumn = 'Region' AND @SortDirection = 'ASC' THEN Region END ASC,
                CASE WHEN @SortColumn = 'Region' AND @SortDirection = 'DESC' THEN Region END DESC,
                CASE WHEN @SortColumn = 'PostalCode' AND @SortDirection = 'ASC' THEN PostalCode END ASC,
                CASE WHEN @SortColumn = 'PostalCode' AND @SortDirection = 'DESC' THEN PostalCode END DESC,
                CASE WHEN @SortColumn = 'Country' AND @SortDirection = 'ASC' THEN Country END ASC,
                CASE WHEN @SortColumn = 'Country' AND @SortDirection = 'DESC' THEN Country END DESC,
                CASE WHEN @SortColumn = 'Phone' AND @SortDirection = 'ASC' THEN Phone END ASC,
                CASE WHEN @SortColumn = 'Phone' AND @SortDirection = 'DESC' THEN Phone END DESC,
                CASE WHEN @SortColumn = 'Fax' AND @SortDirection = 'ASC' THEN Fax END ASC,
                CASE WHEN @SortColumn = 'Fax' AND @SortDirection = 'DESC' THEN Fax END DESC
            ) AS RowNumber,*
    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


<html xmlns="">
<head id="Head1" runat="server">
    <title>Html Table Paging Sorting</title>
    <style type="text/css">
        .Pager span
            text-align: center;
            color: #999;
            display: inline-block;
            width: 20px;
            background-color: #A1DCF2;
            margin-right: 3px;
            line-height: 150%;
            border: 1px solid #3AC0F2;
        .Pager a
            text-align: center;
            display: inline-block;
            width: 20px;
            background-color: #3AC0F2;
            color: #fff;
            border: 1px solid #3AC0F2;
            margin-right: 3px;
            line-height: 150%;
            text-decoration: none;
    <script type="text/javascript" src=""></script>
    <script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        var pagerIndex = 1;
        var direction = 'ASC';
        var column = 'CustomerID';
        $(function () {
            GetCustomers(1, '', 'ASC', column);
            $("#txtSearch").on('keyup', function () {
                GetCustomers(1, $(this).val(), direction, column);
            $("#tblCustomers th a").on('click', function () {
                direction = direction == 'ASC' ? 'DESC' : 'ASC';
                column = $(this).data('sort');
                GetCustomers(pagerIndex, $("#txtSearch").val(), direction, column);
            $('.Pager').on("click", ".page", function () {
                pagerIndex = parseInt($(this).attr('page'));
                GetCustomers(pagerIndex, $("#txtSearch").val());
        function GetCustomers(pageIndex, searchTerm, sortDirection, sortColumn) {
            var obj = {};
            obj.pageIndex = $.trim(pageIndex);
            obj.searchTerm = $.trim(searchTerm);
            obj.sortDirection = $.trim(sortDirection);
            obj.sortColumn = $.trim(sortColumn);
                type: "POST",
                url: "Default.aspx/GetCustomers",
                data: JSON.stringify(obj),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess,
                failure: function (response) {
                error: function (response) {
        var row;
        function OnSuccess(response) {
            var xmlDoc = $.parseXML(response.d);
            var xml = $(xmlDoc);
            var customers = xml.find("Customers");
            if (row == null) {
                row = $("[id*=tblCustomers] tr:last-child").clone(true);
            $("[id*=tblCustomers] tr").not($("[id*=tblCustomers] 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());
                    row = $("[id*=tblCustomers] tr:last-child").clone(true);
            } 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();
            var pager = xml.find("Pager");
                ActiveCssClass: "current",
                PagerCssClass: "pager",
                PageIndex: parseInt(pager.find("PageIndex").text()),
                PageSize: parseInt(pager.find("PageSize").text()),
                RecordCount: parseInt(pager.find("RecordCount").text())
    <form id="form1" runat="server">
        Name : <input type="text" name="name" id="txtSearch" /><br /><br />
        <table id="tblCustomers" width="25%">
                <th><a href="#" data-sort="CustomerID">CustomerID</a></th>
                <th><a href="#" data-sort="ContactName">Contact Name</a></th>
                <th><a href="#" data-sort="City">City</a></th>
        <br /><div class="Pager"></div>



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


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



private static int PageSize = 10;
public static string GetCustomers(int pageIndex, string searchTerm, string sortDirection, string sortColumn)
    string query = "[GetCustomersPageWise_Sort]";
    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.AddWithValue("@SortDirection", sortDirection);
    cmd.Parameters.AddWithValue("@SortColumn", sortColumn);
    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.Rows[0]["PageIndex"] = pageIndex;
                dt.Rows[0]["PageSize"] = PageSize;
                dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                return ds;


Private Shared PageSize As Integer = 10
Public Shared Function GetCustomers(ByVal pageIndex As Integer, ByVal searchTerm As String, ByVal sortDirection As String, ByVal sortColumn As String) As String
    Dim query As String = "[GetCustomersPageWise_Sort]"
    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.AddWithValue("@SortDirection", sortDirection)
    cmd.Parameters.AddWithValue("@SortColumn", sortColumn)
    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.Rows(0)("PageIndex") = pageIndex
                dt.Rows(0)("PageSize") = PageSize
                dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
                Return ds
            End Using
        End Using
    End Using
End Function