ASP.NET

MVC Angular CRUD Operation Using WEB API 2 With Stored Procedure

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

So, you should follow these steps to create a web application.

Step 1

Create a new database table. You can follow this query.

  1. CREATE TABLE [dbo].[Employee] (  
  2.     [Id]      INT           IDENTITY (1, 1) NOT NULL,  
  3.     [Name]    NVARCHAR (50) NOT NULL,  
  4.     [Address] NVARCHAR (50) NOT NULL,  
  5.     [Country] NVARCHAR (50) NOT NULL,  
  6.     [City]    NVARCHAR (50) NOT NULL,  
  7.     [Mobile]  NVARCHAR (10) NOT NULL,  
  8.     PRIMARY KEY CLUSTERED ([Id] ASC)  
  9. );  

Step 2

Create a stored procedure.

  1. CREATE PROCEDURE sp_InsUpdDelEmployee  
  2.     @id INT ,  
  3.     @name NVARCHAR(50) ,  
  4.     @address NVARCHAR(50) ,  
  5.     @country NVARCHAR(50) ,  
  6.     @city NVARCHAR(50) ,  
  7.     @mobile NVARCHAR(50) ,  
  8.     @type VARCHAR(10)  
  9. AS   
  10.     BEGIN  
  11.         IF ( @type = 'Ins' )   
  12.             BEGIN  
  13.                 INSERT  INTO Employee  
  14.                 VALUES  ( @name, @address, @country, @city, @mobile )  
  15.             END  
  16.         IF ( @type = 'Upd' )   
  17.             BEGIN  
  18.                 UPDATE  Employee  
  19.                 SET     Name = @name ,  
  20.                         [Address] = @address ,  
  21.                         Country = @country ,  
  22.                         City = @city ,  
  23.                         Mobile = @mobile  
  24.                 WHERE   Id = @id  
  25.             END  
  26.         IF ( @type = 'Del' )   
  27.             BEGIN  
  28.                 DELETE  FROM Employee  
  29.                 WHERE   Id = @id  
  30.             END   
  31.         IF ( @type = 'GetById' )   
  32.             BEGIN  
  33.                 SELECT  *  
  34.                 FROM    Employee  
  35.                 WHERE   Id = @id  
  36.             END  
  37.         SELECT  *  
  38.         FROM    Employee  
  39.     END  

Step 3

Open Visual Studio and click File > New > Project.

CRUD Operation in Asp.Net

Step 4

Click on Web from the left-side panel and select ASP.NET MVC4 Web Application, give name, and click OK.

CRUD Operation in Asp.Net

Step 5

Select Internet Application.

CRUD Operation in Asp.Net

Step 6

Right-click on Controller and click Add > Controller.

CRUD Operation in Asp.Net

Step 7

Give a name to your Controller and select Empty API Controller.

CRUD Operation in Asp.Net

Step 8

Right-click on App_Data Folder and select Add > New Item.

CRUD Operation in Asp.Net

Step 9

Select SQL Server database, give a name to the database, and click OK.

CRUD Operation in Asp.Net

Step 10

Right-click on Models and click Add > New Item.

CRUD Operation in Asp.Net

Step 11

Select ADO.NET Entity Data Model.

CRUD Operation in Asp.Net

Step 12

Select Generate from Database and click Next.

CRUD Operation in Asp.Net

Step 13

Choose Database Connection and Click Next.

CRUD Operation in Asp.Net

Step 14

Select Created Table and Created Stored Procedure.

CRUD Operation in Asp.Net

CRUD Operation in Asp.Net

Entity Data Model

CRUD Operation in Asp.Net

Step 15

Write Following code in Model

  1. namespace AngularJs_With_Web_API.Models  
  2.     {  
  3.         using System;  
  4.         using System.Collections.Generic;  
  5.   
  6.         public partial class Employee  
  7.         {  
  8.             public int Id { get; set; }  
  9.             public string Name { get; set; }  
  10.             public string Address { get; set; }  
  11.             public string Country { get; set; }  
  12.             public string City { get; set; }  
  13.             public string Mobile { get; set; }  
  14.         }  
  15.     }  

Step 16

Now, Write Following code in BundleConfig.cs File

  1. using System.Web;  
  2. using System.Web.Optimization;  
  3.   
  4. namespace AngularJs_With_Web_API  
  5. {  
  6.     public class BundleConfig  
  7.     {  
  8.         // For more information on Bundling, visit http://go.microsoft.com/fwlink/?LinkId=254725  
  9.         public static void RegisterBundles(BundleCollection bundles)  
  10.         {  
  11.             bundles.Add(new ScriptBundle("~/js").Include(  
  12.                 "~/js/angular.js",  
  13.                 "~/js/app.js"));  
  14.   
  15.             bundles.Add(new StyleBundle("~/css").Include(  
  16.                 "~/css/bootstrap.css"));  
  17.         }  
  18.     }  
  19. }  

