Custom HTML Table Sorting with Sort Image using jQuery AJAX and JSON in C# and VB.Net

Last Reply 2 months ago By dharmendr

Posted 2 months ago

how can add triangle symbol for sorting in html table in this links 

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

Posted 2 months ago Modified on 2 months ago

Hi SajidHussa,

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

SQL

CREATE PROCEDURE [dbo].[GetCustomersPageWise_Sort]
    @SearchTerm VARCHAR(100) = ''
    ,@PageIndex INT = 1
    ,@PageSize INT = 10
    ,@SortDirection VARCHAR(4) = 'ASC'
    ,@SortColumn VARCHAR(40) = ''
    ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ROW_NUMBER() OVER ( ORDER BY
                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
END

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<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;
        }
        /*Displays UP arrow*/
        .arrow-up
        {
            width: 0;
            height: 0;
            border-left: 5px solid transparent;
            border-right: 5px solid transparent;
            border-bottom: 10px solid black;
            display: inline-block;
        }
        
        /*Displays DOWN arrow*/
        .arrow-down
        {
            width: 0;
            height: 0;
            border-left: 5px solid transparent;
            border-right: 5px solid transparent;
            border-top: 10px solid black;
            display: inline-block;
        }
    </style>
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.1.1.min.js"></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").on('click', function () {
                $.each($("#tblCustomers th"), function () {
                    $(this).find('.arrow').hide();
                });
                direction = direction == 'ASC' ? 'DESC' : 'ASC';
                $(this).find('.arrow').show();
                if (direction == 'ASC') {
                    $(this).find('.arrow').removeClass('arrow-down');
                    $(this).find('.arrow').addClass('arrow-up');
                } else if (direction == 'DESC') {
                    $(this).find('.arrow').removeClass('arrow-up');
                    $(this).find('.arrow').addClass('arrow-down');
                }
                column = $(this).find('a').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);
            $.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*=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());
                    $("[id*=tblCustomers]").append(row);
                    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();
                $("[id*=tblCustomers]").append(empty_row);
            }
            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())
            });
        };
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <center>
        Name : <input type="text" name="name" id="txtSearch" /><br /><br />
        <table id="tblCustomers" width="25%">
            <tr>
                <th><a href="#" data-sort="CustomerID">CustomerID</a>&nbsp;<span class="arrow arrow-up"></span></th>
                <th><a href="#" data-sort="ContactName">Contact Name</a>&nbsp;<span class="arrow"></span></th>
                <th><a href="#" data-sort="City">City</a>&nbsp;<span class="arrow"></span></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 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.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 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.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