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 id="Head1" runat="server">
<title></title>
<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("CS.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);
});
</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>Id</th>
<th>Name</th>
<th>City</th>
<th>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(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
