在 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.

我的代码如下:

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.

对源代码的任何帮助将不胜感激.

Any help with source code would be appreciated.

谢谢，

普雷迪普

推荐答案

你需要一个参数 Direction 设置为 ParameterDirection.ReturnValue

You need a parameter with Direction set to 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屋！