In C# how to get return value from stored procedure using ExecuteNonQuery

Question

I have the following query:

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

This compiles perfectly fine.

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

My code is as below:

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();
}

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

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

Any help with source code would be appreciated.

Thanks,

Pradeep

Answer 1

You need a parameter with Direction set to ParameterDirection.ReturnValue

Something like:

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

Answer 2

I don't see that you are returning the value. Please add the Return statement to return any value from the stored proc.