Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

c#

ado.net

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

like image 497
Pradeep Avatar asked Feb 16 '11 11:02

Pradeep


People also ask

What does << mean in C?

<< is the left shift operator. It is shifting the number 1 to the left 0 bits, which is equivalent to the number 1 .

What is && operator in C?

The && (logical AND) operator indicates whether both operands are true. If both operands have nonzero values, the result has the value 1 . Otherwise, the result has the value 0 . The type of the result is int . Both operands must have an arithmetic or pointer type.


2 Answers

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
like image 75
Joe Avatar answered Oct 23 '22 06:10

Joe


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

like image 41
RemoteSojourner Avatar answered Oct 23 '22 06:10

RemoteSojourner