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