Asynchronous waiting while C# function is executing

Question

I have a blocking function that executes an asynchronous MySQL query and returns the result when it is obtained. The reason is is asynchronous is this program is not allowed to lock up during a query.

The function is called when the user presses a button, so the function may get called several times before the first query completes. I thought I could add a boolean to check whether or not a query is executing and have the function wait until it's done before continuing, but it is not working as intended. There is some issue with the two DoEvents() I use. If you comment out either one, it runs just fine, except the UI freezes.

How can I make the function do a non-blocking wait while a query is executing, as well as do a non-blocking wait while the query itself is being fetched? I would really prefer to keep this on one thread, as the function itself is blocking to the code that called it. Any help would b e greatly appreciated!

    public Exception LastError;
    public MySqlConnection Conn;
    public MySqlDataReader Reader;
    public bool IsExecuting = false;

    public MySqlDataReader MySQL_Query(string Query, [Optional] params string[] Values)
    {
        while (IsExecuting)
        {
            System.Windows.Forms.Application.DoEvents();
            System.Threading.Thread.Sleep(20);
        }

        if (IsConnected() == false)
            ConnectToDatabase();

        for (int i = 0; i < Values.Length; i++)
            Values[i] = MySQL_SafeValue(Values[i]);
        if (Reader != null && Reader.IsClosed == false)
            Reader.Close();

        IsExecuting = true;
        try
        {
            MySqlCommand Cmd = new MySqlCommand(String.Format(Query, Values), Conn);
            IAsyncResult aRes = Cmd.BeginExecuteReader();
            while (!aRes.IsCompleted)
            {
                System.Windows.Forms.Application.DoEvents();
                System.Threading.Thread.Sleep(20);
            }
            Reader = Cmd.EndExecuteReader(aRes);
            IsExecuting = false;
        }
        catch (Exception e)
        {
            IsExecuting = false;
            LastError = e;
            return null;
        }

        return Reader;
    }

Answer 1

You should not be using DoEvents and Sleep to create a responsive UI. For performing asynchronous operations in the UI, see the BackgroundWorker class.

Answer 2

There are numerous ways to do async work, from using the thread pool directly to helpers like BackgroundWorker.

However this does not answer your primary question which is a little contradictory, i.e. you want to do a non-blocking wait. I would suggest that you not block at all which is to say, if you are already executing then ignore the request and do nothing. You may want to give some feedback to say "already working" in this situation.

Now to actual issues with your code. As Adam has noted you really shouldn't be using DoEvents and Sleep. Instead you post the long running work item to some background task and use a flag to synchronise between the UI thread and the thread running your task, e.g.

    /// <summary>
    /// Used to prevent more than one worker.
    /// </summary>
    private bool working = false;

    /// <summary>
    /// Must use a lock to synch between UI thread and worker thread.
    /// </summary>
    private object stateLock = new object();

    /// <summary>
    /// Used to pass custom args into the worker function.
    /// </summary>
    private class Data
    {
        public string query;
        public string[] values;
    }

    /// <summary>
    /// Called in your UI thread in response to button press.
    /// </summary>
    /// <param name="Query"></param>
    /// <param name="Values"></param>
    public void UiRequestToDoWork(string Query, params string[] Values)
    {
        lock (stateLock)
        {
            if (working)
            {
                // Do nothing!
                Trace.WriteLine("Already working!");
            }
            else
            {
                var backgroundWorker = new System.ComponentModel.BackgroundWorker();
                backgroundWorker.DoWork += new System.ComponentModel.DoWorkEventHandler(backgroundWorker_DoWork);
                backgroundWorker.RunWorkerAsync(new Data { query = Query, values = Values });
                this.working = true;
            }
        }
    }

    /// <summary>
    /// Does all the background work.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void backgroundWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
    {
        try
        {
            Data data = e.Argument as Data;
            if (data != null)
            {
                // Do your query in here - just simulating work with a sleep.
                Trace.WriteLine("Working...");
                System.Threading.Thread.Sleep(500);

                // Note: you can't access the UI directly here in the worker thread. Use
                // Form.Invoke() instead to update the UI after your work is done.
            }
        }
        finally
        {
            // Note the use of finally to be safe if exceptions get thrown.
            lock (stateLock)
            {
                this.working = false;
            }
            Trace.WriteLine("Finished!");
        }
    }