Получение возвращаемого значения из хранимой процедуры в ADO.NET

Я знаю, что есть еще один вопрос с почти одним и тем же названием, но он не отвечает на мой вопрос. У меня есть хранимая процедура, которая возвращает уникальный идентификатор после вставки (@@identity). Я попробовал его в проводнике сервера и работает как ожидалось (@RETURN_VALUE = [идентификатор]). В моем коде я добавил параметр с именем "@RETURN_VALUE" с первым направлением ReturnValue, чем любые другие параметры, но когда я запускаю свой запрос с помощью ExecuteNonQuery(), этот параметр остается пустым. Я не знаю, что я сделал неправильно. Мой SPROC:

    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

И мой код:

                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;

Ответ 1

Просто попробовал на моем ящике, и это работает для меня:

В SQL Server:

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

В С#

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

Ответ 2

Я решил проблему: вам нужно установить SqlCommand.CommandType в CommandType.StoredProcedure, чтобы получить возвращаемые значения и/или выходные параметры. Я не нашел никакой документации об этом, но теперь все работает.

Ответ 3

Получаете ли вы значение EXEC в TSQL? Интересно, поможет ли рефакторинг TSQL (и используя SCOPE_IDENTITY():

так измените:

COMMIT TRAN T1
RETURN @@IDENTITY

SET @auctionID = SCOPE_IDENTITY()
COMMIT TRAN T1
RETURN @auctionID

(Я также изменил бы другой @@IDENTITY на SCOPE_IDENTITY())

В качестве небольшой оптимизации вы также можете использовать:

return (int)retval.Value;

но эта сторона вещей должна была работать "как есть" из того, что я вижу (следовательно, почему я фокусируюсь на TSQL).

Ответ 4

Некоторые могут также использовать этот простой и короткий метод для вычисления возвращаемого значения из SP

В SQL:

Create Table TestTable 
(
 Int Id
)

CREATE PROCEDURE Proc_TestProc
 @Id
 AS
   Begin
     Set NOCOUNT ON  //Use this line if you don't want to return any message from SQL

     Delete from TestTable where Id = @Id
     return 1

     Set NOCOUNT OFF //NOCOUNT OFF is Optional for NOCOUNT ON property
   End

Сервер Sql всегда возвращает значение типа Int.

и в С#

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TestConnectionString"].ToString()))
using (SqlCommand cmd = new SqlCommand("Proc_TestProc", conn))
{
 cmd.CommandType = CommandType.StoredProcedure;

 cmd.Parameters.AddWithValue("@Id", 1);
 var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
 returnParameter.Direction = ParameterDirection.ReturnValue;

 conn.Open();
 cmd.ExecuteNonQuery();
 var result = returnParameter.Value;
}

Вы также можете проверить возвращаемое значение в SQL с помощью этой команды:

DECLARE @return_status int;
EXEC @return_status = dbo.[Proc_TestProc] 1;
SELECT 'Return Status' = @return_status;
print 'Returned value from Procedure: ' + Convert(varchar, @return_status); // Either previous or this line both will show you the value of returned value

Ответ 5

Вы можете использовать стандартные способы, которые вы использовали ранее в обычных запросах, но в SqlCommand вы должны написать EXEC перед именем вашей процедуры хранения и не использовать CommandType следующим образом:

SqlConnection con = new SqlConnection(["ConnectionString"])
SqlCommand com = new SqlCommand("EXEC _Proc @id",con);
com.Parameters.AddWithValue("@id",["IDVALUE"]);
con.Open();
SqlDataReader rdr = com.ExecuteReader();
ArrayList liste = new ArrayList();
While(rdr.Read())
{
    liste.Add(rdr[0]); // if it returns multiple you can add them another arrays=> liste1.Add(rdr[1]) ..
}
con.Close();