Click here to Skip to main content
13,593,169 members
Click here to Skip to main content
Add your own
alternative version

Stats

103.8K views
6.7K downloads
91 bookmarked
Posted 15 Jul 2015
Licenced CPOL

MVC, Angular JS CRUD using WEB API 2 with Stored Procedure

, 27 Jul 2015
Rate this:
Please Sign up or sign in to vote.
In this article we will see how to create a simple web application using MVC 5 ,Angular JS ,WEB API 2 to perform CRUD(Create/Read/Update and Delete) Operations using Entity Framework with Stored procedure.

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.

-- =============================================                               
-- 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 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();    

        // 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 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.

// 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 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.

 

//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

Same like 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. 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”

// <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 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;


    //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 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:

//Search

    $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.

// 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 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”.

//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 occured 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 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.

//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 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

syed shanu
Team Leader
India India
Microsoft MVP | Code Project MVP | CSharp Corner MVP | Author | Blogger and always happy to Share what he knows to others. MyBlog

My Interview on Microsoft TechNet Wiki Ninja Link

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionThank you for this database-first approach Pin
mxlockwo7-Jan-17 8:45
membermxlockwo7-Jan-17 8:45 
AnswerRe: Thank you for this database-first approach Pin
syed shanu8-Jan-17 12:58
mvpsyed shanu8-Jan-17 12:58 
QuestionWithout Entity Framework Pin
Member 128017052-Jan-17 23:01
memberMember 128017052-Jan-17 23:01 
Questionwhile loading the page, i'm getting js errors Pin
G.Ram Mohan7-Feb-16 20:21
memberG.Ram Mohan7-Feb-16 20:21 
Questioni was reoslved my probs, but api controller selectsutdentlist is not able to call from angular js file Pin
G.Ram Mohan7-Feb-16 19:55
memberG.Ram Mohan7-Feb-16 19:55 
Questionwhile running the appln i'm getting the below error , studlist is not dispalying any recors.. Pin
G.Ram Mohan7-Feb-16 19:34
memberG.Ram Mohan7-Feb-16 19:34 
Question.chtml files for student is not aviliable in Views folder Pin
G.Ram Mohan5-Feb-16 0:28
memberG.Ram Mohan5-Feb-16 0:28 
Suggestiona little enhancement Pin
Member 1215552631-Dec-15 23:40
memberMember 1215552631-Dec-15 23:40 
GeneralRe: a little enhancement Pin
syed shanu3-Jan-16 13:44
mvpsyed shanu3-Jan-16 13:44 
QuestionWhere is the html part? Pin
Member 1215552630-Dec-15 7:29
memberMember 1215552630-Dec-15 7:29 
AnswerRe: Where is the html part? Pin
syed shanu30-Dec-15 14:55
mvpsyed shanu30-Dec-15 14:55 
GeneralRe: Where is the html part? Pin
Member 1215552630-Dec-15 16:24
memberMember 1215552630-Dec-15 16:24 
PraiseGood overview of using SPs. Worked great! The db scripts need modification to run in MSSQL Pin
Member 47595473-Nov-15 14:06
memberMember 47595473-Nov-15 14:06 
GeneralRe: Good overview of using SPs. Worked great! The db scripts need modification to run in MSSQL Pin
syed shanu3-Nov-15 14:11
mvpsyed shanu3-Nov-15 14:11 
QuestionNice Tutorial Pin
sohaibjaved4428-Sep-15 7:29
membersohaibjaved4428-Sep-15 7:29 
AnswerRe: Nice Tutorial Pin
syed shanu28-Sep-15 14:08
mvpsyed shanu28-Sep-15 14:08 
QuestionAltering procedures Pin
Member 1194699521-Sep-15 23:14
memberMember 1194699521-Sep-15 23:14 
AnswerRe: Altering procedures Pin
syed shanu21-Sep-15 23:16
mvpsyed shanu21-Sep-15 23:16 
GeneralRe: Altering procedures Pin
Member 1194699527-Sep-15 23:36
memberMember 1194699527-Sep-15 23:36 
QuestionI have problem the data does not appear in the table Pin
Member 1193248929-Aug-15 6:35
memberMember 1193248929-Aug-15 6:35 
QuestionWhat is USP_Student_Select_Result in the Controller Method: IEnumerable<USP_Student_Select_Result> ? Pin
RAFTOGI24-Aug-15 0:15
memberRAFTOGI24-Aug-15 0:15 
AnswerRe: What is USP_Student_Select_Result in the Controller Method: IEnumerable<USP_Student_Select_Result> ? Pin
syed shanu24-Aug-15 1:00
mvpsyed shanu24-Aug-15 1:00 
GeneralThanks and have a 5 Pin
Member 118880045-Aug-15 4:31
memberMember 118880045-Aug-15 4:31 
QuestionVS 2013 Pin
kiquenet.com28-Jul-15 2:51
memberkiquenet.com28-Jul-15 2:51 
AnswerRe: VS 2013 Pin
syed shanu28-Jul-15 3:17
mvpsyed shanu28-Jul-15 3:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180618.1 | Last Updated 27 Jul 2015
Article Copyright 2015 by syed shanu
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid