Getting return value from stored procedure in ADO.NET

Question

I have a stored procedure, which returns the unique identifier after insertion @@identity. I tried it in the server explorer and it works as expected @RETURN_VALUE = [identifier].

In my code I added a parameter called @RETURN_VALUE, with ReturnValue direction first, than any other parameters, but when I run my query with ExecuteNonQuery() that parameter remains empty. I don't know what I've done wrong.

Stored Procedure

 ALTER PROCEDURE dbo.SetAuction  (   @auctionID int,   @itemID int,   @auctionType tinyint,   @reservationPrice int,   @maxPrice int,   @auctionEnd datetime,   @auctionStart datetime,   @auctionTTL tinyint,   @itemName nchar(50),   @itemDescription nvarchar(MAX),   @categoryID tinyint,   @categoryName nchar(50)  ) AS  IF @auctionID <> 0   BEGIN    BEGIN TRAN T1        UPDATE Auction    SET  AuctionType   = @auctionType,      ReservationPrice = @reservationPrice,      MaxPrice    = @maxPrice,      AuctionEnd    = @auctionEnd,      AuctionStart   = @auctionStart,      AuctionTTL    = @auctionTTL    WHERE AuctionID    = @auctionID;        UPDATE Item    SET     ItemName  = @itemName,     ItemDescription = @itemDescription    WHERE     ItemID = (SELECT ItemID FROM Auction WHERE AuctionID = @auctionID);        COMMIT TRAN T1        RETURN @auctionID   END  ELSE   BEGIN    BEGIN TRAN T1     INSERT INTO Item(ItemName, ItemDescription, CategoryID)     VALUES(@itemName, @itemDescription, @categoryID);          INSERT INTO Auction(ItemID, AuctionType, ReservationPrice, MaxPrice, AuctionEnd, AuctionStart, AuctionTTL)     VALUES(@@IDENTITY,@auctionType,@reservationPrice,@maxPrice,@auctionEnd,@auctionStart,@auctionTTL);    COMMIT TRAN T1    RETURN @@IDENTITY   END 

C# Code

cmd.CommandText = cmdText; SqlParameter retval = new SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int); retval.Direction = System.Data.ParameterDirection.ReturnValue; cmd.Parameters.Add(retval); cmd.Parameters.AddRange(parameters); cmd.Connection = connection;  connection.Open(); cmd.ExecuteNonQuery();  return (int)cmd.Parameters["@RETURN_VALUE"].Value; 

Answer 1

Just tried on my box and this works for me:

In SQL Server:

DROP PROCEDURE TestProc; GO CREATE PROCEDURE TestProc AS    RETURN 123; GO 

In C#

        string cnStr = "Server=.;Database=Sandbox;Integrated Security=sspi;";         using (SqlConnection cn = new SqlConnection(cnStr)) {             cn.Open();             using (SqlCommand cmd = new SqlCommand("TestProc", cn)) {                 cmd.CommandType = CommandType.StoredProcedure;                 SqlParameter returnValue = new SqlParameter();                 returnValue.Direction = ParameterDirection.ReturnValue;                 cmd.Parameters.Add(returnValue);                  cmd.ExecuteNonQuery();                 Assert.AreEqual(123, (int)returnValue.Value);             }         } 

Answer 2

I solved the problem: you have to set SqlCommand.CommandType to CommandType.StoredProcedure in order to get return values and/or output parameters. I haven't found any documentation about that, but now everything works.