SqlDataReader do I need to call close?

  • Thread starter Thread starter CKKwan
  • Start date Start date
C

CKKwan

Connection.Open();

The execute and command and read something.

Before reading is complete, I decided to call:

Connection.Close();

Question:

Must I call DataReader.Close() before closing the connection?

Thanks in advance.
 
CK

You should simple exit the procedure and use the method DataReader.Close()
and then normally the Connection.Close()

However it seems to me that you haven't set your in fact offline actions in
a Try and Catch block.
Something you should always do in this kind of operations.

Cor
 
Thanks for the reply.

Yes, I do have Try ... Catch block, and I do aware that I need to
close the Connection, else it will not be released asap, andprobably
causing someone else to wait for the connection to be released.

However, is there any reason why I need to close the DataReader?

Well, C# is not as convenience as C++ where we can let the destructor
do the cleanup job for us when goes out of scope, that is why I'm
trying to find out exactly what need to be done.
 
CKKwan said:
Connection.Open();

The execute and command and read something.

Before reading is complete, I decided to call:

Connection.Close();

Question:

Must I call DataReader.Close() before closing the connection?
Click to expand...

First call Close on the reader, then Dispose.

There's a tiny problem with SqlDataReader btw. If your data reader
points to a large resultset from a complex query, and you fetch say 100
rows and then call Close() on it, the SqlDataReader will consume the
rest of the rows and then close the datareader. This can take a long
time. In these cases, try calling Cancel() first, as stated in the MSDN
documentation for SqlDataReader. One caveat: if the command is in a
transaction, it's better not to cancel.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
However, is there any reason why I need to close the DataReader?

Well, for one, you can only have one open DataReader on a conection at
any given time. So it's a scarce resource - open as last as possible
and close as quickly as possible is the name of the game!

Marc
 
Hi, have the same questions - is it mandatory to close a reader or it would
close by itself when closing connection and there is no difference? ok i need
cancel for some cases or close it to reuse connection, but in simple cases
does it really matter?
thank you.
 
sorry, but i need to be sure, lets say i use this snippet:
Connection.Open();
dr=cmd.ExecuteReader();
if(dr.Read())
str=dr[0].ToString();
Connection.Close();
/// end of page rpocessing in ASP.Net
will it be working ok? connection closed, datareader will dispose as going
out of scope? i'm not asking about best practice now but about do i need to
review bulk of code and projects to add dr.Close() everywhere or I can leave
it as is, as far as it seem to work and connections are closed without
calling dr.close()? or may be there is still some resource leak or somthing?
thank you.
 
Remember, unlike other languages (like VB6), you should not depend on the GC
to clean up orphaned .NET objects (that have fallen out of scope). IMHO,
you'll likely get away with this approach, except when things go wrong. When
there is an exception, you still need to close the Connection. I would code
(pseudo code).

Try
Connection.Open();
dr=cmd.ExecuteReader();
if(dr.Read())
str=dr[0].ToString();
Click to expand...
catch ...
Finally
Connection.Close();
Click to expand...
dr.close;
end try


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________



vik said:
sorry, but i need to be sure, lets say i use this snippet:
Connection.Open();
dr=cmd.ExecuteReader();
if(dr.Read())
str=dr[0].ToString();
Connection.Close();
/// end of page rpocessing in ASP.Net
will it be working ok? connection closed, datareader will dispose as going
out of scope? i'm not asking about best practice now but about do i need
to
review bulk of code and projects to add dr.Close() everywhere or I can
leave
it as is, as far as it seem to work and connections are closed without
calling dr.close()? or may be there is still some resource leak or
somthing?
thank you.
Click to expand...
 
In this code I would simple use the command.executescalar

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcomm...

Cor

vik said:
sorry, but i need to be sure, lets say i use this snippet:
Connection.Open();
dr=cmd.ExecuteReader();
if(dr.Read())
str=dr[0].ToString();
Connection.Close();
/// end of page rpocessing in ASP.Net
will it be working ok? connection closed, datareader will dispose as going
out of scope? i'm not asking about best practice now but about do i need
to
review bulk of code and projects to add dr.Close() everywhere or I can
leave
it as is, as far as it seem to work and  connections are closed without
calling dr.close()? or may be there is still some resource leak or
somthing?
thank you.
Click to expand...
Click to expand...

In fact, you can. When calling the method ExecuteReader, put as a
parameter CommandBehavior.CloseConnection .

Doing this way, closing the DataReader will imply closing the
Connection also.
 
Victor,

Exact, but connect next time your answers next time to the question from the
OP.

Now I was thinking whatI wrorte wrong.

Thanks in advance

Cor

"Victor Rodrigues" <[email protected]> schreef in bericht
In this code I would simple use the command.executescalar

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcomm...

Cor

sorry, but i need to be sure, lets say i use this snippet:
Connection.Open();
dr=cmd.ExecuteReader();
if(dr.Read())
str=dr[0].ToString();
Connection.Close();
/// end of page rpocessing in ASP.Net
will it be working ok? connection closed, datareader will dispose as
going
out of scope? i'm not asking about best practice now but about do i need
to
review bulk of code and projects to add dr.Close() everywhere or I can
leave
it as is, as far as it seem to work and connections are closed without
calling dr.close()? or may be there is still some resource leak or
somthing?
thank you.
Click to expand...
Click to expand...

In fact, you can. When calling the method ExecuteReader, put as a
parameter CommandBehavior.CloseConnection .

Doing this way, closing the DataReader will imply closing the
Connection also.
 
Back
Top