Introduction
In this article, we are going to learn how to do pagination (server side), searching and sorting for HTML table via Angularjs and retrieving data is from Database via Web API. And to standardize the code we are implementing some Design Pattern
Download Source code from Given GitHub URL
https://github.com/Amitpnk/AngularGrid
Tools Used for development of web application
- Visual studio 2013
- SQL server 2008
Using the code
Creating database
Lets us create a database in SQL server with name EmployeeDB
Right click on database folder and then select new database a new dialog with name “New database” it will pop up and ask for database name. Enter database name as EmployeeDB and click on ok button.
Fig 1.Database View
After creating the database now add tables.
Adding Table in EmployeeDB database
Table: EmployeeDetail
In this table, we are going to store data which is supposed to display in table
Fig 2.EmployeeDetail table
After table structure now let us create stored procedure
Code snippet of stored procedure
GO
create PROCEDURE [dbo].usp_GetEmployeeDetail
@PageSize int,
@PageIndex int
AS
BEGIN
SET NOCOUNT ON;
;WITH InboxRecords AS
(Select
row_number() over(ORDER BY EmpNo ASC) AS CNT,
COUNT(*) OVER (Partition by NULL) 'VirtualItemCount',
* FROM (
SELECT * from dbo.EmployeeDetail
)AS A)
SELECT
*
from InboxRecords C
WHERE CNT BETWEEN ((@PageIndex - 1) * @PageSize + 1)
AND (@PageIndex * @PageSize)
END
Now let us have look on web application
Creating MVC Application
Create a Web application.
Open visual studio IDE from start page click on new project and create ASP.NET Web Application with MVC and Web API check box checked as “AngularGrid.UI”.
Fig 3.New Project
Project structure after creating application
Once after creating Project, application structure look like below image,
Fig 4.Project Structure
To organize the code will implement DAL, MODEL and Repository Class library
After creating the solution we are organizing our application by adding DAL (DataAccessLayer) class library, Model class library and Interface (Repository Design Pattern- For decoupling the DAL and UI)
For implementing DAL, MODEL and Repository pattern we need to add a class library to the solution.
Fig 5.Project Structure
Adding new class EmployeeDetail in AngularGrid.Model
Adding new class EmployeeDetail in AngularGrid.Model project and then declare methods in it.
Fig 6. Adding new class EmployeeDetail in AngularGrid.Model
Code snippet of EmployeeDetail class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AngularGrid.Model
{
public class EmployeeDetail
{
public int ID { get; set; }
public int EmpNo { get; set; }
public string EmpName { get; set; }
public string EmpEmailID { get; set; }
public int VirtualItemCount { get; set; }
}
}
Adding new interface IRepository in AngularGrid.Repository
Adding new interface IRepository in AngularGrid.Repository project and then declare methods in it.
Code snippet of IRepository Interface
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AngularGrid.Repository
{
public interface IRepository<AnyType>
{
List<AnyType> List(int PageSize, int PageIndex);
}
}
Adding Dapper ORM in EmployeeDAL
EmployeeDAL will have a method called List which will communicate with the database. For this we are going to use Dapper [free ORM install it from NuGet.]
For adding Dapper ORM just right click on project then select “Manage NuGet packages” Search for Dapper and select “Dapper dot net” and click on install button to install.
Fig 9. Installing Dapper ORM
After adding Dapper ORM lets add database connection string in web.config file.
Configuring Connection string in web.config file
<connectionStrings>
<add name="AngularGridConnectionString" connectionString="Data Source=(local);Initial Catalog=EmployeeDB;Integrated Security=True" providerName="system.data.sqlclient" />
</connectionStrings>
After adding connection string in web.config file next will add EmployeeDAL
Adding new class EmployeeDAL in AngularGrid.DAL
Adding new class EmployeeDAL in AngularGrid.DAL project and then declare methods in it.
Fig 10. Adding new interface EmployeeDAL in AngularGrid.DAL project
Code snippet of EmployeeDAL class
using AngularGrid.Model;
using AngularGrid.Repository;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using Dapper;
using System.Data;
namespace AngularGrid.DAL
{
public class EmployeeDAL : IRepository<EmployeeDetail>
{
public List<EmployeeDetail> List(int PageSize, int PageIndex)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AngularGridConnectionString"].ToString()))
{
var para = new DynamicParameters();
para.Add("@PageSize", PageSize);
para.Add("@PageIndex", PageIndex);
return con.Query<EmployeeDetail>("usp_GetEmployeeDetail", para, null, true, 0, CommandType.StoredProcedure).ToList();
}
}
}
}
Adding EmployeeWebAPI Controller
For Adding EmployeeWebAPI Controller Just Right click on Controller Folder inside that select Add and then select Web API 2 Controller , After clicking on WebAPI Controller new dialog will pop up with name Add Web API Controller. In this Dialog to add WebAPI Controller we are not going to make any change just click on Add button to add a WebAPI Controller with name EmployeeApiController
Fig 11. Adding new EmployeeWebAPI in AngularGrid.UI project
Code snippet of EmployeeWebAPI class
using AngularGrid.DAL;
using AngularGrid.Model;
using AngularGrid.Repository;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
namespace AngularGrid.Controllers
{
public class EmployeeApiController : ApiController
{
IRepository<EmployeeDetail> obj = null;
public EmployeeApiController()
{
obj = new EmployeeDAL();
}
public HttpResponseMessage GET()
{
var KeyValues = ControllerContext.Request.GetQueryNameValuePairs();
string PageIndex = Convert.ToString(KeyValues.SingleOrDefault(x => x.Key == "PageIndex").Value);
string PageSize = Convert.ToString(KeyValues.SingleOrDefault(x => x.Key == "PageSize").Value);
HttpResponseMessage response = null;
List<EmployeeDetail> employee = obj.List(Convert.ToInt32(PageSize), Convert.ToInt32(PageIndex)).ToList();
response = Request.CreateResponse(HttpStatusCode.OK, employee);
return response;
}
}
}
Adding Angular in AngularGrid.UI
For adding AngularJS just right click on project then select “Manage NuGet packages” Search for “AngularJS” and select “AngularJS core” and click on install button to install.
Fig 14. Installing AngularJS via Package Manager
Once AngularJS is added, we are able to see the Angular JS file in Scripts folder
Fig 15. Angular JS file in Scripts folder
Adding AngularGrid.js, AngularGrid_Model.js and AngularGrid_Utility.js in AngularGrid.UI
For Adding AngularGrid.js, AngularGrid_Model.js and AngularGrid_Utility.js. Just Right click on Script Folder inside that select Add and then select Javascript file, after clicking on Javascript new dialog will pop up with name AngularGrid.js, AngularGrid_Model.js and AngularGrid_Utility.js.
Code snippet of AngularGrid_Model.js
function Employee() {
this.ID = "",
this.EmpNo = "",
this.EmpName = "",
this.EmpEmailID = ""
};
Code snippet of AngularGrid_Utility.js
function Utility() {
this.range = function (min, virtualCount, size, step) {
step = step || 1;
var input = [];
for (var i = min; i <= Math.ceil(virtualCount / size) ; i += step) input.push(i);
return input;
};
this.Paging = function (VitualCount, PageSize, Index) {
var PagingMessage = "";
if (VitualCount > PageSize) {
var Index2 = Index * PageSize;
Index = (PageSize * Index) - (PageSize - 1);
if (Index2 > VitualCount) {
Index2 = VitualCount;
}
return PagingMessage = "Showing " + Index + " to " + Index2 + " of " + VitualCount + " entries";
}
else {
return PagingMessage = "Showing " + Index + " to " + VitualCount + " of " + VitualCount + " entries";
}
}
this.nextPage = function (currentPage, VirtualItemCount, PageSize, NoOfPages) {
if (currentPage < Math.ceil(VirtualItemCount / PageSize)) {
currentPage++;
if (currentPage > 10) {
Utility.range(currentPage, VirtualItemCount, currentPage + 10, 1)
}
}
return currentPage;
}
this.prevPage = function (currentPage) {
if (currentPage > 1) {
currentPage--;
}
return currentPage;
}
}
Code snippet of AngularGrid.js
function AngularGridViewModel($scope, $http, UtilityObject, $q) {
$scope.AngularGrid = new Employee();
$scope.BindGrid = [];
$scope.Utility = UtilityObject;
$scope.PageSize = 10;
$scope.currentPage = 1;
$scope.PagingMessage = "";
function Error(Message) {
alert(Message);
}
var defer = null;
function initPromises() {
defer = $q.defer();
var promise = defer.promise;
promise.then('', Error);
}
$scope.loadGrid = function (Index) {
initPromises();
$http({
method: "GET",
url: "/Api/EmployeeApi?PageIndex=" + Index + "&PageSize=" + $scope.PageSize
}).success(function (data, status, header, config) {
$scope.BindGrid = data;
$scope.VirtualItemCount = $scope.BindGrid[0].VirtualItemCount;
$scope.PagingMessage = $scope.Utility.Paging($scope.BindGrid[0].VirtualItemCount, $scope.PageSize, Index);
$scope.currentPage = Index;
})
.error(function (data, status, header, config) {
defer.reject("Error while getting the data");
});
}
$scope.loadGrid(1);
$scope.sortBy = function (propertyName) {
$scope.reverse = ($scope.propertyName === propertyName) ? !$scope.reverse : false;
$scope.propertyName = propertyName;
};
$scope.prevPage = function () {
$scope.loadGrid($scope.Utility.prevPage($scope.currentPage));
};
$scope.nextPage = function () {
$scope.loadGrid($scope.Utility.nextPage($scope.currentPage, $scope.VirtualItemCount, $scope.PageSize, 10));
}
$scope.setPage = function () {
$scope.currentPage = this.n;
};
}
var MainApp = angular.module("MainPage", []);
MainApp.controller("AngularGridViewModel", AngularGridViewModel);
MainApp.service("UtilityObject", Utility);
Adding Employee Controller
For Adding Employee Controller Just Right click on Controller Folder inside that select Add and then select MVC 5 Controller , After clicking on Controller new dialog will pop up with name Add Controller. In this Dialog to add Controller we are not going to make any change just click on Add button to add a Controller with name EmployeeController.
Code snippet of EmployeeController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace AngularGrid.Controllers
{
public class EmployeeController : Controller
{
public ActionResult Index()
{
return View();
}
}
}
After adding controller, add View to the Index ActionResult
Code snippet of Index.cshtml
@{
Layout = null;
}
<!DOCTYPE html>
<html lang="en">
<head>
<title>Angular Grid</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="~/Scripts/angular.js"></script>
<script src="~/Scripts/AngularGrid_Model.js"></script>
<script src="~/Scripts/AngularGrid_Utility.js"></script>
<script src="~/Scripts/AngularGrid.js"></script>
<style>
.sortorder:after {
content: '\25b2';
}
.sortorder.reverse:after {
content: '\25bc';
}
ul.pagination {
display: inline-block;
padding: 0;
margin: 0;
}
ul.pagination li {
display: inline;
}
ul.pagination li a {
color: black;
float: left;
padding: 8px 16px;
text-decoration: none;
transition: background-color .3s;
}
ul.pagination li a.active {
background-color: #337AC6;
color: white;
}
ul.pagination li a:hover:not(.active) {
background-color: #ddd;
}
</style>
</head>
<body ng-app="MainPage">
<div class="container" ng-controller="AngularGridViewModel">
<h2>Angular Grid</h2>
<p>Table pagination, searching and sorting in Angularjs and Web API</p>
<div>
<br />
<div class="row">
Show <select ng-model="PageSize" ng-change="loadGrid(1)">
<option ng-selected="true" value="10">10</option>
<option value="20">20</option>
<option value="50">50</option>
</select> entries
<div class="box-tools pull-right">
<div class="input-group input-group-sm" style="width: 150px;">
<input type="text" class="form-control pull-right" placeholder="Search" ng-model="search">
<div class="input-group-btn">
</div>
</div>
</div>
</div>
<div class="row">
<div class="box-body table-responsive no-padding">
<table class="table table-bordered">
<thead>
<tr>
<th>
<a style=" cursor: pointer" ng-click="sortBy('EmpNo')">Emp No</a>
<span class="sortorder" ng-show="propertyName === 'EmpNo'" ng-class="{reverse: reverse}"></span>
</th>
<th>
<a style=" cursor: pointer" ng-click="sortBy('EmpName')">Emp Name</a>
<span class="sortorder" ng-show="propertyName === 'EmpName'" ng-class="{reverse: reverse}"></span>
</th>
<th>
<a style=" cursor: pointer" ng-click="sortBy('EmpEmailID')">Email</a>
<span class="sortorder" ng-show="propertyName === 'EmpEmailID'" ng-class="{reverse: reverse}"></span>
</th>
</tr>
</thead>
<tbody>
<tr ng-repeat="x in BindGrid | orderBy:propertyName:reverse | filter : search">
<td>{{x.EmpNo}}</td>
<td>{{x.EmpName}}</td>
<td>{{x.EmpEmailID}}</td>
</tr>
</tbody>
</table>
</div>
<div class="col-lg-3" style="margin-top:30px">
<div class="form-group">
{{PagingMessage}}
</div>
</div>
<div class="pagination pagination-sm no-margin pull-right">
<ul class="pagination disabled">
<li>
<a ng-click="prevPage()">« Prev</a>
</li>
</ul>
<ul class="pagination " ng-repeat="n in Utility.range(1,VirtualItemCount ,PageSize,1)">
<li><a id="P{{n}}" ng-class="{active: n == currentPage}" ng-click="loadGrid(n)">{{n}}</a></li>
</ul>
<ul class="pagination">
<li>
<a ng-click="nextPage()">Next »</a>
</li>
</ul>
</div>
</div>
</div>
</div>
</body>
</html>
