它是更好地执行相同的SQL查询多次重复使用时的SqlCommand？ [英] Is it better to reuse SqlCommand when executing the same SQL query several times?

问题描述

在查询使用相同的查询，但不同的参数数据库，是它更好地：

When querying the database with the same query but different parameters, is it better to:

• 请它在一个单一的使用，


• 或创建两个单独的查询

一个使用的例子：<？ / STRONG>

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.");
}

使用两个单独的查询相同的代码示例：

// 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:

• 这使得它更清楚地看到SQL命令配置，它是多少次执行


• 很容易修改，如果在将来，由于某种原因，查询必须在一种情况下进行修改，但不能在其他


• 第一个可以很容易忘记 SqlCommand.Parameters.Clear（）。

• it makes it more clear to see where the SQL command is disposed and how much times it is executed,
• it is easier to modify if, in future, for some reason, the query must be modified in one case, but not in the other,
• the first one makes it easy to forget the 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.

推荐答案

有是收效甚微再使用命令的实例，除非你打算叫准备。

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.

这篇关于它是更好地执行相同的SQL查询多次重复使用时的SqlCommand？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！