ASP.NET

CRUD - MVC 5 and Web API 2 With AngularJS

This article shows how to do Create, Read, Update and Delete (CRUD) operations in MVC 5, WEB API 2 and AngularJS with a Stored Procedure.

The following is the table I will use to do the CRUD operations:

design table
Image 1.

The following is sample data for the table:

Data in my Table
Image 2.

The following are the four Stored Procedures.

1. ADDNewEmployee

  1. CREATE PROCEDURE [dbo].[ADDNewEmployee]                                                
  2. (                                
  3.      @Emp_Name     VARCHAR(100) = null,    
  4.      @Email        VARCHAR(500) = null,  
  5.      @Country      VARCHAR(100) = null ,  
  6.      @ProjectID    INT,  
  7.      @ManagerName  VARCHAR(100) = null,  
  8.      @ProfileImage VARCHAR(100) = null   
  9. )                                                            
  10. AS      
  11.                                                                 
  12. BEGIN        
  13.     INSERT INTO Employee (Name, Email,Country,ProjectID,ManagerName,ProfileImage)  
  14.     VALUES (@Emp_Name, @Email, @Country,@ProjectID,@ManagerName, @ProfileImage)  
  15. END    
ADDNewEmployee
Image 3.

2. UpdateEmployee
  1. CREATE PROCEDURE [dbo].[UpdateEmployee]                                                
  2. (       
  3.      @Emp_ID INT,                           
  4.      @Emp_Name     VARCHAR(100) = null,    
  5.      @Email        VARCHAR(500) = null,  
  6.      @Country      VARCHAR(100) = null ,       
  7.      @ManagerName  VARCHAR(100) = null       
  8. )                                                            
  9. AS        
  10.                                                               
  11. BEGIN        
  12.     UPDATE Employee   
  13.     SET Name=@Emp_Name,   
  14.         Email=@Email,  
  15.         Country=@Country,        
  16.         ManagerName=@ManagerName          
  17.         WHERE ID=@Emp_ID   
  18. END    
UpdateEmployee
Image 4.

3. DeleteEmployee.
  1. ALTER PROCEDURE [dbo].[DeleteEmployee]   
  2. (   
  3.    @Emp_ID INT   
  4. )   
  5. AS   
  6. BEGIN   
  7. DELETE FROM Employee WHERE ID=@Emp_ID   
  8. END   
DeleteEmployee
Image 5.

4. ReadAllEmployee
  1. -- EXEC [ReadAllEmployee] null, null, null  
  2. ALTER PROCEDURE [dbo].[ReadAllEmployee]                                                
  3. (                                
  4.     @Emp_Name  VARCHAR(100) = null,    
  5.     @Country   VARCHAR(100) = null,  
  6.     @ManagerName VARCHAR(100)   = null     
  7.       )                                                            
  8. AS                                                                    
  9. BEGIN   
  10.     IF  (@Emp_Name IS NULL)  
  11.     BEGIN      
  12.         SET @Emp_Name=''    
  13.     END  
  14.     IF  (@Country IS NULL)  
  15.     BEGIN    
  16.         SET @Country=''    
  17.     END  
  18.     IF  (@ManagerName IS NULL)  
  19.     BEGIN    
  20.         SET @ManagerName=''    
  21.     END  
  22.          
  23.          SELECT ID, Name, Email, Country, ManagerName    
  24.          FROM  Employee     
  25.          WHERE Name like  @Emp_Name +'%'    
  26.                AND Country like @Country +'%'    
  27.                 AND ManagerName like @ManagerName +'%'    
  28.                ORDER BY Name     
  29. END    
query
Image 6.

Now open Visual Studio 2015 and create new ASP.NET web application.

Create New Application
Image 7.

mvc
Image 8.

Now right-click on the project in the Solution Explorer as in the following:

Solution Explorer
Image 9.

Select ADO.NET Entity Data Model.

Data Model
Image 10.

Entity Data Model
Image 11.

Select your Data Source and database.

Select your Data Sourc
Image 12.

connection
Image 13.

Select your data table and Stored Procedure.

Select your Data table
Image 14.

Stored Procedure
Image 15.

Now to add a Web API controller. So right-click on Controllers and Add New Controller.

add Web API controller
Image 16.

Select Web API 2 Controller – Empty.

Web API
Image 17.

Provide it a name.

controller name
Image 18.

Here's the code:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Net;  
  5. using System.Net.Http;  
  6. using System.Web.Http;  
  7. using CRUD_MVC5_WebAPI_SP.Models;  
  8.   
  9. namespace CRUD_MVC5_WebAPI_SP.Controllers  
  10. {  
  11.     public class ManageEmployeeController : ApiController  
  12.     {  
  13.         TestDBEntities obj = new TestDBEntities();  
  14.   
  15.         [HttpGet]  
  16.         public IEnumerable<ReadAllEmployee_Result> ListAllEmployee(string emp_Name, string country, string managerName)  
  17.         {  
  18.             return obj.ReadAllEmployee(emp_Name, country, managerName).AsEnumerable();  
  19.         }  
  20.   
  21.         [HttpGet]  
  22.         public void addNewEmployee(string emp_Name, string email, string country, string managerName)  
  23.         {  
  24.             obj.ADDNewEmployee(emp_Name, email, country, 1, managerName, "Profile.jpg");  
  25.         }  
  26.   
  27.         [HttpGet]  
  28.         public void updateEmployee(int? emp_ID, string emp_Name, string email, string country, string managerName)  
  29.         {  
  30.             obj.UpdateEmployee(emp_ID, emp_Name, email, country, managerName);  
  31.         }  
  32.   
  33.         [HttpGet]  
  34.         public void deleteEmployee(int emp_ID)  
  35.         {  
  36.             obj.DeleteEmployee(emp_ID);  
  37.             obj.SaveChanges();  
  38.         }  
  39.     }  
  40. }  
code
Image 19.

Now to add the AngularJs reference to your project. So right-click on your project in the Solution Explorer as in the following, then click Manage NuGet Packages...

manage nuget package
Image 20.

install
Image 21.

click uninstall
Image 22.

Now create a new folder under the Scripts Folder, then right-click MyScripts and select AngularController as in the following:

Select AngularController
Image 23.

