how to change a sql timeout after command has been started

Question

Question: Is there a way to stop the Command.ExecuteNonQuery() before it is completed, without using timeout?

I have created a multi-threaded MySQL program that stores and run sql statements. Allowing a group of transactions run at the same time (because they do not modify the same tables). I have had to Disable the Timeout(set it to 0), because some SQL Statements can take several mins to run.
The problem comes when I want to stop the queries. Right now I have to wait till that current SQL Statement finished (like I said it could take several mins).

Below is code that works, from my existing knowledge (To help others):

MySqlConnectionStringBuilder ConnectionString = new MySqlConnectionStringBuilder();
ConnectionString.Server = ServerName;  // ServerName is a user defined string
ConnectionString.Database = DatabaseName; // DatabaseName is a user defined string
ConnectionString.UserID = UserName; // UserName is a user defined string
if (!Password.Equals(string.Empty)) // Password is a user defined string
{ ConnectionString.Password = Password; } // If Password string is not empty, then add it.
ConnectionString.AllowUserVariables = true; 

using (MySqlConnection connection = MySqlConnection(ConnectionString))
{
   try
   {
       connection.Open();
      DBTransaction Trans = connection.BeginTransaction();

       using (MySqlCommand Command = connection.CreateCommand())
       {
              foreach(String SQLCommandString in SQLCommands) // SQLCommands is user defined List<String>
              {
                   try
                   {
                          Command.CommandText = SQLCommandString; // SQLCommandString is a user defined string ex "UPDATE MyTable SET MyVar = 3 WHERE id = 3;"
                         NumOfRecordAffected = Command.ExecuteNonQuery();
                   }
                   catch (MySql.Data.MySqlClient.MySqlException ex)
                   {
                         Trans.RollBack();
                         // If code reaches here then there was a problem with the SQLCommandString executing.
                         throw ex;
                   }
                   catch (Exception ex)
                   {
                           // There was a problem other than with the SQLCommandString.
                           throw ex;
                   }
              }
       }
       Trans.Commit();
 }

score 1 · Accepted Answer · answered Apr 10 '13 at 16:31

1

You can't do this in a single threaded application, because control won't return until a) the query has completed execution or b) an exception forces control to return.

Instead, you can start and execute all transactions in a worker thread and close the connection from the original thread if needed. Calling MySqlConnection.Close() will rollback any pending transactions.

answered Apr 10 '13 at 16:31

PinnyM

35,165
3
73
81

Then this solution should be no problem for you :) – PinnyM Apr 11 '13 at 00:01
I think I realized what you were saying sI was driving home. Give an object that is running on another thread a reference to the MySqlConnection. The have that thread close the connection. I will have to try that when I get to work tomorrow. Thank You. :) – Amelia Cox Apr 11 '13 at 02:41
@AmeliaCox: Almost, I was saying the reverse scenario. Have the transactions execute in another worker thread by passing in the connection object. Then if you need to stop the execution manually, close the connection from the primary thread (which will rollback any running transactions), and terminate the worker thread. – PinnyM Apr 11 '13 at 02:45
I have found something this works sometimes. if (Connection.State != ConnectionState.Closed) Connection.CancelQuery(1); Connection.Close(); I have to run CancelQuery(1) so that it stops the sql early. Otherwise, Connection.Close() will wait till the sql statement is finished. (Problem - CancelQuery(1) sometimes works only sometimes. It is better, but still a prob.) – Amelia Cox Apr 11 '13 at 17:08
I didn't know how CancelQuery(int) worked before, it just sounded right on intellesense. But I did find this [link](http://typedescriptor.net/browse/members/1650376-MySql.Data.MySqlClient.MySqlConnection.CancelQuery(Int32)) that might explain it, don't know if it is correct. – Amelia Cox Apr 11 '13 at 17:08
CancelQuery doesn't appear to be supported (not sure where this code came from), but it makes sense to kill the query first using a separate kill connection. This code should work if you put it in a custom method. – PinnyM Apr 11 '13 at 17:19
CancelQuery(Int32) is a function in the MySql.Data.MySqlClient.MySqlConnection class. I am using MySql.Data ADO.Net driver for MySQL, Version 6.3.6.0, with a Runtime Version of v2.0.5.0727 – Amelia Cox Apr 11 '13 at 18:23
I don't know if it is correct, but [link] http://typedescriptor.net/browse/members/1650376-MySql.Data.MySqlClient.MySqlConnection.CancelQuery(Int32)(http://typedescriptor.net/browse/members/1650376-MySql.Data.MySqlClient.MySqlConnection.CancelQuery(Int32)) has code that might do the same thing. – Amelia Cox Apr 11 '13 at 18:24

how to change a sql timeout after command has been started

1 Answers1