Should I call a parameter. Eliminate when reusing SqlCommand with translation?

Question

I encode a transaction manually in ADO.NET. An example that I use to reuse SqlCommand , which seems like a great idea.

However, I have added options to my team.

My question is: in the following code command.Parameters.Clear() correct? Or am I doing it wrong?

 using (var connection = new SqlConnection(EomAppCommon.EomAppSettings.ConnStr)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); SqlCommand command = connection.CreateCommand(); command.Transaction = transaction; try { foreach (var itemIDs in this.SelectedItemIds) { command.CommandText = "UPDATE Item SET payment_method_id = @batchID WHERE id in (@itemIDs)"; // IS THE FOLLOWING CORRECT? command.Parameters.Clear(); command.Parameters.Add(new SqlParameter("@batchID", batchID)); command.Parameters.Add(new SqlParameter("@itemIDs", itemIDs)); command.ExecuteNonQuery(); } transaction.Commit(); } catch (Exception ex) { MessageBox.Show("Failed to update payment batches, rolling back." + ex.Message); try { transaction.Rollback(); } catch (Exception exRollback) { if (!(exRollback is InvalidOperationException)) // connection closed or transaction already rolled back on the server. { MessageBox.Show("Failed to roll back. " + exRollback.Message); } } } } 

Answer 1

Since you repeatedly execute the same query, there is no need to clear them - you can add parameters outside the loop and just fill them inside.

 try { command.CommandText = "UPDATE Item SET payment_method_id = @batchID WHERE id in (@itemIDs)"; command.Parameters.Add(new SqlParameter("@batchID", 0)); command.Parameters.Add(new SqlParameter("@itemIDs", "")); foreach (var itemIDs in this.SelectedItemIds) { command.Parameters["@batchID"].Value = batchID; command.Parameters["@itemIDs"].Value = itemIDs; command.ExecuteNonQuery(); } transaction.Commit(); } 

Note - you cannot use parameters with IN, as you are here - this will not work.

Answer 2

In this state, you need it, since you need to set new parameter values, so its correct.

By the way, move

 command.CommandText = ".." 

outside the cycle, too, since it never changed.