Angularjs Server Side pagination in ASP.NET MVC


In this article, I am going to explain you how to implement server side pagination using AngularJs with asp.net mvc.

Creating ASP.NET MVC Empty Application

Fisrt step is to create ASP.NET MVC empty application as shown below.
Go to FileNewProject. A new window will be open as shown below.
Now go to Web and select .NET Framework 4.5 and give project name and click on OK .

Creating asp.net mvc 5 application

Now new window will open as shown below.
Now Select Empty Template, check on MVC checkbox and click on OK.

Selecting asp.net mvc 5 empty template

Now, a new project will be created as shown below.

Asp.net mvc 5 empty folder structure

Adding Models

Go to Models folder and add new class file Country.cs. Now replace all code with below code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebApplication1.Models
{
    public class Country
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string CityName { get; set; }
        public int TotalRecords { get; set; }
    }
}

Adding Data Access Layer

Create a Repository.cs class file in root directory of application. Add below code. Here, add one method GetAllCountry which will return list of country. Methods takes 2 parameter PageNo and PageSize to sort and fetch records from database.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WebApplication1.Models;

namespace WebApplication1
{
    public class Repository
    {
        public IEnumerable<Country> GetAllCountry(int PageNo, int PageSize)
        {
            List<Country> countryList = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString()))
                {
                    countryList = new List<Country>();
                    SqlCommand cmd = new SqlCommand("uspAngularDemo" , con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue( "@PageNo", PageNo);
                    cmd.Parameters.AddWithValue( "@PageSize", PageSize);
                    con.Open();
                    SqlDataReader sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        Country c = new Country();
                        c.Id = Convert.ToInt32(sdr["Id"].ToString());
                        c.Name = sdr[ "Name"].ToString();
                        c.CityName = sdr[ "CityName"].ToString();
                        c.TotalRecords = Convert.ToInt32(sdr["TotalRecords"].ToString());
                        countryList.Add(c);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return countryList;
        }
    }
}

Creating Table

We will be using tblCountry table for database operation. Use below script to create table.

create table tblCountry
(
Id int primary key identity,
Name varchar(100),
CityName varchar(100)
)

Insert Data

Use below script to insert data into table. Below script will insert 50 records using loop.

declare @count int=1
while(@count<=10)
begin
 insert into tblCountry values('India','City'+cast(@count as varchar(20)))
 insert into tblCountry values('USA','City'+cast(@count as varchar(20)))
 insert into tblCountry values('UK','City'+cast(@count as varchar(20)))
 insert into tblCountry values('Australia','City'+cast(@count as varchar(20)))
 set @count=@count+1
end

Creating Procedure

Use below script to create procedure.

create procedure uspAngularDemo
@PageNo int,
@PageSize int
as
begin
 select Id,Name,CityName,(select count(Id) from tblCountry) as 'TotalRecords'
       from (
         select Id,Name,CityName ,row_number() over (order by id desc) as rowno from tblCountry
        ) temp WHERE rowno between ( (@PageNo-1)*@PageSize)+1 and @PageSize*@PageNo
end

Adding Controller

Next step is to add controller to application. Go to controller folder and add new empty controller named as Home controller. Replace all code with below code. First method is Index which will return index view and second method is GetCountryList which return json data which will be called from index view using ajax method with angularjs.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication1.Models;

namespace WebApplication1.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
        public ActionResult GetCountryList( int PageNo, int PageSize)
        {
            Repository obj = new Repository();
            IEnumerable<Country> Country = null;
            Country = obj.GetAllCountry(PageNo, PageSize);
            return Json(Country, JsonRequestBehavior.AllowGet);
        }
    }
}

Adding View

Now, right click on home controller index action method, add new view and name Index. Select Empty template and uncheck Use layout page and click on Add as shown below.

Addling index view

Now go to Views / Home folder, open Index.cshtml file and replace all code with below code.

Here, angular.min.js can be downloaded from angular website.
dirPagination.js can be downloaded from below link.
dirPagination.js
myScript.js code is written below.
bootstrap js and css files are optional and you can use your own css.

Index.cshtml Code:
@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title> AngularJs server side pagination in asp.net mvc </title>

    <!--this file is required to bootstrap angularjs functionality-->
    <script src="~/Scripts/angular.min.js"></script>
    <!--this file is required to generate page at runtime-->
    <script src="~/Scripts/dirPagination.js"></script>
    <!--write your custom code in myScript.js file-->
    <script src="~/Scripts/myScript.js"></script>

    < !--bootstrap js and css file is required to provide css to table and pagination -->
    <!--you can use you custom css if you are not using bootstrap js and css files-->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>

</head>
<body ng-controller="listCountry as data" ng-app="myApp">
    <div class="container col-lg-6">
        <h2> AngularJs server side pagination in asp.net mvc </h2>
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Country Id</th>
                    <th>Country Name</th>
                    <th>City Name</th>
                </tr>
            </thead>
            <tbody>
                <tr dir-paginate=" obj in data.Country|itemsPerPage:data.itemsPerPage " total-items="data.total_count">
                    <td>{{obj.Id}}</td>
                    <td>{{obj.Name}}</td>
                    <td>{{obj.CityName}}</td>
                </tr>
            </tbody>
        </table>
        <dir-pagination-controls max-size="6"
                                 direction-links="true"
                                 boundary-links="true"
                                 on-page-change="data.getData(newPageNumber)">
        </dir-pagination-controls>
    </div>
</body>
</html>

Writing AngularJS Code in Custom JavaScript File

Now, we need to write custom javascript code in myScript.js file and save in Scripts folder. Now drag and drop Angular.js file at the top of custom javascript file for the reference as shown below.

myScript.js Code:
/// <reference path="angular.min.js" />
var app = angular.module('myApp', ['angularUtils.directives.dirPagination']);
app.controller('listCountry', function ($http) {
    var vm = this;
    vm.Country = []; //declare an empty array
    vm.pageno = 1; // initialize page no to 1
    vm.total_count = 0;
    vm.itemsPerPage = 6; //set 6 initially
    vm.display = false;
    vm.getData = function (pageno) { // This would fetch the data on page change.
        //In practice this should be in a factory.
        vm.Country = [];
        $http.get("/Home/GetCountryList/" , { params: { PageNo: pageno, PageSize: vm.itemsPerPage } }).then( function (response) {
            vm.Country = response.data;  //ajax request to fetch data into vm.data
            vm.total_count = response.data[0].TotalRecords; //response.length;   // total database record count
            vm.display = true;
        });
    };
    vm.getData(vm.pageno); // Call the function to fetch initial data on page load.
});

Output can be seen as below after running application.

AngularJs server side pagination in asp.net mvc


Related Post 

blog comments powered by Disqus