Page 1 of 1

How to cancel a running query

Posted: Tue 09 Oct 2012 09:43
by now77ak
Hi everybody,
Could you tell me how to cancel a query that takes a long time. For example select 1mln records. Like that:

Code: Select all

        public string ExecQuery(string cmd, out DataTable dt)
        {
            dt = new DataTable();
            string error = "";
            try
            {
                OracleCommand command = new OracleCommand(cmd, sqlConnection);
                using (OracleDataReader rdr = command.ExecuteReader())
                {
                    using (rdr)
                    {
                        dt.Load((IDataReader)rdr); // THIS LINE TAKES OVER 1 MINUTE
                    }
                }
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return error;
            }
            return error;
        }
As You can see, program hangs on a single line. I would like to have a Cancel button that will terminate this process.

Right now I run this code in another thread. Object that calls this method needs connection to database to be created and has dispose method - where I disconnect from database. So I use 'using' to create this object.

Code: Select all

          using (DatabaseProvider dp = new DatabaseProvider())
                {
                    this.Result = dp.ExecQuery(this.Query, out this.Dt);
                }
And now.. To stop that query I just kill this thread from another thread, but I don't think is a good idea (And it's not working well). Sometimes, after kill an error appears: NET: Unknown error 1. And then I can't execute nothing anymore. Always "NET: Unknown error 1" appears.

Could you tell me how to cancel this process ?

Re: How to cancel a running query

Posted: Fri 12 Oct 2012 14:19
by Pinturiccio
If you use the ExecuteDataReader method, you can't cancel the operation. But you can use several ExecutePageReader methods in the loop instead and leave the loop after the ExecutePageReader methods finish.

I'm modifying your sample. Suppose that the signal to exit is clicking a button, and in its handler the variable is changed to flag=true;

Code: Select all

OracleConnection conn = new OracleConnection("your connection string");
conn.Open();
int count = Int32.Parse(new OracleCommand("select count(*) from test", conn).ExecuteScalar().ToString());
OracleCommand comm = new OracleCommand("select * from test", conn);
DataTable dt = new DataTable();
int startRecord = 0, maxRecords=10000;

while (dt.Rows.Count < count)
{
	OracleDataReader reader = comm.ExecutePageReader(System.Data.CommandBehavior.Default, startRecord, maxRecords);
        dt.Load(reader);
        if (flag) break;
        startRecord += maxRecords;
}
conn.Close();
For more information, please refer to http://www.devart.com/dotconnect/oracle ... eader.html