Step 17

Now, If talking about VIEW then Write Following code in Index.cshtml File

  1. @Scripts.Render("~/js")  
  2. @Styles.Render("~/css")  
  3.   
  4. <html ng-app="myApp">  
  5. <head><title>AngularJs With WebApi and Stored Procedure</title></head>  
  6. <body>  
  7.     <div ng-controller="employeeController" class="container">          
  8.         <div class="row">  
  9.             <div class="col-md-12">  
  10.                 <h3 class="header">AngularJs With WebApi and Stored Procedure</h3>  
  11.             </div>  
  12.         </div>  
  13.         <div class="row">              
  14.             <div class="col-md-12">  
  15.                 <strong class="error">{{error}}</strong>  
  16.                 <form name="addemployee" style="width: 600px; margin: 0px auto;">  
  17.                     <div class="form-group">  
  18.                         <label for="cname" class="col-sm-2 control-label">Name:</label>  
  19.                         <div class="col-sm-10 space">  
  20.                             <input type="text" class="form-control" id="cname" placeholder="please enter your name" ng-model="newemployee.Name" required />  
  21.                         </div>  
  22.                     </div>  
  23.                     <div class="form-group">  
  24.                         <label for="address" class="col-sm-2 control-label">Address:</label>  
  25.                         <div class="col-sm-10 space">                              
  26.                             <textarea class="form-control" id="address" placeholder="please enter your address" ng-model="newemployee.Address" required></textarea>  
  27.                         </div>  
  28.                     </div>  
  29.                     <div class="form-group">  
  30.                         <label for="country" class="col-sm-2 control-label">Country:</label>  
  31.                         <div class="col-sm-10 space">  
  32.                             <input type="text" class="form-control" id="country" placeholder="please enter your country" ng-model="newemployee.Country" required />  
  33.                         </div>  
  34.                     </div>  
  35.                     <div class="form-group">  
  36.                         <label for="city" class="col-sm-2 control-label">City:</label>  
  37.                         <div class="col-sm-10 space">  
  38.                             <input type="text" class="form-control" id="city" placeholder="please enter your city" ng-model="newemployee.City" required />  
  39.                         </div>  
  40.                     </div>  
  41.                     <div class="form-group">  
  42.                         <label for="mobile" class="col-sm-2 control-label">Mobile:</label>  
  43.                         <div class="col-sm-10 space">  
  44.                             <input type="text" class="form-control" id="mobile" placeholder="please enter your mobile" ng-model="newemployee.Mobile" required />  
  45.                         </div>  
  46.                     </div>  
  47.                     <br />  
  48.                     <div class="form-group space">  
  49.                         <div class="col-sm-offset-2 col-sm-10">  
  50.                             <input type="submit" value="Add" ng-click="add()" ng-show="addShow" ng-disabled="!addemployee.$valid" class="btn btn-primary" />  
  51.                             <input type="submit" value="Update" ng-click="update()" ng-show="updateShow" ng-disabled="!addemployee.$valid" class="btn btn-primary" />  
  52.                             <input type="button" value="Cancel" ng-click="cancel()" class="btn btn-primary" />  
  53.                         </div>  
  54.                     </div>  
  55.                     <br />  
  56.                 </form>  
  57.             </div>  
  58.         </div>  
  59.         <div class="row">  
  60.             <div class="col-md-12">  
  61.                 <div class="table-responsive">  
  62.                     <table class="table table-bordered table-hover" style="width: 800px; margin-left: 170px;">  
  63.                         <tr>  
  64.                             <th>Name</th>  
  65.                             <th>Address</th>  
  66.                             <th>Country</th>  
  67.                             <th>City</th>  
  68.                             <th>Mobile</th>  
  69.                             <th>Actions</th>  
  70.                         </tr>  
  71.                         <tr ng-repeat="employee in employees">  
  72.                             <td>  
  73.                                 <p>{{ employee.Name }}</p>  
  74.                             </td>  
  75.                             <td>  
  76.                                 <p>{{ employee.Address }}</p>  
  77.                             </td>  
  78.                             <td>  
  79.                                 <p>{{ employee.Country }}</p>  
  80.                             </td>  
  81.                             <td>  
  82.                                 <p>{{ employee.City }}</p>  
  83.                             </td>  
  84.                             <td>  
  85.                                 <p>{{ employee.Mobile }}</p>  
  86.                             </td>  
  87.                             <td>  
  88.                                 <p><a ng-click="edit()" href="javascript:void(0);">Edit</a> | <a ng-click="delete()" href="javascript:void(0);">Delete</a></p>  
  89.                             </td>  
  90.                         </tr>  
  91.                     </table>  
  92.                 </div>  
  93.             </div>  
  94.         </div>          
  95.     </div>  
  96. </body>  
  97. </html>  

