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:
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
Code:
//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();
}
}
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
Comment