SqlConnection和SqlDataReader的重用 [英] Reuse of SqlConnection and SqlDataReader

问题描述

如果我想在不同的表运行多个SELECT查询，我可以使用所有这些相同的SqlDataReader对象和SqlConnection的？请问以下是明智之举？ （我打这个快，因此它缺乏的try / catch）：

If I want to run multiple SELECT queries on different tables, can I use the same SqlDataReader and SqlConnection for all of them?? Would the following be wise?? (I typed this up fast, so it lacks try/catch):

MySqlCommand myCommand = new MySqlCommand("SELECT * FROM table1", myConnection);

myConnection.Open();
SqlDataReader myDataReader = myCommand.ExecuteReader();

while(myReader.Read())
{
    //Perform work.
}

myCommand.commandText = "SELECT * FROM table2";

myReader = myCommand.ExecuteReader();

while(myReader.Read())
{
    //Perform more work
}

myReader.Close();
myConnection.Close();

多谢了。

推荐答案

您可以使用他们每个人相同的连接，只要你不尝试从不同的线程在同一个连接并发执行多个查询。

You can use the same connection for each of them, as long as you do not try to execute multiple queries concurrently on the same connection from different threads.

对于数据读取器，你实际上并没有重复使用的阅读器，每次调用的ExecuteReader 返回一个新的阅读器的新实例，你是重使用的是，保持参照读者变量。在这里躺着一个问题，你只明确关闭最后一个读者，留下第一个在一段时间后进行GC'd。

As for the data reader, you are not actually re-using the reader, each call to ExecuteReader returns a new instance of a new reader, all you are re-using is the variable that maintains the reference to the reader. Here in lies a problem, you are only explicitly closing the last reader and leaving the first to be GC'd at some later time.

您可以重复使用的命令，但是记住，如果你提供的参数等，您将需要清除它们的下一个查询，除非它们应用到一个查询也是如此。

You can reuse the Command as well, but remember if you supply parameters etc. you will need to clear them for the next query unless they apply to the next query as well.

您应该使用尝试/终于块，以确保您清理资源，或这里是一个快速改变你的code使用使用语句，以确保资源的清理，即使有一个例外是$ P $从执行pvents的code中的其余部分。

You should use try/finally blocks to ensure that you clean up the resources, or here is a quick change to your code to use using statements to ensure resource clean-up even if there is an exception that prevents the rest of the code from executing.

using (var myConnection = GetTheConnection())
{
  myConnection.Open();

  var myCommand = new MySqlCommand("SELECT * FROM table1", myConnection))
  using (var myDataReader = myCommand.ExecuteReader())
  {
    while(myReader.Read())
    {
      //Perform work.
    }
  } // Reader will be Disposed/Closed here

  myCommand.commandText = "SELECT * FROM table2";
  using (var myReader = myCommand.ExecuteReader())
  {
    while(myReader.Read())
    {
      //Perform more work
    }
  } // Reader will be Disposed/Closed here
} // Connection will be Disposed/Closed here

注： GetTheConnection 只是一个占位符功能，您使用的是什么都机制，让你的连接实例

Note: GetTheConnection is just a place holder function for what ever mechanism you are using to get your connection instance.

这篇关于SqlConnection和SqlDataReader的重用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！