ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Custom HTML Table Sorting with Sort Image using jQuery AJAX and JSON in C# and VB.Net<p>Hi SajidHussa,</p> <p>Check this example. Now please take its reference and correct your code.</p> <p><span style="text-decoration: underline;"><strong>Database</strong></span></p> <p>For this example I have used of Northwind database that you can download using the link given below.</p> <p><a href="https://www.aspsnippets.com/Articles/Install-Microsoft-Northwind-and-Pubs-Sample-databases-in-SQL-Server-Management-Studio.aspx" target="_blank">Download Northwind Database</a></p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">CREATE PROCEDURE [dbo].[GetCustomersPageWise_Sort] @SearchTerm VARCHAR(100) = &#39;&#39; ,@PageIndex INT = 1 ,@PageSize INT = 10 ,@SortDirection VARCHAR(4) = &#39;ASC&#39; ,@SortColumn VARCHAR(40) = &#39;&#39; ,@RecordCount INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT ROW_NUMBER() OVER ( ORDER BY CASE WHEN @SortColumn = &#39;CustomerID&#39; AND @SortDirection = &#39;ASC&#39; THEN CustomerID END ASC, CASE WHEN @SortColumn = &#39;CustomerID&#39; AND @SortDirection = &#39;DESC&#39; THEN CustomerID END DESC, CASE WHEN @SortColumn = &#39;CompanyName&#39; AND @SortDirection = &#39;ASC&#39; THEN CompanyName END ASC, CASE WHEN @SortColumn = &#39;CompanyName&#39; AND @SortDirection = &#39;DESC&#39; THEN CompanyName END DESC, CASE WHEN @SortColumn = &#39;ContactName&#39; AND @SortDirection = &#39;ASC&#39; THEN ContactName END ASC, CASE WHEN @SortColumn = &#39;ContactName&#39; AND @SortDirection = &#39;DESC&#39; THEN ContactName END DESC, CASE WHEN @SortColumn = &#39;ContactTitle&#39; AND @SortDirection = &#39;ASC&#39; THEN ContactTitle END ASC, CASE WHEN @SortColumn = &#39;ContactTitle&#39; AND @SortDirection = &#39;DESC&#39; THEN ContactTitle END DESC, CASE WHEN @SortColumn = &#39;Address&#39; AND @SortDirection = &#39;ASC&#39; THEN Address END ASC, CASE WHEN @SortColumn = &#39;Address&#39; AND @SortDirection = &#39;DESC&#39; THEN Address END DESC, CASE WHEN @SortColumn = &#39;City&#39; AND @SortDirection = &#39;ASC&#39; THEN City END ASC, CASE WHEN @SortColumn = &#39;City&#39; AND @SortDirection = &#39;DESC&#39; THEN City END DESC, CASE WHEN @SortColumn = &#39;Region&#39; AND @SortDirection = &#39;ASC&#39; THEN Region END ASC, CASE WHEN @SortColumn = &#39;Region&#39; AND @SortDirection = &#39;DESC&#39; THEN Region END DESC, CASE WHEN @SortColumn = &#39;PostalCode&#39; AND @SortDirection = &#39;ASC&#39; THEN PostalCode END ASC, CASE WHEN @SortColumn = &#39;PostalCode&#39; AND @SortDirection = &#39;DESC&#39; THEN PostalCode END DESC, CASE WHEN @SortColumn = &#39;Country&#39; AND @SortDirection = &#39;ASC&#39; THEN Country END ASC, CASE WHEN @SortColumn = &#39;Country&#39; AND @SortDirection = &#39;DESC&#39; THEN Country END DESC, CASE WHEN @SortColumn = &#39;Phone&#39; AND @SortDirection = &#39;ASC&#39; THEN Phone END ASC, CASE WHEN @SortColumn = &#39;Phone&#39; AND @SortDirection = &#39;DESC&#39; THEN Phone END DESC, CASE WHEN @SortColumn = &#39;Fax&#39; AND @SortDirection = &#39;ASC&#39; THEN Fax END ASC, CASE WHEN @SortColumn = &#39;Fax&#39; AND @SortDirection = &#39;DESC&#39; THEN Fax END DESC ) AS RowNumber,* INTO #Results FROM [Customers] WHERE [ContactName] LIKE @SearchTerm + &#39;%&#39; OR @SearchTerm = &#39;&#39; 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</pre> <p><strong><span style="text-decoration: underline;">HTML</span></strong></p> <pre class="brush: html">&lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head id="Head1" runat="server"&gt; &lt;title&gt;Html Table Paging Sorting&lt;/title&gt; &lt;style type="text/css"&gt; .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; } &lt;/style&gt; &lt;script type="text/javascript" src="https://code.jquery.com/jquery-3.1.1.min.js"&gt;&lt;/script&gt; &lt;script src="ASPSnippets_Pager.min.js" type="text/javascript"&gt;&lt;/script&gt; &lt;script type="text/javascript"&gt; 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 &gt; 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()) }); }; &lt;/script&gt; &lt;/head&gt; &lt;body&gt; &lt;form id="form1" runat="server"&gt; &lt;center&gt; Name : &lt;input type="text" name="name" id="txtSearch" /&gt;&lt;br /&gt;&lt;br /&gt; &lt;table id="tblCustomers" width="25%"&gt; &lt;tr&gt; &lt;th&gt;&lt;a href="#" data-sort="CustomerID"&gt;CustomerID&lt;/a&gt;&amp;nbsp;&lt;span class="arrow arrow-up"&gt;&lt;/span&gt;&lt;/th&gt; &lt;th&gt;&lt;a href="#" data-sort="ContactName"&gt;Contact Name&lt;/a&gt;&amp;nbsp;&lt;span class="arrow"&gt;&lt;/span&gt;&lt;/th&gt; &lt;th&gt;&lt;a href="#" data-sort="City"&gt;City&lt;/a&gt;&amp;nbsp;&lt;span class="arrow"&gt;&lt;/span&gt;&lt;/th&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;/td&gt; &lt;td&gt;&lt;/td&gt; &lt;td&gt;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt;&lt;br /&gt; &lt;div class="Pager"&gt;&lt;/div&gt; &lt;/center&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt;</pre> <p><strong><span style="text-decoration: underline;">Namespaces</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Web.Services;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Data.SqlClient Imports System.Data Imports System.Web.Services</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">private static int PageSize = 10; [WebMethod] public static string GetCustomers(int pageIndex, string searchTerm, string sortDirection, string sortColumn) { string query = &#34;[GetCustomersPageWise_Sort]&#34;; SqlCommand cmd = new SqlCommand(query); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(&#34;@SearchTerm&#34;, searchTerm); cmd.Parameters.AddWithValue(&#34;@PageIndex&#34;, pageIndex); cmd.Parameters.AddWithValue(&#34;@PageSize&#34;, PageSize); cmd.Parameters.AddWithValue(&#34;@SortDirection&#34;, sortDirection); cmd.Parameters.AddWithValue(&#34;@SortColumn&#34;, sortColumn); cmd.Parameters.Add(&#34;@RecordCount&#34;, SqlDbType.Int, 4).Direction = ParameterDirection.Output; return GetData(cmd, pageIndex).GetXml(); } private static DataSet GetData(SqlCommand cmd, int pageIndex) { string strConnString = ConfigurationManager.ConnectionStrings[&#34;conString&#34;].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, &#34;Customers&#34;); DataTable dt = new DataTable(&#34;Pager&#34;); dt.Columns.Add(&#34;PageIndex&#34;); dt.Columns.Add(&#34;PageSize&#34;); dt.Columns.Add(&#34;RecordCount&#34;); dt.Rows.Add(); dt.Rows[0][&#34;PageIndex&#34;] = pageIndex; dt.Rows[0][&#34;PageSize&#34;] = PageSize; dt.Rows[0][&#34;RecordCount&#34;] = cmd.Parameters[&#34;@RecordCount&#34;].Value; ds.Tables.Add(dt); return ds; } } } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Private Shared PageSize As Integer = 10 &lt;WebMethod()&gt; 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 = &#34;[GetCustomersPageWise_Sort]&#34; Dim cmd As SqlCommand = New SqlCommand(query) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue(&#34;@SearchTerm&#34;, searchTerm) cmd.Parameters.AddWithValue(&#34;@PageIndex&#34;, pageIndex) cmd.Parameters.AddWithValue(&#34;@PageSize&#34;, PageSize) cmd.Parameters.AddWithValue(&#34;@SortDirection&#34;, sortDirection) cmd.Parameters.AddWithValue(&#34;@SortColumn&#34;, sortColumn) cmd.Parameters.Add(&#34;@RecordCount&#34;, 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(&#34;conString&#34;).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, &#34;Customers&#34;) Dim dt As DataTable = New DataTable(&#34;Pager&#34;) dt.Columns.Add(&#34;PageIndex&#34;) dt.Columns.Add(&#34;PageSize&#34;) dt.Columns.Add(&#34;RecordCount&#34;) dt.Rows.Add() dt.Rows(0)(&#34;PageIndex&#34;) = pageIndex dt.Rows(0)(&#34;PageSize&#34;) = PageSize dt.Rows(0)(&#34;RecordCount&#34;) = cmd.Parameters(&#34;@RecordCount&#34;).Value ds.Tables.Add(dt) Return ds End Using End Using End Using End Function</pre> <p><strong><span style="text-decoration: underline;">Screenshot</span></strong></p> <p><img src="https://i.imgur.com/ViPo1gA.gif" alt="" width="371" height="386" /></p>https://www.aspforums.net:443/Threads/126695/Custom-HTML-Table-Sorting-with-Sort-Image-using-jQuery-AJAX-and-JSON-in-C-and-VBNet/https://www.aspforums.net:443/Threads/126695/Custom-HTML-Table-Sorting-with-Sort-Image-using-jQuery-AJAX-and-JSON-in-C-and-VBNet/Fri, 02 Aug 2019 02:15:12 GMT