C# command doesn't time out appropriately...why?

[troylenparker]

Hi,

I'm trying to conduct a search operation using C# and ADO.NET. I'm
using a DataAdapter object to execute an Oracle stored procedure, and
this particular command is known to take about 80 seconds to execute.
But I'm trying to set the CommandTimeout parameter so that the command
will time out after a specified number of seconds. For testing, I've
set this value very low...at 5 seconds.

Here is the shortened code (assume that the database connection
"dbConnection" is already open):

// Create the command object.
System.Data.OleDb.OleDbCommand Cmd =
Global.dbConnection.CreateCommand();
Cmd.CommandText = "{Call
DWG_CUSTOMER_ACCESS.CUSTOMER_SEARCH(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
Cmd.CommandTimeout = 5;

/* SET COMMAND PARAMETERS HERE */

// Get a new data set called "Response".
dsReturnSet = new DataSet("Response");

// Try the command query.
try
{
// Get a new data adapter from the command object.
System.Data.OleDb.OleDbDataAdapter daRequest = new
OleDbDataAdapter(Cmd);


// Fill the data set with the response. THIS IS THE LINE THAT
TAKES 80 SECONDS WHEN IT SHOULD TIME OUT AFTER 5 SECONDS.
daRequest.Fill(dsReturnSet);



// If the command is still executing/fetching when code execution
reaches this point, the request timed out.
if (Cmd.Connection.State == ConnectionState.Executing ||
Cmd.Connection.State == ConnectionState.Fetching)
{
Cmd.Cancel();
return ReturnCustomError("SEARCH TIMED OUT.");
}
}
catch (System.Exception E)
{
/* ERROR CATCHING CODE HERE */
}

Why doesn't the command time out after 5 seconds, as I've indicated in
the CommandTimeout parameter? Am I using the property incorrectly? In
this example, I want, after 5 seconds, to stop executing and I'd like
to determine that the command was unsuccessful due to the fact that it
exceeded the timeout, and then return a custom error message as such.
But the command will execute up to the full 80 seconds until it
completes, in effect ignoring the CommandTimeout parameter. Am I doing
something wrong?

 

[W.G. Ryan - MVP]

How are you determing the 80 seconds? I know it sounds dumb but is that
based on how long it takes the table to fill? It's possible that the
command has executed in under 5 seconds but filling the datatable, network
traffic etc is what's taking the time.

 

[troylenparker]

No, not a dumb question at all. I have executed this same query in a
different application...a Visual Basic application using ADO. The
specific parameters that we provided to the stored procedure are known
to take about 80 seconds to successfully complete. There is only one
record in our database that matches the search criteria, but it takes
about 80 seconds to successfully find the record. Our VB application
has a timeout set to 60 seconds, so that's how the problem originally
came to our attention. We knew that the matching record existed, but
only by increasing the timeout in our VB application, little by little,
to 80 seconds did the stored procedure call execute successfully.
We've spoken with our database administrator and they're going to do a
database reorg soon that will hopefully solve the problem.

So...we're trying to duplicate the same search in our web service
application, and since we know that this particular search will take
about 80 seconds to complete, I used the same parameters to test the
timeout feature of this web service routine (the code I posted above).
But...so I didn't have to agonizingly wait a full minute during my
testing, I just simply changed the CommandTimeout parameter to 5
seconds to get it to time out almost immediately and verify that the
timeout was working correctly.

I'm basically trying to execute the command, which of course is a call
to a stored procedure with the command parameters set appropriately.
If the command times out - in this example, in 5 seconds - I'd like to
cancel the command and return an error to the client notifying them
that the search timed out. And then, of course, if the command is
successful, I'd like to populate a DataSet object and return this
populated object as the return value of my function.

I know that there is only one matching record in the database, so upon
locating the record, it shouldn't take it but a microsecond to populate
the DataSet. But the command never times out after five seconds...I
put my watch on it and it takes up to the full 80 seconds before
control is returned to the next line of code after the line:

daRequest.Fill(dsReturnSet);

Hope that helps describe the problem in more detail...based on that,
any idea why the CommandTimeout would fail?