SQL Server Scope_identity存储过程在C#中返回null

我有一个名为DvdInsert的存储过程，它看起来像这样：

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'DvdInsert')
DROP PROCEDURE DvdInsert
GO
CREATE PROCEDURE DvdInsert 
(@RatingName char(10),
@FName nvarchar(30),
@LName nvarchar(30),
@Title nvarchar(125),
@ReleaseYear int,
@Notes nvarchar(150),
@DvdId int OUTPUT)
AS
BEGIN
INSERT INTO Director (FName, LName)
VALUES (@FName, @LName)
INSERT INTO Dvd (DirectorId, RatingId, Title, ReleaseYear, Notes)
VALUES ((SELECT DirectorId
FROM Director
WHERE FName = @FName AND LName = @LName),
(SELECT RatingId
FROM Rating
WHERE RatingName = @RatingName), @Title, @ReleaseYear, @Notes)
SET @DvdId = CAST(SCOPE_IDENTITY() AS INT);
END
GO

它应该返回id号，但在Visual Studio 2017中，我有代码：

public int Insert(DvdItem dvdItem)
{
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("DvdInsert", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@DvdId", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
string[] names = dvdItem.Director.ToString().Trim().Split(new char[] 
{ ' ' }, 2);
if (names.Length == 1)
{
cmd.Parameters.AddWithValue("FName", "");
cmd.Parameters.AddWithValue("LName", names[0]);
}
else
{
cmd.Parameters.AddWithValue("FName", names[0]);
cmd.Parameters.AddWithValue("LName", names[1]);
}
cmd.Parameters.AddWithValue("RatingName", dvdItem.Rating);
cmd.Parameters.AddWithValue("Title", dvdItem.Title);
cmd.Parameters.AddWithValue("ReleaseYear", dvdItem.RealeaseYear);
cmd.Parameters.AddWithValue("Notes", dvdItem.Notes);
cn.Open();
int i = 0;
object a = cmd.ExecuteScalar();
if (a != null)
i = (int)a;
if (cn.State == System.Data.ConnectionState.Open)
cn.Close();
return i;
}
}

我有一个Nunit测试来验证功能，但在调试模式下，我得到的返回值为0，而不是4

我的测试代码：

[Test]
public void CanAddDvd()
{
DvdItem dvdItem = new DvdItem();
var repo = new DvdRepositoryADO();
dvdItem.Rating = "R";
dvdItem.Director = "Hello";
dvdItem.Title = "World";
dvdItem.RealeaseYear = "2004";
dvdItem.Notes = "TESTING";
repo.Insert(dvdItem);
Assert.AreEqual(4, dvdItem.DvdId);
}

在我添加之前：

int i = 0;
object a = cmd.ExecuteScalar();
if (a != null)
i = (int)a;
if (cn.State == System.Data.ConnectionState.Open)
cn.Close();

我在这里得到了一个空引用异常：

object a = cmd.ExecuteScalar();

SQL Server中的表如下所示：

CREATE TABLE Dvd 
(
DvdId INT NOT NULL IDENTITY(1,1),
DirectorId INT NOT NULL,
RatingId INT NOT NULL,
Title NVARCHAR(125) NOT NULL,
ReleaseYear int NOT NULL,
Notes VARCHAR(150) NULL,
CONSTRAINT PK_Dvd_DvdId PRIMARY KEY (DvdId),
CONSTRAINT FK_Dvd_DirectorId
FOREIGN KEY (DirectorId) REFERENCES Director(DirectorId),
CONSTRAINT FK_Dvd_RatingId
FOREIGN KEY (RatingId) REFERENCES Rating(RatingId)
)

我不明白为什么我没有从存储过程中获得返回值。有什么想法吗？我是一个初学者，所以如果愿意，请把你的解释分解一下。

提前感谢您的帮助。

我有一张我在邮递员中收到的错误截图，如果这有助于点击这里

我的POST代码：

[Route("dvd/")]
[AcceptVerbs("POST")]
public IHttpActionResult Add(DvdItem dvdItem)
{
repo.Insert(dvdItem);
return Created($"dvd/{dvdItem.DvdId}", dvdItem);
}

我在调试时遇到一条错误消息，上面写着："System.Data.SqlClient.SqlException:'子查询返回了多个值。当子查询跟在=、！=、<、<=、>、>=之后时，或者当子查询用作表达式时，都不允许这样做。该语句已终止。'">

这是我的VS在调试模式下的图像：

这只是一个偶然的机会，但我的问题可能是我的子查询，我在其中插入了一个新的导演吗？

调用ExecuteNonQuery()方法后，您需要获得outout参数的值，并像这样读取：

int dvdID = 
Convert.ToInt32(cmd.Parameters["@DvdId"].Value);

或者将其分配给dvdItem.DvdId = dvdId;。

顺便说一下，你的测试是一个集成测试，而不是一个单元测试。即使是集成测试，它也是非常脆弱的，因为dvd id不会总是4，所以它会失败。尽管如此，它还是比使用调试器进行手动测试要好。

听起来你的问题有点深，不仅仅是异常，更多的是关于你的方法。

您在插入Director表时没有首先检查该直接目录是否已经存在。这可能会创建重复项，这意味着您的第一个子查询可能会返回多个结果。也许更改查询以检查direct是否存在是合适的，如果存在，则使用ID，否则插入并从scope_identity()获取
与其将评级名称传递给过程，不如传递评级ID。这意味着不需要第二个子查询，并且通过按ID而不是评级名称查找，效率也会高得多。

所以看起来你有一系列问题，我的第一个答案将解决未返回的返回值，这个答案应该可以帮助你解决异常，也可以帮助你创建一个更高效的解决方案。

如果我没记错，如果你试图通过参数返回一个值，那么你需要在SqlCommand中使用一个输出参数，并在执行查询后读取它。如果您想使用ExecuteScalar，那么SQL的最后一行只需要是"SELECT SCOPE_IDENTITY()"，那么它将在我们的"对象a"中。我还没有试过这个，所以请告诉我这是否有效。

我认为这可能是我的问题：

INSERT INTO Dvd (DirectorId, RatingId, Title, ReleaseYear, Notes)
VALUES ((SELECT DirectorId
FROM Director
WHERE FName = @FName AND LName = @LName),
(SELECT RatingId
FROM Rating
WHERE RatingName = @RatingName), @Title, @ReleaseYear, @Notes)

select DirectorId子查询返回多个值正如RatingId select语句一样，它将解释异常。

我仍然需要测试这个理论，尽管

更新。。。

所以我的理论是正确的

INSERT INTO Dvd (DirectorId, RatingId, Title, ReleaseYear, Notes)
VALUES ((SELECT TOP 1 DirectorId
FROM Director
WHERE FName = @FName AND LName = @LName),
(SELECT TOP 1 RatingId
FROM Rating
WHERE RatingName = @RatingName), @Title, @ReleaseYear, @Notes)

将TOP 1添加到select语句只返回1个值。就编码最佳实践而言，这可能不是最好的，但对SQL有了非常基本的理解，这是我目前拥有的最佳解决方案。

相关内容

最新更新

热门标签：