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.

create proc [dbo].[sp_getStudentDetails]
as
begin
select * from tblStudentDetails;
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.

create proc [dbo].[sp_getStudentDetailsbyCity]
@City varchar(50)
as
begin
select * from tblStudentDetails where City=@City;
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.

create proc [dbo].[sp_getStudentDetailsbyCityandAge]
@City varchar(50),
@Age int
as
begin
select * from tblStudentDetails where City=@City and Age>@Age;
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.

CREATE proc [dbo].[sp_getStudentsCountbyCity]
@City varchar(50),
@Count int out
as
begin
select @Count=COUNT(*) from tblStudentDetails where City=@City;
select @Count
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.

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcEntityWithStoredProcedure.DataModel;
using MvcEntityWithStoredProcedure.Models;
using System.Data.Objects;
using System.Data;

namespace MvcEntityWithStoredProcedure.Controllers
{
    public class StudentController : Controller
    {
        //
        // GET: /Student/

        public ActionResult getStudentDetails()  // to get the Student Details
        {
            SampleStudentDBEntities _entity = new SampleStudentDBEntities();
            return View(_entity.getStudentDetails());  //calling our entity imported function
        }
        public ActionResult getStudentDetailbyCity()
        {
            SampleStudentDBEntities _entity = new SampleStudentDBEntities(); //to Get the student details filtered with student city
            return View(_entity.getStudentDetailbyCity("Bangalore")); //calling our entity imported function "Bangalore" is our input parameter
        }
        public ActionResult getStudentDetailsbyCityandAge() //to Get the student details filter with student city and age
        {
            SampleStudentDBEntities _entity = new SampleStudentDBEntities();
            return View(_entity.getStudentDetailsbyCityandAge("Bangalore", 12)); //calling our entity imported function "Bangalore" and 12 is our input parameters
        }
        public ActionResult getStudentsCountbyCity() //to get the count of student details filter with student city
        {
            SampleStudentDBEntities _entity = new SampleStudentDBEntities();
            ObjectParameter returnId = new ObjectParameter("Count", typeof(int)); //Create Object parameter to receive a output value.It will behave like output parameter
            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
            ViewBag.StudentsCount = Convert.ToInt32(returnId.Value); //set the out put value to StudentsCount ViewBag
            return View();
        }

    }
}

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.  

@model IEnumerable<MvcEntityWithStoredProcedure.DataModel.tblStudentDetail>

@{
    ViewBag.Title = "getStudentDetailsbyCity";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table>
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Age)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.City)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Department)
        </th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Age)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.City)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Department)
        </td>
    </tr>
}

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

@{
    ViewBag.Title = "getStudentsCountbyCity";
}

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