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

Last Reply 11 months ago By dharmendr

Posted 11 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 11 months ago Modified on 11 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