Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reuse of SqlConnection and SqlDataReader

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.

like image 767
PaulG Avatar asked Apr 13 '12 19:04

PaulG


People also ask

How do I dispose of SqlDataReader?

Note that disposing a SqlDataReader instantiated using SqlCommand. ExecuteReader() will not close/dispose the underlying connection. and the calling code just needs to dispose the reader thus: using(SqlDataReader reader = ExecuteReader(...))

What is the use of SqlDataReader?

A SqlDataReader is read-only, forward-only cursor that allows you to iterate through the result set and read one record at a time. To read and store the customer data we create a List of Customer records (C# record). It makes sense to iterate through the result set only if there are any rows in it.

What is SqlDataReader in asp net?

The ADO.NET SqlDataReader class in C# is used to read data from the SQL Server database in the most efficient manner. It reads data in the forward-only direction. It means, once it read a record, it will then read the next record, there is no way to go back and read the previous record.

What is SqlDataReader in VB net?

SqlDataReader Object provides a connection oriented data access to the SQL Server data Sources. ExecuteReader() in the SqlCommand Object send the SQL statements to SqlConnection Object and populate a SqlDataReader Object based on the SQL statement.


2 Answers

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.

like image 57
Chris Taylor Avatar answered Sep 21 '22 13:09

Chris Taylor


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.

like image 33
Rozwel Avatar answered Sep 20 '22 13:09

Rozwel