从 ADO.NET 中的存储过程获取返回值 [英] Getting return value from stored procedure in ADO.NET

问题描述

我有一个存储过程，它在插入 @@identity 后返回唯一标识符.我在服务器资源管理器中尝试过，它按预期工作 @RETURN_VALUE = [identifier].

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

在我的代码中，我添加了一个名为 @RETURN_VALUE 的参数，它首先使用 ReturnValue 方向，而不是任何其他参数，但是当我使用 ExecuteNonQuery() 运行我的查询时，该参数仍然是空的.我不知道我做错了什么.

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.

存储过程

 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# 代码

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;

推荐答案

刚刚在我的盒子上试了一下，这对我有用:

Just tried on my box and this works for me:

在 SQL Server 中:

In SQL Server:

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

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

这篇关于从 ADO.NET 中的存储过程获取返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！