在C＃中如何使用的ExecuteNonQuery存储过程获取返回值 [英] In C# how to get return value from stored procedure using ExecuteNonQuery

问题描述

我有以下查询：

create proc [dbo].[DeleteParts] 
    @TransNo nvarchar (6), @fpart nvarchar(25) 
AS 
    DECLARE @Returns BIT 
    SET @Returns = 1 

    BEGIN 
       TRY  
          BEGIN TRANSACTION 

          DELETE FROM PARTABLE 
          WHERE TransNo = @TransNo and fpart = @fpart

          COMMIT 
       END TRY 
       BEGIN CATCH   
           Print 'Delete failed'    
           SET @Returns = 0      
           -- Any Error Occurred during Transaction. Rollback     
           IF @@TRANCOUNT > 0       
               ROLLBACK  -- Roll back 
       END CATCH

       RETURN @Returns

这编译完全正常。

在C＃中，我想执行这个查询并获得返回值。

In C#, I want to execute this query and get the return value.

我的code是如下：

using(System.Data.SqlClient.SqlCommand deletecommand = this._connection.CreateCommand())
{
   deletecommand.CommandText = "DeleteParts";
   deletecommand.CommandType = System.Data.CommandType.StoredProcedure;
   deletecommand.Parameters.AddWithValue("@TransNo", ItemSODBOM.SONO);
   deletecommand.Parameters.AddWithValue("@fpart", ItemSODBOM.fbompart);

   string ReturnValue = deletecommand.ExecuteNonQuery().ToString();
}

它没有给我任何的错误，而是它返回受影响的行数，我要回1或0。

It does not give me any error but instead it is returning number of rows affected, I want to return 1 or 0.

例如：如果删除操作成功则返回1，如果失败则返回0

Example: if delete operation success then return 1 and if it fails then return 0.

任何帮助，源$ C ​​$ C将AP preciated。

Any help with source code would be appreciated.

谢谢

普拉迪普

推荐答案

您需要用方向设置为 ParameterDirection.ReturnValue

是这样的：

SqlParameter returnParameter = deleteCommand.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
...
deleteCommand.ExecuteNonQuery();
...
int returnValue = (int) returnParameter.Value;

您存储过程需要返回，这当然返回​​值：

You Stored Procedure needs to return this return value of course:

create proc [dbo].[DeleteParts]      
    @TransNo nvarchar (6),   
    @fpart nvarchar(25)  
AS      
DECLARE @Returns BIT      
SET @Returns = 1     
...
RETURN @Returns

这篇关于在C＃中如何使用的ExecuteNonQuery存储过程获取返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！