I added the ability to take an array of SqlParameters to some static database helper methods this week. The methods themselves follow the general pattern:
SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(sql,conn);
foreach (SqlParameter p in parms) {
cmd.Parameters.Add(p);
}
conn.Open;
using (conn) {
// Do stuff with the SqlCommand instance
}
When the method returns, cmd goes out of scope, and normally so would all the objects referenced by cmd. Yet, on the next call, even though I create a brand new SqlCommand instance, the error “SqlParameter is already contained in another SqlParameterCollection” is thrown. Somehow the original SqlParameterCollection is staying alive between calls.
The obvious work around is to call cmd.Parameters.Clear() before exiting the method, and indeed that works. But it seemed like I must have been missing some subtle “gotcha” so in the interest of not just band-aiding a problem I didn’t see or understand, I did some searching. It turns out that it really is a bug, though … see here and here.
