Introduction
In Internet there are lots of examples related to WEB API using Entity Framework. But in community forum I saw many questions was been asked by members for a simple example using WEB API Entity Framework with Stored Procedure. I search a lot to find a simple Article which explains a simple way to perform CRUD operation using Stored Procedure with MVC and Web API. But I couldn’t able to find any article which explains all this .I plan to make a simple web application using MVC 5 ,Angular JS ,WEB API to perform CRUD(Create/Read/Update and Delete) using Entity Framework with Stored procedure.
In this article we will see how to
C -> (Create - Insert New Student Details to database using EF and WEB API with Stored Procedure)
R-> (Read – Search/Select Student Details from database using EF and WEB API with Stored Procedure)
U-> (Update - Update Student Details to database using EF and WEB API with Stored Procedure)
D-> (Delete - Delete Student Details from database using EF and WEB API with Stored Procedure)
Prerequisites:
Visual Studio 2015 - You can download it from here https://www.visualstudio.com/en-us/downloads/visual-studio-2015-downloads-vs.aspx (In my example I have used Visual Studio Community 2015 RC).
You can also view my previous articles related to AngularJs using MVC and the WCF Rest Serice.
previous articles related to AngularJs using MVC and and using Web API 2 http://www.codeproject.com/Articles/996994/Image-Preview-using-MVC-AngularJs-and-Web-API
Angular JS
We might be be familiar with what the Model, View, View Model (MVVM) and what Model, View and Controller (MVC) are. Angular JS is a JavaScript framework that is purely based on HTML, CSS and JavaScript.
The Angular JS Model View Whatever (MVW) pattern is similar to the MVC and MVVM patterns. In our example I have used Model, View and Service. In the code part let's see how to install and create Angular JS in our MVC application.
If you are interested in reading more about Angular JS then kindly go through the following link.
http://www.w3schools.com/angular/default.asp
Using the code
1) Create Database and Table
We will create a StudentMasters table under the Database 'studentDB'. The following is the script to create a database, table and sample insert query. Run this script in your SQL Server. I have used SQL Server 2012.
USE MASTER
GO
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'studentDB' )
DROP DATABASE studentDB
GO
CREATE DATABASE studentDB
GO
USE studentDB
GO
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'StudentMasters' )
DROP TABLE StudentMasters
GO
CREATE TABLE [dbo].[StudentMasters](
[StdID] INT IDENTITY PRIMARY KEY,
[StdName] [varchar](100) NOT NULL,
[Email] [varchar](100) NOT NULL,
[Phone] [varchar](20) NOT NULL,
[Address] [varchar](200) NOT NULL
)
INSERT INTO [StudentMasters] ([StdName],[Email],[Phone],[Address])
VALUES ('Shanu','syedshanumcain@gmail.com','01030550007','Madurai,India')
INSERT INTO [StudentMasters] ([StdName],[Email],[Phone],[Address])
VALUES ('Afraz','Afraz@afrazmail.com','01030550006','Madurai,India')
INSERT INTO [StudentMasters] ([StdName],[Email],[Phone],[Address])
VALUES ('Afreen','Afreen@afreenmail.com','01030550005','Madurai,India')
select * from [StudentMasters]
After creating our Table we will create a Stored procedure to perform our CRUD Operations.
Create PROCEDURE [dbo].[USP_Student_Select]
(
@StdName VARCHAR(100) = '',
@email VARCHAR(100) = ''
)
AS
BEGIN
Select [StdID],
[StdName],
[Email],
[Phone],
[Address]
FROM
StudentMasters
WHERE
StdName like @StdName +'%'
AND Email like @email +'%'
ORDER BY
StdName
END
Create PROCEDURE [dbo].[USP_Student_Insert]
(
@StdName VARCHAR(100) = '',
@email VARCHAR(100) = '',
@Phone VARCHAR(20) = '',
@Address VARCHAR(200) = ''
)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM StudentMasters WHERE StdName=@StdName)
BEGIN
INSERT INTO [StudentMasters]
([StdName],[Email],[Phone],[Address])
VALUES (@StdName,@Email,@Phone,@Address)
Select 'Inserted' as results
END
ELSE
BEGIN
Select 'Exists' as results
END
END
Create PROCEDURE [dbo].[USP_Student_Update]
( @StdID Int=0,
@StdName VARCHAR(100) = '',
@email VARCHAR(100) = '',
@Phone VARCHAR(20) = '',
@Address VARCHAR(200) = ''
)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM StudentMasters WHERE StdID!=@StdID AND StdName=@StdName)
BEGIN
UPDATE StudentMasters
SET [StdName]=@StdName,
[Email]=@email,
[Phone]=@Phone,
[Address]=@Address
WHERE
StdID=@StdID
Select 'updated' as results
END
ELSE
BEGIN
Select 'Exists' as results
END
END
Create PROCEDURE [dbo].[USP_Student_Delete]
( @StdID Int=0 )
AS
BEGIN
DELETE FROM StudentMasters WHERE StdID=@StdID
END
2) Create our MVC Web Application in Visual Studio 2015:
After installing our Visual Studio 2015.Click Start -> Programs-> select Visual Studio 2015- Click Visual Studio 2015 RC.
Click New -> Project - > Select Web -> ASP.NET Web Application. Select your project location and enter your web application Name.
Select MVC and in Add Folders and Core reference for. Select the Web API and click ok.
Now we have created our MVC Application as a next step we add our SQL server database as Entity Data Model to our application.
Add Database using ADO.NET Entity Data Model
Right click our project and click Add -> New Item.
Select Data->Select ADO.NET Entity Data Model> Give the name for our EF and click Add
Select EF Designer from database and click next.
Here click New Connection and provide your SQL-Server Server Name and connect to your database.
Here we can see I have given my SQL server name, Id and PWD and after it connected I have selected the data base as studentDB as we have created the Database using my SQL Script.
Click next and select our tables need to be used and click finish.
Here we can see I have selected our table studentMasters.To use our Stored Procedure select the entire SP which need to be used in our project. Here we can see for performing CRUD operation I have created 4 SP for Select/Insert/Update and Delete. Select the entire SP and click Finish.
Here we can see now I have created our StudentDetailsEntities.
Once Entity has been created next step we add WEB API to our controller and write function to select/Insert/Update and Delete.
Steps to add our WEB API Controller.
Right Click Controllers folder-> Click Add-> Click Controller.
As we are going to create our WEB API Controller. Select Controller and Add Empty WEB API 2 Controller. Give your Name to Web API controller and click ok. Here for my Web API Controller I have given name as “StudentsController”.
As we have created Web API controller, we can see our controller has been inherited ApiController.
As we all know Web API is a simple and easy to build HTTP Services for Browsers and Mobiles
Web API has four methods as Get/Post/Put and Delete where
Get is to request for the data. (Select)
Post is to create a data. (Insert)
Put is to update the data.
Delete is to delete data.
In our example we will use both Get and Post as we need to get all image name and descriptions from database and to insert new Image Name and Image Description to database.
Get Method
In our example I have used only Get method as I am using only Stored Procedure. We need to create object for our Entity and write our Get Method to perform Select/Insert/Update and Delete operations.
Select Operation
We use get Method to get all the details of StudentMasters table using entity object and we return the result as IEnumerable .We use this method in our AngularJS and display the result in MVC page from AngularJs controller using the Ng-Repeat we can see detail step by step as fallows.
Here we can see in get Method I have pass the search parameter to the USP_Student_Select Stored procedure method. In SP I have used the like ‘%’ to return all records if the search parameter is empty.
public class studentsController : ApiController
{
studentDBEntities objapi = new studentDBEntities();
[HttpGet]
public IEnumerable<USP_Student_Select_Result> Get(string StudentName, string StudentEmail)
{
if (StudentName == null)
StudentName = "";
if (StudentEmail == null)
StudentEmail = "";
return objapi.USP_Student_Select(StudentName, StudentEmail).AsEnumerable();
}
Here in my example I have used the get method for all Select/Insert/Update and Delete Operation as in my stored procedure after insert/update and delete I have return the message from database.
Insert Operation
Same like select I have pass all the parameter to insert procedure .This insert method will return the result from database as record Inserted or not. I will get the result and display it from the Angular JS Controller to MVC application.
[HttpGet]
public IEnumerable<string> insertStudent(string StudentName, string StudentEmail, string Phone, string Address)
{
return objapi.USP_Student_Insert(StudentName, StudentEmail, Phone, Address).AsEnumerable();
}
Update Operation
Same like Insert I have pass all the parameter to insert procedure .This Update method will return the result from database as record updated or not. I will pass the Student ID to update procedure to update the record for the Student ID. I will get the result and display it from the Angular JS Controller to MVC application.
[HttpGet]
public IEnumerable<string> updateStudent(int stdID,string StudentName, string StudentEmail, string Phone, string Address)
{
return objapi.USP_Student_Update(stdID,StudentName, StudentEmail, Phone, Address).AsEnumerable();
}
Update Operation
Same like Update I have passed the Student ID to the procedure to delete the record.
[HttpGet]
public string deleteStudent(int stdID)
{
objapi.USP_Student_Delete(stdID);
return "deleted";
}
Now we have created our Web API Controller Class. Next step we need to create our AngularJs Module and Controller. Let’s see how to create our AngularJS Controller. In Visual Studio 2015 it’s much easy to add our AngularJs Controller. Let’s see step by Step on how to create and write our AngularJs Controller.
Creating AngularJs Controller
First create a folder inside the Script Folder and I given the folder name as “MyAngular”
Now add your Angular Controller inside the folder.
Right Click the MyAngular Folder and click Add and New Item. select Web and select AngularJs Controller and give name to Controller. I have given my Angular JS Controller as “Controller.js”
Once the AngularJs Controller is created ,we can see by default the controller will have the code with default module definition and all.
I have change the above code like adding Module and controller like below.
If the Angular JS package is missing then add the package to your project.
Right Click your MVC project and Click-> Manage NuGet Packages. Search for AngularJs and click Install.
Now we can see all Angular JS package has been installed and we can all the files in Script folder.
Steps to Create Angular Js Script Files:
Modules.js : here we add the reference to the Angular.js javascript and create a Angular Module named “RESTClientModule”
var app;
(function () {
app = angular.module("RESTClientModule", ['ngAnimate']);
})();
Controllers: In Angular JS Controller I have performed all the business logic and return the data from WEB API to our MVC html page.
1) Variable declarations:
First I declared all the local Variable which needs to be used .
app.controller("AngularJs_studentsController", function ($scope, $timeout, $rootScope, $window, $http) {
$scope.date = new Date();
$scope.MyName = "shanu";
$scope.stdName = "";
$scope.stdemail = "";
$scope.showStudentAdd = true;
$scope.addEditStudents = false;
$scope.StudentsList=true;
$scope.showItem = true;
$scope.StdIDs = 0;
$scope.stdNames = "";
$scope.stdEmails = "";
$scope.Phones = "";
$scope.Addresss = "";
2) Methods:
Select Method
In select method I have used the $http.get to get the details from WEB API.In get method I will give our API Controller name and method to get the details .Here we can see I have passed the search parameter of StudentName and studentEmail using
{ params: { StudentName: StudentName, StudentEmail: StudentEmail }.The final result will be displayed to the MVC HTML page using the data-ng-repeat
function selectStudentDetails(StudentName, StudentEmail) {
$http.get('/api/students/', { params: { StudentName: StudentName, StudentEmail: StudentEmail } }).success(function (data) {
$scope.Students = data;
$scope.showStudentAdd = true;
$scope.addEditStudents = false;
$scope.StudentsList = true;
$scope.showItem = true;
})
.error(function () {
$scope.error = "An Error has occured while loading posts!";
});
Search Button Click
In search button click I will call the SearchMethod to bind the result.Here we can see in the search Name and Email text box I have used the ng-model="stdName" .Using the ng-model in Angular JS Controller we can get the Textbox input value or we can set the value to the Textbox.
HTML part:
<input type="text" name="txtstudName" ng-model="stdName" value="" />
<input type="text" name="txtemail" ng-model="stdemail" />
<input type="submit" value="Search" style="background-color:#336699;color:#FFFFFF" ng-click="searchStudentDetails()" />
Angular JS Controller code part for search click:
$scope.searchStudentDetails = function () {
selectStudentDetails($scope.stdName, $scope.stdemail);
}
Insert new Student Details:
In ADD New Student Detail button click I will make visible of Add Student table where user can enter the new student information. For new student I will make the Student ID as 0 .In New Student save button click I will call the save method.
$scope.showStudentDetails = function () {
cleardetails();
$scope.showStudentAdd = true;
$scope.addEditStudents = true;
$scope.StudentsList = true;
$scope.showItem = true;
}
In save method I will check for the Student ID. If the student ID is “0” then it is to insert the new student details here I will call the Insert WEB API method and if the Student ID is > 0 means to update the student record then I will call the Update WEB API method.
To Insert WEB API Method I will pass all the Input parameter. In my Stored procedure I will check for the Student Name already exists or not .If the Student name is not exist in database then I will insert the records and return the success message as “inserted” and if the student name is already exists then I will return the message as “Exists”.
$scope.saveDetails = function () {
$scope.IsFormSubmitted = true;
if ($scope.IsFormValid ) {
if ($scope.StdIDs == 0) {
$http.get('/api/students/insertStudent/', { params: { StudentName: $scope.stdNames, StudentEmail: $scope.stdEmails, Phone: $scope.Phones, Address: $scope.Addresss } }).success(function (data) {
$scope.StudentsInserted = data;
alert($scope.StudentsInserted);
cleardetails();
selectStudentDetails('', '');
})
.error(function () {
$scope.error = "An Error has occured while loading posts!";
});
}
else {
$http.get('/api/students/updateStudent/', { params: { stdID: $scope.StdIDs, StudentName: $scope.stdNames, StudentEmail: $scope.stdEmails, Phone: $scope.Phones, Address: $scope.Addresss } }).success(function (data) {
$scope.StudentsUpdated = data;
alert($scope.StudentsUpdated);
cleardetails();
selectStudentDetails('', '');
})
.error(function () {
$scope.error = "An Error has occured while loading posts!";
});
}
}
else {
$scope.Message = "All the fields are required.";
}
}
Update Student Details:
Same like Insert I will display the update details for user to edit the details and save. In Edit method I will get all the details for the Row where user clicks on Edit Icon and set all the result to the appropriate textbox .In save button click I will call the save method to save all the changes to the database same like Insert.
$scope.studentEdit = function studentEdit(StudentID, Name, Email, Phone, Address)
{
cleardetails();
$scope.StdIDs = StudentID;
$scope.stdNames = Name
$scope.stdEmails = Email;
$scope.Phones = Phone;
$scope.Addresss = Address;
$scope.showStudentAdd = true;
$scope.addEditStudents = true;
$scope.StudentsList = true;
$scope.showItem = true;
}
Delete Student Details
In Delete button click I will display the confirmation message to the user as to delete the detail or not. If the user clicks on ok button then I will call the pass the Student ID to the delete method of WEB API to delete the record from the database.
$scope.studentDelete = function studentDelete(StudentID, Name) {
cleardetails();
$scope.StdIDs = StudentID;
var delConfirm = confirm("Are you sure you want to delete the Student " + Name + " ?");
if (delConfirm == true) {
$http.get('/api/students/deleteStudent/', { params: { stdID: $scope.StdIDs } }).success(function (data) {
alert("Student Deleted Successfully!!");
cleardetails();
selectStudentDetails('', '');
})
.error(function () {
$scope.error = "An Error has occured while loading posts!";
});
}
}
Conclusion
The main aim of this article is to create a simple MVC Web Based CRUD Operation using Angular JS WEB API 2 with Stored Procedure.
Supported Browsers: Chrome and Firefox.
History
shanuWebAPICRUDSP.zip - 2015-07-15
Submit an article or tip
News 
Bug 
Joke 
Rant 
Admin