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

问题描述

我知道，有几乎相同标题的另一个问题，但它并没有回答我的问题。 我有一个存储过程，它返回插入后的唯一标识符（@@标识）。我试了一下在服务器资源管理器，它工作正常（@RETURN_VALUE = [标识符]）。 在我的code我增加了一个名为@RETURN_VALUE参数，返回值与第一个方向，比任何其他的参数，但是whene我跑我用的ExecuteNonQuery查询（）的参数仍然为空。我不知道我做了什么错。 我的存储过程：

  ALTER PROCEDURE dbo.SetAuction
 （
  @auctionID INT，
  @itemID INT，
  @auctionType TINYINT，
  @reservationPrice INT，
  @maxPrice INT，
  @auctionEnd日期时间，
  @auctionStart日期时间，
  @auctionTTL TINYINT，
  @itemName的nchar（50），
  @itemDescription为nvarchar（max），
  @CategoryID TINYINT，
  @CategoryName的nchar（50）
 ） 如
 如果@auctionID＆LT;＆GT; 0
  开始
   BEGIN TRAN T1

   UPDATE拍卖
   SET AuctionType = @auctionType，
     ReservationPrice = @reservationPrice，
     MaxPrice = @maxPrice，
     AuctionEnd = @auctionEnd，
     AuctionStart = @auctionStart，
     AuctionTTL = @auctionTTL
   WHERE AuctionID = @auctionID;

   更新项目
   组
    =了itemname @itemName，
    ItemDescription = @itemDescription
   哪里
    条目标识号=（SELECT的ItemID从拍卖WHERE AuctionID = @auctionID）;

   COMMIT TRAN T1

   返回@auctionID
  结束
 其他
  开始
   BEGIN TRAN T1
    INSERT INTO项目（了itemname，ItemDescription，类别id）
    VALUES（@itemName，@itemDescription，@CategoryID）;

    INSERT INTO拍卖（条目标识号，AuctionType，ReservationPrice，MaxPrice，AuctionEnd，AuctionStart，AuctionTTL）
    VALUES（@@ IDENTITY，@ auctionType，@ reservationPrice，@ maxPrice，@ auctionEnd，@ auctionStart，@ auctionTTL）;
   COMMIT TRAN T1
   RETURN @@ IDENTITY
  结束
 

和我的code是：

  cmd.CommandText =声明cmdtext;
                SqlParameter的RETVAL =新的SqlParameter（@ RETURN_VALUE，System.Data.SqlDbType.Int）;
                retval.Direction = System.Data.ParameterDirection.ReturnValue;
                cmd.Parameters.Add（RETVAL）;
                cmd.Parameters.AddRange（参数）;
                cmd.Connection =连接;

                connection.Open（）;
                cmd.ExecuteNonQuery（）;

                返回（INT）cmd.Parameters [@ RETURN_VALUE]值。
 

解决方案

刚试过在我的盒子，这对我的作品：

在SQL Server的：

  DROP PROCEDURE TestProc;
走
CREATE PROCEDURE TestProc
如
   RETURN 123;
走
 

在C＃

 字符串cnStr =服务器=;数据库=沙箱;集成安全性= SSPI;;
        使用（SqlConnection的CN =新的SqlConnection（cnStr））{
            cn.Open（）;
            使用（CMD的SqlCommand =新的SqlCommand（TestProc，CN））{
                cmd.CommandType = CommandType.StoredProcedure;
                的SqlParameter的returnValue =新的SqlParameter（）;
                returnValue.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add（的returnValue）;

                cmd.ExecuteNonQuery（）;
                Assert.AreEqual（123，（int）的returnValue.Value）;
            }
        }
 

I know that there is another question with nearly the same title, but it doesn't answer my 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 whene I run my query with ExecuteNonQuery() that parameter remains empty. I don't know what I've done wrong. My 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

And my code is:

                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:

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

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