When querying the database with the same query but different parameters, is it better to:
Example of a single using:
using (SqlCommand addProduct = new SqlCommand(@"insert into [Products].[Products] ([Name], [Price]) values (@name, @price)", sqlConnection))
{
// Insert the first product.
addProduct.Parameters.AddWithValue("@name", "Product 1");
addProduct.Parameters.AddWithValue("@price", 41F);
int countAffectedRows = addProduct.ExecuteNonQuery();
Debug.Assert(countAffectedRows == 1, "Wrong number of rows affected.");
addProduct.Parameters.Clear();
// Insert the second product.
addProduct.Parameters.AddWithValue("@name", "Product 2");
addProduct.Parameters.AddWithValue("@price", 49.9);
countAffectedRows = addProduct.ExecuteNonQuery();
Debug.Assert(countAffectedRows == 1, "Wrong number of rows affected.");
}
Example of the same code using two separate queries:
// Insert the first product.
using (SqlCommand addProduct = new SqlCommand(@"insert into [Products].[Products] ([Name], [Price]) values (@name, @price)", sqlConnection))
{
addProduct.Parameters.AddWithValue("@name", "Product 1");
addProduct.Parameters.AddWithValue("@price", 41F);
int countAffectedRows = addProduct.ExecuteNonQuery();
Debug.Assert(countAffectedRows == 1, "Wrong number of rows affected.");
}
// Insert the second product.
using (SqlCommand addProduct = new SqlCommand(@"insert into [Products].[Products] ([Name], [Price]) values (@name, @price)", sqlConnection))
{
addProduct.Parameters.AddWithValue("@name", "Product 2");
addProduct.Parameters.AddWithValue("@price", 49.9);
int countAffectedRows = addProduct.ExecuteNonQuery();
Debug.Assert(countAffectedRows == 1, "Wrong number of rows affected.");
}
In my opinion, the second one must be preferred, because:
SqlCommand.Parameters.Clear()
.On the other hand, the first sample is more explicit about the fact that the query is the same in both cases, and that only parameters change.
Unlike reusing an instance where the text and parameters are kept the same, reusing an instance to clear everything before making it a "new" command has much less practical application (as opposed to actually creating a new command).
SQL stands for Structured Query Language. SQL commands are the instructions used to communicate with a database to perform tasks, functions, and queries with data. SQL commands can be used to search the database and to do other functions like creating tables, adding data to tables, modifying data, and dropping tables.
There's very little benefit to reusing the command instance, unless you're planning to call Prepare.
If you're going to run the command many times (dozens or more), then you probably want to create the command, prepare it, execute it in a loop, and then dispose it. The performance gains are significant if you're running the command many times. (You would add the parameters once, though, before you prepare -- not delete and re-add them every time like you're doing in your first code sample. You should change the parameters' values each time, not create new parameters.)
If you're only going to be running the command a handful of times, performance isn't an issue, and you should go with whichever style you prefer. Creating the command each time has the benefit that it's easy to extract into a method so you don't repeat yourself.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With