Safe way to close an SqlDataReader?

  • Thread starter Thread starter Bryan
  • Start date Start date
B

Bryan

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 said:
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
Click to expand...

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,

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
 
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 said:
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
Click to expand...
Right.
In your code, the statement:

Dim dtrReader As SqlDataReader

creates an empty reference.
 
Bryan said:
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
Click to expand...

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
 
Back
Top