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.Implement HTML Table Sorting with Stored Procedure 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=&#34;http://www.w3.org/1999/xhtml&#34;&gt; &lt;head id=&#34;Head1&#34; runat=&#34;server&#34;&gt; &lt;title&gt;Html Table Paging Sorting&lt;/title&gt; &lt;style type=&#34;text/css&#34;&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; } &lt;/style&gt; &lt;script type=&#34;text/javascript&#34; src=&#34;https://code.jquery.com/jquery-3.1.1.min.js&#34;&gt;&lt;/script&gt; &lt;script src=&#34;ASPSnippets_Pager.min.js&#34; type=&#34;text/javascript&#34;&gt;&lt;/script&gt; &lt;script type=&#34;text/javascript&#34;&gt; var pagerIndex = 1; var direction = &#39;ASC&#39;; var column = &#39;CustomerID&#39;; $(function () { GetCustomers(1, &#39;&#39;, &#39;ASC&#39;, column); $(&#34;#txtSearch&#34;).on(&#39;keyup&#39;, function () { GetCustomers(1, $(this).val(), direction, column); }); $(&#34;#tblCustomers th a&#34;).on(&#39;click&#39;, function () { direction = direction == &#39;ASC&#39; ? &#39;DESC&#39; : &#39;ASC&#39;; column = $(this).data(&#39;sort&#39;); GetCustomers(pagerIndex, $(&#34;#txtSearch&#34;).val(), direction, column); }); $(&#39;.Pager&#39;).on(&#34;click&#34;, &#34;.page&#34;, function () { pagerIndex = parseInt($(this).attr(&#39;page&#39;)); GetCustomers(pagerIndex, $(&#34;#txtSearch&#34;).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: &#34;POST&#34;, url: &#34;Default.aspx/GetCustomers&#34;, data: JSON.stringify(obj), contentType: &#34;application/json; charset=utf-8&#34;, dataType: &#34;json&#34;, 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(&#34;Customers&#34;); if (row == null) { row = $(&#34;[id*=tblCustomers] tr:last-child&#34;).clone(true); } $(&#34;[id*=tblCustomers] tr&#34;).not($(&#34;[id*=tblCustomers] tr:first-child&#34;)).remove(); if (customers.length &gt; 0) { $.each(customers, function () { var customer = $(this); $(&#34;td&#34;, row).eq(0).html($(this).find(&#34;CustomerID&#34;).text()); $(&#34;td&#34;, row).eq(1).html($(this).find(&#34;ContactName&#34;).text()); $(&#34;td&#34;, row).eq(2).html($(this).find(&#34;City&#34;).text()); $(&#34;[id*=tblCustomers]&#34;).append(row); row = $(&#34;[id*=tblCustomers] tr:last-child&#34;).clone(true); }); } else { var empty_row = row.clone(true); $(&#34;td:first-child&#34;, empty_row).attr(&#34;colspan&#34;, $(&#34;td&#34;, row).length); $(&#34;td:first-child&#34;, empty_row).attr(&#34;align&#34;, &#34;center&#34;); $(&#34;td:first-child&#34;, empty_row).html(&#34;No records found for the search criteria.&#34;); $(&#34;td&#34;, empty_row).not($(&#34;td:first-child&#34;, empty_row)).remove(); $(&#34;[id*=tblCustomers]&#34;).append(empty_row); } var pager = xml.find(&#34;Pager&#34;); $(&#34;.Pager&#34;).ASPSnippets_Pager({ ActiveCssClass: &#34;current&#34;, PagerCssClass: &#34;pager&#34;, PageIndex: parseInt(pager.find(&#34;PageIndex&#34;).text()), PageSize: parseInt(pager.find(&#34;PageSize&#34;).text()), RecordCount: parseInt(pager.find(&#34;RecordCount&#34;).text()) }); }; &lt;/script&gt; &lt;/head&gt; &lt;body&gt; &lt;form id=&#34;form1&#34; runat=&#34;server&#34;&gt; &lt;center&gt; Name : &lt;input type=&#34;text&#34; name=&#34;name&#34; id=&#34;txtSearch&#34; /&gt;&lt;br /&gt;&lt;br /&gt; &lt;table id=&#34;tblCustomers&#34; width=&#34;25%&#34;&gt; &lt;tr&gt; &lt;th&gt;&lt;a href=&#34;#&#34; data-sort=&#34;CustomerID&#34;&gt;CustomerID&lt;/a&gt;&lt;/th&gt; &lt;th&gt;&lt;a href=&#34;#&#34; data-sort=&#34;ContactName&#34;&gt;Contact Name&lt;/a&gt;&lt;/th&gt; &lt;th&gt;&lt;a href=&#34;#&#34; data-sort=&#34;City&#34;&gt;City&lt;/a&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=&#34;Pager&#34;&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><strong><span style="text-decoration: underline;"><img src="https://i.imgur.com/79gyDZl.gif" alt="" width="373" height="389" /></span></strong></p>https://www.aspforums.net:443/Threads/208205/Implement-HTML-Table-Sorting-with-Stored-Procedure-using-jQuery-AJAX-and-JSON-in-C-and-VBNet/https://www.aspforums.net:443/Threads/208205/Implement-HTML-Table-Sorting-with-Stored-Procedure-using-jQuery-AJAX-and-JSON-in-C-and-VBNet/Wed, 31 Jul 2019 07:51:55 GMT