Step 18

Now, Write Following code in App.js File.

  1. var app = angular.module('myApp', []);  
  2. app.controller('employeeController', ['$scope', '$http', employeeController]);  
  3.   
  4. // Angularjs Controller  
  5. function employeeController($scope, $http) {  
  6.     // Declare variable  
  7.     $scope.loading = true;  
  8.     $scope.updateShow = false;  
  9.     $scope.addShow = true;  
  10.   
  11.     // Get All Employee  
  12.     $http.get('/api/EmployeeAPI/').success(function (data) {  
  13.         $scope.employees = data;  
  14.     }).error(function () {  
  15.         $scope.error = "An Error has occured while loading posts!";  
  16.     });  
  17.   
  18.     //Insert Employee  
  19.     $scope.add = function () {  
  20.         $scope.loading = true;  
  21.         $http.post('/api/EmployeeAPI/', this.newemployee).success(function (data) {  
  22.             $scope.employees = data;  
  23.             $scope.updateShow = false;  
  24.             $scope.addShow = true;  
  25.             $scope.newemployee = '';  
  26.         }).error(function (data) {  
  27.             $scope.error = "An Error has occured while Adding employee! " + data;  
  28.         });  
  29.     }  
  30.   
  31.     //Edit Employee  
  32.     $scope.edit = function () {  
  33.         var Id = this.employee.Id;  
  34.         $http.get('/api/EmployeeAPI/' + Id).success(function (data) {  
  35.             $scope.newemployee = data;  
  36.             $scope.updateShow = true;  
  37.             $scope.addShow = false;  
  38.         }).error(function () {  
  39.             $scope.error = "An Error has occured while loading posts!";  
  40.         });  
  41.     }  
  42.   
  43.     $scope.update = function () {  
  44.         $scope.loading = true;  
  45.         console.log(this.newemployee);  
  46.         $http.put('/api/EmployeeAPI/', this.newemployee).success(function (data) {  
  47.             $scope.employees = data;  
  48.             $scope.updateShow = false;  
  49.             $scope.addShow = true;  
  50.             $scope.newemployee = '';  
  51.         }).error(function (data) {  
  52.             $scope.error = "An Error has occured while Saving employee! " + data;  
  53.         });  
  54.     }  
  55.   
  56.     //Delete Employee  
  57.     $scope.delete = function () {  
  58.         var Id = this.employee.Id;  
  59.         $scope.loading = true;  
  60.         $http.delete('/api/EmployeeAPI/' + Id).success(function (data) {  
  61.             $scope.employees = data;  
  62.         }).error(function (data) {  
  63.             $scope.error = "An Error has occured while Saving employee! " + data;  
  64.         });  
  65.     }  
  66.   
  67.     //Cancel Employee  
  68.     $scope.cancel = function () {  
  69.         $scope.updateShow = false;  
  70.         $scope.addShow = true;  
  71.         $scope.newemployee = '';  
  72.     }  
  73. }  

Step 19

Now, Write Following code in ControllerFile.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace AngularJs_With_Web_API.Controllers  
  8. {  
  9.     public class TestController : Controller  
  10.     {  
  11.         public ActionResult Index()  
  12.         {  
  13.             return View();  
  14.         }  
  15.     }  
  16. }  

Step 20

Now, Write Following code in WebApiConfig.cs File

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web.Http;  
  5.   
  6. namespace AngularJs_With_Web_API  
  7. {  
  8.     public static class WebApiConfig  
  9.     {  
  10.         public static void Register(HttpConfiguration config)  
  11.         {  
  12.             config.Routes.MapHttpRoute(  
  13.                 name: "DefaultApi",  
  14.                 routeTemplate: "api/{controller}/{id}",  
  15.                 defaults: new { id = RouteParameter.Optional }  
  16.             );  
  17.   
  18.             // Uncomment the following line of code to enable query support for actions with an IQueryable or IQueryable return type.  
  19.             // To avoid processing unexpected or malicious queries, use the validation settings on QueryableAttribute to validate incoming queries.  
  20.             // For more information, visit http://go.microsoft.com/fwlink/?LinkId=279712.  
  21.             //config.EnableQuerySupport();  
  22.         }  
  23.     }  
  24. }  

