Safe way to close an SqlDataReader?

I'm sure this is a basic question, but I'm just not getting it.

I'm using a Try / Catch block to surround my database access code. For
example:

Try
cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
cmdDBCommand.CommandType = CommandType.StoredProcedure
cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)

dtrReader = cmdDBCommand.ExecuteReader

If dtrReader.HasRows Then
dtrReader.Read()
bResult = dtrReader.Item("BillMonthly")
End If
dtrReader.Close()
Catch ex As Exception
bResult = False
End Try

The problem is this -- if there is a problem with the try block, we
jump to the Catch and the reader is not closed.

If I just blindly close it in the catch, I may get an error saying that
it's not open.

So, how can I safely check the reader to see if it needs to be closed?

Thanks in advance for your help!

- Bryan

"Bryan" <bmomalley@gmail.com> wrote in message
news:1150986232.272243.79050@m73g2000cwd.googlegroups.com...
> I'm sure this is a basic question, but I'm just not getting it.
>
> I'm using a Try / Catch block to surround my database access code. For
> example:
>
> Try
> cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
> cmdDBCommand.CommandType = CommandType.StoredProcedure
> cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
>
> dtrReader = cmdDBCommand.ExecuteReader
>
> If dtrReader.HasRows Then
> dtrReader.Read()
> bResult = dtrReader.Item("BillMonthly")
> End If
> dtrReader.Close()
> Catch ex As Exception
> bResult = False
> End Try
>
> The problem is this -- if there is a problem with the try block, we
> jump to the Catch and the reader is not closed.
>
> If I just blindly close it in the catch, I may get an error saying that
> it's not open.
>
> So, how can I safely check the reader to see if it needs to be closed?
>
> Thanks in advance for your help!
>
> - Bryan
>

Check to see if your SqlDataReader is open before attempting to close.

The IsClosed property may be useful.

Visual Basic (Usage)
Dim instance As SqlDataReader
Dim value As Boolean

value = instance.IsClosed



--
Peter [MVP Visual Developer]
Jack of all trades, master of none.

Peter,

Thanks for the response. That approach is what I had been doing, but
it returns:

Object reference not set to an instance of an object.

Here's the test example:


Dim dtrReader As SqlDataReader

Try
dtrReader.Read()
Catch ex As Exception
Finally
If Not dtrReader.IsClosed Then
dtrReader.Close()
End If
End Try

Since dtrReader hasn't been instanciated yet, just defined, the
IsClosed won't work.

I guess I need to figure out if it's been instanciated first, then if
it needs to be closed, right?

- Bryan


pvdg42 wrote:
> "Bryan" <bmomalley@gmail.com> wrote in message
> news:1150986232.272243.79050@m73g2000cwd.googlegroups.com...
> > I'm sure this is a basic question, but I'm just not getting it.
> >
> > I'm using a Try / Catch block to surround my database access code. For
> > example:
> >
> > Try
> > cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
> > cmdDBCommand.CommandType = CommandType.StoredProcedure
> > cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
> >
> > dtrReader = cmdDBCommand.ExecuteReader
> >
> > If dtrReader.HasRows Then
> > dtrReader.Read()
> > bResult = dtrReader.Item("BillMonthly")
> > End If
> > dtrReader.Close()
> > Catch ex As Exception
> > bResult = False
> > End Try
> >
> > The problem is this -- if there is a problem with the try block, we
> > jump to the Catch and the reader is not closed.
> >
> > If I just blindly close it in the catch, I may get an error saying that
> > it's not open.
> >
> > So, how can I safely check the reader to see if it needs to be closed?
> >
> > Thanks in advance for your help!
> >
> > - Bryan
> >
>
> Check to see if your SqlDataReader is open before attempting to close.
>
> The IsClosed property may be useful.
>
> Visual Basic (Usage)
> Dim instance As SqlDataReader
> Dim value As Boolean
>
> value = instance.IsClosed
>
>
>
> --
> Peter [MVP Visual Developer]
> Jack of all trades, master of none.

This is what I was getting at. Seems to work...

If Not IsNothing(dtrReader) Then
If Not dtrReader.IsClosed Then
dtrReader.Close()
End If
End If

