Connecting to a database on a different thread

Question

I have a WPF application where the user enters database information in some textboxes. Once the user clicks "connect", a connection string is created from what the user had entered and a connection is established. I noticed that if the user enters any info that is wrong, the application will hang until the connection times out. By hang, I mean the user can't interact with the rest of the application at all.

It is my goal to keep the application responsive while the connection string is tested.

I thought that putting this workflow on a different thread is a good solution. My idea is to just disable anything that may need a database connection while the thread runs. Once the thread comes back (and has has confirmed the connection string to be valid) I would re-enable everything. Otherwise, leave everything disabled.

However, the Thread class doesn't have an event notification when the thread is done (or at least I am unaware of one).

I have also worked with the BackgroundWorker class. This works better. However, when the RunWorkerCompletedEventHandler event is fired and the connection string isn't valid, I get the following exception:

The calling thread cannot access this object because a different thread owns it.

This is probably because the connection still hasn't timed out when the completed event handler is fired.

Does anybody have any ideas, or should I just NOT try to multithread a connection to a database?

A code outline of what I am doing:

private void bw_DoWork(object sender, DoWorkEventArgs e)
{
    dbTool = new DBTool();
    // Create the connection string
    e.Result = dbTool.connectToDB(); // connectToDB() returns a bool (true if connection established)
}

private void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    // connectToDB() returns a bool (true if connection established)
    if(e.Result == true)  // Trying to read e.Result here throws the exception
    {
        // e.Error and e.Cancel should be checked first
        // However, I would like the thread to finish before
        // this event is fired
    }
    if (e.Error != null)
    {
        Console.WriteLine(e.Error.Message);
    }
}

Answer 1

Don't preserve your DbConnection object in a single global variable and share it between threads.

The .NET environment will automatically pool your connections and share them, so calling new DbConnection() is very fast.

You should keep the connection string in a global variable, but then create connections as required on each thread.

EDIT: The original poster may have actually wanted ideas on how to keep the WinForms application responsive while a connection string is being tested. In that case, you want to spawn a different thread to test the connection. From the "connection test thread," you can update the UI by following this pattern - How to update the GUI from another thread in C#?

public void TestConnectionThread(String connstr_to_test)
{
    // Notify the user that we're doing our test
    string message = "Testing...";
    lblTestResultMessage.SetPropertyThreadSafe(() => lblTestResultMessage.Text, message);

    try {
        dbTool = new DBTool();
        message = dbTool.connectToDB();

    // If something failed, show a useful debugging message
    } catch (Exception ex) {
        message = ex.ToString();
    }

    // Use a lambda expression to communicate results to the user safely
    lblTestResultMessage.SetPropertyThreadSafe(() => lblTestResultMessage.Text, message);
}