Server Side HTML Table Paging and Sorting with Stored Procedure using AngularJS in ASP.Net

Last Reply 23 days ago By dharmendr

Posted 27 days ago

How to do the sorting using server side code with stored procedure.

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

How to send the parameters sortdirection and sortcolumn from angularjs controller to code behind to use the stored procedure.

You are viewing reply posted by: dharmendr 23 days ago.
Posted 23 days ago Modified on 22 days 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

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 runat="server">
    <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.SortColumn = "CustomerID";
            $scope.ReverseSort = false;
            $scope.SortDirection = "ASC";
            $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("Default.aspx/GetCustomers", data, { headers: { 'Content-Type': 'application/json'} })
                .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 ? "DESC" : "ASC";
                $scope.SortColumn = column;
                $scope.GetCustomers($scope.PageIndex);
            }
            $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('CustomerID')">
                            Id&nbsp;<div ng-class="GetSortClass('CustomerID')">
                        </th>
                        <th ng-click="SortData('ContactName')">
                            Name&nbsp;<div ng-class="GetSortClass('ContactName')">
                        </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|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(string searchTerm, int pageNo, int pageSize, string sortDirection, string sortColumn)
{
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    List<object> customers = new List<object>();
    SqlConnection con = new SqlConnection(strConnString);
    SqlCommand cmd = new SqlCommand("GetCustomersPageWise_Sort", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
    cmd.Parameters.AddWithValue("@PageIndex", pageNo);
    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;
    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 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("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_Sort", con)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
    cmd.Parameters.AddWithValue("@PageIndex", pageNo)
    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
    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