In this article I will explain with an example, how to call Stored Procedure using Entity Framework in ASP.Net MVC.
The Stored Procedure will be called by passing the parameter value using Entity Framework in ASP.Net MVC.
Note: For beginners in ASP.Net MVC, please refer my article ASP.Net MVC Hello World Tutorial with Sample Program example.
 
 

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 

Stored Procedure

The following Stored Procedurestored Procedures accepts @ContactName parameter which is used to perform a search on the records in Customers Table.
CREATE PROCEDURE Customers_SearchCustomers
       @ContactName NVARCHAR(30)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT *
      FROM Customers
      WHERE ContactName LIKE '%' + @ContactName + '%'
END
 
 

Configuring Entity Framework

You will need to configure the Entity Framework in order to connect to the database.
Note: The complete details of configuring and using Entity Framework in ASP.Net MVC are provided in my article ASP.Net MVC: Entity Framework Database First Approach example.
 
 

Configuring Stored Procedure in Entity Framework Model

Once the Entity Framework is configured, the next step is to import the Stored Procedurestored Procedures in the Entity Framework model.
In order to do so, you will need to Right Click the Entity Model and select Update Model from Database option from the context menu.
ASP.Net MVC: Call Stored Procedure using Entity Framework
 
Then, inside the Update Wizard, check (select) the Stored Procedure and click Finish.
ASP.Net MVC: Call Stored Procedure using Entity Framework
 
Now we will need to import the Stored Procedure into the Entity Framework so that it can be called as a Function using Entity Framework.
Thus you will need to Right Click the Entity Model, click Add and then click Function Import.
ASP.Net MVC: Call Stored Procedure using Entity Framework
 
This will open the Add Function Import dialog window. Here first you need to specify the Function Import Name which is the name of the function used to call the Stored Procedure and then select the Stored Procedure that will be executed when the function is called.
The Return Type is selected as Entities which is the Customer Entity class.
ASP.Net MVC: Call Stored Procedure using Entity Framework
 
Finally, you will see the function name in the Model Browser.
ASP.Net MVC: Call Stored Procedure using Entity Framework
 
 

Controller

The Controller consists of following Action methods.

Action method for handling GET operation

Inside the Index Action method, the Stored Procedure is called using the SearchCustomers function created using the Function Import procedure done earlier.
The parameter ContactName is passed as empty string and hence it gets all records from the Customers table of the Northwind database.
Finally, the list of Customers Entity is returned to the View.
 

Action method for handling POST operation

When the Form is submitted, the value of the Customer Name TextBox is submitted this Action method and the value is passed as parameter to the SearchCustomers function and the list of Customers Entity is returned to the View.
public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        NorthwindEntities  entities = new NorthwindEntities();
        return View(entities.SearchCustomers(""));
    }
 
    [HttpPost]
    public ActionResult Index(string customerName)
    {
        NorthwindEntities entities = new NorthwindEntities();
        return View(entities.SearchCustomers(customerName));
    }
}
 
 

View

HTML Markup

Inside the View, in the very first line the Customer Entity is declared as IEnumerable which specifies that it will be available as a Collection.
The View consists of an HTML Form which has been created using the Html.BeginForm method with the following parameters.
ActionName – Name of the Action. In this case the name is Index.
ControllerName – Name of the Controller. In this case the name is Home.
FormMethod – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
Inside the Form, a TextBox is created using the Html.TextBox HTML Helper function and there is a Submit button which when clicked, the Form gets submitted.
 

Displaying the records

For displaying the records, an HTML Table is used. A FOR EACH loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
@model IEnumerable<Customer>
 
@{
     Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    @using (Html.BeginForm("Index", "Home", FormMethod.Post))
    {
        <span>Customer Name:</span> @Html.TextBox("CustomerName")
        <input type="submit" value="Search" />
        <br />
        <br />
        <table cellpadding="0" cellspacing="0">
            <tr>
                <th>CustomerID</th>
                <th>ContactName</th>
                <th>City</th>
                <th>Country</th>
            </tr>
            @foreach (Customer customer in Model)
            {
                <tr>
                    <td>@customer.CustomerID</td>
                    <td>@customer.ContactName</td>
                    <td>@customer.City</td>
                    <td>@customer.Country</td>
                </tr>
            }
        </table>
    }
</body>
</html>
 
 

Screenshot

ASP.Net MVC: Call Stored Procedure using Entity Framework
 
 

Downloads

Download Code