OracleDataAdapter, Fill method hangs, how do I handle connection terminations/drop offs/cut outs?

Question

I have a C# program that connects to a remote server to query data. The data is quite big so the query takes about 2 mins to finish. During this 2 min window, the internet went down. This resulted in the job being unable to finish with the program stuck in the getting data routine.

It established connection but during the select query it was cut off. Setting the command timeout to 30 seconds did not work. I need the query to fail when encountering this error because the program can handle failure but it cannot handle being stuck. Thanks!

UPDATE: included code

OracleConnection connection = new OracleConnection(connectionstring);
OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(new OracleCommand(query, connection));
oracleDataAdapter.SelectCommand.CommandTimeout = 30;

  DataSet dataSet = new DataSet();
  try
  {
    oracleDataAdapter.Fill(dataSet, table); //Hangs on this line when connection is lost
    return dataSet;
  }
  catch
  {
    throw;
  }
  finally
  {
    dataSet.Dispose();
    oracleDataAdapter.Dispose();
  }

UPDATE AGAIN:

What I need to do is handle this situation because I don't want a dangling process.

Simplest would be once the connection is lost is that the program will throw an error. That is what I don't know how to do. I assumed that the commandtimeout will fix it but it did not.

Answer 1

I see a couple issues with your statement, assuming it's using ODP.Net. Try the following:

DataSet dataSet = new DataSet();
using (OracleConnection connection = new OracleConnection(connectionstring))
{
  using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(new OracleCommand(query, connection)))
  {
    oracleDataAdapter.Fill(dataSet, table);
  }
}
return dataSet;

The using blocks will handle disposing of the connection and data adapter. In your example the connection did not get disposed which may have been part of your issue. Additionally I don't think you want to dispose the dataset if you intend to return it.

Since you were bubbling up the exception with a Throw I removed the exception handling. Keep in mind that this will bubble the exception so somewhere in your calling code chain you will need to catch the exception and handle it. If the app is just sitting there then be wary of any empty "catch" blocks eating exceptions.

Answer 2

There are a few duplicates reporting this problem, eg: System being hang when the connection is lost while adapter is filling the datatables

I found a good thread on MSDN where the OP answers:

I have solved this problem a while back, sorry i forgot to come and let you all know. I worked out that the code stopped executing at that line because (for some reason) there was already an open connection to the database.

Since DA.Fill would open a connection itself if there wasnt one previously opened, it was having a hissy fit and bombing out.

I solved this by putting Connection.Close(); before and after any connection to the database is needed.

Based on this we can see you are not explicitly opening a Connection to the Database. Suggest you do a:

connection.Open();

Also follow Steve Py's answer with the using to confirm you are closing the connection and disposing unmanaged resources.

Answer 3

Updated answer:

        DataSet dataset = new DataSet();

        using (OracleConnection connection = new OracleConnection(connection))
        {
            using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(new OracleCommand(query, connection)))
            {
                oracleDataAdapter.SelectCommand.CommandTimeout = 30;
                connection.Open();
                oracleDataAdapter.Fill(dataset, table);
            }
        }

        return dataset;