Introduction

In web there are lots of examples relatedto 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 makea simple web application using MVC 5 ,Angular JS ,WEB API to perform CRUD(Create/Read/Update and Delete) using Entity Framework using 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/download-visual-studio-vs.aspx
(In my example I have used Visual Studio Community 2015 RC you can download the latest Visual Studio 2015 ).
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

Building the Sample

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.
Description
 
SQL
Edit|Remove
-- =============================================                                
-- 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 [nameFROM sys.databases WHERE [name] = 'studentDB' ) 
DROP DATABASE studentDB 
GO 
 
CREATE DATABASE studentDB 
GO 
 
USE studentDB 
GO 
 
 
-- 1) //////////// StudentMasters 
 
IF EXISTS ( SELECT [nameFROM sys.tables WHERE [name] = 'StudentMasters' ) 
DROP TABLE StudentMasters 
GO 
 
CREATE TABLE [dbo].[StudentMasters]( 
        [StdIDINT IDENTITY PRIMARY KEY, 
        [StdName] [varchar](100NOT NULL,    
        [Email]  [varchar](100NOT NULL,    
        [Phone]  [varchar](20NOT NULL,    
        [Address]  [varchar](200NOT 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.

SQL
Edit|Remove
-- 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 '',''-- =============================================                                                           CreatePROCEDURE [dbo].[USP_Student_Select]                                               
   (                             
     @StdNameVARCHAR(100)     = '', 
     @emailVARCHAR(100)     = ''     
      )                                                         
ASBEGINSelect [StdID], 
                [StdName], 
                [Email], 
                [Phone], 
                [Address] 
            FROMStudentMastersWHEREStdNamelike@StdName +'%'ANDEmaillike@email +'%'ORDERBYStdNameEND-- 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'-- =============================================                                                           alterPROCEDURE [dbo].[USP_Student_Insert]                                               
   (                        
     @StdNameVARCHAR(100)     = '', 
     @emailVARCHAR(100)     = '', 
     @PhoneVARCHAR(20)     = '', 
     @AddressVARCHAR(200)     = '' 
      )                                                         
ASBEGINIFNOTEXISTS (SELECT * FROMStudentMastersWHEREStdName=@StdName) 
            BEGININSERTINTO [StudentMasters]  
                    ([StdName],[Email],[Phone],[Address]) 
                     VALUES (@StdName,@Email,@Phone,@Address) 
                                
                    Select'Inserted'asresultsENDELSEBEGINSelect'Exists'asresultsENDEND-- 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'-- =============================================                                                           AlterPROCEDURE [dbo].[USP_Student_Update]                                               
   ( @StdIDInt=0,                            
     @StdNameVARCHAR(100)     = '', 
     @emailVARCHAR(100)     = '', 
     @PhoneVARCHAR(20)     = '', 
     @AddressVARCHAR(200)     = '' 
      )                                                         
ASBEGINIFNOTEXISTS (SELECT * FROMStudentMastersWHEREStdID!=@StdIDANDStdName=@StdName) 
            BEGINUPDATEStudentMastersSET    [StdName]=@StdName, 
                    [Email]=@email, 
                    [Phone]=@Phone, 
                    [Address]=@AddressWHEREStdID=@StdIDSelect'updated'asresultsENDELSEBEGINSelect'Exists'asresultsENDEND-- 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'-- =============================================                                                           CreatePROCEDURE [dbo].[USP_Student_Delete]                                               
   ( @StdIDInt=0 )                                                         
ASBEGINDELETEFROMStudentMastersWHEREStdID=@StdIDEND

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.
C#
Edit|Remove
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.
C#
Edit|Remove
// 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.
C#
Edit|Remove
//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(); 
        } 
 Deletev Operation
Same like Update I have passed the Student ID to the procedure to delete the record.
 
C#
Edit|Remove
//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.Select AngularJs Controller and give name to Controller.I have given my AngularJs 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 AngularJs 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” 

JavaScript
Edit|Remove
// <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 .
JavaScript
Edit|Remove
app.controller("AngularJs_studentsController"function ($scope, $timeout, $rootScope, $window, $http) { 
    $scope.date = newDate(); 
    $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
 
 
JavaScript
Edit|Remove
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; 
          
         
            if ($scope.Students.length > 0{ 
              
            } 
        }) 
   .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.
JavaScript
Edit|Remove
<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()" /> 
 
//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. 
JavaScript
Edit|Remove
// 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”.
  
JavaScript
Edit|Remove
//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.
JavaScript
Edit|Remove
//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.
JavaScript
Edit|Remove
//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!"; 
      }); 
           
        }      
      } 
 
 
 
 
 
 

Source Code Files

More Information

For more information on X, see ...?