Here's the code:
  1. // <reference path="../angular.js" />    
  2. /// <reference path="../angular.min.js" />     
  3. /// <reference path="../angular-animate.js" />     
  4. /// <reference path="../angular-animate.min.js" />     
  5.   
  6. var app;  
  7. (function () {  
  8.     app = angular.module("RESTClientModule", ['ngAnimate']);  
  9. })();  
  10.   
  11. app.controller("AngularJs_ManageEmployeeInfoController", function ($scope, $timeout, $rootScope, $window, $http) {  
  12.     $scope.date = new Date();  
  13.     $scope.empNameSearch = "";  
  14.     $scope.empCountrySearch = "";  
  15.     $scope.ManagerNameSearch = "";  
  16.   
  17.     $scope.ManageEMP_Info = false;  
  18.     $scope.ListEmployee = true;  
  19.     $scope.searchEmployee = true;  
  20.   
  21.        
  22.     $scope.EmpIds = 0;  
  23.     $scope.empName = "";  
  24.     $scope.empEmail = "";  
  25.     $scope.empCountry = "";  
  26.     $scope.empManager = "";  
  27.   
  28.     getEmployeeList($scope.empNameSearch, $scope.empCountrySearch, $scope.ManagerNameSearch);  
  29.   
  30.     function getEmployeeList(EmployeeName, Country, ManagerName) {  
  31.         $http.get('/api/ManageEmployee/', { params: { emp_Name: EmployeeName, country: Country, managerName: ManagerName } }).success(function (data) {  
  32.             $scope.Employees = data;  
  33.             $scope.ManageEMP_Info = false;  
  34.             $scope.ListEmployee = true;  
  35.             $scope.searchEmployee = true;  
  36.             if ($scope.Employees.length > 0) {  
  37.             }  
  38.         })  
  39.    .error(function () {  
  40.        $scope.error = "Some Error.";  
  41.    });  
  42.     }  
  43.   
  44.     //Search  
  45.     $scope.searchEmp = function () {  
  46.   
  47.         getEmployeeList($scope.empNameSearch, $scope.empCountrySearch, $scope.ManagerNameSearch);  
  48.     }  
  49.   
  50.     //Edit Employee Details  
  51.     $scope.editEmployeeInfo = function editEmployeeInfo(Employee_ID, Name, Email, Country, ManagerName) {  
  52.         cleardetails();  
  53.         $scope.EmpIds = Employee_ID;  
  54.         $scope.empName = Name  
  55.         $scope.empEmail = Email;  
  56.         $scope.empCountry = Country;  
  57.         $scope.empManager = ManagerName;  
  58.   
  59.         $scope.ManageEMP_Info = true;  
  60.         $scope.ListEmployee = true;  
  61.         $scope.searchEmployee = true;  
  62.     }  
  63.   
  64.     //Delete Dtudent Detail  
  65.     $scope.Employee_Delete = function Employee_Delete(EmpIds, Name) {  
  66.         cleardetails();  
  67.         $scope.EmpIds = EmpIds;  
  68.         var delConfirm = confirm("Are you sure you want to delete the Employee " + Name + " ?");  
  69.         if (delConfirm == true) {  
  70.   
  71.             $http.get('/api/ManageEmployee/deleteEmployee/', { params: { emp_ID: $scope.EmpIds } }).success(function (data) {  
  72.                 alert("Employee Deleted Successfully!!");  
  73.                 cleardetails();  
  74.                 $scope.empNameSearch = "";  
  75.                 $scope.empCountrySearch = "";  
  76.                 $scope.ManagerNameSearch = "";  
  77.                 getEmployeeList($scope.empNameSearch, $scope.empCountrySearch, $scope.ManagerNameSearch);  
  78.             })  
  79.       .error(function () {  
  80.           $scope.error = "Some Error.";  
  81.       });  
  82.   
  83.         }  
  84.     }  
  85.   
  86.     $scope.AddEmployeeForm = function () {  
  87.         cleardetails();  
  88.         $scope.ManageEMP_Info = true;  
  89.         $scope.ListEmployee = true;  
  90.         $scope.searchEmployee = true;  
  91.     }  
  92.   
  93.     function cleardetails() {  
  94.         $scope.EmpIds = 0;  
  95.         $scope.empName = "";  
  96.         $scope.empEmail = "";  
  97.         $scope.empCountry = "";  
  98.         $scope.empManager = "";  
  99.     }  
  100.   
  101.     //Form Validation  
  102.     $scope.Message = "";  
  103.     $scope.IsFormSubmitted = false;  
  104.     $scope.IsFormValid = false;  
  105.     $scope.$watch("f1.$valid", function (isValid) {  
  106.         $scope.IsFormValid = isValid;  
  107.     });  
  108.   
  109.     //Save-Edit Employee  
  110.     $scope.saveDetails = function () {  
  111.   
  112.         $scope.IsFormSubmitted = true;  
  113.         if ($scope.IsFormValid) {  
  114.             if ($scope.EmpIds == 0) {  
  115.                 $http.get('/api/ManageEmployee/addNewEmployee/', { params: { emp_Name: $scope.empName, email: $scope.empEmail, country: $scope.empCountry, managerName: $scope.empManager } }).success(function (data) {  
  116.                     alert("Employee Added Successfully.");  
  117.                     cleardetails();  
  118.                     $scope.empNameSearch = "";  
  119.                     $scope.empCountrySearch = "";  
  120.                     $scope.ManagerNameSearch = "";  
  121.                     getEmployeeList($scope.empNameSearch, $scope.empCountrySearch, $scope.ManagerNameSearch);  
  122.   
  123.                 })  
  124.          .error(function () {  
  125.              $scope.error = "Some Error.";  
  126.          });  
  127.             }  
  128.             else {  
  129.                 $http.get('/api/ManageEmployee/updateEmployee/', { params: { emp_ID: $scope.EmpIds, emp_Name: $scope.empName, email: $scope.empEmail, country: $scope.empCountry, managerName: $scope.empManager } }).success(function (data) {  
  130.   
  131.                     alert("Employee Updated Successfully");  
  132.                     cleardetails();  
  133.                     $scope.empNameSearch = "";  
  134.                     $scope.empCountrySearch = "";  
  135.                     $scope.ManagerNameSearch = "";  
  136.                     getEmployeeList($scope.empNameSearch, $scope.empCountrySearch, $scope.ManagerNameSearch);  
  137.   
  138.                 })  
  139.         .error(function () {  
  140.             $scope.error = "Some Error.";  
  141.         });  
  142.             }  
  143.   
  144.         }  
  145.         else {  
  146.             $scope.Message = "All the fields are required.";  
  147.         }  
  148.     }  
  149. });  
