Post
Ask Question

Stored Procedure With Input And Output Parameter Using Entity Framework In ASP.NET MVC

In this article, we are going to learn how to get the list and output value fro a stored procedure using Entity Framework in ASP.NET MVC.
  
Note - Here, I have attached my project with the database. If you want to know how to restore DB, please read my previous article here.
 
For that, here I have created a simple student details database.
  
Step 1
 
Let's create a database and tables. Here, I have created a database and named it as "SampleStudentDB".
 
Please find the below image for your reference.  
 
 
 
Next, we have to create a simple student table. Here, I have designed a simple table and named it as "tblStudentDetails". Insert some sample records.
 
 
 
 
 
Step 2
 
Next, write simple Stored Procedures to get the Student's list and count. Here, I have created 4 Stored Procedures with different conditions. 
 
sp_getStudentDetails
sp_getStudentDetailsbyCity
sp_getStudentDetailsbyCityandAge
sp_getStudentsCountbyCity 
 
sp_getStudentDetails
 
In this stored procedure, we are just returning all the records from "tblStudentDetails" table. We never passed any input and output parameters. We just used a simple SELECT query to return all the records.
  1. create proc [dbo].[sp_getStudentDetails]  
  2. as  
  3. begin  
  4. select * from tblStudentDetails;  
  5. end  
sp_getStudentDetailsbyCity
    
In this stored procedure, we are getting student records from  "tblStudentDetails" filtered with student city. Here, "City" is an input parameter and we passed it to our stored procedure to filter the records.
  1. create proc [dbo].[sp_getStudentDetailsbyCity]  
  2. @City varchar(50)  
  3. as   
  4. begin  
  5. select * from tblStudentDetails where City=@City;  
  6. end  
Note

Here, we filter the student records that match with the city.
 
sp_getStudentDetailsbyCityandAge
    
 In this stored procedure, we are getting student records from "tblStudentDetails" filtered with student's City and Age. Here, "City" and "Age" are input parameters and we passed them to our stored procedures to filter the records.
  1. create proc [dbo].[sp_getStudentDetailsbyCityandAge]   
  2. @City varchar(50),  
  3. @Age int  
  4. as   
  5. begin  
  6. select * from tblStudentDetails where City=@City and Age>@Age;  
  7. end  
 Note

Here, we filter the student records matching with the City and Age greater than our input age parameter.
 
sp_getStudentsCountbyCity
 
In this stored procedure, we are getting student count from "tblStudentDetails"  filtered with student city. Here, "City" is an input parameter and we passed it to our stored procedure to filter and get the student count. "@Count" is an output parameter to store a number of student count values.
  1. CREATE proc [dbo].[sp_getStudentsCountbyCity]  
  2. @City varchar(50),  
  3. @Count int out  
  4. as   
  5. begin  
  6. select @Count=COUNT(*) from tblStudentDetails where City=@City;  
  7. select @Count  
  8. end  
Step 3
 
Let's create a simple ASP MVC Project. Here, I have created my project and named it as "MvcEntityWithStoredProcedure".
 
Please find the below image for your reference.
 
 
 
Note

Here, I have selected Internet Application while creating my project (It will automattically create required script, CSS files, and folders).
 
Step 4
 
Next, we will create a new folder "DataModel" in our project to keep the entity.

 
Let's add an "ADO.NET Entity Data Model" in "DataModel" folder. To add, right-click the DataModel folder and go to Add -> New Item.  A dialog window will open. Select "Data" option on the left side of the dialog window and select "Ado.Net Entity Data Model". Finally, click OK.
 
 
 
 
 
 
 
 
 
Step 5
 
Now, we have to import the stored procedure into Entity Framework. Stored procedure in Entity Framework is called a function. For that, right click the entity model -> Add->Function Import. Please refer to the below images for reference.

 
Step 6
 
Let's import all our stored procedures as functions in EF with required return format. Please find the below images for your reference.
 
Funciton Import for Stored Procedure sp_getStudentDetails 
 
 
 
 Note

Here, I have imported a function "getStudentDetails" for stored procedure sp_getStudentDetails. The return value of function is our entity model class "tblStudentDetail". 
 
Funciton Import for Stored Procedure sp_getStudentDetailsbyCity
 
 
 
Note

Here, I have imported a function "getStudentDetailsbyCity" for stored procedure sp_getStudentDetailsbyCity. The return value of function is our entity model class "tblStudentDetail".
 
Funciton Import for Stored Procedure sp_getStudentDetailsbyCityandAge
 
 
 
Note

Here, I have imported a function "getStudentDetailsbyCityandAge" for stored procedure sp_getStudentDetailsbyCityandAge. The return value of function is our entity model class "tblStudentDetail".
 
Funciton Import for Stored Procedure sp_getStudentsCountbyCity
 
 
 
Note

Here, I have imported a function "getStudentsCountbyCity" for stored procedure sp_getStudentCountbyCity. The return value of function is integer value, So here I have selected our "Scalars" property.
 
