How to: Execute a Query Using a Stored Procedure (Entity Framework)

Article
04/15/2009

Many application developers and database administrators use stored procedures to enforce security, provide predictability, and encapsulate logic on data inside the database. Application code that retrieves data that is mapped to a stored procedure uses a function identified by the FunctionImport element. The basic elements of schema syntax required to map a stored procedure to an Entity Data Model (EDM) implementation are described in How to: Define a Model with a Stored Procedure (Entity Framework).

Two kinds of stored procedure mapping are supported by the EDM. For more information about mapping stored procedures that update data, see Stored Procedure Support (Entity Framework).

The examples in this topic are based on the Adventure Works Sales Model. To run the code in this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework).

Five entities are defined in the schema:

Address
Contact
Product
SalesOrderDetail
SalesOrderHeader

The following steps implement a client application and code that executes a stored procedure mapped to the GetOrderDetails FunctionImport in the conceptual schema of the data model. The function retrieves SalesOrderDetail entities related to a given SalesOrderHeader.(The FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID association in this model can do the same thing as this example.).

To create the stored procedure in the database

Create a console application.
Add a reference to the dll implemented in the topic How to: Define a Model with a Stored Procedure (Entity Framework).
Add references to System.Data.Entity and System.Runtime.Serialization.
Add the preprocessor directive for the AdventureWorksModel implemented in How to: Define a Model with a Stored Procedure (Entity Framework).

Example

The stored procedure is used with the required parameter for a SalesOrderHeaderId. You can find the syntax in the Object Browser as a method on the AdventureWorksEntities namespace: GetOrderDetails(int). The following code runs the stored procedure to return results that are then enumerated in a foreach loop.

Option Explicit On
Option Strict On
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports AdvWrksSalesModel

Module Module1
    Sub Main()
        Try
            Using db As AdvWksSalesEntities = New AdvWksSalesEntities()
                Dim soHeaderNumber As Integer = 43659
                For Each order As SalesOrderDetail _
                            In db.GetOrderDetails(soHeaderNumber)
                    Console.WriteLine("Header#: {0} " & _
                        "Order#: {1} ProductID: {2} Quantity: {3} Price: {4}", _
                        soHeaderNumber, order.SalesOrderDetailID, order.ProductID, _
                        order.OrderQty, order.UnitPrice)
                Next

            End Using

        Catch ex As System.Data.MappingException
            Console.WriteLine(ex.ToString())
        Catch ex As System.Data.CommandExecutionException
            Console.WriteLine(ex.ToString())
        End Try
    End Sub
End Module

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AdvWrksSalesModel;

namespace AdvWksSalesSProcs
{
    class Program
    {
        static void Main(string[] args)
        {
            using (AdvWksSalesEntities objCtx = 
                                new AdvWksSalesEntities())
            {
                try
                {
                    int soHeaderNumber = 43659;
                    foreach (SalesOrderDetail order in
                              objCtx.GetOrderDetails(soHeaderNumber))
                        Console.WriteLine("Header#: {0} " +
                        "Order#: {1} ProductID: {2} Quantity: {3} Price: {4}",
                        soHeaderNumber, order.SalesOrderDetailID,
                        order.ProductID,
                        order.OrderQty, order.UnitPrice);

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }

            }
        }
    }
}

The output will look like this:

Header#: 43659 Order#: 1 ProductID: 776 Quantity: 1 Price: 2024.9940
Header#: 43659 Order#: 2 ProductID: 777 Quantity: 3 Price: 2024.9940
Header#: 43659 Order#: 3 ProductID: 778 Quantity: 1 Price: 2024.9940
Header#: 43659 Order#: 4 ProductID: 771 Quantity: 1 Price: 2039.9940
Header#: 43659 Order#: 5 ProductID: 772 Quantity: 1 Price: 2039.9940
Header#: 43659 Order#: 6 ProductID: 773 Quantity: 2 Price: 2039.9940
Header#: 43659 Order#: 7 ProductID: 774 Quantity: 1 Price: 2039.9940
Header#: 43659 Order#: 8 ProductID: 714 Quantity: 3 Price: 28.8404
Header#: 43659 Order#: 9 ProductID: 716 Quantity: 1 Price: 28.8404
Header#: 43659 Order#: 10 ProductID: 709 Quantity: 6 Price: 5.7000
Header#: 43659 Order#: 11 ProductID: 712 Quantity: 2 Price: 5.1865
Header#: 43659 Order#: 12 ProductID: 711 Quantity: 4 Price: 20.1865

How to: Execute a Query Using a Stored Procedure (Entity Framework)

To create the stored procedure in the database

Example

See Also

Tasks

Concepts

Additional resources