MVC, AngularJS CRUD Using WEB API 2 With Stored Procedure



In the web there are many examples related to the Web API using Entity Framework, but in community forums I see many questions 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 that explains a simple way to do CRUD operations using a Stored Procedure with MVC and Web API. But I couldn"t find an article that explains all this. I plan to make a simple web application using MVC 5, AngularJs, Web API to do CRUD (Create/Read/Update and Delete) using Entity Framework and a Stored Procedure.

In this article we will see the following:

  • C: (Create): Insert New Student Details into the database using EF and Web API with Stored Procedure.
  • R: (Read): Search/Select Student Details from the database using EF and Web API with Stored Procedure.
  • U: (Update): Update Student Details to the database using EF and Web API with Stored Procedure.
  • D: (Delete): Delete Student Details from the database using EF and Web API with Stored Procedure.
Prerequisites

Visual Studio 2015. You can download it from here (in my example I used Visual Studio Community 2015 RC).

You can also view my previous articles related to AngularJs using MVC and the WCF Rest Service.

Previous articles related to AngularJs using MVC and and using Web API 2.

AngularJs

We might be familiar with what the Model, View, View Model (MVVM) and what Model, View and Controller (MVC) are. AngularJs is a JavaScript framework that is purely based on HTML, CSS and JavaScript.

The AngularJs 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 AngularJs in our MVC application.

If you are interested in reading more about AngularJs then kindly go through the following link.

