Gil Fink, Microsoft MVP, is an expert in Web development and Microsoft data platform. He works as a senior architect at Sela Group. He is currently consulting for various enterprises and companies, where he architects and develops Web and RIA-based solutions. He conducts lectures and workshops for developers and enterprises who want to specialize in infrastructure and Web development. He is also a co-author of several Microsoft Official Courses and training kits. You can read his publications at his blog: http://blogs.microsoft.co.il/blogs/gilf. Gil is a DZone MVB and is not an employee of DZone and has posted 151 posts at DZone. You can read more from them at their website. View Full User Profile

How to Retrieve Stored Procedure Output Parameters in Entity Framework

05.13.2010
| 16835 views |
  • submit to reddit
One question that 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.


References
Published at DZone with permission of Gil Fink, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)