Cancelling SQL Command forcibly / Command timeout

[enggwaqas]

I have an Oracle stored procedure that takes approx 3 minutes to execute and I am using .net 2.0 data access provider for oracle. Now i want to cancel the execution of stored procedure from .net after few seconds, the code i wrote for this is:

 
//Start main function
 
System.Data.OracleClient.OracleDataAdapter objAdapter;
System.Data.OracleClient.OracleConnection con;
System.Threading.Timer objTimer = null;
 
if (!this.makeConnection(ref con, ref strExcpConn))
    {
                throw new ExceptionBRE(strExcpConn);
}
 
objTimer = new System.Threading.Timer(new System.Threading.TimerCallback(TimeClick),con, 5000, 5050);
objAdapter.Fill(objDataTable);
 
cmd.Connection.Close();
 
                strB = BuildCommaSep(ref objDataTable);
                return strB.ToString();
            }
            catch (Exception ex)
            {
                if (_iFlag == 1)
                {
                    throw new Exception("Process kill by timer");
                }
                else
                    throw ex;
 
            }
            finally
            {
                objTimer.Dispose();
                _iFlag = 0;
 
                if (con != null && con.State != ConnectionState.Closed)
                {
                    con.Close();
                    con.Dispose();
                }
            }
        }
//End main function
 
 
//Timer callback function
        //Closes and disposes OracleConnection object
        //If command is still executing then OracleConnection.Close() will result in an exception
        private void TimeClick(object state)
        {
            _iFlag = 1;
 
            System.Data.OracleClient.OracleConnection objConn = (System.Data.OracleClient.OracleConnection)state;
            if (objConn != null && objConn.State != ConnectionState.Closed)
            {
                objConn.Close();
                objConn.Dispose();
            }
        }
 

Working of above code is that: Forcibly cancel running SQL Command if it doesn't reply in specified time by Closing the connection to database and throw custom exception message (using _iFlag variable).

Though it's throwing the correct exception but after the command executes completely. i.e. Timer is set to 5 secs to kill a procedure that takes ~3 mins but i get response that process kill by time after 3 mins, can any one tell me why?

Or is there any better of doing this?
Thanks in advance

[QVeen72]

Hi,

Why dont you set the "CommandTimeOut " property...

Dim TCmd As New OleDbCommand
TCmd.CommandTim eout = 20 'In Seconds

Once execution starts, After waiting for 20 seconds, if there is no result, it is terminated and an error is Generated..

REgards
Veena

[kenobewan]

In my mind it may not be cancelling the request that is the problem here. If this was my application, I would spend time more redesigning/optimizing my stored procedures.

[enggwaqas]

Hi,

Why dont you set the "CommandTimeOut " property...

Dim TCmd As New OleDbCommand
TCmd.CommandTim eout = 20 'In Seconds

Once execution starts, After waiting for 20 seconds, if there is no result, it is terminated and an error is Generated..

REgards
Veena

Data access provider for oracle from MS and ODP.NET 9.xxx doesn't support CommandTimeOut property.

[Emmet M]

Hi,

I have got the same problem with my application recently, and I had to create the helper for defeasible asynchronous oracle database operations.
http://www.codeproject.com/script/Ar...spx?aid=325884