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

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

Solution 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.

Solution 3:

Adding cmd.Parameters.Clear(); after execution should be fine.

Solution 4:

using defines a scope, and does the automatic call of Dispose() for which we love it.

A reference falling out of scope will not make the object itself "disappear" if another object has a reference to it, which in this case will be the case for parameters having a reference to cmd1.