Reuse of SqlConnection and SqlDataReader

Question

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();

Thanks a lot.

Solution 1

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.

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.

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

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

Solution 2

I generally use Adapters so I am rusty on the details of the reader, but I think you are on the right track.

One item of note in your code is that each call to ExecuteReader should be generating a new data reader. You may be reusing the variable name, but the reference to the existing reader is discarded and replaced by a new one on each call. Point being, close the previous reader before using ExecuteReader to get a new one.