AngularGrid with ASP.NET Web API

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

1. Visual studio 2013
2. 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
/*****************************************************************************
	** Description:	Get the Employee Detail table
	** Author:		Amit P Naik
	** Date:		21-Dec-2016
	** Example 1:	
		exec [usp_GetEmployeeDetail] 10,1
		
	******************************************************************************
	** Change History
	******************************************************************************
	** PR   Date			Author					Description	
	** --   --------		-------					------------------------------------
	** 1    					
	******************************************************************************/
	
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() {

    //Function to get the range for the Paging number
    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;
    };

    //Function to get the display paging message
    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";
        }
    }

    //Function to go for next page
    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;
    }

    //Function to go for previous page
    this.prevPage = function (currentPage) {
        if (currentPage > 1) {
            currentPage--;
        }
        return currentPage;
    }


}

Code snippet of AngularGrid.js

function AngularGridViewModel($scope, $http, UtilityObject, $q) {

    //declaring the variable
    $scope.AngularGrid = new Employee();
    $scope.BindGrid = [];
    $scope.Utility = UtilityObject;
    $scope.PageSize = 10;
    $scope.currentPage = 1;
    $scope.PagingMessage = "";

    function Error(Message) {
        alert(Message);
    }

    //declaring the variable for defer and promise
    var defer = null;

    //Initializing for defer and promise
    function initPromises() {
        defer = $q.defer();
        var promise = defer.promise;
        promise.then('', Error);
    }

    //Function to bind Angular Grid
    $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;
    };


}





//Initialize the angular with our project which name as MainPage
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
    {
        //
        // GET: /Employee/
        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>