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.Server Side HTML Table Paging and Sorting with Stored Procedure using AngularJS in ASP.Net<p>Hi&nbsp;rani,</p> <p>Check this example. Now please take its reference and correct your code.</p> <p><strong><span style="text-decoration: underline;">Database</span></strong></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 runat=&#34;server&#34;&gt; &lt;title&gt;Searching Sorting and Paging in AngularJS&lt;/title&gt; &lt;style type=&#34;text/css&#34;&gt; /*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;link rel=&#34;stylesheet&#34; type=&#34;text/css&#34; href=&#34;https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css&#34; /&gt; &lt;script type=&#34;text/javascript&#34; src=&#34;https://ajax.googleapis.com/ajax/libs/angularjs/1.4.3/angular.min.js&#34;&gt;&lt;/script&gt; &lt;script type=&#34;text/javascript&#34; src=&#34;https://cdn.jsdelivr.net/npm/angular-utils-pagination@0.11.1/dirPagination.js&#34;&gt;&lt;/script&gt; &lt;script type=&#34;text/javascript&#34;&gt; var app = angular.module(&#39;MyApp&#39;, [&#39;angularUtils.directives.dirPagination&#39;]); app.controller(&#39;MyController&#39;, function ($scope, $http) { $scope.Customers = []; $scope.PageIndex = 1; $scope.RecordCount = 0; $scope.PageSize = 10; $scope.SearchTerm = &#34;&#34;; $scope.SortColumn = &#34;CustomerID&#34;; $scope.ReverseSort = false; $scope.SortDirection = &#34;ASC&#34;; $scope.GetCustomers = function (index) { $scope.PageIndex = index; $scope.Customers = []; var data = { searchTerm: $scope.SearchTerm, PageNo: $scope.PageIndex, PageSize: $scope.PageSize, sortDirection: $scope.SortDirection, sortColumn: $scope.SortColumn }; $http.post(&#34;Default.aspx/GetCustomers&#34;, data, { headers: { &#39;Content-Type&#39;: &#39;application/json&#39;} }) .success(function (response) { $scope.Customers = response.d.Customers; $scope.RecordCount = response.d.TotalRecords; }); } $scope.GetCustomers($scope.PageIndex); $scope.SortData = function (column) { $scope.ReverseSort = ($scope.SortColumn == column) ? !$scope.ReverseSort : false; $scope.SortDirection = $scope.ReverseSort ? &#34;DESC&#34; : &#34;ASC&#34;; $scope.SortColumn = column; $scope.GetCustomers($scope.PageIndex); } $scope.GetSortClass = function (column) { if ($scope.SortColumn == column) { return $scope.ReverseSort ? &#39;arrow-down&#39; : &#39;arrow-up&#39;; } return &#39;&#39;; } }); &lt;/script&gt; &lt;/head&gt; &lt;body ng-app=&#34;MyApp&#34; ng-controller=&#34;MyController&#34;&gt; &lt;div class=&#34;panel panel-primary&#34;&gt; &lt;div class=&#34;panel-body&#34;&gt; &lt;input type=&#34;text&#34; ng-model=&#34;SearchTerm&#34; ng-keyup=&#34;GetCustomers(1)&#34; class=&#34;form-control&#34; placeholder=&#34;Enter Name&#34; /&gt;&lt;br /&gt; &lt;table class=&#34;table table-bordered table-hover table-striped&#34;&gt; &lt;thead&gt; &lt;tr class=&#34;success&#34;&gt; &lt;th ng-click=&#34;SortData(&#39;CustomerID&#39;)&#34;&gt; Id&amp;nbsp;&lt;div ng-class=&#34;GetSortClass(&#39;CustomerID&#39;)&#34;&gt; &lt;/th&gt; &lt;th ng-click=&#34;SortData(&#39;ContactName&#39;)&#34;&gt; Name&amp;nbsp;&lt;div ng-class=&#34;GetSortClass(&#39;ContactName&#39;)&#34;&gt; &lt;/th&gt; &lt;th ng-click=&#34;SortData(&#39;City&#39;)&#34;&gt; City&amp;nbsp;&lt;div ng-class=&#34;GetSortClass(&#39;City&#39;)&#34;&gt; &lt;/th&gt; &lt;th ng-click=&#34;SortData(&#39;Country&#39;)&#34;&gt; Country&amp;nbsp;&lt;div ng-class=&#34;GetSortClass(&#39;Country&#39;)&#34;&gt; &lt;/th&gt; &lt;/tr&gt; &lt;/thead&gt; &lt;tbody&gt; &lt;tr dir-paginate=&#34;customer in Customers|itemsPerPage:PageSize&#34; total-items=&#34;RecordCount&#34;&gt; &lt;td&gt;{{customer.Id}}&lt;/td&gt; &lt;td&gt;{{customer.Name}}&lt;/td&gt; &lt;td&gt;{{customer.City}}&lt;/td&gt; &lt;td&gt;{{customer.Country}}&lt;/td&gt; &lt;/tr&gt; &lt;/tbody&gt; &lt;/table&gt; &lt;dir-pagination-controls max-size=&#34;PageSize&#34; direction-links=&#34;true&#34; boundary-links=&#34;true&#34; on-page-change=&#34;GetCustomers(newPageNumber)&#34;&gt; &lt;/dir-pagination-controls&gt; &lt;/div&gt; &lt;/div&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.Collections.Generic; 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.Collections.Generic Imports System.Configuration Imports System.Data Imports System.Data.SqlClient 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">[WebMethod] public static CustomerData GetCustomers(string searchTerm, int pageNo, int pageSize, string sortDirection, string sortColumn) { string strConnString = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; List&lt;object&gt; customers = new List&lt;object&gt;(); SqlConnection con = new SqlConnection(strConnString); SqlCommand cmd = new SqlCommand(&#34;GetCustomersPageWise_Sort&#34;, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(&#34;@SearchTerm&#34;, searchTerm); cmd.Parameters.AddWithValue(&#34;@PageIndex&#34;, pageNo); 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; con.Open(); SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { customers.Add(new { Id = sdr[&#34;CustomerID&#34;], Name = sdr[&#34;ContactName&#34;], City = sdr[&#34;City&#34;], Country = sdr[&#34;Country&#34;] }); } con.Close(); CustomerData tableData = new CustomerData(); tableData.Customers = customers; tableData.TotalRecords = Convert.ToInt32(cmd.Parameters[&#34;@RecordCount&#34;].Value); return tableData; } public class CustomerData { public List&lt;object&gt; Customers { get; set; } public int TotalRecords { get; set; } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">&lt;WebMethod()&gt; Public Shared Function GetCustomers(ByVal searchTerm As String, ByVal pageNo As Integer, ByVal pageSize As Integer, ByVal sortDirection As String, ByVal sortColumn As String) As CustomerData Dim strConnString As String = ConfigurationManager.ConnectionStrings(&#34;constr&#34;).ConnectionString Dim customers As List(Of Object) = New List(Of Object)() Dim con As SqlConnection = New SqlConnection(strConnString) Dim cmd As SqlCommand = New SqlCommand(&#34;GetCustomersPageWise_Sort&#34;, con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue(&#34;@SearchTerm&#34;, searchTerm) cmd.Parameters.AddWithValue(&#34;@PageIndex&#34;, pageNo) 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 con.Open() Dim sdr As SqlDataReader = cmd.ExecuteReader() While sdr.Read() customers.Add(New With { .Id = sdr(&#34;CustomerID&#34;), .Name = sdr(&#34;ContactName&#34;), .City = sdr(&#34;City&#34;), .Country = sdr(&#34;Country&#34;) }) End While con.Close() Dim tableData As CustomerData = New CustomerData() tableData.Customers = customers tableData.TotalRecords = Convert.ToInt32(cmd.Parameters(&#34;@RecordCount&#34;).Value) Return tableData End Function Public Class CustomerData Public Property Customers As List(Of Object) Public Property TotalRecords As Integer End Class</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/8FRRqpO.gif" alt="" width="395" height="471" /></span></strong></p>https://www.aspforums.net:443/Threads/133115/Server-Side-HTML-Table-Paging-and-Sorting-with-Stored-Procedure-using-AngularJS-in-ASPNet/https://www.aspforums.net:443/Threads/133115/Server-Side-HTML-Table-Paging-and-Sorting-with-Stored-Procedure-using-AngularJS-in-ASPNet/Mon, 29 Jul 2019 04:32:58 GMT