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 <div ng-class="GetSortClass('CustomerID')">
</th>
<th ng-click="SortData('ContactName')">
Name <div ng-class="GetSortClass('ContactName')">
</th>
<th ng-click="SortData('City')">
City <div ng-class="GetSortClass('City')">
</th>
<th ng-click="SortData('Country')">
Country <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
