The SqlParameter is already contained by another SqlParameterCollection - Does using() {} cheat?

Question

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

The SqlParameter is already contained by another SqlParameterCollection

Does this mean that the resources and/or handles - with the parameters (SqlParameterCollection) - added to cmd1 are not released when it has been 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);

        }

    }

}

NOTE: Doing cmd1.Parameters.Clear() just before the last brace of the first using() {} block would be saving 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

Answer 1

I doubt that SqlParameter "knows" which command it is part of, and that information is not cleared when the command is disposed but is cleared when you call command.Parameters.Clear().

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

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.

For more information visit :