OracleDataAdapter, no command timeout and fill method hang

252 Views Asked by At

I have a VB program using .NET Framework 3.5 and ODP.NET managed driver (Oracle.DataAccess.dll 2.112.3.0) that connects to a remote server(pooling is default) to query data.

I have assigned CommandTimeout to 5 seconds and tried to query data that takes about 10 seconds to finish. But OracleDataAdapter does not throw SQL time out error when connect to a remote server. Meanwhile OracleDataAdapter do throw SQL time out error when connect to a local database. It looks like the CommandTimeout did not work to a remote server. How to make CommandTimeout work to a remote server?

Another problem is program stuck in the fill method when internet connection lost during execution of method. Setting the CommandTimeout to 60 seconds did not work. Why CommandTimeout no working when connection lost during query data? Included code:

Dim connection As OracleConnection = New OracleConnection(connectionstring)  
Dim oracleDataAdapter As New OracleDataAdapter(connection.CreateCommand())
Dim dataset as New DataSet
Try
 oracleDataAdapter.SelectCommand.AddToStatementCache = False
 oracleDataAdapter.SelectCommand.NotificationAutoEnlist = False
 oracleDataAdapter.SelectCommand.CommandTimeout = 60
 oracleDataAdapter.SelectCommand.CommandText = str_SQL
 Dim int_Count As Integer = oracleDataAdapter.Fill(dataset, table)
 Return int_Count
Catch
 Throw
Finally
 Dataset.Dispose()
 oracleDataAdapter.Dispose()
End Try
0

There are 0 best solutions below