In this article I will explain with an example, how to fetch (retrieve) data from
SQL Server database using
AngularJS in ASP.Net MVC.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Creating an Entity Data Model
The very first step is to create an ASP.Net MVC Application and connect it to the
Northwind Database using
Entity Framework.
Following is the Entity Data Model of the Customers Table of the Northwind Database which will be used later in this project.
![Fetch (Retrieve) data from Database using AngularJS in ASP.Net MVC]()
Model
The Model class consists of the following property.
public class CustomerModel
{
///<summary>
/// Gets or sets Name.
///</summary>
public string Name { get; set; }
}
Controller
The Controller consists of following Action method.
Action method for handling GET operation
Inside this Action method, simply the View is returned.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
}
Namespaces
You will need to import the following namespaces.
using System.Linq;
using System.Web.Http;
Web API Controller
The Web API Controller consists of the following Action method.
Action method for handling POST operation
This Action method accepts an object of CustomerModel class object.
The records of the Customers are fetched using
Entity Framework and are filtered using the
StartsWith function based on the value of the
Name property.
Finally, the records are returned as Generic List collection.
This method is decorated with Route attribute which defines its Route for calling the Web API method and HttpPost attribute which signifies that the method will accept Post requests.
public class AjaxAPIController : ApiController
{
[Route("api/AjaxAPI/AjaxMethod")]
[HttpPost]
public List<Customer> AjaxMethod(CustomerModel customer)
{
NorthwindEntities entities = new NorthwindEntities();
return (from c in entities.Customers.Take(10)
where c.ContactName.StartsWith(customer.Name)
select c).ToList();
}
}
View
Inside the View, the following
AngularJS script file is inherited.
1. angular.min.js
The View consists of an HTML DIV to which
ng-app and
ng-controller AngularJS directives have been assigned.
The HTML DIV consists of an HTML INPUT TextBox, a Submit Button and an HTML Table which will be populated from JSON array using ng-repeat directive.
The HTML Table has been also applied with the
AngularJS ng-show directive and set to
IsVisible.
The INPUT TextBox has been applied with an
AngularJS ng-model directive which allows changes in the input field to update the model.
The Button has been assigned with an
AngularJS ng-click directive.
When the Button is clicked, the Search function is called which will be used to call Web API Controller.
Inside the
AngularJS controller, first the
IsVisible property is set to FALSE and the
Search function is executed.
Then, inside the
Search function, the
$http service is used to make an
AJAX call to the Web API 2 Controller’s method. The
$http service has following properties and methods.
Properties
1. method – The method type of HTTP Request i.e. GET or POST. Here it is set to POST.
2. url – URL of the Web API 2 Controller’s method.
3. datatype – The format of the data i.e. XML or JSON. Here it is set as JSON.
4. data – The parameters to be sent to the Web API 2 Controller’s method i.e. the Prefix TextBox value.
5. headers – List of headers to be specified for the HTTP Request.
Event Handlers
1. success – This event handler is triggered once the
AJAX call is successfully executed.
2. error – This event handler is triggered when the
AJAX call encounters an error.
The response from the
AJAX call is received in JSON format inside the
Success event handler of the
$http service. It is assigned to the
Customers variable.
The ng-repeat directive as the name suggests repeats the element based on the length of the collection, in this scenario it will repeat the TR element i.e. HTML Table Row.
The TBODY element of the HTML Table has been assigned with an ng-repeat directive in order to iterate through all the items of the Customers JSON array.
For each JSON object in the Customers JSON array, a TR element i.e. HTML Table Row is generated and appended into the HTML Table.
Finally, all the data is displayed in the HTML Table.
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.5.9/angular.min.js"></script>
<script type="text/javascript">
var app = angular.module('MyApp', [])
app.controller('MyController', function ($scope, $http, $window) {
$scope.IsVisible = false;
$scope.Search = function () {
var customer = '{Name: "' + $scope.Prefix + '" }';
var post = $http({
method: "POST",
url: "/api/AjaxAPI/AjaxMethod",
dataType: 'json',
data: customer,
headers: { "Content-Type": "application/json" }
});
post.success(function (data, status) {
$scope.Customers = data;
$scope.IsVisible = true;
});
post.error(function (data, status) {
$window.alert(data.Message);
});
}
});
</script>
<div ng-app="MyApp" ng-controller="MyController">
Name:
<input type="text" ng-model="Prefix"/>
<input type="button" value="Submit" ng-click="Search()" />
<hr/>
<table cellpadding="0" cellspacing="0" ng-show="IsVisible">
<tr>
<th>Customer Id</th>
<th>Name</th>
<th>Country</th>
</tr>
<tbody ng-repeat="m in Customers">
<tr>
<td>{{m.CustomerID}}</td>
<td>{{m.ContactName}}</td>
<td>{{m.Country}}</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
Screenshot
![Fetch (Retrieve) data from Database using AngularJS in ASP.Net MVC]()
Downloads
