SqlParameter 已被另一个 SqlParameterCollection 包含 - using() {} 作弊吗? [英] The SqlParameter is already contained by another SqlParameterCollection - Does using() {} cheat?

问题描述

在使用 using() {} (sic) 块时，如下所示，并假设 cmd1 不会超出第一个 using 的范围() {} 块，为什么第二块要抛出带有消息的异常

While using the using() {} (sic) blocks as shown below, and assuming that cmd1 does not live beyond the scope of the first using() {} block, why should the second block throw an exception with the message

SqlParameter 已被另一个 SqlParameterCollection 包含

The SqlParameter is already contained by another SqlParameterCollection

这是否意味着附加到 cmd1 的资源和/或句柄 - 包括参数 (SqlParameterCollection) - 在块末尾被销毁时不会被释放?

Does it mean that resources and/or handles - including the parameters (SqlParameterCollection) - attached to cmd1 are not released when its destroyed at the end of the block?

using (var conn = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True"))
{
    var parameters = new SqlParameter[] { new SqlParameter("@ProductId", SqlDbType.Int ) };

    using(var cmd1 = new SqlCommand("SELECT ProductName FROM Products WHERE ProductId = @ProductId"))
    {
        foreach (var parameter in parameters)
        {
            cmd1.Parameters.Add(parameter);                
        }
        // cmd1.Parameters.Clear(); // uncomment to save your skin!
    }

    using (var cmd2 = new SqlCommand("SELECT Review FROM ProductReviews WHERE ProductId = @ProductId"))
    {
        foreach (var parameter in parameters)
        {
            cmd2.Parameters.Add(parameter);
        }
    }
}

注意: 在第一个 using() {} 块的最后一个大括号之前执行 cmd1.Parameters.Clear() 将使您免于异常(并且可能尴尬).

NOTE: Doing cmd1.Parameters.Clear() just before the last brace of the first using() {} block will save you from the exception (and possible embarrassment).

如果您需要重现，您可以使用以下脚本来创建对象:

If you need to reproduce you can use the following scripts to create the objects:

CREATE TABLE Products
(
    ProductId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    ProductName nvarchar(32) NOT NULL
)
GO

CREATE TABLE ProductReviews
(
    ReviewId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    ProductId int NOT NULL,
    Review nvarchar(128) NOT NULL
)
GO

推荐答案

我怀疑 SqlParameter 知道"它属于哪个命令，并且在处理命令时不会清除该信息, 但是当你调用 command.Parameters.Clear() 时被清除.

I suspect that SqlParameter "knows" which command it's part of, and that that information isn't cleared when the command is disposed, but is cleared when you call command.Parameters.Clear().

我个人认为我首先会避免重复使用这些对象，但这取决于你:)

Personally I think I'd avoid reusing the objects in the first place, but it's up to you :)

这篇关于SqlParameter 已被另一个 SqlParameterCollection 包含 - using() {} 作弊吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！