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

Last Reply 16 days ago By dharmendr

Posted 16 days 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 16 days ago

I will get back to you soon.


Posted 16 days ago Modified on 16 days 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


Posted 16 days ago Modified on 16 days ago

it work but if i click th then it not work mens user clcik out side the anchor tag then it not work 

i wan to it cover all th and anchor tag inside th section 

and it is possible to remove anchor tag anf it work on th section 

when i click th then sort