Avoiding the window (WPF) to freeze while using TPL

Question

I am building a WPF which has a button that execute a sql query in sql server (the query could take a long time to run). I want to use TPL for doing that.

This code: var result = Task.Factory.StartNew(() => { command.ExecuteNonQuery(); });

gives this exception: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

I guess this is due to the fact that the query runs on a different thread and is not aware of the open connection.

I have 2 questions: 1. How do I make the new thread know of this open connection? 2. After solving this ,How do I get the window not to freeze due to this query.

Thanks

Answer 1

You will have to create and open the connection for this command within the Task's body. Either that or don't close the connection outside the Task, which I assume is what you're doing here, but can't tell from the one line of code you pasted.

I would personally do it all inside the Task body. Why should the user have to wait for you to even get the connection/command setup if they don't have to? Also there's the chance that you connection is a shared instance and that won't work across threads.

Once you get the DB work into a Task it will be executed on a Thread Pool thread by default which will free up the WPF dispatcher thread to go back to processing UI events preventing the "freezing". Most likely you will want to update the UI after that DB task has completed and to do that you would hpjust add a continuation task, but in order to be able to manipulate the UI from that continuation task you need to make sure it's explicitly scheduled to run on the Dispatcher thread. This is done by explicitly specifying a TaskScheduler for the current synchronization context while scheduling the continuation. That would look something like this:

Task backgroundDBTask = Task.Factory.StartNew(() =>
{
    ... DB work here ...
});

backgroundDBTask.ContinueWith((t) =>
{
    ... UI update work here ...
},
TaskScheduler.FromCurrentSynchronizationContext());

The magic here is the use of the TaskScheduler::FromCurrentSynchronizationContext method which will schedule the continuation to be executed on the Dispatcher thread of the current call.