I need to send a list of commands to SQL 2008 using ADO.NET, to be executed one after the other. Should I create a new SQLCommand for each SQL that I'm sending? Or reuse the same SQLCommand and just change the CommandText property? Thanks, Nestor
+4
A:
SqlCommands are pretty light-weight. You are safe to create a new one each time.
There are complications with parametrized commands where you need to clear and reset all parameters, and at that point, creating a new command is clean, easy to understand, and effective.
In addition, it is usually OK to use a new SqlConnection each time as well. The automatic, built-in connection pooling is the "magic" that makes this efficient.
I use this:
public void ExecuteQuery(string query)
{
this.ExecuteQuery(query, null);
}
public void ExecuteQuery(string query, Dictionary<string, object> parameters)
{
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
if (parameters != null)
{
foreach (string parameter in parameters.Keys)
{
cmd.Parameters.AddWithValue(parameter, parameters[parameter]);
}
}
cmd.ExecuteNonQuery();
}
}
}
John Gietzen
2009-10-05 03:46:58