EF 接收OUTPUT参数的方法 How to Retrieve Stored Procedure Output Parameters in Entity Framework...

原文地址：http://blogs.microsoft.co.il/gilf/2010/05/09/how-to-retrieve-stored-procedure-output-parameters-in-entity-framework/ 

 

How to Retrieve Stored Procedure Output Parameters in Entity Framework

 

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.

 

----------------------------------------

自我测试

ALTER PROC [dbo].[usp_AddTeacherInfo]
@Name NVARCHAR(50),
@Phone NVARCHAR(50),
@Address NVARCHAR(50),
@Age INT,
@Sum UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @TmpID UNIQUEIDENTIFIER
SET @TmpID = NEWID()
INSERT INTO dbo.Teacher
(ID, Name, Phone, Address, Age )
VALUES ( @TmpID,@Name,@Phone,@Address,@Age)

SET @Sum = @TmpID
SET NOCOUNT OFF
END

 

public void AddTeacherInfo()
{
using (DemoDBEntities entity = new DemoDBEntities())
{
var outputParameter = new ObjectParameter("sum", typeof(Guid));

var dd = entity.usp_AddTeacherInfo("dapeng", "021-99999999", "address222", 86, outputParameter);

}
}