Why do SQL connection leave parameters in?

Question

While coding with sqlite everytime i always had the exact number of parameters and when i executed the query i always had 0 parameters after it. I kept using the cmd object and it worked fine.

Now while porting to use sql server (2008) my SqlConnection has parameters left over from a successful command. Why? I seem to be able to create tables without the problem (then again i may have use a clone of an empty cmd since i use recursion). Does SqlCommand always leave the parameters in after a query? This always breaks the following query unless i do parameter.clear().

Should i create a new SqlCommand object? or use parameter.clear() each time? I'm somewhat confused.

Answer 1

I guess it's up to the provider whether or not it clears the parameter list each time you execute the command. Personally, I think the SqlCommand way makes more sense, because then I can do something like this:

var cmd = new SqlCommand("SomeSprocName", ...);
cmd.Parameters.Add("@param1", SqlDbType.NVarChar).Value = "some string";
cmd.Parameters.Add("@param2", SqlDbType.Int);

for(int i = 0; i < 10; i++)
{
    cmd.Parameters["@param2"].Value = i;
    cmd.ExecuteNonQuery();
}

That is, I can execute the same command over-and-over in a loop and only have to change the parameters that are actually different.

If you're executing a totally different command, then I would say it probably makes sense to just create another instance of the command object. It wouldn't hurt to call cmd.Parameters.Clear() and re-add the new parameters, but there are other properties on the command object that can affect the execution (e.g. CommandType, CommandTimeout, etc) and if you're executing a whole new command, it makes more sense to start from scratch.

Answer 2

Sure it leaves those there - you never told it otherwise.

What if you need to call the same SqlCommand hundreds of times in a row - do you want to keep re-creating all the parameters after each call?? Doesn't make a lot of sense to me....

My advice: use one SqlCommand per "command" or "query" you want to execute and set up its parameters as needed. I wouldn't "recycle" a single SqlCommand for a dozen different queries... just create a new one! That's definitely not an expensive operation.