Introduction
In this article, I am going to explain how to do CRUD operations in ASP.NET MVC AngularJS using WEB API 2 with Stored Procedure.
 |
| MVC Angular CRUD Operation Using WEB API 2 With Stored Procedure |
So, you should follow these steps to create a web application.
Step 1
Create a new database table. You can follow this query.
CREATE TABLE [dbo].[Employee] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (50) NOT NULL,
[Address] NVARCHAR (50) NOT NULL,
[Country] NVARCHAR (50) NOT NULL,
[City] NVARCHAR (50) NOT NULL,
[Mobile] NVARCHAR (10) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
Step 2
Create a stored procedure.
CREATE PROCEDURE sp_InsUpdDelEmployee
@id INT ,
@name NVARCHAR(50) ,
@address NVARCHAR(50) ,
@country NVARCHAR(50) ,
@city NVARCHAR(50) ,
@mobile NVARCHAR(50) ,
@type VARCHAR(10)
AS
BEGIN
IF ( @type = 'Ins' )
BEGIN
INSERT INTO Employee
VALUES ( @name, @address, @country, @city, @mobile )
END
IF ( @type = 'Upd' )
BEGIN
UPDATE Employee
SET Name = @name ,
[Address] = @address ,
Country = @country ,
City = @city ,
Mobile = @mobile
WHERE Id = @id
END
IF ( @type = 'Del' )
BEGIN
DELETE FROM Employee
WHERE Id = @id
END
IF ( @type = 'GetById' )
BEGIN
SELECT *
FROM Employee
WHERE Id = @id
END
SELECT *
FROM Employee
END
Step 3
Open Visual Studio and click File > New > Project.
 |
| Open Visual Studio and Create New Project |
Step 4
Click on Web from the left-side panel and select ASP.NET MVC4 Web Application, give name, and click OK.
 |
| ASP.NET MVC4 Web Application |
Step 5
Select Internet Application.
 |
| Internet Application |
Step 6
Right-click on Controller and click Add > Controller.
 |
| Add New Controller |
Step 7
Give a name to your Controller and select Empty API Controller.
 |
| Empty API Controller |
Step 8
Right-click on App_Data Folder and select Add > New Item.
 |
| New Item |
Step 9
Select SQL Server database, give a name to the database, and click OK.
 |
| Select SQL Server database |
Step 10
Right-click on Models and click Add > New Item.
 |
| Add New Entity Data Model |
Step 11
Select ADO.NET Entity Data Model.
 |
| ADO.NET Entity Data Model |
Step 12
Select Generate from Database and click Next.
 |
| Generate from Database |
Step 13
Choose Database Connection and Click Next.
 |
| Database Connection |
Step 14
Select Created Table and Created Stored Procedure.
 |
| Tables and Stored Procedures |
 |
| Tables and Procedures |
Entity Data Model
 |
