How to cancel transaction at SQL Server via C# code

Question

Hi.

I'm try to modify this example from MSDN for my needs
http://msdn.microsoft.com/en-us/library/1a674khd%28VS.80%29.aspx

I have one thread for GUI and one thread for calculations.

When user press cancel button, ExecuteReader must be stopped and transaction at server must be canceled.

So if cancel button is pressed i'm not waiting for ending of ExecuteReader and call EndExecuteReader. But when call transaction Rollback, get error that timeout period is expired. But as you see CommandTimeout is very big.

As workaround i tried to don't call Rollback. In this way transaction continue working at server for a long time (equal to durration of process without cancel ).

How to cancel transaction at server?

I rewrote few methods and got something like this:

private
 void
 MyMethod(object
 sender, System.ComponentModel.DoWorkEventArgs e)
        {
            SetGuiState(false
);
            string
 commandText = "MyCommand"

            
            RunCommandAsynchronously(commandText, GetConnectionString());

        }
private
 void
 RunCommandAsynchronously(
        string
 commandText, string
 connectionString)
        {
            SqlConnection connection = new
 SqlConnection(connectionString);

            try

            {
                SqlCommand command = new
 SqlCommand(commandText, connection);
                
                command.CommandTimeout = 6000;

                connection.Open();

                SqlTransaction transaction = connection.BeginTransaction("MyTran"
);

                command.Transaction = transaction;

                IAsyncResult result = command.BeginExecuteReader();

                while
 (!result.IsCompleted)
                {
                   // if user pressed cancel

                    if
 (CancellationPending)
                    {
                        break
;
                    }
                }

                SqlDataReader reader = command.EndExecuteReader(result);

                 //if user pressed cancel

                 if
 (!CancellationPending)
                {
                    DisplayResults(reader);
                    reader.Close();
                    transaction.Commit();
                }
                // if user dont pressed cancel

                else

                {
                    
                    reader.Close();
                    transaction.Rollback();
                }
            }
            catch
 (SqlException ex)
            {
                MessageBox.Show("Error: "
 + ex.Number + " "
 + ex.Message);
            }
            catch
 (InvalidOperationException ex)
            {
                MessageBox.Show("Error: "
 + ex.Message);
            }
            catch
 (Exception ex)
            {
                MessageBox.Show("Error: "
 + ex.Message);
            }
            finally

            {
                connection.Close();
            }
        }

        private
 void
 DisplayResults(SqlDataReader reader)
        {
            while
 (reader.Read())
            {
            }
        }

        private
 string
 GetConnectionString()
        {
            return
 @"Persist Security Info=true;Integrated Security=false;server=MyServer;
                            Pwd=MyPassword;User Id=MyUser;Asynchronous Processing=true"
;
        }

Thanks

PS. SQL Server 2005 SP3. C#.NET 3.5

Answer 1

Hi,

The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

The connection timeout period expired before the data source responded to the request. The connection timeout period is set through SQLSetConnectAttr, SQL_ATTR_CONNECTION_TIMEOUT

Have a look on this site:
http://www-01.ibm.com/support/docview.wss?uid=swg21190442
http://msdn.microsoft.com/en-us/library/ms713605(VS.85).aspx

Thanks
Binze

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Thanks.
I just  added Connection Timeout Keyword to my connection string and changed default value.