How To Fetch Data From The Database Using AngularJS In Web API

Background

We know AngularJS is a client-side script. So, we cannot use it on server side. In this article, we will fetch the data from database so we will use SQL Server database and the data will be called from database in server side by using web API. The client side will be called using AngularJS. After getting all the data, we will bind it within HTML table.

Sounds good? Here I am going to use Visual Studio Application.

Now, we will go create our application. I hope you will like this.

You can always download the source code here.

Firstly, we will create a database. The following query can be used to create a database in our SQL Server.

To create Database

CREATE DATABASE SchoolMangement;
To create Table
USE [SchoolMangement]
GO
/****** Object: Table [dbo].[tbl_Student] Script Date: 7/24/2016 11:12:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[Address] [nvarchar](250) NULL,
CONSTRAINT [PK_tbl_Student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now, database has been created. Finally, we get the table like,


We can now insert the data in database.

INSERT [dbo].[tbl_Student] ([StudentID], [FirstName], [LastName], [Email], [Address]) VALUES (1, N'Parvaz', N'Reza', N'[email protected]', N'Amla,Kushtia')
GO
INSERT [dbo].[tbl_Student] ([StudentID], [FirstName], [LastName], [Email], [Address]) VALUES (2, N'Mamun', N'Uddin', N'[email protected]', N'mirpur,Kushtia')
GO
INSERT [dbo].[tbl_Student] ([StudentID], [FirstName], [LastName], [Email], [Address]) VALUES (3, N'Seam', N'Alli', N'[email protected]', N'Dhaka,Bangladesh')

Creating MVC Application

Click File >> New >> Project. Select ASP.NET web application. Give project a name, location and click OK. From the following pop up, we will select the MVC. Also, we select the core references and folders for MVC and Web API.



Once you click OK, a project with MVC will be created for you. You can see its folder structure with core references.



We will setup AngularJS. If you do not know how to setup AngularJS, click here. after setting up the AngularJS, we will create a data model.

Create Entity Data Model

Right click on your Models folder and click New. Select ADO.NET Entity Data Model. Follow the steps given. Once you are done with the process, you can see the edmx file and other files in your Models folder. Here, I gave SchoolMangementEntities for our Entity Data Model name. Now, you can see that a file with edmx extension have been created.

Create Controller

Right click on your Controller folder and select Add, click Controller, select MVC 5 Controller- Empty and name it as StudentController.

public class StudentController : Controller
    {
        //
        // GET: /Student/
        public ActionResult Index()
        {
            return View();
        }
    }

Create View

Right click on Index Method which we have in StudentController. Click on Add View. Follow the steps given

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

Create Web API Controller

We will create a folder for API in our project named "API". Right click on API folder, select add, click controller, select Web API 2 Controller-Empty and give it name as "StudentController".

public class StudentController : ApiController
    {
        SchoolMangementEntities _ctx = null; // SchoolMangementEntities is data model name

        public StudentController()
        {
            _ctx = new SchoolMangementEntities();
        }
        public List<tbl_Student> GetStudents()
        {
            List<tbl_Student> students = null;
            try
            {
                students = _ctx.tbl_Student.ToList();
            }
            catch
            {
                students = null;
            }
            return students;
        }
    }

So, the coding part to fetch the data from the database is ready. Now, we need to check whether our Web API is ready for action. To check that, you just need to run the URL:

http://localhost:10956/api/student/GetStudents



Now, we will start our AngularJS part.

Right click on Modules folder. Select JavaScript file. Give it name as "app.js" which we have in ScriptsNg folder. Write the following code in this file.

var app;
(function () {
    'use strict'; //Defines that JavaScript code should be executed in "strict mode"
    app = angular.module('myapp', []);
})();

We create custom service here. Right click on Services folder, select JavaScript file, name it as “StudentService” which we have in ScriptsNg folder. Write the following code in this file.

app.service('StudentService', function ($http) {
    //**********----Get All Record----***************
    var urlGet = '';
    this.getAll = function (apiRoute) {
        urlGet = apiRoute;
        return $http.get(urlGet);
    }
});

We create StudentController now. Right click on Controller folder, select JavaScript file, name it as "StudentController” which we have in ScriptsNg folder. Write the following code in this file.

app.controller('StudentCtrl', ['$scope', 'StudentService',
    // we inject StudentService  inject becuse we call getAll method for get all student
function ($scope, StudentService) {
    // this is base url
    var baseUrl = '/api/student/';
    // get all student from databse
    $scope.getStudents=function()
    {
        var apiRoute = baseUrl + 'GetStudents/';
        var _student = StudentService.getAll(apiRoute);
        _student.then(function (response) {
            $scope.students = response.data;
        },
        function (error) {
            console.log("Error: " + error);
        });

    }
    $scope.getStudents();

}]);

Now, we will work on the Index View which we have created.

<div ng-app="myapp">
    <div ng-controller="StudentCtrl">
        <table class="table table-striped table-bordered table-hover table-checkable datatable">
            <thead class="grid-top-panel">
                <tr>
                    <th>StudentID</th>
                    <th>First Name</th>
                    <th>LastName</th>
                    <th>Email</th>
                </tr>
            </thead>
            <tbody>
                <tr ng-repeat="dataModel in students">
                    <td>{{dataModel.StudentID}}</td>
                    <td>{{dataModel.FirstName}}</td>
                    <td>{{dataModel.LastName}}</td>
                    <td>{{dataModel.Email}}</td>
                </tr>
            </tbody>
        </table>
    </div>
</div>
<script src="~/Scripts/angular.min.js"></script>
<script src="~/ScriptsNg/Module/app.js"></script>
<script src="~/ScriptsNg/Controller/StudentController.js"></script>
<script src="~/ScriptsNg/Services/StudentService.js"></script>

Finally, we get the result.