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
  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
  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
  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;
  9. namespace CRUD_MVC5_WebAPI_SP.Controllers
  10. {
  11. public class ManageEmployeeController : ApiController
  12. {
  13. TestDBEntities obj = new TestDBEntities();
  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. }
  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. }
  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. }
  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" />
  6. var app;
  7. (function () {
  8. app = angular.module("RESTClientModule", ['ngAnimate']);
  9. })();
  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 = "";
  17. $scope.ManageEMP_Info = false;
  18. $scope.ListEmployee = true;
  19. $scope.searchEmployee = true;
  22. $scope.EmpIds = 0;
  23. $scope.empName = "";
  24. $scope.empEmail = "";
  25. $scope.empCountry = "";
  26. $scope.empManager = "";
  28. getEmployeeList($scope.empNameSearch, $scope.empCountrySearch, $scope.ManagerNameSearch);
  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. }
  44. //Search
  45. $scope.searchEmp = function () {
  47. getEmployeeList($scope.empNameSearch, $scope.empCountrySearch, $scope.ManagerNameSearch);
  48. }
  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;
  59. $scope.ManageEMP_Info = true;
  60. $scope.ListEmployee = true;
  61. $scope.searchEmployee = true;
  62. }
  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) {
  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. });
  83. }
  84. }
  86. $scope.AddEmployeeForm = function () {
  87. cleardetails();
  88. $scope.ManageEMP_Info = true;
  89. $scope.ListEmployee = true;
  90. $scope.searchEmployee = true;
  91. }
  93. function cleardetails() {
  94. $scope.EmpIds = 0;
  95. $scope.empName = "";
  96. $scope.empEmail = "";
  97. $scope.empCountry = "";
  98. $scope.empManager = "";
  99. }
  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. });
  109. //Save-Edit Employee
  110. $scope.saveDetails = function () {
  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);
  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) {
  131. alert("Employee Updated Successfully");
  132. cleardetails();
  133. $scope.empNameSearch = "";
  134. $scope.empCountrySearch = "";
  135. $scope.ManagerNameSearch = "";
  136. getEmployeeList($scope.empNameSearch, $scope.empCountrySearch, $scope.ManagerNameSearch);
  138. })
  139. .error(function () {
  140. $scope.error = "Some Error.";
  141. });
  142. }
  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>
  112. <td>
  113. {{emp.Email}}
  114. </td>
  116. <td>
  117. {{emp.Country}}
  118. </td>
  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>
  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>
  135. </tr>
  136. </tbody>
  137. </table>
  139. </td>
  140. </tr>
  141. </table>
  142. </body>
  143. </html>
  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.