C# Connect to database in separate thread

Question

I have an application that sits in the taskbar and periodically checks a database (via SQLConnection). If the database is unreachable, it just shows a red status on the notify icon, and nothing else. This is intended since the users will be on laptops traveling between facilities, and the database is only reachable on an internal network or when connected via VPN.

The issue I'm having is that when the database is unreachable, I'd still like the notify icon context menu to be responsive. Currently if the user tries to interact with the menu while the database is attempting to connect, the thread is locked up with the connection attempt.

What I'd like is for the database connection to happen in a separate thread, but I know that sharing the connection object between threads is a bad thing to try to do. Once the database has connected, I need to query it from the original thread. Is there any good way to do this?

I know that I could have a function that rus in a new thread and simply checks if the connection fails or not, then if that returns success the original thread could move on and connect it's own database object. But that method seems like a bad workaround. Ideas?

Thanks!

---

Thanks for the suggestions - I have learned quite a bit from them. In the end, I did what I should have done from the start. The main (UI) thread just kicks off a new thread (which includes database object creation and connection) whenever a periodic database update is needed.

So, like HenkHolterman suggested in a comment to my original message, I really did not need to create the database and run operations with it in separate threads.

I will definitely be interested in using SqlConnection.OpenAsync once I've made the upgrade to VS2010.

Thanks again.

Answer 1

A SqlConnection does not have thread affinity requirements so you can create and open it on a separate thread and pass the reference back to the UI thread. So long as you do not call any properties or methods on the same instance simultaneously from multiple threads you should not have any problems.

Use the Task class (System.Threading.Task) to start this operation in the background and then call ContinueWith to transfer ownership of the instance back to the UI thread.

public class TrayIconForm : Form
{
  private SqlConnection connection = null;

  private void Form_Load(object sender, EventArgs args)
  {
    TaskScheduler ui = TaskScheduler.FromCurrentSynchronizationContext();

    Task.Factory.StartNew(() =>
      {
        var sql = new SqlConnection();
        sql.ConnectionString = "your connection string";
        sql.Open();
        return sql;
      }).ContinueWith((task =>
      {
        connection = task.Result;
        // You can access other UI elements here as needed.
      }), ui);
  }
}

With the new async and await keywords to be introduced in C# 5.0 and available now via the Async CTP you can do this.

private async void Form_Load(object sender, EventArgs args)
{
  connection = await Task.Run(() =>   // TaskEx.Run in the CTP
    {
      var sql = new SqlConnection();
      sql.ConnectionString = "your connection string";
      sql.Open();
      return sql;
    });
}

Answer 2

I would recommend using the new C# Async CTP. This will allow you to stay on the UI thread, therefore reducing complexity.

SqlConnection.OpenAsync