Step 7
 
Next, we will create a simple Controller and add Action Methods to call the functions. 
 
Please find the image and code for your referecne.
 
 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using MvcEntityWithStoredProcedure.DataModel;  
  7. using MvcEntityWithStoredProcedure.Models;  
  8. using System.Data.Objects;  
  9. using System.Data;  
  10.   
  11. namespace MvcEntityWithStoredProcedure.Controllers  
  12. {  
  13.     public class StudentController : Controller  
  14.     {  
  15.         //  
  16.         // GET: /Student/  
  17.   
  18.         public ActionResult getStudentDetails()  // to get the Student Details  
  19.         {  
  20.             SampleStudentDBEntities _entity = new SampleStudentDBEntities();  
  21.             return View(_entity.getStudentDetails());  //calling our entity imported function  
  22.         }  
  23.         public ActionResult getStudentDetailbyCity()  
  24.         {  
  25.             SampleStudentDBEntities _entity = new SampleStudentDBEntities(); //to Get the student details filtered with student city  
  26.             return View(_entity.getStudentDetailbyCity("Bangalore")); //calling our entity imported function "Bangalore" is our input parameter  
  27.         }  
  28.         public ActionResult getStudentDetailsbyCityandAge() //to Get the student details filter with student city and age  
  29.         {  
  30.             SampleStudentDBEntities _entity = new SampleStudentDBEntities();  
  31.             return View(_entity.getStudentDetailsbyCityandAge("Bangalore", 12)); //calling our entity imported function "Bangalore" and 12 is our input parameters  
  32.         }  
  33.         public ActionResult getStudentsCountbyCity() //to get the count of student details filter with student city  
  34.         {  
  35.             SampleStudentDBEntities _entity = new SampleStudentDBEntities();  
  36.             ObjectParameter returnId = new ObjectParameter("Count", typeof(int)); //Create Object parameter to receive a output value.It will behave like output parameter  
  37.             var value = _entity.getStudentsCountbyCity("Bangalore", returnId).ToList(); //calling our entity imported function "Bangalore" is our input parameter, returnId is a output parameter, it will receive the output value   
  38.             ViewBag.StudentsCount = Convert.ToInt32(returnId.Value); //set the out put value to StudentsCount ViewBag  
  39.             return View();  
  40.         }  
  41.   
  42.     }  
  43. }  
Step 8
 
Let us create a View for our action result. Please find all the Views,
 
Note

Keep reminder that here, our return value of functions is "tblStudentDetail".
 
"getStudentDetails","getStudentDetailsbyCity","getStudentDetailsbyCityandAge" have the same View code. Please find the below HTML code. 
 
Note

I just posted here Single View code, because HTML code is same for those action methods. But you have to create different View for those action methods.  
  1. @model IEnumerable<MvcEntityWithStoredProcedure.DataModel.tblStudentDetail>  
  2.   
  3. @{  
  4.     ViewBag.Title = "getStudentDetailsbyCity";  
  5. }  
  6.   
  7. <h2>Index</h2>  
  8.   
  9. <p>  
  10.     @Html.ActionLink("Create New", "Create")  
  11. </p>  
  12. <table>  
  13.     <tr>  
  14.         <th>  
  15.             @Html.DisplayNameFor(model => model.Name)  
  16.         </th>  
  17.         <th>  
  18.             @Html.DisplayNameFor(model => model.Age)  
  19.         </th>  
  20.         <th>  
  21.             @Html.DisplayNameFor(model => model.City)  
  22.         </th>  
  23.         <th>  
  24.             @Html.DisplayNameFor(model => model.Department)  
  25.         </th>  
  26.     </tr>  
  27.   
  28. @foreach (var item in Model) {  
  29.     <tr>  
  30.         <td>  
  31.             @Html.DisplayFor(modelItem => item.Name)  
  32.         </td>  
  33.         <td>  
  34.             @Html.DisplayFor(modelItem => item.Age)  
  35.         </td>  
  36.         <td>  
  37.             @Html.DisplayFor(modelItem => item.City)  
  38.         </td>  
  39.         <td>  
  40.             @Html.DisplayFor(modelItem => item.Department)  
  41.         </td>  
  42.     </tr>  
  43. }  
  44.   
  45. </table>  
Step 9
 
Let us create a simple View for action method "getStudentsCountbyCity". It's going to return integer value instead of student list. We just passed this integer value using ViewBag.StudentsCount.
  1. @{  
  2.     ViewBag.Title = "getStudentsCountbyCity";  
  3. }  
  4.   
  5. <h2>Student Count is : @ViewBag.StudentsCount</h2>  
Step 10
 
Run the project and test the result.
 
Please find the sample output here.
 
 
 
 
 
 
Thanks for reading this article. If you have any doubts or comments, please mention them in the Comment Box. I hope this article was helpful for you.


Similar Articles