"Bryan" <bmomalley@gmail.com> wrote in message
news:1150991981.177090.176770@g10g2000cwb.googlegroups.com...
> Peter,
>
> Thanks for the response. That approach is what I had been doing, but
> it returns:
>
> Object reference not set to an instance of an object.
>
> Here's the test example:
>
>
> Dim dtrReader As SqlDataReader
>
> Try
> dtrReader.Read()
> Catch ex As Exception
> Finally
> If Not dtrReader.IsClosed Then
> dtrReader.Close()
> End If
> End Try
>
> Since dtrReader hasn't been instanciated yet, just defined, the
> IsClosed won't work.
>
> I guess I need to figure out if it's been instanciated first, then if
> it needs to be closed, right?
>
> - Bryan
>
Right.
In your code, the statement:

Dim dtrReader As SqlDataReader

creates an empty reference.

Bryan wrote:
> I'm sure this is a basic question, but I'm just not getting it.
>
> I'm using a Try / Catch block to surround my database access code. For
> example:
>
> Try
> cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
> cmdDBCommand.CommandType = CommandType.StoredProcedure
> cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
>
> dtrReader = cmdDBCommand.ExecuteReader
>
> If dtrReader.HasRows Then
> dtrReader.Read()
> bResult = dtrReader.Item("BillMonthly")
> End If
> dtrReader.Close()
> Catch ex As Exception
> bResult = False
> End Try
>
> The problem is this -- if there is a problem with the try block, we
> jump to the Catch and the reader is not closed.
>
> If I just blindly close it in the catch, I may get an error saying that
> it's not open.
>
> So, how can I safely check the reader to see if it needs to be closed?
>
> Thanks in advance for your help!
>
> - Bryan
>

Use a nested try block:

Try
cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
cmdDBCommand.CommandType = CommandType.StoredProcedure
cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
dtrReader = cmdDBCommand.ExecuteReader

Try
If dtrReader.HasRows Then
dtrReader.Read()
bResult = dtrReader.Item("BillMonthly")
End If
Catch ex As Exception
'Errors from dtrReader goes here
bResult = False
Finally
'Close reader
dtrReader.Close()
End Try
Catch ex As Exception
'Errors from cmdDBCommand goes here
bResult = False
Finally
'Don't forget to dispose the Command
cmdDBComand.Dispose
End Try

Theo -- thanks for the suggestion.

The double Try/Catch block seems like overkill though...

- Bryan


Theo Verweij wrote:
> Bryan wrote:
> > I'm sure this is a basic question, but I'm just not getting it.
> >
> > I'm using a Try / Catch block to surround my database access code. For
> > example:
> >
> > Try
> > cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
> > cmdDBCommand.CommandType = CommandType.StoredProcedure
> > cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
> >
> > dtrReader = cmdDBCommand.ExecuteReader
> >
> > If dtrReader.HasRows Then
> > dtrReader.Read()
> > bResult = dtrReader.Item("BillMonthly")
> > End If
> > dtrReader.Close()
> > Catch ex As Exception
> > bResult = False
> > End Try
> >
> > The problem is this -- if there is a problem with the try block, we
> > jump to the Catch and the reader is not closed.
> >
> > If I just blindly close it in the catch, I may get an error saying that
> > it's not open.
> >
> > So, how can I safely check the reader to see if it needs to be closed?
> >
> > Thanks in advance for your help!
> >
> > - Bryan
> >
>
> Use a nested try block:
>
> Try
> cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
> cmdDBCommand.CommandType = CommandType.StoredProcedure
> cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
> dtrReader = cmdDBCommand.ExecuteReader
>
> Try
> If dtrReader.HasRows Then
> dtrReader.Read()
> bResult = dtrReader.Item("BillMonthly")
> End If
> Catch ex As Exception
> 'Errors from dtrReader goes here
> bResult = False
> Finally
> 'Close reader
> dtrReader.Close()
> End Try
> Catch ex As Exception
> 'Errors from cmdDBCommand goes here
> bResult = False
> Finally
> 'Don't forget to dispose the Command
> cmdDBComand.Dispose
> End Try