Close Reader or not?

[tshad]

I have a situation where I could

Try
Open a Connection
ExecuteReader
Close Reader
Catch

may not happen - Possible if this a Timer in a Windows Service

End Try

The problem is that the connection and/or the Reader may be open when I get
to the "Open a Connection".

The Connection and Reader are Container objects in VS and are Global.

If the Sql Server is down, I may get bounced out of the Openning Connection
or ExecuteReader statement and never get to the Catch because the delay
waiting for a response may be longer that the Timer delay.

So when I come back, how do I handle the Connect and Reader before the "Open
a Connection" code?

I know I could do something like:

if SqlConnection1.State = "Open" then Close it

But what about the Reader?

Do I need to do something about it? I don't know if it is open or not.

Thanks,

Tom

 

[Scott M.]

ADO.NET .Close() method calls do not throw execptions (any ADO.NET object
that supports a Close() method) if you try to close them and they are
already closed. So, it would never hurt you to call close, even if the
object may already be closed. As such, I always put an additional Close()
call in the Finally section of my Try...Catch statements since the Finally
is guaranteed to run, no matter whether an exception was thrown or not.

 

[Cor Ligthert [MVP]]

Tshad



Try
Open Connection
Try
Execute Reader
Catch
Show the error
End Try
Catch
Show the error
Finally
Connection Close
End Try

I hope this helps,

Cor

 

[tshad]

ADO.NET .Close() method calls do not throw execptions (any ADO.NET object
that supports a Close() method) if you try to close them and they are
already closed. So, it would never hurt you to call close, even if the
object may already be closed. As such, I always put an additional Close()
call in the Finally section of my Try...Catch statements since the Finally
is guaranteed to run, no matter whether an exception was thrown or not.

Would this be for both the Reader and the Connection?

My problem is that I am running this from a Windows Service with a timer
that executes every 10 seconds. So in affect I have the following (using my
previous example):

*****************************************
Sub Timer_event() (every 10 seconds)
Call Poller()
End Sub

Sub Poller()
Try
Open a Connection
ExecuteReader
Close Reader
Catch

may not happen - Possible if this a Timer in a Windows Service
Finally
Close Reader (or would closing the connection also close
the Reader?)
Close Connection
End Try
End Sub
*******************************************

What happens is if the Sql Server is down, I may be sitting at ExecuteReader
(or Open a Connection) for longer that 10 seconds.

Then what seems to happen is that the Timer_event() takes over again and the
Poller never finishes (or at least not that you can see). Poller is called
again and the same thing can happen.

I am not sure what happens if Poller is called many times without finishing.

But if I am sitting at ExecuteReader and Poller takes over, I may get a
message at ExecuteReader that says:

"There is already an open DataReader associated with this Connection which
must be closed first"

I may not even get to the Finally Clause in this case. So I was thinking of
putting an extra Close Reader and Close Connection in the Timer_Event before
calling the Poller or maybe in the Try section before the Open Connection.

Does that seem reasonable?

I shouldn't call Connection.State (don't know if the reader has a State
associated with it)?

Thanks,

Tom

 

[Jim Hughes]

Sounds like the service shoud use a thread from the thread pool for the
database work and go on with it's timer in the main service thread.

 

[Miha Markic [MVP C#]]

Hi,

I would rather do it in this way:

Try CreateConnection
Open a Connection
ExecuteReader
Close Reader CloseConnection
Catch

And instead of closing I would use dispose pattern wrapped in nice
try/finally blocks.

 

[David Browne]

<DIV>&quot;tshad&quot; &lt;[email protected]&gt; wrote in message

Try
Open a Connection
ExecuteReader
Close Reader
Catch

may not happen - Possible if this a Timer in a Windows Service

End Try

The problem is that the connection and/or the Reader may be open when I
get
to the "Open a Connection".

The Connection and Reader are Container objects in VS and are Global.

That is your problem. A DataReader should NEVER be a global variable.

David

 

[Scott M.]

It is for ANY ADO.NET class that has an Open method. So, yes, you could
close the reader and the connection BEFORE Poller attempts to open them and
do no harm.

 

[Edwin Knoppert]

I ever had an issue, if you close the connection you can watch the debug
imm. close the reader(s) as well.
That's very helpful.
While it's illogical it's a positive side-effect, iow.. no sweat.

 

[tshad]

<DIV>&quot;tshad&quot; &lt;[email protected]&gt; wrote in


That is your problem. A DataReader should NEVER be a global variable.

I agree.

And I made a slight mistake. The DataReader is not Global. The Connection
object is.

If I close just the Connection object I assume the DataReader is not closed.
In my other example of the Service where the Timer re-executes before the
Poller finishes- there is no way to close the DataReader. What happens will
my Service keep accumulating DataReader Objects?

Thanks,

Tom

 

[tshad]

Sorry, I just realized I made a slight mistake. The problem is that the
DataReader is Local and the Connection is Global

*****************************************
Sub Timer_event() (every 10 seconds)
Call Poller()
End Sub

Sub Poller()
Dim DR as SqlDataReader
Try
Open a Connection
DR = ExecuteReader
.....
DR.Close
Catch

may not happen - Possible if this a Timer in a Windows Service
Finally
DR.Close() (or would closing the connection also close
the Reader?)
Close Connection
End Try
End Sub
*******************************************

I can't close the DataReader if I am stuck at the "DR = ExecuteReader" line
and the Timer_event() re-executes and re-calls Poller and another DataReader
is generated. What happens to the old DataReader that is never called?

Is the old Poller still running and does it go to the finally clause behind
the scenes or does the re-executing of the poller wipe out the old
subroutine?

Thanks,

Tom