Best way to run long running Sql Scripts in winforms and keep UI responsive

Question

I have been working to write a SQL installer for my application and haven't been given a lot of time to work on it therefore its a bit dirty. My UI thread collects values from the form, plugs them into a series of SQL scritps and then one by one kicks them of "asynchronously" using SMO. Here is a sample of the calling method on the UI thread:

        private static bool BeginScriptExecution(Dictionary<string, string[]> scripts)
        {
            try
            {
                foreach (var script in scripts)
                {
                    if (script.Key.Length > 0)
                    {
                        SqlConnection conn = new SqlConnection();
                        conn = ReplaceDatabaseName(GetConnectionString(), script.Value[0]);
                        if (TestSqlConnection())
                        {
                            if (ConfigurationManager.AppSettings["useSMO"] == "1")
                            {

                                if (!RunDatabaseCommandsSmo(conn, script.Key, script.Value[1]).Result)
                                {
                                    throw new Exception("Script failed to run.  Error in SMO functions.");
                                }
                            }
                            else
                            {
                                if (!RunDatabaseCommandsNonSmo(conn, script.Key, script.Value[1]))
                                {
                                    throw new Exception("Script failed to run.  Non-SMO related failure.");
                                }
                            }
                        }
                        else
                        {
                            throw new Exception("Connection not available.  Script failed to run");
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                return false;
            }
        }

Here is my async method to run the command using SMO:

        public static async Task<bool> RunDatabaseCommandsSmo(SqlConnection connectionString, string scriptText, string scriptName)
        {
            Helper.UpdateProgressLabel(scriptName);
            bool isSuccess = false;

            try
            {
                ServerConnection srvCon = new ServerConnection(connectionString);
                Server server = new Server(srvCon);
                //script = scriptText;
                if (scriptName.Contains("Creating Database")||scriptName.Contains("Building objects"))
                {
                    try
                    {
                        isSuccess = await Task.Run(() => RunCommand(scriptText, server))
                            .ConfigureAwait(continueOnCapturedContext:false);
                        return isSuccess;
                    }
                    catch(Exception ex)
                    {
                        log.Error("Cannot create database");
                        log.Error(ex.StackTrace.ToString());

                        return false;
                    }
                }
                else
                {
                    try
                    {
                        //server.ConnectionContext.ExecuteNonQuery(scriptText);
                        isSuccess = await Task.Run(() => RunTransaction(scriptText, server, srvCon))
                            .ConfigureAwait(continueOnCapturedContext: false);

                        return isSuccess;
                    }
                    catch (Exception ex)
                    {
                        log.Error(string.Format("Error writing transaction from script {0}.  Installation halted - check inner exception.", scriptName));
                        log.Error(ex.ToString());
                        log.Error(ex.StackTrace.ToString());

                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error writing transaction from script {0}.  Installation halted - check inner exception.", scriptName));
                log.Error(ex.StackTrace.ToString());

                return false;
            }
        }

Here is the code running the transactions:

        static bool RunCommand(string script, Server server)
        {
            try
            {
                server.ConnectionContext.ExecuteNonQuery(script);
                return true;
            }
            catch(Exception ex)
            {
                log.Error(ex.ToString());
                return false;
            }

        }

        static bool RunTransaction(string script, Server server, ServerConnection srvCon)
        {
            try
            {
                srvCon.BeginTransaction();
                server.ConnectionContext.ExecuteNonQuery(script);
                srvCon.CommitTransaction();
                return true;
            }
            catch (Exception ex)
            {
                srvCon.RollBackTransaction();
                log.Error(ex.ToString());
                return false;
            }

        }

Before I started moving towards async I was running all on the UI thread and the form would give me "Not Responding" until the script returned completed. Once I moved to this style of async the app is a bit more responsive and I no longer get not responding, but I am not sure of this method and whether or not it is correct use of async. Can someone let me know how I can alter my code to make this work correctly? There are 4 scripts that run and must run in specific order meaning script 2 cannot run until script 1 returns.

Answer 1

A good way to use asynchronous tasks is using a background worker,

take a look at the code example here: Background Worker

Answer 2

If B.G. is the way you go, here is an example: How to: Implement a Form That Uses a Background Operation

using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Threading;
using System.Windows.Forms;

namespace BackgroundWorkerExample
{   
    public class FibonacciForm : System.Windows.Forms.Form
    {   
        private int numberToCompute = 0;
        private int highestPercentageReached = 0;

        private System.Windows.Forms.NumericUpDown numericUpDown1;
        private System.Windows.Forms.Button startAsyncButton;
        private System.Windows.Forms.Button cancelAsyncButton;
        private System.Windows.Forms.ProgressBar progressBar1;
        private System.Windows.Forms.Label resultLabel;
        private System.ComponentModel.BackgroundWorker backgroundWorker1;

        public FibonacciForm()
        {   
            InitializeComponent();

            InitializeBackgroundWorker();
        }

        // Set up the BackgroundWorker object by 
        // attaching event handlers. 
        private void InitializeBackgroundWorker()
        {
            backgroundWorker1.DoWork += 
                new DoWorkEventHandler(backgroundWorker1_DoWork);
            backgroundWorker1.RunWorkerCompleted += 
                new RunWorkerCompletedEventHandler(
            backgroundWorker1_RunWorkerCompleted);
            backgroundWorker1.ProgressChanged += 
                new ProgressChangedEventHandler(
            backgroundWorker1_ProgressChanged);
        }

        private void startAsyncButton_Click(System.Object sender, 
            System.EventArgs e)
        {
            // Reset the text in the result label.
            resultLabel.Text = String.Empty;

            // Disable the UpDown control until 
            // the asynchronous operation is done.
            this.numericUpDown1.Enabled = false;

            // Disable the Start button until 
            // the asynchronous operation is done.
            this.startAsyncButton.Enabled = false;

            // Enable the Cancel button while 
            // the asynchronous operation runs.
            this.cancelAsyncButton.Enabled = true;

            // Get the value from the UpDown control.
            numberToCompute = (int)numericUpDown1.Value;

            // Reset the variable for percentage tracking.
            highestPercentageReached = 0;

            // Start the asynchronous operation.
            backgroundWorker1.RunWorkerAsync(numberToCompute);
        }

        private void cancelAsyncButton_Click(System.Object sender, 
            System.EventArgs e)
        {   
            // Cancel the asynchronous operation.
            this.backgroundWorker1.CancelAsync();

            // Disable the Cancel button.
            cancelAsyncButton.Enabled = false;
        }

        // This event handler is where the actual,
        // potentially time-consuming work is done.
        private void backgroundWorker1_DoWork(object sender, 
            DoWorkEventArgs e)
        {   
            // Get the BackgroundWorker that raised this event.
            BackgroundWorker worker = sender as BackgroundWorker;

            // Assign the result of the computation
            // to the Result property of the DoWorkEventArgs
            // object. This is will be available to the 
            // RunWorkerCompleted eventhandler.
            e.Result = ComputeFibonacci((int)e.Argument, worker, e);
        }

        // This event handler deals with the results of the
        // background operation.
        private void backgroundWorker1_RunWorkerCompleted(
            object sender, RunWorkerCompletedEventArgs e)
        {
            // First, handle the case where an exception was thrown.
            if (e.Error != null)
            {
                MessageBox.Show(e.Error.Message);
            }
            else if (e.Cancelled)
            {
                // Next, handle the case where the user canceled 
                // the operation.
                // Note that due to a race condition in 
                // the DoWork event handler, the Cancelled
                // flag may not have been set, even though
                // CancelAsync was called.
                resultLabel.Text = "Canceled";
            }
            else
            {
                // Finally, handle the case where the operation 
                // succeeded.
                resultLabel.Text = e.Result.ToString();
            }

            // Enable the UpDown control.
            this.numericUpDown1.Enabled = true;

            // Enable the Start button.
            startAsyncButton.Enabled = true;

            // Disable the Cancel button.
            cancelAsyncButton.Enabled = false;
        }

        // This event handler updates the progress bar.
        private void backgroundWorker1_ProgressChanged(object sender,
            ProgressChangedEventArgs e)
        {
            this.progressBar1.Value = e.ProgressPercentage;
        }

        // This is the method that does the actual work. For this
        // example, it computes a Fibonacci number and
        // reports progress as it does its work.
        long ComputeFibonacci(int n, BackgroundWorker worker, DoWorkEventArgs e)
        {
            // The parameter n must be >= 0 and <= 91.
            // Fib(n), with n > 91, overflows a long.
            if ((n < 0) || (n > 91))
            {
                throw new ArgumentException(
                    "value must be >= 0 and <= 91", "n");
            }

            long result = 0;

            // Abort the operation if the user has canceled.
            // Note that a call to CancelAsync may have set 
            // CancellationPending to true just after the
            // last invocation of this method exits, so this 
            // code will not have the opportunity to set the 
            // DoWorkEventArgs.Cancel flag to true. This means
            // that RunWorkerCompletedEventArgs.Cancelled will
            // not be set to true in your RunWorkerCompleted
            // event handler. This is a race condition.

            if (worker.CancellationPending)
            {   
                e.Cancel = true;
            }
            else
            {   
                if (n < 2)
                {   
                    result = 1;
                }
                else
                {   
                    result = ComputeFibonacci(n - 1, worker, e) + 
                             ComputeFibonacci(n - 2, worker, e);
                }

                // Report progress as a percentage of the total task.
                int percentComplete = 
                    (int)((float)n / (float)numberToCompute * 100);
                if (percentComplete > highestPercentageReached)
                {
                    highestPercentageReached = percentComplete;
                    worker.ReportProgress(percentComplete);
                }
            }

            return result;
        }


        #region Windows Form Designer generated code

        private void InitializeComponent()
        {
            this.numericUpDown1 = new System.Windows.Forms.NumericUpDown();
            this.startAsyncButton = new System.Windows.Forms.Button();
            this.cancelAsyncButton = new System.Windows.Forms.Button();
            this.resultLabel = new System.Windows.Forms.Label();
            this.progressBar1 = new System.Windows.Forms.ProgressBar();
            this.backgroundWorker1 = new System.ComponentModel.BackgroundWorker();
            ((System.ComponentModel.ISupportInitialize)(this.numericUpDown1)).BeginInit();
            this.SuspendLayout();
            // 
            // numericUpDown1
            // 
            this.numericUpDown1.Location = new System.Drawing.Point(16, 16);
            this.numericUpDown1.Maximum = new System.Decimal(new int[] {
            91,
            0,
            0,
            0});
            this.numericUpDown1.Minimum = new System.Decimal(new int[] {
            1,
            0,
            0,
            0});
            this.numericUpDown1.Name = "numericUpDown1";
            this.numericUpDown1.Size = new System.Drawing.Size(80, 20);
            this.numericUpDown1.TabIndex = 0;
            this.numericUpDown1.Value = new System.Decimal(new int[] {
            1,
            0,
            0,
            0});
            // 
            // startAsyncButton
            // 
            this.startAsyncButton.Location = new System.Drawing.Point(16, 72);
            this.startAsyncButton.Name = "startAsyncButton";
            this.startAsyncButton.Size = new System.Drawing.Size(120, 23);
            this.startAsyncButton.TabIndex = 1;
            this.startAsyncButton.Text = "Start Async";
            this.startAsyncButton.Click += new System.EventHandler(this.startAsyncButton_Click);
            // 
            // cancelAsyncButton
            // 
            this.cancelAsyncButton.Enabled = false;
            this.cancelAsyncButton.Location = new System.Drawing.Point(153, 72);
            this.cancelAsyncButton.Name = "cancelAsyncButton";
            this.cancelAsyncButton.Size = new System.Drawing.Size(119, 23);
            this.cancelAsyncButton.TabIndex = 2;
            this.cancelAsyncButton.Text = "Cancel Async";
            this.cancelAsyncButton.Click += new System.EventHandler(this.cancelAsyncButton_Click);
            // 
            // resultLabel
            // 
            this.resultLabel.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D;
            this.resultLabel.Location = new System.Drawing.Point(112, 16);
            this.resultLabel.Name = "resultLabel";
            this.resultLabel.Size = new System.Drawing.Size(160, 23);
            this.resultLabel.TabIndex = 3;
            this.resultLabel.Text = "(no result)";
            this.resultLabel.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
            // 
            // progressBar1
            // 
            this.progressBar1.Location = new System.Drawing.Point(18, 48);
            this.progressBar1.Name = "progressBar1";
            this.progressBar1.Size = new System.Drawing.Size(256, 8);
            this.progressBar1.Step = 2;
            this.progressBar1.TabIndex = 4;
            // 
            // backgroundWorker1
            // 
            this.backgroundWorker1.WorkerReportsProgress = true;
            this.backgroundWorker1.WorkerSupportsCancellation = true;
            // 
            // FibonacciForm
            // 
            this.ClientSize = new System.Drawing.Size(292, 118);
            this.Controls.Add(this.progressBar1);
            this.Controls.Add(this.resultLabel);
            this.Controls.Add(this.cancelAsyncButton);
            this.Controls.Add(this.startAsyncButton);
            this.Controls.Add(this.numericUpDown1);
            this.Name = "FibonacciForm";
            this.Text = "Fibonacci Calculator";
            ((System.ComponentModel.ISupportInitialize)(this.numericUpDown1)).EndInit();
            this.ResumeLayout(false);

        }
        #endregion

        [STAThread]
        static void Main()
        {
            Application.Run(new FibonacciForm());
        }
    }
}