Code Part

  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.
    1. -- =============================================                                 
    2. -- Author      : Shanu                                  
    3. -- Create date : 2015-07-13                                   
    4. -- Description : To Create Database,Table and Sample Insert Query                              
    5. -- Latest                                 
    6. -- Modifier    : Shanu                                  
    7. -- Modify date : 2015-07-13                             
    8. -- =============================================  
    9. --Script to create DB,Table and sample Insert data  
    10. USE MASTER  
    11. GO  
    12.   
    13. -- 1) Check for the Database Exists .If the database is exist then drop and create new DB  
    14. IF EXISTS (SELECT [nameFROM sys.databases WHERE [name] = 'studentDB' )  
    15. DROP DATABASE studentDB  
    16. GO  
    17.   
    18. CREATE DATABASE studentDB  
    19. GO  
    20.   
    21. USE studentDB  
    22. GO  
    23.   
    24.   
    25. -- 1) //////////// StudentMasters  
    26.   
    27. IF EXISTS ( SELECT [nameFROM sys.tables WHERE [name] = 'StudentMasters' )  
    28. DROP TABLE StudentMasters  
    29. GO  
    30.   
    31. CREATE TABLE [dbo].[StudentMasters](  
    32.         [StdID] INT IDENTITY PRIMARY KEY,  
    33.         [StdName] [varchar](100) NOT NULL,     
    34.         [Email]  [varchar](100) NOT NULL,     
    35.         [Phone]  [varchar](20) NOT NULL,     
    36.         [Address]  [varchar](200) NOT NULL  
    37. )  
    38.   
    39. -- insert sample data to Student Master table  
    40. INSERT INTO [StudentMasters]   ([StdName],[Email],[Phone],[Address])  
    41.      VALUES ('Shanu','syedshanumcain@gmail.com','01030550007','Madurai,India')  
    42.   
    43. INSERT INTO [StudentMasters]   ([StdName],[Email],[Phone],[Address])  
    44.      VALUES ('Afraz','Afraz@afrazmail.com','01030550006','Madurai,India')  
    45.        
    46. INSERT INTO [StudentMasters]   ([StdName],[Email],[Phone],[Address])  
    47.      VALUES ('Afreen','Afreen@afreenmail.com','01030550005','Madurai,India')  
    48.        
    49.        
    50.      select * from [StudentMasters]
    After creating our Table we will create a Stored Procedure to do our CRUD Operations.
    1. -- 1) Stored procedure to Select Student Details  
    2.   
    3. -- Author      : Shanu                                                                  
    4. -- Create date : 2015-07-13                                                                  
    5. -- Description : Student Details                                                
    6. -- Tables used :  StudentMasters                                                                 
    7. -- Modifier    : Shanu                                                                  
    8. -- Modify date : 2015-07-13                                                                  
    9. -- =============================================                                                                  
    10. -- exec USP_Student_Select '',''  
    11.           
    12. -- =============================================                                                             
    13. Create PROCEDURE [dbo].[USP_Student_Select]                                                
    14.    (                              
    15.      @StdName          VARCHAR(100)     = '',  
    16.      @email            VARCHAR(100)     = ''      
    17.       )                                                          
    18. AS                                                                  
    19. BEGIN         
    20.          Select [StdID],  
    21.                 [StdName],  
    22.                 [Email],  
    23.                 [Phone],  
    24.                 [Address]  
    25.             FROM   
    26.                 StudentMasters   
    27.             WHERE  
    28.                 StdName like  @StdName +'%'  
    29.                 AND Email like @email +'%'  
    30.             ORDER BY  
    31.                 StdName   
    32. END  
    33.   
    34.   
    35. -- 2) Stored procedure to insert Student Details  
    36.   
    37. -- Author      : Shanu                                                                  
    38. -- Create date : 2015-07-13                                                                  
    39. -- Description : Student Details                                                
    40. -- Tables used :  StudentMasters                                                                 
    41. -- Modifier    : Shanu                                                                  
    42. -- Modify date : 2015-07-13                                                                  
    43. -- =============================================                                                                  
    44. -- exec USP_Student_Insert 'Raj','raj@rajmail.com','01030550008','seoul,Korea'  
    45.           
    46. -- =============================================                                                             
    47. alter PROCEDURE [dbo].[USP_Student_Insert]                                                
    48.    (                         
    49.      @StdName          VARCHAR(100)     = '',  
    50.      @email            VARCHAR(100)     = '',  
    51.      @Phone            VARCHAR(20)     = '',  
    52.      @Address          VARCHAR(200)     = ''  
    53.       )                                                          
    54. AS                                                                  
    55. BEGIN         
    56.         IF NOT EXISTS (SELECT * FROM StudentMasters WHERE StdName=@StdName)  
    57.             BEGIN  
    58.   
    59.                     INSERT INTO [StudentMasters]   
    60.                     ([StdName],[Email],[Phone],[Address])  
    61.                      VALUES (@StdName,@Email,@Phone,@Address)  
    62.                                  
    63.                     Select 'Inserted' as results  
    64.                           
    65.             END  
    66.          ELSE  
    67.              BEGIN  
    68.                      Select 'Exists' as results  
    69.               END  
    70.   
    71. END  
    72.   
    73. -- 3) Stored procedure to Update Student Details  
    74.       
    75. -- Author      : Shanu                                                                  
    76. -- Create date : 2015-07-13                                                                  
    77. -- Description : Update Student Details                                                
    78. -- Tables used :  StudentMasters                                                                 
    79. -- Modifier    : Shanu                                                                  
    80. -- Modify date : 2015-07-13                                                                  
    81. -- =============================================                                                                  
    82. -- exec USP_Student_Update 'Raj','raj@rajmail.com','01030550008','seoul,Korea'  
    83.           
    84. -- =============================================                                                             
    85. Alter PROCEDURE [dbo].[USP_Student_Update]                                                
    86.    ( @StdID            Int=0,                             
    87.      @StdName          VARCHAR(100)     = '',  
    88.      @email            VARCHAR(100)     = '',  
    89.      @Phone            VARCHAR(20)     = '',  
    90.      @Address          VARCHAR(200)     = ''  
    91.       )                                                          
    92. AS                                                                  
    93. BEGIN         
    94.         IF NOT EXISTS (SELECT * FROM StudentMasters WHERE StdID!=@StdID AND StdName=@StdName)  
    95.             BEGIN  
    96.                     UPDATE StudentMasters  
    97.                     SET [StdName]=@StdName,  
    98.                     [Email]=@email,  
    99.                     [Phone]=@Phone,  
    100.                     [Address]=@Address  
    101.                     WHERE  
    102.                         StdID=@StdID  
    103.                                  
    104.                     Select 'updated' as results                       
    105.             END  
    106.          ELSE  
    107.              BEGIN  
    108.                      Select 'Exists' as results  
    109.               END  
    110. END  
    111.   
    112.   
    113. -- 4) Stored procedure to Delete Student Details  
    114.       
    115. -- Author      : Shanu                                                                  
    116. -- Create date : 2015-07-13                                                                  
    117. -- Description : Delete Student Details                                                
    118. -- Tables used :  StudentMasters                                                                 
    119. -- Modifier    : Shanu                                                                  
    120. -- Modify date : 2015-07-13                                                                  
    121. -- =============================================                                                                  
    122. -- exec USP_Student_Delete '0'  
    123.           
    124. -- =============================================                                                             
    125. Create PROCEDURE [dbo].[USP_Student_Delete]                                                
    126.    ( @StdID  Int=0 )                                                          
    127. AS                                                                  
    128. BEGIN         
    129.         DELETE FROM StudentMasters WHERE StdID=@StdID                 
    130.               
    131. END
    Create our MVC Web Application in Visual Studio 2015:

    After installing our Visual Studio 2015, click Start, then Programs and select Visual Studio 2015. Then click Visual Studio 2015 RC.

    Create our MVC Web Application

    Click New, Project and Select Web, then choose ASP.NET Web Application. Select your project location and enter your web application Name.

    open Visual Studio

    Select MVC and in Add Folders and Core reference for select the Web API and click OK.

    mvc

    Now we have created our MVC application as a next step and we will 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.

