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;
  11. namespace MvcEntityWithStoredProcedure.Controllers
  12. {
  13. public class StudentController : Controller
  14. {
  15. //
  16. // GET: /Student/
  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. }
  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>
  3. @{
  4. ViewBag.Title = "getStudentDetailsbyCity";
  5. }
  7. <h2>Index</h2>
  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>
  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. }
  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. }
  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.