从实体框架中的存储过程获取结果反馈 [英] Getting a result feedback from a stored procedure in Entity Framework

问题描述

  CREATE PROCEDURE [ dbo] .MyProc（@ParamRecDateTime [datetime]）
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
 INSERT INTO [dbo] .Table2 
 SELECT 
 ...，
 ... 
 FROM [dbo] .Table1 
 WHERE RecDateTime< = @ParamRecDateTime 
 
 DELETE FROM [dbo] .Table1 
 WHERE RecDateTime< = @ParamRecDateTime 
 END 
  pre> 
 
 
从SQL Server Management Studio中运行它，我完成工作并返回值= 0 
  DECLARE @return_value int 
 EXEC @return_value = dbo.MyProc @ParamRecDateTime ='2011-06-25 11：00：00.000'
 SELECT'返回值'= @ return_value 
  
但是当我使用Entity框架从应用程序调用相同的存储过程时，我也得到了工作完成但返回值为-1：
  int result = myrepository.MyProc（datetimePar）; 
 MessageBox.Show（result.ToString（））; 
  
我没有设法找到这个错误的解释，但发现这个令人沮丧的帖子，据说没有这种类型的退货代码的标准在SQL Server中。
 
 
 
 从Entity Framework调用存储过程执行结果时，什么是好的，可靠的方法，当存储过程不不要返回任何实体？ 
解决方案
一种方法是调用  ExecuteStoreCommand  ，然后传入 SqlParameter ，方向为输出：
  var dtparm = new SqlParameter（@ dtparm，DateTime.Now）; 
 var retval = new SqlParameter（@ retval，SqlDbType.Int）; 
 
 retval.Direction = ParameterDirection.Output; 
 
 context.ExecuteStoreCommand（exec @retval = MyProc @dtparm，retval，dtparm）; 
 
 int return_value =（int）retval.Value; 
  
最初我尝试使用 ReturnValue  ：
  retval.Direction = ParameterDirection.ReturnValue; 
 
 context.ExecuteStoreCommand（MyProc @dtparm，retval，dtparm）; 
  
但 retval.Value 永远是 0 。我意识到 retval 是执行 MyProc @dtparm 语句的结果，所以我改变它来捕获返回值的 MyProc 并将其作为输出参数返回。
 
In a SQL Server 2008 I have a simple stored procedure moving a bunch of records to another table:
CREATE PROCEDURE [dbo].MyProc(@ParamRecDateTime [datetime])
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [dbo].Table2 
SELECT 
...,
    ...
FROM [dbo].Table1 
      WHERE RecDateTime <= @ParamRecDateTime    

DELETE FROM [dbo].Table1 
      WHERE RecDateTime <= @ParamRecDateTime        
END
Running it from within SQL Server Management Studio, I get the job done and return value = 0
 DECLARE @return_value int
 EXEC    @return_value = dbo.MyProc @ParamRecDateTime = '2011-06-25 11:00:00.000'
 SELECT  'Return Value' = @return_value
But when I call the same stored procedure from an app using Entity framework, I also get the job done but the return value is "-1":
int result = myrepository.MyProc(datetimePar);
MessageBox.Show(result.ToString());
I didn't manage to find an explanation for this error, but found this discouraging post, where it's said that there is no standard for this type of return codes in SQL Server.

What is the good, reliable way of getting know of a Stored Procedure execution result when calling it from Entity Framework and when the Stored Procedure doesn't return any entities?
解决方案
One way to do it is to call ExecuteStoreCommand, and pass in a SqlParameter with a direction of Output:
var dtparm = new SqlParameter("@dtparm", DateTime.Now);
var retval = new SqlParameter("@retval", SqlDbType.Int);

retval.Direction = ParameterDirection.Output;

context.ExecuteStoreCommand("exec @retval = MyProc @dtparm", retval, dtparm);

int return_value = (int)retval.Value;
Originally I tried using a direction of ReturnValue:
retval.Direction = ParameterDirection.ReturnValue;

context.ExecuteStoreCommand("MyProc @dtparm", retval, dtparm);
but retval.Value would always be 0.  I realized that retval was the result of executing the MyProc @dtparm statement, so I changed it to capture the return value of MyProc and return that as an output parameter.

                        
这篇关于从实体框架中的存储过程获取结果反馈的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！