How to Fetch Data from the Database using AngularJS in Web API

• Download source code - 7 MB

Background

We know AngularJS is a client side script so we cannot use it server side. In this article, we fetch data from database so we will use SQL Server database and data will call by using Web API from database in server side and client side call using AngularJS. At last, after getting all data, we will bind within HTML table. Sounds good? Here, I am going to use Visual Studio application. Now, we will go and create our application. I hope you will like this.

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

CREATE DATABASE SchoolMangement;

To create table:

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 will get a table like:

We can insert data in database:

INSERT [dbo].[tbl_Student] ([StudentID], [FirstName], [LastName], [Email], [Address]) _
VALUES (1, N'Parvaz', N'Reza', N'parvaz@gmail.com', N'Amla,Kushtia')
GO
INSERT [dbo].[tbl_Student] ([StudentID], [FirstName], [LastName], [Email], [Address]) _
VALUES (2, N'Mamun', N'Uddin', N'mamun@yahoo.com', N'mirpur,Kushtia')
GO
INSERT [dbo].[tbl_Student] ([StudentID], [FirstName], [LastName], [Email], [Address]) _
VALUES (3, N'Seam', N'Alli', N'seam@gmail.com', N'Dhaka,Bangladesh')

Let us create an MVC application. Click File, New, then Project and then select ASP.NET Web Application. This will give project name, location and once click ok button. From the following pop up, we will select the MVC and select the core references and folders for MVC and Web API.

Once you click OK, a project with MVC like folder structure with core references will be created for you.

We will setup AngularJS. If you do not know how to setup AngularJS, click here. After setting up AngularJS, we will create a data model. Create Entity Data Model. Right click on your model folder and click new, select ADO.NET Entity Data Model. Follow the steps given. Once you have done the process , you can see the edmx file and other files in your model folder. Here, I gave SchoolMangementEntities for our Entity data model name. Now you can see a file with edmx extension has been created. Create Controller. Right click on your Controller folder and select Add, click Controller, select MVC 5 Controller - Empty and give name StudentController.

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

Create View

Right click Index method which you have in StudentController, click 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 API folder, select add, click controller, select Web API 2 Controller - Empty and give name "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;
        }
    }

As you can see, we have action GetStudents for data get 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 Angular part JS. Right click on Module folder, select JavaScript file, give name "app.js" which has 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 costume service. Right click on Services folder, select JavaScript file, give named “StudentService” which has 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, right click on Controller folder, select JavaScript file, give named “StudentController” which has ScriptsNg folder. Write the following code in this file.

app.controller('StudentCtrl', ['$scope', 'StudentService',
    // we inject StudentService  inject because we call getAll method for get all student
function ($scope, StudentService) {
    // this is base url 
    var baseUrl = '/api/student/';
    // get all student from database
    $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 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 following result: