CRUD AngularJs With Web Api And Stored Procedure
13:51
Introduction
In this post I want to show how to implement CRUD operation in AngularJs with web API and Stored procedure. To know more about Web API, Please follow my previous article.CRUD Image
Step To Create Application
Folow the following step to create application:
Table
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)
);
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
Entity Data Model
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; }
}
}
BundleConfig.cs
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"));
}
}
}
Views
Index.cshtml@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>
app.js
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 = '';
}
}
Controller
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace AngularJs_With_Web_API.Controllers
{
public class TestController : Controller
{
public ActionResult Index()
{
return View();
}
}
}
WebApiConfig.cs
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 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();
}
}
}
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 Get()
{
List emplist = new List();
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 emplist = new List();
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 emplist = new List();
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);
}
}
}
Download
You can download this application zip file here
0 comments