none
Problem executing a series of SQL Statements from ThreadPool RRS feed

  • Question

  • Hi,
        I have a  C# application in which i need to execute a series of SQL statement from ThreadPool using ThreadPool.QueueUserWorkItem(...) method.These SQL string are stored in a string Array
    as a string[] and are executed using a For Loop inside QueueUserWorkItem Waitcallback method.I want to show a ProgressWindow with a cancel button which is the callback also while
    this process is running.When user presses Cancel the thread is aborted.Whole of the sql statements are being executed in a single transaction.If any of sql satements encouter
    error and throws an SQLexception(comes in catch block), then we need to show the error to the user and ask him to ignore the error and continue with the execution of next statement
    and will come to commit transaction in the finally block once it is done or cancel to rollback the complete transaction and come out of the loop.This works fine when there are no errors in executing any of the sqls and also
    if an sql exception is encountered and user cancels it so every thing is Rollback properly.But once if there are too many sql exceptions and if user ignores all and continue then sometimes the thread gives error in Commit() in finally
    block saying there is no BeginTransaction corresponding to Commit.But we can see all changes are committed in database before that itself.Why again it is coming to finally and doing Commit().
    This happens only if we call this method to run as a thread using .QueueUserWorkItem(..).Other wise if we call the same process in a single method then there is no error in finally block commit in the same situation.Can any body help me to solve this issue.
    Here is the code i am using,

    public interface IProgressCallBack
        {
        void Begin(int minimum, int maximum );
            void Begin();
    .........................
    }

    public class frmProgressDialog : System.Windows.Forms.Form, Classes.IProgressCallBack
        {
    ..................................................
    }

    From Another Method i am calling

    frmProgressDialog progressDialog = new frmProgressDialog();
    System.Threading.ThreadPool.QueueUserWorkItem( new System.Threading.WaitCallback(ExecuteSQL), progressDialog);
    progressDialog.ShowDialog();

     private void ExecuteSQL(object pStatus)
            {
                IProgressCallBack callback = pStatus as IProgressCallBack;
                bool result = true;
          
                callback.Begin(0, lines.Length);

                int i = 0;
             
                ConnectionObject.BeginTransaction();  //ConnectionObject will set the transaction

                try
                {
                    foreach (string line in lines)// lines is a string Array
                    {
                        if (callback.IsAborting)
                        {
                            result = false;
                            ConnectionObject.RollbackTransaction();
                            break;
                        }
                        if (line.Length > 0)
                        {
                            try
                            {
                                callback.SetText(line);
                                System.Threading.Thread.Sleep(50);
                                callback.StepTo(i);
                                ++i;
                                BusinessObject.ExecuteScript(line);//will call ExecuteNonQuery(line)
                            }
                            catch (SqlException ex)
                            {
                                DialogResult dr = MessageBox.Show(ex.Message, MessageBoxButtons.OKCancel);
                                if (dr == System.Windows.Forms.DialogResult.Cancel)
                                {
                                    result = false;
                                    ConnectionObject.RollbackTransaction();
                                    break;
                                }
                            }
                        }
                    }
                }
                finally
                {
                    if (callback != null)
                    {
                        callback.End();
                    }
                    if (result)
                    {
                        ConnectionObject.CommitTransaction();
                    }
                    else
                    {
                        ConnectionObject.RollBackTransaction();
                    }
                }
            }
    Please here each sql statement  can be a DROP Procedure script, CREATE Procedure script, ALTER Procedure script, CREATE VIEW etc.In the above
    scenario where this problem is happening there are no internal  COMMIT happening also either from executing SPs or statements.I have verified the same using SQL Profiler.
    Please tell me why is happening only when we execute from thread pool.
    Friday, October 19, 2007 7:33 AM

All replies

  • Hi,
        With respect to my above problem, i need to get a solution for another problem also.

    In the above code

    "From Another Method i am calling

    frmProgressDialog progressDialog = new frmProgressDialog();
    System.Threading.ThreadPool.QueueUserWorkItem( new System.Threading.WaitCallback(ExecuteSQL), progressDialog);
    progressDialog.ShowDialog();"

    This code is in a method say CallExecutionThread() in a separate class  Say Class1.Now from MainForm  i am  calling
    Class1.CallExecutionThread();
    if (Class1.Status)
    {
    .......Do something
    }
    Here Status is a boolean property of Class1 which will have some values set after executing the thread.But here sometimes after the call from Class1.CallExecuteThread() immediately it goes to the next line ie, if (Class1.Status) with out even the operations on the thread is executed.How can i prevent this.How can i block the code execution if (Class1.Status) until the thread finishes.Please help.
    Friday, October 19, 2007 12:13 PM