Step 21

Now, Write Following code in API Controller

  1. using AngularJs_With_Web_API.Models;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Net;  
  6. using System.Net.Http;  
  7. using System.Web.Http;  
  8. using System.Data;  
  9. using System.Data.Entity.Infrastructure;  
  10.   
  11. namespace AngularJs_With_Web_API.Controllers  
  12. {  
  13.     public class EmployeeAPIController : ApiController  
  14.     {  
  15.         // Get All The Employee  
  16.         [HttpGet]  
  17.         public List Get()  
  18.         {  
  19.             List emplist = new List();  
  20.             using (dbEntities db = new dbEntities())  
  21.             {  
  22.                 var results = db.sp_InsUpdDelEmployee(0, "", "", "", "", "", "Get").ToList();  
  23.                 foreach (var result in results)  
  24.                 {  
  25.                     var employee = new Employee()  
  26.                     {  
  27.                         Id = result.Id,  
  28.                         Name = result.Name,  
  29.                         Address = result.Address,  
  30.                         Country = result.Country,  
  31.                         City = result.City,  
  32.                         Mobile = result.Mobile  
  33.                     };  
  34.                     emplist.Add(employee);  
  35.                 }  
  36.                 return emplist;  
  37.             }  
  38.         }  
  39.   
  40.         // Get Employee By Id  
  41.         public Employee Get(int id)  
  42.         {  
  43.             using (dbEntities db = new dbEntities())  
  44.             {  
  45.                 Employee employee = db.Employees.Find(id);  
  46.                 if (employee == null)  
  47.                 {  
  48.                     throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));  
  49.                 }  
  50.                 return employee;  
  51.             }  
  52.         }  
  53.   
  54.         // Insert Employee  
  55.         public HttpResponseMessage Post(Employee employee)  
  56.         {  
  57.             if (ModelState.IsValid)  
  58.             {  
  59.                 using (dbEntities db = new dbEntities())  
  60.                 {  
  61.                     var emplist = db.sp_InsUpdDelEmployee(0, employee.Name, employee.Address, employee.Country, employee.City, employee.Mobile, "Ins").ToList();  
  62.                     HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, emplist);  
  63.                     return response;  
  64.                 }  
  65.             }  
  66.             else  
  67.             {  
  68.                 return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);  
  69.             }  
  70.         }  
  71.   
  72.         // Update Employee  
  73.         public HttpResponseMessage Put(Employee employee)  
  74.         {  
  75.             List emplist = new List();  
  76.             if (!ModelState.IsValid)  
  77.             {  
  78.                 return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);  
  79.             }  
  80.             using (dbEntities db = new dbEntities())  
  81.             {  
  82.                 try  
  83.                 {  
  84.                     emplist = db.sp_InsUpdDelEmployee(employee.Id, employee.Name, employee.Address, employee.Country, employee.City, employee.Mobile, "Upd").ToList();  
  85.                 }  
  86.                 catch (DbUpdateConcurrencyException ex)  
  87.                 {  
  88.                     return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);  
  89.                 }  
  90.             }  
  91.             return Request.CreateResponse(HttpStatusCode.OK, emplist);  
  92.         }  
  93.   
  94.         // Delete employee By Id  
  95.         public HttpResponseMessage Delete(int id)  
  96.         {  
  97.             using (dbEntities db = new dbEntities())  
  98.             {  
  99.                 List emplist = new List();  
  100.                 var results = db.sp_InsUpdDelEmployee(id, "", "", "", "", "", "GetById").ToList();  
  101.                 if (results.Count == 0)  
  102.                 {  
  103.                     return Request.CreateResponse(HttpStatusCode.NotFound);  
  104.                 }  
  105.                 try  
  106.                 {  
  107.                     emplist = db.sp_InsUpdDelEmployee(id, "", "", "", "", "", "Del").ToList();  
  108.                 }  
  109.                 catch (DbUpdateConcurrencyException ex)  
  110.                 {  
  111.                     return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);  
  112.                 }  
  113.                 return Request.CreateResponse(HttpStatusCode.OK, emplist);  
  114.             }  
  115.         }  
  116.   
  117.         // Prevent Memory Leak  
  118.         protected override void Dispose(bool disposing)  
  119.         {  
  120.             using (dbEntities db = new dbEntities())  
  121.                 db.Dispose();  
  122.             base.Dispose(disposing);  
  123.         }  
  124.     }  
  125. }  

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 the beginners.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Codingvila is an educational website, developed to help tech specialists/beginners.
    Membership not found