add new item

Select Data, then choose Select ADO.NET Entity Data Model and provide the name and click Add

Select ADO dot NET

Select EF Designer from the database and click Next.

Select EF Designer

Here click New Connection and provide your SQL-Server Server Name and connect to your database.

click New Connection

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 since we have created the database using my SQL Script.

Database

Click Next and select our tables need to be used and click Finish.

Click next

Here we can see I have selected the table StudentMasters. To use our Stored Procedure select the entire SP to be used in our project. Here we can see for performing CRUD operations I have created 4 Stored Procedures to Select/Insert/Update and Delete. Select the entire Stored Procedure and click Finish.

table

Here we can see now I have created our StudentDetailsEntities.

StudentDetailsEntities

Once the Entity has been created the next step is to add a Web API to our controller and write function to Select/Insert/Update and Delete.

Procedure to add our Web API Controller

Right-click the Controllers folder, click Add and then click Controller.

Add Controller

Since we will create our Web API Controller, select Controller and add an Empty Web API 2 Controller. Provide your name to the Web API controller and click OK. Here for my Web API Controller I have given the name “StudentsController”.

StudentsController

Since we have created a Web API controller, we can see our controller has been inherited and thre is something about ApiController.

ApiController

As we all know Web API is a simple and easy way to build HTTP Services for Browsers and Mobiles.
Web API has the following 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 since we need to get all the image names and descriptions from the database and to insert a new Image Name and Image Description to the database.

Get Method

In our example I have used only a Get method since I am using only a Stored Procedure. We need to create an object for our Entity and write our Get Method to do Select/Insert/Update and Delete operations.

Select Operation

We use a get method to get all the details of the StudentMasters table using an entity object and we return the result as an IEnumerable. We use this method in our AngularJs and display the result in an MVC page from the AngularJs controller. Using Ng-Repeat we can see the details step-by-step as follows.