Now for adding a new controller right-click on the Controller, select Add and click Controller as in the following:

add controller
Image 24.

controller
Image 25.

type controller name
Image 26.

add view in project
Image 27.

add view
Image 28.

Here's the code in Index.cshtml:
  1. <html data-ng-app="RESTClientModule">  
  2. @{  
  3.     ViewBag.Title = "Manage Employee Information Uisng MVC 5 , WebAPI2, AngularJS with Stored Procedure";  
  4. }  
  5. <body data-ng-controller="AngularJs_ManageEmployeeInfoController">  
  6.     <table style='width: 99%; background-color:skyblue; text-align:center;'>  
  7.         <tr ng-show="searchEmployee">  
  8.             <td>  
  9.                 <table style=" background-color:crimson; border: solid 4px green; padding: 5px;width: 99%; color:white;" cellpadding="2" cellspacing="2">  
  10.                     <tr>  
  11.                         <td>  
  12.                             <b>Name</b>  
  13.                             : <input type="text" name="txtEmpName" ng-model="empNameSearch" value="" />  
  14.                         </td>  
  15.                         <td>  
  16.                             <b>   Country </b> :  
  17.                             <input type="text" name="txtCountry" ng-model="empCountrySearch" />  
  18.                         </td>  
  19.                         <td>  
  20.                             <b>   Manager Name </b> :  
  21.                             <input type="text" name="txtManager" ng-model="ManagerNameSearch" />  
  22.                         </td>  
  23.                         <td>  
  24.                             <input type="submit" value="Search Employee" ng-click="searchEmp()" />  
  25.                         </td>  
  26.                         <td align="right">  
  27.                             <input type="button" value="Add New Employee" ng-click="AddEmployeeForm()" />  
  28.                         </td>  
  29.                     </tr>  
  30.                 </table>  
  31.             </td>  
  32.         </tr>  
  33.         <tr ng-show="ManageEMP_Info">  
  34.             <td>  
  35.                 <table style="border:solid 4px blue; background-color:orange; width:100%;">  
  36.                     <tr>  
  37.                         <td width="20"></td>  
  38.                         <td valign="top">  
  39.                             <form novalidate name="f1" ng-submit="saveDetails()">  
  40.                                 <table style="color:#0d3d2b; font-weight:bold; font-size:12pt; text-align:right;">  
  41.                                     <tr>  
  42.                                         <td>  
  43.                                             Employee ID :  
  44.                                         </td>  
  45.                                         <td>  
  46.                                             <input type="text" name="txtEmpId" ng-model="EmpIds" value="0" style="background-color:tan" readonly />  
  47.                                         </td>  
  48.                                         <td>  
  49.                                             Name :  
  50.                                         </td>  
  51.                                         <td>  
  52.                                             <input type="text" name="txtEmpName" ng-model="empName" placeholder=" Employee Name..." required />  
  53.                                             <br />  
  54.                                             <span class="error" ng-show="(f1.file.$dirty || IsFormSubmitted) && f1.txtEmpName.$error.required">Name required!</span>  
  55.                                         </td>  
  56.                                     </tr>  
  57.                                     <tr>  
  58.                                         <td>  
  59.                                             Email :  
  60.                                         </td>  
  61.                                         <td>  
  62.                                             <input type="text" name="txtEmail" ng-model="empEmail" placeholder=" Email..." required />  
  63.                                             <br />  
  64.                                             <span class="error" ng-show="(f1.file.$dirty || IsFormSubmitted) && f1.txtEmail.$error.required">Email required!</span>  
  65.                                         </td>  
  66.                                         <td>  
  67.                                             Country :  
  68.                                         </td>  
  69.                                         <td>  
  70.                                             <input type="text" name="txtCountry" ng-model="empCountry" placeholder=" Country ..." required />  
  71.                                             <br />  
  72.                                             <span class="error" ng-show="(f1.file.$dirty || IsFormSubmitted) && f1.txtCountry.$error.required">Country required!</span>  
  73.                                         </td>  
  74.                                     </tr>  
  75.                                     <tr>  
  76.                                         <td>  
  77.                                             Manager Name:  
  78.                                         </td>  
  79.                                         <td>  
  80.                                             <input type="text" name="txtManager" ng-model="empManager" placeholder=" Manager ..." required />  
  81.                                             <br />  
  82.                                             <span class="error" ng-show="(f1.file.$dirty || IsFormSubmitted) && f1.txtManager.$error.required">Manager Name required!</span>  
  83.                                         </td>  
  84.                                         <td colspan="2">  
  85.                                             <input type="submit" value="Save Employee" style="background-color:#336699;color:#FFFFFF" required />  
  86.                                         </td>  
  87.                                     </tr>  
  88.                                 </table>  
  89.                             </form>  
  90.                         </td>  
  91.                     </tr>  
  92.                 </table>  
  93.             </td>  
  94.         </tr>  
  95.         <tr ng-show="ListEmployee">  
  96.             <td>  
  97.                 <table style="width:100%;">  
  98.                     <tr style="background-color:darkgreen; color:white; font-weight:bold;">  
  99.                         <td width="120px" align="center">Name</td>  
  100.                         <td width="120" align="center">Email</td>  
  101.                         <td width="80" align="center">Country</td>  
  102.                         <td width="200" align="center">Manager Name</td>  
  103.                         <td width="40" align="center">Edit</td>  
  104.                         <td width="40" align="center">Delete</td>  
  105.                     </tr>  
  106.                     <tbody data-ng-repeat="emp in Employees">  
  107.                         <tr style="background-color:whitesmoke; font-family:Cambria, Cochin, Georgia, Times, Times New Roman, serif; font-size:11pt; color:midnightblue; text-align:left; padding-left:10px;">  
  108.                             <td style="width:120px;">  
  109.                                 {{emp.Name}}  
  110.                             </td>  
  111.   
  112.                             <td>  
  113.                                 {{emp.Email}}  
  114.                             </td>  
  115.   
  116.                             <td>  
  117.                                 {{emp.Country}}  
  118.                             </td>  
  119.   
  120.                             <td>  
  121.                                 {{emp.ManagerName}}  
  122.                             </td>  
  123.                             <td align="center">  
  124.                                 <span style="color:#9F000F;">  
  125.                                     <input type="submit" value="Edit" ng-click="editEmployeeInfo(emp.ID,emp.Name,emp.Email,emp.Country,emp.ManagerName)" />  
  126.                                 </span>  
  127.                             </td>  
  128.   
  129.                             <td align="center">  
  130.                                 <span style="color:#9F000F;">  
  131.                                     <input type="submit" value="Delete" ng-click="Employee_Delete(emp.ID,emp.Name)" />  
  132.                                 </span>  
  133.                             </td>  
  134.   
  135.                         </tr>  
  136.                     </tbody>  
  137.                 </table>  
  138.   
  139.             </td>  
  140.         </tr>  
  141.     </table>  
  142. </body>  
  143. </html>  
  144.   
  145. <script src="~/Scripts/angular-animate.js"></script>  
  146. <script src="~/Scripts/angular.js"></script>  
  147. <script src="~/Scripts/angular.min.js"></script>  
  148. <script src="~/Scripts/angular-animate.min.js"></script>  
  149. <script src="~/Scripts/MyScripts/controller.js"></script>  
To make your view as the default run you need to make a change in the rout.config like the following:

view
Image 29.

Now run your application:

Now run your Application
Image 30.

Now provide some search criteria as in the following:

put some search criteria
Image 31.

select country
Image 32.

manage employee information
Image 33.

result
Image 34.

Now click on Add New Employee.

Country
Image 35.

save employee
Image 36.

message
Image 37.

table
Image 38.

Now edit a record:

Edit Any Record
Image 39.

index
Image 40.

Record
Image 41.

Now delete a record:

Delete Any Record
Image 42.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs