CODEWITHSUNDEEP
c#sqldatareader
ryantpayton
ryantpayton

Reputation: 405

Does while (reader.Read()) automatically close itself?

Usually when I am coding in C# I either write my SqlDataReader one of two ways:

reader.Read();

if (reader.HasRows)
{
    // Code here
}

reader.Close();

Or:

while (reader.Read())
{
    // Code here
}

In the first instance I open the SqlDataReader and close it. The second one I am just called reader.Read() in a while loop.

My question is: Does calling reader.Read() close itself after it has read all data? Or should I still call reader.Close()?

Upvotes: 2

Views: 3852

Answers (7)

Matías Fidemraizer
Matías Fidemraizer

Reputation: 64943

Or:

using(SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        // Code here
    }
}

...which calls IDisposable.Dispose if assigned object inside the using statement is an IDisposable when the using block ends (if it's not an IDisposable compiler will cry!), which is a syntactic sugar of:

SqlDataReader reader = cmd.ExecuteReader();

try
{
    while (reader.Read())
    {
        // Code here
    }
}
finally
{
    if(reader != null)
    {
        reader.Dispose();
    }
}

You can either call Close or Dispose, but use the using approach always in order to avoid leaving a disposable object non disposed if an exception is thrown before the Close or Dispose is called.

Update...

As some commenter has said, using block is also for who's vague and this way it's not possible that you forgot to call Dispose as it's implicitly-called.

BTW, this is subjective because who misses a Dispose call probably won't use using block... From my experience, there're still a lot of .NET developers who don't understand the importance of why an object implement IDisposable.

It's not just a cosmetic detail: if a type implements IDisposable is because it is mandatory to call Dispose either when you instantiate the IDisposable type and you don't need it anymore or during a specific moment in the application life-cycle. But either way, the IDisposable.Dispose call is mandatory.

My two cents about the topic will be that using block is a good way of not having to explain low-level details to unexperienced .NET developers. It's easier to define a team development rule / guide where you may state that code review won't pass if a IDisposable object isn't instantiated within an using block without a clear justification.

Upvotes: 3

Soner Gönül
Soner Gönül

Reputation: 98858

Does calling reader.Read() close itself after it has read all data?

No. Read() method just moves cursor to the next row. The default position is before the first record. That's why you have to use this method to read any data in your reader.

should I still call a reader.Close();

Yes but instead of that, use using statement to dispose your SqlDataReader automatically.

using(var reader = command.ExecuteReader())
{
     while (reader.Read())
     {
         // Code here
     }
} <-- your reader will be disposed here.

Upvotes: 6

Stephen Kennedy
Stephen Kennedy

Reputation: 21568

No, it does not. SqlDataReader implements IDisposable to release unmanaged resources - the easiest way to code this is to wrap your usage of the reader inside a using block:

using (var reader = getYourReader())
{
    while (reader.Read())
    {
         // work is done here
    }
} // leaving the using block reader.Dispose() will be called automatically

Upvotes: 0

Stu
Stu

Reputation: 2446

Yes, you need to close the connection.

SqlDataReader is IDisposable so the safest way to use it if you can is in a using statment:

using (SqlDataReader reader = GetSqlDataReader())
{
    while (reader.Read())
    {
        // Code here
    }
}

Upvotes: 0

bizzehdee
bizzehdee

Reputation: 21023

No, it will remain open until the GC cleans it up, which may or may not happen right away, or at all.

you should probably go with something like:

using(var reader = cmd.ExecuteReader())
{
    while(reader.Read())
    {
         //do stuff with row
    }
}

as the reader will be disposed after exiting the using and will be closed.

Upvotes: 1

poke
poke

Reputation: 388303

No, Read will not close it, nor will any other method do that without you telling it to do so. So you will have to close it directly all the time.

However, since SqlDataReader implements IDisposable, you should just wrap your reader in a using block:

using (SqlDataReader reader = GetReaderFromSomewhere())
{
    while (reader.Read())
    {
        // Code here
    }
}

Then you don’t need to worry about closing it manually, and it will be automatically closed as soon as you leave the using block.

Upvotes: 1

Felipe Oriani
Felipe Oriani

Reputation: 38638

No, it will not close your dataReader. When you dispose the object, then yes, it will close. As a good pratice and since your type implement the IDisposable, prefer using the using keyword.

using (var reader = command.ExecuteReader())
{
    while (reader.Read())
    {
      // ....
    }
}

It will close the reader for you implicity.

Upvotes: 0

Related Questions