获得返回值从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<> 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屋!