Here we can see in the get method I have passed  the search parameter to the USP_Student_Select Stored Procedure method. In the Stored Procedure I used like "%" to return all the records if the search parameter is empty.

  1. public class studentsController : ApiController  
  2. {  
  3.     studentDBEntities objapi = new studentDBEntities();  
  4.   
  5.    
  6.     // to Search Student Details and display the result  
  7.     [HttpGet]  
  8.     public IEnumerable<USP_Student_Select_Result> Get(string StudentName, string StudentEmail)  
  9.     {  
  10.         if (StudentName == null)  
  11.             StudentName = "";  
  12.         if (StudentEmail == null)  
  13.             StudentEmail = "";  
  14.         return objapi.USP_Student_Select(StudentName, StudentEmail).AsEnumerable();  
  15.   
  16.   
  17.     }

Here in my example I have used the get method for Select/Insert/Update and Delete operations, since in my Stored Procedure after insert/update and delete I have returned the message from the database.

Insert Operation

The same as select I passed all the parameters to the insert procedure. This insert method will return the result from the database as a record is inserted or maybe not. I will get the result and display it from the AngularJs Controller to MVC application.

  1. // To Insert new Student Details  
  2. [HttpGet]  
  3. public IEnumerable<string> insertStudent(string StudentName, string StudentEmail, string Phone, string Address)  
  4. {             
  5.       return  objapi.USP_Student_Insert(StudentName, StudentEmail, Phone, Address).AsEnumerable();             

Update Operation

The same as Insert I have passed all the parameter to the insert procedure. This Update method will return the result from the database as a record is updated or maybe not. I will pass the Student ID to the update procedure to update the record for the Student ID. I will get the result and display it from the AngularJs Controller to the MVC application.

  1. //to Update Student Details  
  2.  [HttpGet]  
  3.  public IEnumerable<string> updateStudent(int stdID,string StudentName, string StudentEmail, string Phone, string Address)  
  4.  {  
  5.      return objapi.USP_Student_Update(stdID,StudentName, StudentEmail, Phone, Address).AsEnumerable();  
  6.  }

Delete Operation

The same as update I have passed the Student ID to the procedure to delete the record.

  1. //to Update Student Details  
  2.  [HttpGet]  
  3.  public string deleteStudent(int stdID)  
  4.  {  
  5.      objapi.USP_Student_Delete(stdID);  
  6.       "deleted";  
  7.  } 

Now we have created our Web API Controller Class. The next step is to create our AngularJs Module and Controller. Let's see how to create our AngularJs Controller. In Visual Studio 2015 it's much easier 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 have given the folder name as “MyAngular”.

MyAngular Folder

Now add your Angular Controller inside the folder.

Right-click the MyAngular folder and click Add and New Item. Select Web and then AngularJs Controller and provide a name for the Controller. I have named my AngularJs Controller “Controller.js”.

AngularJs Controller

Once the AngularJs Controller is created, we can see by default the controller will have the code with the default module definition and all.

controller

I have changed the preceding code like adding a Module and controller as in the following.

If the AngularJs 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.

angularJS

Now we can see all the AngularJs packages have been installed and we can see all the files in the Script folder.

my anjular

Procedure to Create AngularJs Script Files

Modules.js: Here we will add the reference to the AngularJs JavaScript and create an Angular Module named “RESTClientModule”.

  1. // <reference path="../angular.js" />    
  2. /// <reference path="../angular.min.js" />     
  3. /// <reference path="../angular-animate.js" />     
  4. /// <reference path="../angular-animate.min.js" />     
  5.   
  6.   
  7. var app;  
  8.   
  9.   
  10. (function () {  
  11.     app = angular.module("RESTClientModule", ['ngAnimate']);  
  12. })();

Controllers: In AngularJs Controller I have done all the business logic and returned the data from Web API to our MVC HTML page.

1. Variable declarations

First I declared all the local variables that need to be used.

  1. app.controller("AngularJs_studentsController", function ($scope, $timeout, $rootScope, $window, $http) {  
  2.     $scope.date = new Date();  
  3.     $scope.MyName = "shanu";  
  4.     $scope.stdName = "";  
  5.     $scope.stdemail = "";  
  6.   
  7.     $scope.showStudentAdd = true;  
  8.     $scope.addEditStudents = false;  
  9.     $scope.StudentsList=true;  
  10.     $scope.showItem = true;  
  11.   
  12.     //This variable will be used for Insert/Edit/Delete Students details.  
  13.     $scope.StdIDs = 0;  
  14.     $scope.stdNames = "";  
  15.     $scope.stdEmails = "";  
  16.     $scope.Phones = "";  
  17.     $scope.Addresss = ""

2. Methods

Select Method

In the select method I have used $http.get to get the details from Web API. In the get method I will provide 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 data-ng-repeat.

  1. function selectStudentDetails(StudentName, StudentEmail) {  
  2.          
  3.   
  4.         $http.get('/api/students/', { params: { StudentName: StudentName, StudentEmail: StudentEmail } }).success(function (data) {  
  5.             $scope.Students = data;  
  6.   
  7.             $scope.showStudentAdd = true;  
  8.             $scope.addEditStudents = false;  
  9.             $scope.StudentsList = true;  
  10.             $scope.showItem = true;  
  11.            
  12.           
  13.             if ($scope.Students.length > 0) {  
  14.                
  15.             }  
  16.         })  
  17.    .error(function () {  
  18.        $scope.error = "An Error has occured while loading posts!";  
  19.    });

Search Button Click

In the 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 ng-model="stdName". Using ng-model in the AngularJs Controller we can get the TextBox input value or we can set the value to the TextBox.

  1. <input type="text" name="txtstudName" ng-model="stdName" value="" />  
  2. <input type="text" name="txtemail" ng-model="stdemail" />  
  3. <input type="submit" value="Search" style="background-color:#336699;color:#FFFFFF" ng-click="searchStudentDetails()" />  
  4.   
  5. //Search  
  6. $scope.searchStudentDetails = function () {  
  7.         
  8.     selectStudentDetails($scope.stdName, $scope.stdemail);  

Student Details

Insert new Student Details

In the ADD New Student Detail button click I will make visible the StudentAdd table details where the user can enter the new student information. For a new student I will make the Student ID as 0. In the New Student save button click I will call the save method.

  1. // New Student Add Details  
  2. $scope.showStudentDetails = function () {  
  3.     cleardetails();  
  4. pe.showStudentAdd = true;  
  5.     $scope.addEditStudents = true;  
  6.     $scope.StudentsList = true;  
  7.     $scope.showItem = true;  
  8.   
  9.       
  10. }

return the message

In the Save method I will check for the Student ID. If the student ID is “0” then it will insert the new student details. Here I will call the Insert Web API method and if the Student ID is > 0 then that means that to update the student record I will call the Update Web API method.

To the Insert Web API Method I will pass all the Input parameters. In my Stored Procedure I will check whether the Student Name already exists. If the Student name does not exist in the 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”.

message

  1. //Save Student  
  2. $scope.saveDetails = function () {  
  3.   
  4.     $scope.IsFormSubmitted = true;  
  5.     if ($scope.IsFormValid ) {  
  6.         //if the Student ID=0 means its new Student insert here i will call the Web api insert method  
  7.         if ($scope.StdIDs == 0) {  
  8.           
  9.             $http.get('/api/students/insertStudent/', { params: { StudentName: $scope.stdNames, StudentEmail: $scope.stdEmails, Phone: $scope.Phones, Address: $scope.Addresss } }).success(function (data) {  
  10.   
  11.                 $scope.StudentsInserted = data;  
  12.                 alert($scope.StudentsInserted);  
  13.                
  14.                  
  15.                 cleardetails();  
  16.                 selectStudentDetails('''');  
  17.   
  18.             })  
  19.      .error(function () {  
  20.          $scope.error = "An Error has occured while loading posts!";  
  21.      });  
  22.         }  
  23.         else {  // to update to the student details  
  24.             $http.get('/api/students/updateStudent/', { params: { stdID: $scope.StdIDs, StudentName: $scope.stdNames, StudentEmail: $scope.stdEmails, Phone: $scope.Phones, Address: $scope.Addresss } }).success(function (data) {  
  25.                 $scope.StudentsUpdated = data;  
  26.                 alert($scope.StudentsUpdated);  
  27.   
  28.                 cleardetails();  
  29.                 selectStudentDetails('''');  
  30.   
  31.             })  
  32.     .error(function () {  
  33.         $scope.error = "An Error has occured while loading posts!";  
  34.     });  
  35.         }  
  36.          
  37.     }  
  38.     else {  
  39.         $scope.Message = "All the fields are required.";  
  40.     }  
  41.   
  42.      
  43. }

Update Student Details

The same as Insert I will display the update details for the user to edit the details and save it. In the Edit method I will get all the details for the row where the user clicks on the Edit Icon and sets all the results to the appropriate TextBox. In the Save button click I will call the save method to save all the changes to the database like Insert.

  1. //Edit Student Details  
  2. $scope.studentEdit = function studentEdit(StudentID, Name, Email, Phone, Address)  
  3. {  
  4.         cleardetails();  
  5.         $scope.StdIDs = StudentID;  
  6.         $scope.stdNames = Name  
  7.         $scope.stdEmails = Email;  
  8.         $scope.Phones = Phone;  
  9.         $scope.Addresss = Address;  
  10.         
  11.         $scope.showStudentAdd = true;  
  12.         $scope.addEditStudents = true;  
  13.         $scope.StudentsList = true;  
  14.         $scope.showItem = true;  
  15.     }

search student detail

Delete Student Details

In the Delete button click, I will display the confirmation message to the user as to whether to delete the detail or not. If the user clicks the OK button I will pass the Student ID to the delete method of the Web API to delete the record from the database.

  1. //Delete Dtudent Detail  
  2. $scope.studentDelete = function studentDelete(StudentID, Name) {  
  3.     cleardetails();  
  4.     $scope.StdIDs = StudentID;  
  5.     var delConfirm = confirm("Are you sure you want to delete the Student " + Name + " ?");  
  6.     if (delConfirm == true) {  
  7.   
  8.         $http.get('/api/students/deleteStudent/', { params: { stdID: $scope.StdIDs } }).success(function (data) {  
  9.             alert("Student Deleted Successfully!!");  
  10.             cleardetails();  
  11.             selectStudentDetails('''');  
  12.         })  
  13.   .error(function () {  
  14.       $scope.error = "An Error has occured while loading posts!";  
  15.   });  
  16.         
  17.     }       
  18.   }

search