How to Retrieve Stored Procedure Output Parameters in Entity Framework

May 9, 2010

no comments

How to Retrieve Stored Procedure Output Parameters in Entity Framework


One question that How to Retrieve Stored Procedure Output Parameters in Entity Framework
raises from time to
time in EF forums
is how you can
retrieve stored
procedure output
parameters in EF
Function Import.
This post will show you how to do that.


The Stored Procedure


In the example I’m going to use the following stored procedure:



CREATE PROCEDURE dbo.SchoolBudgetForDateRange
    @StartDate DATETIME,
    @EndDate DATETIME,
    @Sum money output
AS
SET NOCOUNT ON;
SELECT @Sum = SUM(Department.Budget)
FROM Department 
WHERE StartDate BETWEEN @StartDate AND @EndDate

The stored procedure returns the school budget for a given date.
By of course the stored procedure could return that calculation
without using an output parameter.


Retrieving Stored Procedure Output Parameter


After creating a Function Import (which is explained here) we can
use the SchoolBudgetforDateRange method with the context we have.
In order to get an output parameter you need to supply an ObjectParameter
to the stored procedure call which holds the parameter name and type.
After the execution of the stored procedure you can retrieve the
parameter using the Value property of the ObjectParameter.
The following code shows how to that exactly what I wrote:



static void Main(string[] args)
{
  using (SchoolEntities context = new SchoolEntities())
  {
    var outputParameter = new ObjectParameter(“sum”, typeof(decimal));
    context.SchoolBudgetForDateRange(new DateTime(2007, 1, 1), 
      new DateTime(2008, 1, 1), 
      outputParameter);
    Console.WriteLine(outputParameter.Value);
  }
}

Summary


 


Once you need to retrieve output parameters from EF Function Imports,
you need to supply an ObjectParameter to hold the output. In the post
I showed how to do that.


Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>