I discovered an interesting bug today. While executing some SQL Stored Procedures via C# I was trying to reuse a SqlParameter. However, when the SqlParameter was added to a second SqlCommand, it would throw an error when trying to execute the second command. The error encountered was the “SqlParameter is already contained by another SqlParameterCollection“.
It seems that .Net’s garbage collection retains a reference to the SqlParameter used in the first SqlCommand. This reference seems to survive the closing and even disposing of the connection in use. As long as the SqlCommand object is in use, .Net seems to remember that it has already been used. To bypass this, simply call the sqlCommand.Parameters.Clear() method. You’ll need to readd the SqlParameter or SqlParameter[] to the SqlCommand and it’ll work.
