多次执行相同的 SQL 查询时,重用 SqlCommand 是否更好?

发布于 2024-10-10 10:23:34 字数 2072 浏览 5 评论 0原文

当使用相同的查询但不同的参数查询数据库时,是

  • 在单个使用中执行
  • 还是创建两个单独的查询更好?

单个使用的示例:

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

在我看来,第二个必须是首选,因为:

  • 它使人更清楚地看到SQL命令的处理位置以及执行次数,
  • 如果将来由于某种原因必须在一种情况下修改查询,但在另一种情况下则不需要修改查询,那么修改起来会更容易,
  • 第一个可以很容易地修改忘记SqlCommand.Parameters.Clear()

另一方面,第一个示例更明确地说明了以下事实:两种情况下的查询都是相同的,并且只有参数发生变化。

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

  • do it in a single using,
  • or to create two separate queries?

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:

  • 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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

柳若烟 2024-10-17 10:23:34

重用命令实例没有什么好处,除非您打算调用 准备

如果您要多次运行该命令(数十次或更多),那么您可能需要创建该命令、准备它、循环执行它,然后处理它。如果您多次运行该命令,性能提升将非常显着。 (不过,在准备之前,您可以添加一次参数 - 而不是像在第一个代码示例中那样每次都删除并重新添加它们。您应该每次更改参数的时间,而不是创建新参数。)

如果您只想运行该命令几次,那么性能不是问题,您应该选择您喜欢的风格。每次创建命令的好处是可以轻松提取到方法中,这样您就不必重复自己。

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.

欢烬 2024-10-17 10:23:34

如果“更好”是指“更清晰”或“更干净”,请使用单独的 SqlCommand 对象。这也将有助于以后重构您的代码。

如果“更好”的意思是“更快”,则重新使用 SqlCommand 将消除创建新 SqlConnection 的机会(相对于从连接池中拉出)。

If by "better" you mean "clearer" or "cleaner", use separate SqlCommand objects. This will also assist with refactoring your code down the road.

If by "better" you mean "faster", re-using the SqlCommand will eliminate the chance that a new SqlConnection will be created (vs. being pulled from the connection pool).

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文