Unable to close OledbDataReader to Sybase Database in VB.NET

Question

I don't seem to be able to close the OledbDataReader object after reading data from it. Here is the relevant code -

Dim conSyBase As New OleDb.OleDbConnection("Provider=Sybase.ASEOLEDBProvider.2;Server Name=xx.xx.xx.xx;Server Port Address=5000;Initial Catalog=xxxxxxxxxx;User ID=xxxxxxxx;Password=xxxxxxxxx;")

conSyBase.Open()

Dim cmdSyBase As New OleDb.OleDbCommand("MySQLStatement", conSyBase)
Dim drSyBase As OleDb.OleDbDataReader = cmdSyBase.ExecuteReader

Try

    While drSyBase.Read
     /*Do some stuff with the data here */

    End While

Catch ex As Exception

    NotifyError(ex, "Read failed.")

End Try

drSyBase.Close() /* CODE HANGS HERE */
conSyBase.Close()
drSyBase.Dispose()
cmdSyBase.Dispose()
conSyBase.Dispose()

The console application just hangs at the point at which I try to close the reader. Opening and closing a connection is not a problem, therefore does anyone have any ideas for what may be causing this?

Answer 1

This is a long-shot, but try moving your .Close() and .Dispose() lines in a Finally block of the Try. Like this:

Dim conSyBase As New OleDb.OleDbConnection("Provider=Sybase.ASEOLEDBProvider.2;Server Name=xx.xx.xx.xx;Server Port Address=5000;Initial Catalog=xxxxxxxxxx;User ID=xxxxxxxx;Password=xxxxxxxxx;")
conSyBase.Open()
Dim cmdSyBase As New OleDb.OleDbCommand("MySQLStatement", conSyBase)
Dim drSyBase As OleDb.OleDbDataReader = cmdSyBase.ExecuteReader
Try
  While drSyBase.Read
   /*Do some stuff with the data here */
  End While
Catch ex As Exception 
  NotifyError(ex, "Read failed.")
Finally
  drSyBase.Close() 
  conSyBase.Close()
  drSyBase.Dispose()
  cmdSyBase.Dispose()
  conSyBase.Dispose()
End Try

Answer 2

I found the answer!

Before

drSyBase.Close()

You need to call the cancel method of the Command object

cmdSyBase.Cancel()

I believe that this may be specific to Sybase databases

Answer 3

It's been a while since I used VB.NET, but the most safe way to handle this in C# is to use a "using" statement.

It's like an implicit try-catch and it makes sure all resources are closed/cancelled and disposed when the "using" ends.

using (OleDb.OleDbConnection connection = new OleDb.OleDbConnection(connectionString)) 
{
    DoDataAccessStuff();
} // Your resource(s) are killed, disposed and all that

Update: Found a link about Using statement in VB.NET 2.0, hope it helps.

Using conSyBase As New OleDb.OleDbConnection("Provider=Sybase.ASEOLEDBProvider.2;Server Name=xx.xx.xx.xx;Server Port Address=5000;Initial Catalog=xxxxxxxxxx;User ID=xxxxxxxx;Password=xxxxxxxxx;"), _
     cmdSyBase As New OleDb.OleDbCommand("MySQLStatement", conSyBase) 

    conSyBase.Open()
    Dim drSyBase As OleDb.OleDbDataReader = cmdSyBase.ExecuteReader

    Try
        While drSyBase.Read()

            '...'

        End While
    Catch ex As Exception
        NotifyError(ex, "Read failed.")
    End Try

    cmdSyBase.Cancel()
End Using