How to cancel a running query

How to cancel a running query

Postby now77ak » Tue 09 Oct 2012 09:43

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 ?
now77ak
 
Posts: 1
Joined: Tue 09 Oct 2012 09:10

Re: How to cancel a running query

Postby Pinturiccio » Fri 12 Oct 2012 14:19

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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleCommand~ExecutePageReader.html
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle