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

entities

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.

selectprocedure

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.

procedurevalues

Advertisements


Categories: ADO.NET, ASP.NET

Tags:

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Diary Of A Programmer

Because every day is worth noting

Chara Plessa

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack Developer | ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, MS candidate @LUMS, Grad & EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA.

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft |

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

Tony Sneed's Blog

A glimpse into the lives of Tony & Zuzana Sneed

%d bloggers like this: