How to handle exceptions in methods running asynchronous?

Question

I'm building a program that keeps track of a directory and adds an entry to an mysql database whenever a new file has been created in the directory. A while ago I posted a question for how to let the class FileSystemWatcher run independently while filling a backgroundqueue of actions to process, while the FileSystemWatcher keeps searching. I got a solution for that, but would like to ask a follow up question. First of all, here is the code:

This is the constructor for the watcher

FileSystemWatcher watcher = new FileSystemWatcher
{
    Path = directoryToWatch,
    IncludeSubdirectories = true,
    NotifyFilter = NotifyFilters.Attributes |
                   NotifyFilters.DirectoryName |
                   NotifyFilters.FileName,
    EnableRaisingEvents = true,
    Filter = "*.*"
};

watcher.Created += (OnDirectoryChange);

Here is the OnDirectoryChange method:

public void OnDirectoryChange(object sender, FileSystemEventArgs e)
{
    try
    {
        bq.QueueTask(() => storage.Insert(Settings.Default.added_files, e.Name));
    }
    catch (StorageException se)
    {
        Console.WriteLine(se.Message);
        HandleException(se, se.Filename, se.Number);
    }
    catch (Exception ex)
    {
        Console.WriteLine("Catch all exceptions");
    }
}

The HandleException method:

public void HandleException(Exception exceptionToHandle, string fileName = "empty", int number = 0)
{
    try
    {
        if (number < Acceptedammountofexceptions)
        {
            AddExceptionToStack(exceptionToHandle, fileName);
            RetryFailedFiles(new KeyValuePair<string, int>(fileName, number));
        }
        else if (number >= Acceptedammountofexceptions)
        {
            AddExceptionToCriticalList(exceptionToHandle, fileName);
            Console.WriteLine("Sorry, couldnt insert this file. See the log for more information.");
        }
    }
    catch (Exception e)
    {
        Console.WriteLine("Exception in HandleException");
        throw;
    }
}

The RetryFailedFiles method:

public void RetryFailedFiles(KeyValuePair<string, int> file)
{
    int count = file.Value + 1;
    try
    {
        bq.QueueTask(() => storage.Insert(Settings.Default.added_files, file.Key));
    }
    catch (StorageException se)
    {
        HandleException(se, file.Key, count);
        Console.WriteLine(se);
    }
}

As you can see, I want to retry inserting failed files a certain amount of tries. However, the failed file will not be retried at this moment. I'm feeling a bit lost in my own program to be honest. If it helps, here is the code for the insert statement.

public Task Insert(string tablename, string filename, int number=0)
{
    try
    {
        string query = "INSERT INTO " + tablename + " (FILE_NAME, " +
                       "Id) VALUES('" + filename + "', '" + Id + "')";

        using (MySqlConnection insertConn = new MySqlConnection(connectionString))
        {
            insertConn.Open();
            if (insertConn.State == ConnectionState.Open)
            {
                MySqlCommand insertCmd = new MySqlCommand(query, insertConn) {CommandType = CommandType.Text};
                while (insertCmd.Connection.State != ConnectionState.Open)
                {
                    // Spinlock
                }

                insertCmd.ExecuteNonQuery();
                insertConn.Close();
                return Task.CompletedTask;
            }
            StorageException se = new StorageException("Couldn't connect to database!", filename, number);
            Console.WriteLine(se.Message);
            return Task.FromException(se);
        }

    }
    catch (Exception ex)
    {

        Console.WriteLine(ex.Message);
        if (ex.Message.ToLower().Contains("duplicate"))
        {
            MessageBox.Show("Already exists", "Duplicate entry", MessageBoxButton.OK);
            return Task.CompletedTask;
        }
        else
        {
            StorageException se = new StorageException(ex.Message, filename, number);
            Console.WriteLine("Well");
            return Task.FromException(se);
        }
    }
}

As you can see, I was already trying to get an exception, but I can't seem to trace it, if that makes sense.

So how would I go and handle the exceptions thrown to make sure the filenames will be tried again when they failed the first time?

Edit: Backgroundqueue class:

public class BackgroundQueue
{
    private Task previousTask = Task.FromResult(true);
    private object key = new object();
    public Task QueueTask(Action action)
    {
        lock (key)
        {
            previousTask = previousTask.ContinueWith(t => action()
                , CancellationToken.None
                , TaskContinuationOptions.None
                , TaskScheduler.Default);
            return previousTask;
        }
    }

    public Task<T> QueueTask<T>(Func<T> work)
    {
        lock (key)
        {
            var task = previousTask.ContinueWith(t => work()
                , CancellationToken.None
                , TaskContinuationOptions.None
                , TaskScheduler.Default);
            previousTask = task;
            return task;
        }
    }
}

Answer 1

It isn't clear what bq is, but I'm guessing that it is essentially a wrapper around a Queue<Func<Task>> - i.e. a queue of things that when invoked start an async operation. If that is the case, then your work loop should (if it intends to preserve order) do something like:

async Task RunQueue() {
    while(keepGoing) {
        if(no work) await work; // somehow, not shown; needs to be thread-safe

        Func<Task> nextAction = queue.ThreadSafeDequeue();
        try {
            await nextAction();
        } catch(Exception ex) {
            DoSomethingUseful(ex);
        }
    }
}

The important thing is the await which waits for the async completion, and re-raises any exception observed.

However, note that in your specific case, the Insert work being done is not actually async - it will always complete (or fail) synchronously. This isn't necessarily a problem, but: data access like this is a prime candidate for async.