0

This is causing me a headache. I know this question (or atleast variants of it) has been asked many times but before one flags it as a duplicate please consider the following code:

    string myConnectionString =  myConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ToString();
     SqlConnection mySQLConnection;
     SqlCommand mySQLCommand;
     SqlDataReader mySQLDataReader;

    using (mySQLConnection = new SqlConnection(myConnectionString))
    {
       mySQLCommand = new SqlCommand("SELECT TOP 1 * FROM Table ORDER BY Id DESC", mySQLConnection);
       mySQLCommand.Connection = mySQLConnection;
       mySQLCommand.Connection.Open();
       using(mySQLDataReader = mySQLCommand.ExecuteReader())
       {
         if (mySQLDataReader.HasRows)
         {
             if (mySQLConnection.State == ConnectionState.Open)
             {
                while (mySQLDataReader.Read())
                {
                  //Perform Logic : If the last record being returned meets some condition then call the below method
                   MethodCalled();
                }            
            }
         }
      }
         MessageBox.Show("Connection state: " + mySQLConnection.State);
   }

I would like to find a way to either:

  1. Close the reader after it has finished reading
  2. Break out of the while-loop when it has finished reading and there are no more rows left

But I just keep on getting a SqlException stating the following:

invalid attempt to call read when reader is closed

Just from broad observation, I can trace that error is due to me returning data that contains one row only. The problem is that after it has read that row, the compiler goes back to While(mySQLDataReader.Read()){} and attempts to read through a table that does not contain any rows.

I attempted the following:

  1. Wrapping the ExecuteReader() from the command object in a using block so that it automatically closes the reader and the connection respectively once it has done reading like so:

    using(mySQLDataReader = mySQLCommand.ExecuteReader())
{
    //Logic performed
}

  2. Just before the closing brace of the while-loop, I tried checking if there are any more rows left/returned from the sql command and breaking out the loop once that condition is satisfied:

    if(mySQLDataReader.HasRows == false) //No more rows left to read
{
     break; //break out of loop
}

Both attempts were unsuccessful. How can I get around this?

Improve this question
9
  • This would be strange. I have used it numerous times. The Read() function returns false at the end of data and doesn't throw exception. Can you post more code so we see what you're doing? Commented Sep 5, 2017 at 9:07
  • Could you post an minimal example highlighting your problem? Commented Sep 5, 2017 at 9:08
  • 1
    Could you, please, provide more code? It seems that you're trying to Read out of using(...) {} scope: using(mySQLDataReader = mySQLCommand.ExecuteReader()) {...} ... mySQLDataReader.Read(); Commented Sep 5, 2017 at 9:09
  • Can you post the entire code block you are using that replicates this error? Commented Sep 5, 2017 at 9:09
  • What's the code you replaced with //Logic performed? It should be something like while(mySQLDataReader.Read()) { /* do stuff with rows */ }. The Read() method returns true while there are rows left to read, and false when you are reading the last row. Commented Sep 5, 2017 at 9:10

2 Answers 2

Reset to default
3

It must be one of the following 3 things:

  1. You're using Read() OUTSIDE the using block. Remember that using block will implicitly call Close and Dispose on your reader. Thus any Read() calls must be placed inside the using block.
  2. The body of your using block is explicitly closing the reader. This seems improbable.
  3. Apparently you have declared your mySQLDataReader at a higher level. It could be that some other (async) code is closing the reader. This also is unlikely. You shouldn't, in most cases, define a DataReader at global level.

Edit

Reading the full code block that you have posted now, I'd suggest a few changes. Can you run the following and tell us if it runs:

using (var mySQLConnection = new SqlConnection(myConnectionString))
{
   mySQLCommand = new SqlCommand("SELECT TOP 1 * FROM Table ORDER BY Id DESC", mySQLConnection, mySQLConnection);
   mySQLCommand.Connection.Open();
   using(mySQLDataReader = mySQLCommand.ExecuteReader())
   {
      while (mySQLDataReader.Read())
      {
        //Perform Logic : If the last record being returned meets some condition then call the below method
         MethodCalled();
      }            
   }
}

If this version runs fine, we can then dig the problem better.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

2. I don't call Dispose nor Close anywhere within the while loop
3. Yes I have declared the DataReader object globally and I have it used in my other functions without any problems so why would it be problematic now?
I added this before the closing brace of the while-loop and it worked as expected. You can update your answer after testing it. if(mySQLDataReader.HasRows == false || mySQLDataReader.Read() == false) { mySQLDataReader.Close(); break; }
1

If there is no data to iterate, while loop will not execute at all. Do you need to check for HasRows as such? Also, you should use CommandBehavior.CloseConnection when you are creating data reader. This will make sure that underlying connection is closed once you have read through it.

Should if call SqlDataReader.HasRows if I am calling SqlReader.Read

SQLDataReader Source Code 

using (SqlConnection mySQLConnection = new SqlConnection(myConnectionString))
{
using (SqlCommand mySQLCommand = new SqlCommand("SELECT TOP 1 * FROM Table ORDER BY Id DESC", mySQLConnection))
  {
    mySQLConnection.Open();

    SqlDataReader mySQLDataReader = mySQLCommand.ExecuteReader(CommandBehavior.CloseConnection);
     while (mySQLDataReader.Read())
       {
          //Code logic here
        }
        // this call to mySQLDataReader.Close(); will close the underlying connection
         mySQLDataReader.Close();
    }
     MessageBox.Show("Connection state: " + mySQLConnection.State);
}
Improve this answer

4 Comments

So it does turn out that it is good practice to use mySQLDataReader.HasRows) hence I haven't omitted it from the question.
if you want to do something with it, inside the if condition.
I added this before the closing brace of the while-loop and it worked as expected. You can update your answer after testing it. if(mySQLDataReader.HasRows == false || mySQLDataReader.Read() == false) { mySQLDataReader.Close(); break; }
Read() enhances SqlDataReader cursor to next record, so, as such, when you have to actually do something with records, do not use it in if condition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.