问题描述
我正在 ADO.NET 中手动编写事务代码.我正在使用的示例重用了 SqlCommand,这看起来是个好主意.
I'm coding a transaction manually in ADO.NET. The example I'm working from reuses the SqlCommand which seem like a fine idea.
但是,我在命令中添加了参数.
However, I have added parameters to my command.
我的问题是:在下面的代码中,command.Parameters.Clear() 是否正确?还是我做错了?
My question is: in the following code, is 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); } } } }
推荐答案
由于您重复执行相同的查询,因此没有必要清除它们 - 您可以将参数添加到循环外,只需将它们填充到内即可.
Since you're repeatedly executing the same query, it's unnecessary to clear them - you can add the 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(); }
注意 - 您不能在此处使用带有 IN 的参数 - 它不会工作.
Note - you can't use parameters with IN as you've got here - it won't work.