In this article I am going to show how we can perform
create, read, update and delete operation in MVC 5, WEB API 2, AngularJS with
Stored Procedure.
Below is the table from which I am going to perform
CRUD.
Image 1.
Data in my Table:
Image 2.
Below are the 4 Stored Procedure :
1.
ADDNewEmployee
CREATE PROCEDURE [dbo].[ADDNewEmployee]
(
@Emp_Name VARCHAR(100) = null,
@Email VARCHAR(500) = null,
@Country VARCHAR(100) = null ,
@ProjectID INT,
@ManagerName VARCHAR(100) = null,
@ProfileImage VARCHAR(100) = null
)
AS
BEGIN
INSERT INTO Employee (Name, Email,Country,ProjectID,ManagerName,ProfileImage)
VALUES (@Emp_Name, @Email, @Country,@ProjectID,@ManagerName, @ProfileImage)
END
Image 3.
2. UpdateEmployee
CREATE PROCEDURE [dbo].[UpdateEmployee]
(
@Emp_ID INT,
@Emp_Name VARCHAR(100) = null,
@Email VARCHAR(500) = null,
@Country VARCHAR(100) = null ,
@ManagerName VARCHAR(100) = null
)
AS
BEGIN
UPDATE Employee
SET Name=@Emp_Name,
Email=@Email,
Country=@Country,
ManagerName=@ManagerName
WHERE ID=@Emp_ID
END
Image 4.
3. DeleteEmployee
ALTER PROCEDURE [dbo].[DeleteEmployee]
(
@Emp_ID INT
)
AS
BEGIN
DELETE FROM Employee WHERE ID=@Emp_ID
END
Image 5.
4.ReadAllEmployee
-- EXEC [ReadAllEmployee] null, null, null
ALTER PROCEDURE [dbo].[ReadAllEmployee]
(
@Emp_Name VARCHAR(100) = null,
@Country VARCHAR(100) = null,
@ManagerName VARCHAR(100) = null
)
AS
BEGIN
IF (@Emp_Name IS NULL)
BEGIN
SET @Emp_Name=''
END
IF (@Country IS NULL)
BEGIN
SET @Country=''
END
IF (@ManagerName IS NULL)
BEGIN
SET @ManagerName=''
END
SELECT ID, Name, Email, Country, ManagerName
FROM
Employee
WHERE Name like @Emp_Name +'%'
AND Country like
@Country +'%'
AND ManagerName like
@ManagerName +'%'
ORDER BY Name
END
Image 6.
Now Open Visual Studio 2015 -> Create New
Application.
Image 7.
Image 8.
Now Right Click on Project's Solution Explorer ->
Image 9.
Select ADO.NET Entity Data Model.
Image 10.
Image 11.
Select your Data Source & Data Base.
Image 12.
Image 13.
Select your Data table and Stored Procedure.
Image 14.
Image 15.
Now Time to add Web API controller. So right click on
Controllers -> Add New -> Controller
Image 16.
Select Web API 2 Controller - Empty.
Image 17.
Give it a Name.
Image 18.
Here
do below code:
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using
CRUD_MVC5_WebAPI_SP.Models;
namespace
CRUD_MVC5_WebAPI_SP.Controllers
{
public class ManageEmployeeController : ApiController
{
TestDBEntities obj = new TestDBEntities();
[HttpGet]
public IEnumerable<ReadAllEmployee_Result>
ListAllEmployee(string emp_Name, string country, string managerName)
{
return obj.ReadAllEmployee(emp_Name, country,
managerName).AsEnumerable();
}
[HttpGet]
public void addNewEmployee(string emp_Name, string email, string country, string managerName)
{
obj.ADDNewEmployee(emp_Name, email,
country, 1, managerName, "Profile.jpg");
}
[HttpGet]
public void updateEmployee(int? emp_ID, string emp_Name, string email, string country, string managerName)
{
obj.UpdateEmployee(emp_ID,
emp_Name, email, country, managerName);
}
[HttpGet]
public void deleteEmployee(int emp_ID)
{
obj.DeleteEmployee(emp_ID);
obj.SaveChanges();
}
}
}
Image 19.
Now time to add AngularJS reference in your project. So
right click on your Project's Solution explorer-> Manage NuGet
Image 20.
Image 21.
Image 22.
Now create a new folder under Script Folder ->
MyScripts -> Right click here Select AngularController:
Image 23.
Here
do the below code:
// <reference
path="../angular.js" />
/// <reference
path="../angular.min.js" />
/// <reference
path="../angular-animate.js" />
/// <reference
path="../angular-animate.min.js" />
var app;
(function () {
app = angular.module("RESTClientModule", ['ngAnimate']);
})();
app.controller("AngularJs_ManageEmployeeInfoController", function ($scope, $timeout,
$rootScope, $window, $http) {
$scope.date = new Date();
$scope.empNameSearch = "";
$scope.empCountrySearch = "";
$scope.ManagerNameSearch = "";
$scope.ManageEMP_Info = false;
$scope.ListEmployee = true;
$scope.searchEmployee = true;
$scope.EmpIds = 0;
$scope.empName = "";
$scope.empEmail = "";
$scope.empCountry = "";
$scope.empManager = "";
getEmployeeList($scope.empNameSearch,
$scope.empCountrySearch, $scope.ManagerNameSearch);
function getEmployeeList(EmployeeName,
Country, ManagerName) {
$http.get('/api/ManageEmployee/', { params: {
emp_Name: EmployeeName, country: Country, managerName: ManagerName }
}).success(function (data)
{
$scope.Employees = data;
$scope.ManageEMP_Info = false;
$scope.ListEmployee = true;
$scope.searchEmployee = true;
if ($scope.Employees.length > 0) {
}
})
.error(function () {
$scope.error = "Some
Error.";
});
}
//Search
$scope.searchEmp = function () {
getEmployeeList($scope.empNameSearch,
$scope.empCountrySearch, $scope.ManagerNameSearch);
}
//Edit Employee Details
$scope.editEmployeeInfo = function
editEmployeeInfo(Employee_ID, Name, Email, Country, ManagerName) {
cleardetails();
$scope.EmpIds = Employee_ID;
$scope.empName = Name;
$scope.empEmail = Email;
$scope.empCountry = Country;
$scope.empManager = ManagerName;
$scope.ManageEMP_Info = true;
$scope.ListEmployee = true;
$scope.searchEmployee = true;
}
//Delete
$scope.Employee_Delete = function
Employee_Delete(EmpIds, Name) {
cleardetails();
$scope.EmpIds = EmpIds;
var delConfirm = confirm("Are you sure
you want to delete the Employee " + Name + " ?");
if (delConfirm == true) {
$http.get('/api/ManageEmployee/deleteEmployee/', { params: {
emp_ID: $scope.EmpIds } }).success(function (data) {
alert("Employee
Deleted Successfully!!");
cleardetails();
$scope.empNameSearch = "";
$scope.empCountrySearch = "";
$scope.ManagerNameSearch = "";
getEmployeeList($scope.empNameSearch,
$scope.empCountrySearch, $scope.ManagerNameSearch);
})
.error(function () {
$scope.error = "Some
Error.";
});
}
}
$scope.AddEmployeeForm = function () {
cleardetails();
$scope.ManageEMP_Info = true;
$scope.ListEmployee = true;
$scope.searchEmployee = true;
}
function cleardetails() {
$scope.EmpIds = 0;
$scope.empName = "";
$scope.empEmail = "";
$scope.empCountry = "";
$scope.empManager = "";
}
//Form Validation
$scope.Message = "";
$scope.IsFormSubmitted = false;
$scope.IsFormValid = false;
$scope.$watch("f1.$valid", function (isValid) {
$scope.IsFormValid = isValid;
});
//Save-Edit Employee
$scope.saveDetails = function () {
$scope.IsFormSubmitted = true;
if ($scope.IsFormValid) {
if ($scope.EmpIds == 0) {
$http.get('/api/ManageEmployee/addNewEmployee/', { params: {
emp_Name: $scope.empName, email: $scope.empEmail,
country: $scope.empCountry,
managerName: $scope.empManager } }).success(function (data) {
alert("Employee
Added Successfully.");
cleardetails();
$scope.empNameSearch = "";
$scope.empCountrySearch = "";
$scope.ManagerNameSearch = "";
getEmployeeList($scope.empNameSearch, $scope.empCountrySearch,
$scope.ManagerNameSearch);
})
.error(function () {
$scope.error = "Some
Error.";
});
}
else {
$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) {
alert("Employee
Updated Successfully");
cleardetails();
$scope.empNameSearch = "";
$scope.empCountrySearch = "";
$scope.ManagerNameSearch = "";
getEmployeeList($scope.empNameSearch, $scope.empCountrySearch,
$scope.ManagerNameSearch);
})
.error(function () {
$scope.error = "Some
Error.";
});
}
}
else {
$scope.Message = "All the
fields are required.";
}
}
});
Now Add a New
Controller. So Right click on Controller -> Add New-> Controller
Image 24.
Image 25.
Image 26.
Image 27.
Image 28.
Now
do the below Code in Index.cshtml:
<html data-ng-app="RESTClientModule">
@{
ViewBag.Title = "Manage
Employee Information Uisng MVC 5 , WebAPI2, AngularJS with Stored
Procedure";
}
<body data-ng-controller="AngularJs_ManageEmployeeInfoController">
<table style='width: 99%; background-color:skyblue; text-align:center;'>
<tr ng-show="searchEmployee">
<td>
<table style=" background-color:crimson; border: solid 4px green; padding: 5px;width: 99%; color:white;" cellpadding="2" cellspacing="2">
<tr>
<td>
<b>Name</b>
: <input type="text" name="txtEmpName" ng-model="empNameSearch" value="" />
</td>
<td>
<b> Country </b> :
<input type="text" name="txtCountry" ng-model="empCountrySearch" />
</td>
<td>
<b> Manager Name </b> :
<input type="text" name="txtManager" ng-model="ManagerNameSearch" />
</td>
<td>
<input type="submit" value="Search
Employee"
ng-click="searchEmp()" />
</td>
<td align="right">
<input type="button" value="Add New
Employee"
ng-click="AddEmployeeForm()" />
</td>
</tr>
</table>
</td>
</tr>
<tr ng-show="ManageEMP_Info">
<td>
<table style="border:solid 4px blue; background-color:orange; width:100%;">
<tr>
<td width="20"></td>
<td valign="top">
<form novalidate name="f1" ng-submit="saveDetails()">
<table style="color:#0d3d2b; font-weight:bold; font-size:12pt; text-align:right;">
<tr>
<td>
Employee ID :
</td>
<td>
<input type="text" name="txtEmpId" ng-model="EmpIds" value="0" style="background-color:tan" readonly />
</td>
<td>
Name :
</td>
<td>
<input type="text" name="txtEmpName" ng-model="empName" placeholder=" Employee
Name..."
required />
<br />
<span class="error" ng-show="(f1.file.$dirty
|| IsFormSubmitted) && f1.txtEmpName.$error.required">
Name required!</span>
</td>
</tr>
<tr>
<td>
Email :
</td>
<td>
<input type="text" name="txtEmail" ng-model="empEmail" placeholder="
Email..."
required />
<br />
<span class="error" ng-show="(f1.file.$dirty
|| IsFormSubmitted) && f1.txtEmail.$error.required">
Email required!</span>
</td>
<td>
Country :
</td>
<td>
<input type="text" name="txtCountry" ng-model="empCountry" placeholder=" Country
..."
required />
<br />
<span class="error" ng-show="(f1.file.$dirty
|| IsFormSubmitted) && f1.txtCountry.$error.required">
Country required!</span>
</td>
</tr>
<tr>
<td>
Manager Name:
</td>
<td>
<input type="text" name="txtManager" ng-model="empManager" placeholder=" Manager
..."
required />
<br />
<span class="error" ng-show="(f1.file.$dirty
|| IsFormSubmitted) && f1.txtManager.$error.required">
Manager Name
required!</span>
</td>
<td colspan="2">
<input type="submit" value="Save
Employee"
style="background-color:#336699;color:#FFFFFF" required />
</td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</td>
</tr>
<tr ng-show="ListEmployee">
<td>
<table style="width:100%;">
<tr style="background-color:darkgreen; color:white; font-weight:bold;">
<td width="120px" align="center">Name</td>
<td width="120" align="center">Email</td>
<td width="80" align="center">Country</td>
<td width="200" align="center">Manager Name</td>
<td width="40" align="center">Edit</td>
<td width="40" align="center">Delete</td>
</tr>
<tbody data-ng-repeat="emp in
Employees">
<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;">
<td style="width:120px;">
{{emp.Name}}
</td>
<td>
{{emp.Email}}
</td>
<td>
{{emp.Country}}
</td>
<td>
{{emp.ManagerName}}
</td>
<td align="center">
<span style="color:#9F000F;">
<input type="submit" value="Edit" ng-click="editEmployeeInfo(emp.ID,emp.Name,emp.Email,emp.Country,emp.ManagerName)" />
</span>
</td>
<td align="center">
<span style="color:#9F000F;">
<input type="submit" value="Delete" ng-click="Employee_Delete(emp.ID,emp.Name)" />
</span>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</body>
</html>
<script src="~/Scripts/angular-animate.js"></script>
<script src="~/Scripts/angular.js"></script>
<script src="~/Scripts/angular.min.js"></script>
<script src="~/Scripts/angular-animate.min.js"></script>
<script src="~/Scripts/MyScripts/controller.js"></script>
TO make
your view as default run you have to make change in rout.config like below:
Image 29.
Now
run your Application:
Image 30.
Now put some search criteria:
Image 31.
Image 32.
Image 33.
Image 34.
Now Click on Add
New Employee
Image 35.
Image 36.
Image 37.
Image 38.
Now Edit Any Record:
Image 39.
Image 40.
Image 41.
Now Delete Any Record:
Image 42.