Call a stored procedure using the Entity Framework

By Christos S. on April 8, 2013 • ( 1 )

I am going to show you how easy is to call a stored procedure using the Entity Framework. First of all you need to create the stored procedure which you want to call (in case it doesn’t already exist), in your database server. I am using Sql Server 2012 and I will create a stored procedure to retrieve all products with ListPrice greater than a value. This value will be the procedure’s parameter. Just open Sql Server Management Studio, right click the database and select “New Query”. Put the following code and click execute.


USE AdventureWorks2012;
GO

CREATE PROCEDURE dbo.uspGetProductsWithListPriceGreaterThan(@Price AS INT)

AS
SET NOCOUNT ON;
SELECT Name, ProductNumber, ListPrice
FROM Production.Product
WHERE ListPrice > @Price;
GO

In Visual Studio 2012 create a new empty ASP.NET website. Right click it and add a App_Code folder. Right click the folder and add a new Item. Select to add a “ADO.NET Entity Data Model. I named it “AdventureWorksModel.edmx”. Click add, select “Generate from Database” and configure the connection to point the “AdventureWorksDatabase” (or the database you actually want to use).

In the next step of the Wizard, expand the “Tables” item and select the Production.Product table. Then expand the “Stored Procedures and Functions” item and select your stored procedure. Leave all the rest to their default values.

Note that as soon as you click finish, the Product Entity will only appear in the .edmx Model diagram despite the fact that we added a stored procedure too. Never mind about that. Add a new Web Form in the website with a textbox to get the ListValue and a GridView to display the results as follow:







    


    
    <div>
        Enter a price: 
        &nbsp;
        
        <br />
        
        
    </div>

In the code behind file (Press F7 while being at Design or Source View, or double click the button in Design View) configure to call the store procedure and bind the results to the GridView control, when the button is pressed. Make sure you have System.Data.Entity referenced to your website.

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnGetProducts_Click(object sender, EventArgs e)
    {
        using (AdventureWorks2012Entities entities = new AdventureWorks2012Entities())
        {
            if (!String.IsNullOrEmpty(txtListPrice.Text))
            {
                int value = Int32.Parse(txtListPrice.Text);
                if (value >= 0)
                {
                    var products = from p in entities.uspGetProductsWithListPriceGreaterThan(value)
                                   select p;
                    GridView1.DataSource = products;
                    GridView1.DataBind();
                }
            }
        }
        txtListPrice.Text = "";
    }
}

That’s it. Build and run your website. Enter a positive value into the text and click the button.