MVC, Angular JS CRUD using WEB API 2 with Stored Procedure
4.88/5 (47 votes)
How to create simple web app using MVC 5, Angular JS, WEB API with stored procedure
Introduction
On the internet, there are lots of examples related to WEB API using Entity Framework. But in community forum, I saw many questions were being asked by members for a simple example using WEB API Entity Framework with Stored Procedure. I searched 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 wasn'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 (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 Service.
- MVC AngularJs and WCF Rest Service For Mind Reader Quiz
- MVC, AngularJs and WCF Rest Service For Master Detail Grid
- AngularJs Filter, Sorting and Animation Using MVC and WCF Rest
- AngularJs Shopping Cart Using MVC and WCF Rest Service
- AngularJs Dynamic Menu Creation Using MVC and WCF Rest
The previous articles are related to AngularJs using MVC and using Web API 2.
Angular JS
We might 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 this link.
Using the Code
1) Create Database and Table
We will create a StudentMasters table under the 'studentDB' database. 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.
-- =============================================
-- Author : Shanu
-- Create date : 2015-07-13
-- Description : To Create Database,Table and Sample Insert Query
-- Latest
-- Modifier : Shanu
-- Modify date : 2015-07-13
-- =============================================
--Script to create DB,Table and sample Insert data
USE MASTER
GO
-- 1) Check for the Database Exists .If the database is exist then drop and create new DB
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'studentDB' )
DROP DATABASE studentDB
GO
CREATE DATABASE studentDB
GO
USE studentDB
GO
-- 1) //////////// StudentMasters
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 sample data to Student Master table
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.
-- 1) Stored procedure to Select Student Details
-- Author : Shanu
-- Create date : 2015-07-13
-- Description : Student Details
-- Tables used : StudentMasters
-- Modifier : Shanu
-- Modify date : 2015-07-13
-- =============================================
-- exec USP_Student_Select '',''
-- =============================================
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
-- 2) Stored procedure to insert Student Details
-- Author : Shanu
-- Create date : 2015-07-13
-- Description : Student Details
-- Tables used : StudentMasters
-- Modifier : Shanu
-- Modify date : 2015-07-13
-- =============================================
-- exec USP_Student_Insert 'Raj','raj@rajmail.com','01030550008','seoul,Korea'
-- =============================================
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
-- 3) Stored procedure to Update Student Details
-- Author : Shanu
-- Create date : 2015-07-13
-- Description : Update Student Details
-- Tables used : StudentMasters
-- Modifier : Shanu
-- Modify date : 2015-07-13
-- =============================================
-- exec USP_Student_Update 'Raj','raj@rajmail.com','01030550008','seoul,Korea'
-- =============================================
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
-- 4) Stored procedure to Delete Student Details
-- Author : Shanu
-- Create date : 2015-07-13
-- Description : Delete Student Details
-- Tables used : StudentMasters
-- Modifier : Shanu
-- Modify date : 2015-07-13
-- =============================================
-- exec USP_Student_Delete '0'
-- =============================================
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. 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 database as studentDB as we have created the database using my SQL Script.
Click Next and select our tables that 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 needs 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:
Getis to request for the data. (Select)Postis to create a data. (Insert)Putis to update the data.Deleteis 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 details step by step as follows.
Here, we can see in get method, I have passed 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();
// to Search Student Details and display the result
[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 returned the message from database.
Insert Operation
Same like select, I have passed all the parameters 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.
// To Insert new Student Details
[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 passed all the parameters 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.
//to Update Student Details
[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
Similar to Update, I have passed the Student ID to the procedure to delete the record.
//to Update Student Details
[HttpGet]
public string deleteStudent(int stdID)
{
objapi.USP_Student_Delete(stdID);
return "deleted";
}
Now we have created our Web API Controller Class. In the 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 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 changed 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”:
// <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']);
})();
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 Variables which need 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;
//This variable will be used for Insert/Edit/Delete Students details.
$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 that 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 occurred 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 that 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:
//Search
$scope.searchStudentDetails = function () {
selectStudentDetails($scope.stdName, $scope.stdemail);
}
Insert New Student Details
In ADD New Student Detail button click, I will make visible 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.
// New Student Add Details
$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 whether the Student Name already exists or not. If the Student name does not exist in database, then I will insert the records and return the success message as “inserted” and if the student name already exists, then I will return the message as “Exists”.
//Save Student
$scope.saveDetails = function () {
$scope.IsFormSubmitted = true;
if ($scope.IsFormValid ) {
//if the Student ID=0 means its new Student insert here
//I will call the Web API insert method
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 occurred while loading posts!";
});
}
else { // to update to the student details
$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 occurred 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.
//Edit Student Details
$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.
//Delete Dtudent Detail
$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 occurred 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
- 15th July, 2015: Initial version