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.

Maybe helpful: http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060 — Tim Schmelter, Apr 13 '12 at 19:20
Does your above code gives any error? If not, then it's surely okay. — sarwar026, Apr 13 '12 at 19:21
You don't reuse the reader, the `ExecuteReader` creates a new reader. — ChrisWue, Apr 13 '12 at 19:23

Chris Taylor · Accepted Answer · 2012-04-13T19:28:48.940

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.

Thanks. So I guess I just need to close the reader object after every execution. — PaulG, Apr 13 '12 at 19:24
Actually I am supplying parameters for the SqlCommand, guess I should use myCommand.Parameters.Clear(). Thanks a lot. — PaulG, Apr 13 '12 at 19:36
@PaulG, as a rule of thumb whenever a class implement IDisposable you should be disposing it as early as possible. In the case of readers, they tie-up database server resources until they are closed or disposed so you want to dispose then as soon as you are finished with them. Now I see you are using MySQL so I do not know what the resource usage is there, but for server based DBMS systems like Oracle or SQL Server this is the case, and you should always assume this is the case. — Chris Taylor, Apr 13 '12 at 19:37

score 2 · Answer 2 · answered Apr 13 '12 at 19:29

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.

Reuse of SqlConnection and SqlDataReader

2 Answers2

Linked