Safe way to close an SqlDataReader?

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
 
P

pvdg42

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

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
 
B

Bryan

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
 
B

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
 
P

pvdg42

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

Dim dtrReader As SqlDataReader

creates an empty reference.
 
T

Theo Verweij

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

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
 
B

Bryan

Theo -- thanks for the suggestion.

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

- Bryan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top