Server Side Paging and Searching using AngularJS dirPagination directive in ASP.Net

Last Reply 2 months ago By dharmendr

Posted 3 months ago

I have gone through the below link. Its implemented in mvc.

So how to implement this with searching on textbox keyup using angularjs with web method in asp.net.

Server Side Paging using AngularJS dirPagination directive in ASP.Net MVC

Posted 2 months ago Modified on 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 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