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.

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
   13. -- 1) Check for the Database Exists .If the database is exist then drop and create new DB
   14. IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'studentDB' )
   15. DROP DATABASE studentDB
   16. GO
   18. CREATE DATABASE studentDB
   19. GO
   21. USE studentDB
   22. GO
   25. -- 1) //////////// StudentMasters
   27. IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'StudentMasters' )
   28. DROP TABLE StudentMasters
   29. GO
   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. )
   39. -- insert sample data to Student Master table
   40. INSERT INTO [StudentMasters] ([StdName],[Email],[Phone],[Address])
   41. VALUES ('Shanu','[email protected]','01030550007','Madurai,India')
   43. INSERT INTO [StudentMasters] ([StdName],[Email],[Phone],[Address])
   44. VALUES ('Afraz','[email protected]','01030550006','Madurai,India')
   46. INSERT INTO [StudentMasters] ([StdName],[Email],[Phone],[Address])
   47. VALUES ('Afreen','[email protected]','01030550005','Madurai,India')
   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
   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 '',''
   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
   35. -- 2) Stored procedure to insert Student Details
   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','[email protected]','01030550008','seoul,Korea'
   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
   59. INSERT INTO [StudentMasters]
   60. ([StdName],[Email],[Phone],[Address])
   61. VALUES (@StdName,@Email,@Phone,@Address)
   63. Select 'Inserted' as results
   65. END
   66. ELSE
   67. BEGIN
   68. Select 'Exists' as results
   69. END
   71. END
   73. -- 3) Stored procedure to Update Student Details
   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','[email protected]','01030550008','seoul,Korea'
   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
   104. Select 'updated' as results
   105. END
   106. ELSE
   107. BEGIN
   108. Select 'Exists' as results
   109. END
   110. END
   113. -- 4) Stored procedure to Delete Student Details
   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'
   124. -- =============================================
   125. Create PROCEDURE [dbo].[USP_Student_Delete]
   126. ( @StdID Int=0 )
   127. AS
   128. BEGIN
   129. DELETE FROM StudentMasters WHERE StdID=@StdID
   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.
   
   
   
   Click New, Project and Select Web, then choose 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 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.



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



Select EF Designer from the 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 since 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 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.



Here we can see now I have created our 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.



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



Since we have created a Web API controller, we can see our controller has been inherited and thre is something about 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.

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.

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.

Delete Operation

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

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



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



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



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.



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



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

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.

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.

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.

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.

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

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.

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.