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

Question

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

The SqlParameter is already contained by another 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);         }     } } 

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 

Answer 1

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 :)

Answer 2

Using blocks do not ensure that an object is "destroyed", simply that the Dispose() method is called. What that actually does is up to the specific implementation and in this case it clearly does not empty the collection. The idea is to ensure that unmanaged resources that would not be cleaned up by the garbage collector are correctly disposed. As the Parameters collection is not an unmanaged resource it is not entirely suprising it is not cleared by the dispose method.