| Entity Data Model |
Step 15
Write Following code in Model
namespace AngularJs_With_Web_API.Models
{
using System;
using System.Collections.Generic;
public partial class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Country { get; set; }
public string City { get; set; }
public string Mobile { get; set; }
}
}
Step 16
Now, Write the following code in BundleConfig.cs File
using System.Web;
using System.Web.Optimization;
namespace AngularJs_With_Web_API
{
public class BundleConfig
{
// For more information on Bundling, visit http://go.microsoft.com/fwlink/?LinkId=254725
public static void RegisterBundles(BundleCollection bundles)
{
bundles.Add(new ScriptBundle("~/js").Include(
"~/js/angular.js",
"~/js/app.js"));
bundles.Add(new StyleBundle("~/css").Include(
"~/css/bootstrap.css"));
}
}
}
Step 17
Now, If talking about VIEW then Write the Following code in the Index.cshtml File
@Scripts.Render("~/js")
@Styles.Render("~/css")
<html ng-app="myApp">
<head><title>AngularJs With WebApi and Stored Procedure</title></head>
<body>
<div ng-controller="employeeController" class="container">
<div class="row">
<div class="col-md-12">
<h3 class="header">AngularJs With WebApi and Stored Procedure</h3>
</div>
</div>
<div class="row">
<div class="col-md-12">
<strong class="error">{{error}}</strong>
<form name="addemployee" style="width: 600px; margin: 0px auto;">
<div class="form-group">
<label for="cname" class="col-sm-2 control-label">Name:</label>
<div class="col-sm-10 space">
<input type="text" class="form-control" id="cname" placeholder="please enter your name" ng-model="newemployee.Name" required />
</div>
</div>
<div class="form-group">
<label for="address" class="col-sm-2 control-label">Address:</label>
<div class="col-sm-10 space">
<textarea class="form-control" id="address" placeholder="please enter your address" ng-model="newemployee.Address" required></textarea>
</div>
</div>
<div class="form-group">
<label for="country" class="col-sm-2 control-label">Country:</label>
<div class="col-sm-10 space">
<input type="text" class="form-control" id="country" placeholder="please enter your country" ng-model="newemployee.Country" required />
</div>
</div>
<div class="form-group">
<label for="city" class="col-sm-2 control-label">City:</label>
<div class="col-sm-10 space">
<input type="text" class="form-control" id="city" placeholder="please enter your city" ng-model="newemployee.City" required />
</div>
</div>
<div class="form-group">
<label for="mobile" class="col-sm-2 control-label">Mobile:</label>
<div class="col-sm-10 space">
<input type="text" class="form-control" id="mobile" placeholder="please enter your mobile" ng-model="newemployee.Mobile" required />
</div>
</div>
<br />
<div class="form-group space">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" value="Add" ng-click="add()" ng-show="addShow" ng-disabled="!addemployee.$valid" class="btn btn-primary" />
<input type="submit" value="Update" ng-click="update()" ng-show="updateShow" ng-disabled="!addemployee.$valid" class="btn btn-primary" />
<input type="button" value="Cancel" ng-click="cancel()" class="btn btn-primary" />
</div>
</div>
<br />
</form>
</div>
</div>
<div class="row">
<div class="col-md-12">
<div class="table-responsive">
<table class="table table-bordered table-hover" style="width: 800px; margin-left: 170px;">
<tr>
<th>Name</th>
<th>Address</th>
<th>Country</th>
<th>City</th>
<th>Mobile</th>
<th>Actions</th>
</tr>
<tr ng-repeat="employee in employees">
<td>
<p>{{ employee.Name }}</p>
</td>
<td>
<p>{{ employee.Address }}</p>
</td>
<td>
<p>{{ employee.Country }}</p>
</td>
<td>
<p>{{ employee.City }}</p>
</td>
<td>
<p>{{ employee.Mobile }}</p>
</td>
<td>
<p><a ng-click="edit()" href="javascript:void(0);">Edit</a> | <a ng-click="delete()" href="javascript:void(0);">Delete</a></p>
</td>
</tr>
</table>
</div>
</div>
</div>
</div>
</body>
</html>
Step 18
Now, Write the following code in App.js File.
var app = angular.module('myApp', []);
app.controller('employeeController', ['$scope', '$http', employeeController]);
// Angularjs Controller
function employeeController($scope, $http) {
// Declare variable
$scope.loading = true;
$scope.updateShow = false;
$scope.addShow = true;
// Get All Employee
$http.get('/api/EmployeeAPI/').success(function (data) {
$scope.employees = data;
}).error(function () {
$scope.error = "An Error has occured while loading posts!";
});
//Insert Employee
$scope.add = function () {
$scope.loading = true;
$http.post('/api/EmployeeAPI/', this.newemployee).success(function (data) {
$scope.employees = data;
$scope.updateShow = false;
$scope.addShow = true;
$scope.newemployee = '';
}).error(function (data) {
$scope.error = "An Error has occured while Adding employee! " + data;
});
}
//Edit Employee
$scope.edit = function () {
var Id = this.employee.Id;
$http.get('/api/EmployeeAPI/' + Id).success(function (data) {
$scope.newemployee = data;
$scope.updateShow = true;
$scope.addShow = false;
}).error(function () {
$scope.error = "An Error has occured while loading posts!";
});
}
$scope.update = function () {
$scope.loading = true;
console.log(this.newemployee);
$http.put('/api/EmployeeAPI/', this.newemployee).success(function (data) {
$scope.employees = data;
$scope.updateShow = false;
$scope.addShow = true;
$scope.newemployee = '';
}).error(function (data) {
$scope.error = "An Error has occured while Saving employee! " + data;
});
}
//Delete Employee
$scope.delete = function () {
var Id = this.employee.Id;
$scope.loading = true;
$http.delete('/api/EmployeeAPI/' + Id).success(function (data) {
$scope.employees = data;
}).error(function (data) {
$scope.error = "An Error has occured while Saving employee! " + data;
});
}
//Cancel Employee
$scope.cancel = function () {
$scope.updateShow = false;
$scope.addShow = true;
$scope.newemployee = '';
}
}
Step 20
Now, Write the following code in WebApiConfig.cs File
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;
namespace AngularJs_With_Web_API
{
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
// Uncomment the following line of code to enable query support for actions with an IQueryable or IQueryable<T> return type.
// To avoid processing unexpected or malicious queries, use the validation settings on QueryableAttribute to validate incoming queries.
// For more information, visit http://go.microsoft.com/fwlink/?LinkId=279712.
//config.EnableQuerySupport();
}
}
}
Step 21
Now, Write the following code in API Controller
using AngularJs_With_Web_API.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Data;
using System.Data.Entity.Infrastructure;
namespace AngularJs_With_Web_API.Controllers
{
public class EmployeeAPIController : ApiController
{
// Get All The Employee
[HttpGet]
public List<Employee> Get()
{
List<Employee> emplist = new List<Employee>();
using (dbEntities db = new dbEntities())
{
var results = db.sp_InsUpdDelEmployee(0, "", "", "", "", "", "Get").ToList();
foreach (var result in results)
{
var employee = new Employee()
{
Id = result.Id,
Name = result.Name,
Address = result.Address,
Country = result.Country,
City = result.City,
Mobile = result.Mobile
};
emplist.Add(employee);
}
return emplist;
}
}
// Get Employee By Id
public Employee Get(int id)
{
using (dbEntities db = new dbEntities())
{
Employee employee = db.Employees.Find(id);
if (employee == null)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
}
return employee;
}
}
// Insert Employee
public HttpResponseMessage Post(Employee employee)
{
if (ModelState.IsValid)
{
using (dbEntities db = new dbEntities())
{
var emplist = db.sp_InsUpdDelEmployee(0, employee.Name, employee.Address, employee.Country, employee.City, employee.Mobile, "Ins").ToList();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, emplist);
return response;
}
}
else
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
}
}
// Update Employee
public HttpResponseMessage Put(Employee employee)
{
List<sp_InsUpdDelEmployee_Result> emplist = new List<sp_InsUpdDelEmployee_Result>();
if (!ModelState.IsValid)
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
}
using (dbEntities db = new dbEntities())
{
try
{
emplist = db.sp_InsUpdDelEmployee(employee.Id, employee.Name, employee.Address, employee.Country, employee.City, employee.Mobile, "Upd").ToList();
}
catch (DbUpdateConcurrencyException ex)
{
return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
}
}
return Request.CreateResponse(HttpStatusCode.OK, emplist);
}
// Delete employee By Id
public HttpResponseMessage Delete(int id)
{
using (dbEntities db = new dbEntities())
{
List<sp_InsUpdDelEmployee_Result> emplist = new List<sp_InsUpdDelEmployee_Result>();
var results = db.sp_InsUpdDelEmployee(id, "", "", "", "", "", "GetById").ToList();
if (results.Count == 0)
{
return Request.CreateResponse(HttpStatusCode.NotFound);
}
try
{
emplist = db.sp_InsUpdDelEmployee(id, "", "", "", "", "", "Del").ToList();
}
catch (DbUpdateConcurrencyException ex)
{
return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
}
return Request.CreateResponse(HttpStatusCode.OK, emplist);
}
}
// Prevent Memory Leak
protected override void Dispose(bool disposing)
{
using (dbEntities db = new dbEntities())
db.Dispose();
base.Dispose(disposing);
}
}
}
Summary
In this article, I clearly explained how to perform CURD operations in ASP.NET MVC with AngulerJS using Web API and also explain how to use Stored Procedure. I hope this article will help beginners.
