Call a stored procedure using the Entity Framework

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;

CREATE PROCEDURE dbo.uspGetProductsWithListPriceGreaterThan(@Price AS INT)

SELECT Name, ProductNumber, ListPrice
FROM Production.Product
WHERE ListPrice > @Price;

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:


        Enter a price: 
        <br />

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;
        txtListPrice.Text = "";

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


Categories: ADO.NET, ASP.NET


1 reply

  1. These are actually wonderful ideas in about blogging. You have touched some pleasant points here.
    Any way keep up wrinting!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: