473,320 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Cancelling SQL Command forcibly / Command timeout

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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. //Start main function
  3.  
  4. System.Data.OracleClient.OracleDataAdapter objAdapter;
  5. System.Data.OracleClient.OracleConnection con;
  6. System.Threading.Timer objTimer = null;
  7.  
  8. if (!this.makeConnection(ref con, ref strExcpConn))
  9.     {
  10.                 throw new ExceptionBRE(strExcpConn);
  11. }
  12.  
  13. objTimer = new System.Threading.Timer(new System.Threading.TimerCallback(TimeClick),con, 5000, 5050);
  14. objAdapter.Fill(objDataTable);
  15.  
  16. cmd.Connection.Close();
  17.  
  18.                 strB = BuildCommaSep(ref objDataTable);
  19.                 return strB.ToString();
  20.             }
  21.             catch (Exception ex)
  22.             {
  23.                 if (_iFlag == 1)
  24.                 {
  25.                     throw new Exception("Process kill by timer");
  26.                 }
  27.                 else
  28.                     throw ex;
  29.  
  30.             }
  31.             finally
  32.             {
  33.                 objTimer.Dispose();
  34.                 _iFlag = 0;
  35.  
  36.                 if (con != null && con.State != ConnectionState.Closed)
  37.                 {
  38.                     con.Close();
  39.                     con.Dispose();
  40.                 }
  41.             }
  42.         }
  43. //End main function
  44.  
  45.  
  46. //Timer callback function
  47.         //Closes and disposes OracleConnection object
  48.         //If command is still executing then OracleConnection.Close() will result in an exception
  49.         private void TimeClick(object state)
  50.         {
  51.             _iFlag = 1;
  52.  
  53.             System.Data.OracleClient.OracleConnection objConn = (System.Data.OracleClient.OracleConnection)state;
  54.             if (objConn != null && objConn.State != ConnectionState.Closed)
  55.             {
  56.                 objConn.Close();
  57.                 objConn.Dispose();
  58.             }
  59.         }
  60.  
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
Mar 8 '08 #1
4 3733
QVeen72
1,445 Expert 1GB
Hi,

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

Dim TCmd As New OleDbCommand
TCmd.CommandTimeout = 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
Mar 8 '08 #2
kenobewan
4,871 Expert 4TB
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.
Mar 8 '08 #3
Hi,

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

Dim TCmd As New OleDbCommand
TCmd.CommandTimeout = 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.
Mar 9 '08 #4
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
Feb 8 '12 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Cath B | last post by:
I am pretty sure I am getting a command timeout when execute a SQL procedure that has an output parameter. The code below is in an asp page that is called using RSGetASPObject. I want to be able...
3
by: rh0dium | last post by:
Hi all, Another newbie question. So you can't use signals on threads but you can use select. The reason I want to do this in the first place it I need a timeout. Fundamentally I want to run a...
0
by: Mike | last post by:
VB .NET 2003, Pocket PC 2003, CF 1.0 SP 2 Only when I use a proxy server, I'm getting this error when attempting to write data to a Stream object from an HTTPWebRequest object....
2
by: twigster | last post by:
Hi, I need to display in real time the output of a command line tool in a GUI written so far with Tkinter and Pmw. I've got a command line tool that I want to integrate to a GUI. The parameters...
0
by: Sanjay T | last post by:
Hi, We are using ASP.Net 2005 and SQL Server 2005. We are storing sessions in the database. From time to time we get the following exception in the application while the ASP.Net session tries...
4
by: Vicente García | last post by:
Hello!! sorry for my english, I've made an application that ran correctly for 3 days, It's a service, however in the third day the application failed because of "An existing connection was...
2
by: John Kotuby | last post by:
Hi all, I have set up a page with a Repeater and a SQLDatasource control, the data control suggested by online Help for use with the Repeater. I am getting Timeout Expired after 30 seconds. I...
2
by: Gil_H | last post by:
Hi, I'm trying to run a script over unix on a remote machine. In order to automate it, the procedure requests the following: 1. Using SSH connection. 2. Operating a command on the remote...
1
by: priravi | last post by:
Hi, I have a connection timeout of 400 and command timeout of 0 for a stored procedure . the stored procedure retrieves a long data and store it in dataset. I'm getting the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.