Server Side (Custom) Paging Searching and Sorting in HTML Table using AngularJS in ASP.Net

Last Reply 2 months ago By dharmendr

Posted 2 months ago

How to apply sorting with the code.

Server Side table Paging in AngularJS

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago

Hi rani,

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

Procedure

CREATE PROCEDURE [dbo].[GetCustomersPageWise]
      @SearchTerm VARCHAR(100) = ''
      ,@PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER (ORDER BY [CustomerID] ASC) AS RowNumber
             ,[CustomerID]
             ,[CompanyName]
             ,[ContactName]
             ,[ContactTitle]
             ,[Address]
             ,[City]
             ,[Region]
             ,[PostalCode]
             ,[Country]
             ,[Phone]
             ,[Fax]
      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>
    <title>Searching Sorting and Paging in AngularJS</title>
    <style type="text/css">       
        /*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>
    <link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"/>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.4.3/angular.min.js"></script>
    <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/angular-utils-pagination@0.11.1/dirPagination.js"></script>
    <script type="text/javascript">
        var app = angular.module('MyApp', ['angularUtils.directives.dirPagination']);
        app.controller('MyController', function ($scope, $http) {
            $scope.Customers = [];
            $scope.PageIndex = 1;
            $scope.RecordCount = 0;
            $scope.PageSize = 10;
            $scope.SearchTerm = "";
            $scope.GetCustomers = function (index) {
                $scope.Customers = [];
                $http.post("Default.aspx/GetCustomers",
                { PageNo: index, PageSize: $scope.PageSize, searchTerm: $scope.SearchTerm },
                { headers: { 'Content-Type': 'application/json'} })
                .success(function (response) {
                    $scope.Customers = response.d.Customers;
                    $scope.RecordCount = response.d.TotalRecords;
                });
            }
            $scope.GetCustomers($scope.PageIndex);
            $scope.SortColumn = "Id";
            $scope.ReverseSort = false;
            $scope.SortData = function (column) {
                $scope.ReverseSort = ($scope.SortColumn == column) ? !$scope.ReverseSort : false;
                $scope.SortColumn = column;
            }
            $scope.GetSortClass = function (column) {
                if ($scope.SortColumn == column) {
                    return $scope.ReverseSort ? 'arrow-down' : 'arrow-up';
                }
                return '';
            }
        });
    </script>
</head>
<body ng-app="MyApp" ng-controller="MyController">
    <div class="panel panel-primary">
        <div class="panel-body">
            <input type="text" ng-model="SearchTerm" ng-keyup="GetCustomers(1)" class="form-control" placeholder="Enter Name" />
            <br />
            <table class="table table-bordered table-hover table-striped">
                <thead>
                    <tr class="success">
                        <th ng-click="SortData('Id')">Id&nbsp;<div ng-class="GetSortClass('Id')"></th>
                        <th ng-click="SortData('Name')">Name&nbsp;<div ng-class="GetSortClass('Name')"></th>
                        <th ng-click="SortData('City')">City&nbsp;<div ng-class="GetSortClass('City')"></th>
                        <th ng-click="SortData('Country')">Country&nbsp;<div ng-class="GetSortClass('Country')"></th>
                    </tr>
                </thead>
                <tbody>
                    <tr dir-paginate="customer in Customers|orderBy:SortColumn:ReverseSort|itemsPerPage:PageSize" total-items="RecordCount">
                        <td>{{customer.Id}}</td>
                        <td>{{customer.Name}}</td>
                        <td>{{customer.City}}</td>
                        <td>{{customer.Country}}</td>
                    </tr>
                </tbody>
            </table>
            <dir-pagination-controls max-size="PageSize" direction-links="true" boundary-links="true"
                on-page-change="GetCustomers(newPageNumber)">
            </dir-pagination-controls>
        </div>
    </div>
</body>
</html>

Namespaces

C#

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

[WebMethod]
public static CustomerData GetCustomers(int PageNo, int PageSize, string searchTerm)
{
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    List<object> customers = new List<object>();
    SqlConnection con = new SqlConnection(strConnString);
    SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@PageIndex", PageNo);
    cmd.Parameters.AddWithValue("@PageSize", PageSize);
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
    con.Open();
    SqlDataReader sdr = cmd.ExecuteReader();
    while (sdr.Read())
    {
        customers.Add(new
        {
            Id = sdr["CustomerID"],
            Name = sdr["ContactName"],
            City = sdr["City"],
            Country = sdr["Country"]
        });
    }
    con.Close();
    CustomerData tableData = new CustomerData();
    tableData.Customers = customers;
    tableData.TotalRecords = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
    return tableData;
}

public class CustomerData
{
    public List<object> Customers { get; set; }
    public int TotalRecords { get; set; }
}

VB.Net

<WebMethod()>
Public Shared Function GetCustomers(ByVal PageNo As Integer, ByVal PageSize As Integer, ByVal searchTerm As String) As CustomerData
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim customers As List(Of Object) = New List(Of Object)()
    Dim con As SqlConnection = New SqlConnection(strConnString)
    Dim cmd As SqlCommand = New SqlCommand("GetCustomersPageWise", con)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@PageIndex", PageNo)
    cmd.Parameters.AddWithValue("@PageSize", PageSize)
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
    con.Open()
    Dim sdr As SqlDataReader = cmd.ExecuteReader()
    While sdr.Read()
        customers.Add(New With {
            .Id = sdr("CustomerID"),
            .Name = sdr("ContactName"),
            .City = sdr("City"),
        .Country = sdr("Country")
        })
    End While

    con.Close()
    Dim tableData As CustomerData = New CustomerData()
    tableData.Customers = customers
    tableData.TotalRecords = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
    Return tableData
End Function

Public Class CustomerData
    Public Property Customers As List(Of Object)
    Public Property TotalRecords As Integer
End Class

Screenshot