Ensure DB connection is closed on Background worker Cancel Async

Question

I have this code so far for a background worker that will run the background worker:

if (backgroundWorker1.IsBusy == true)
{
    backgroundWorker1.CancelAsync();
}
else
{
    backgroundWorker1.RunWorkerAsync();
}

Will this close out the connection in the backgrounder worker as well? Or do I need to add lines of code for the progress change?

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
            try
            {
                superset = new DataSet();
                connectionString = "Driver={IBM DB2 ODBC DRIVER}; Database=" + lines[i] + "; Hostname=" + lines[i] + "." + lines[i] + ".XXX; Port = XXXX; Protocol = TCPIP; Uid=XX; Pwd= XXXX;
            }
            connection = new OdbcConnection(connectionString);
            adapter = new OdbcDataAdapter(masterquery, connection);

            connection.Open();
            adapter.Fill(superset);
            superset.Merge(superset);
            connection.Close();
        }
    }
    dataGridView1.DataSource = superset;
    dataGridView1.DataSource = superset.Tables[0];
    //tabControl1.SelectedTab = tabPage3;
}

I was trying to format you code but i got lost, can you please fix it? — bto.rdz, Sep 30 '15 at 02:10
@bto.rdz: you are correct. Thus I have done it so that atleast code would be readable that it is buggy. \ — Mohit S, Sep 30 '15 at 02:19

score 2 · Accepted Answer · answered Sep 30 '15 at 02:19

2

the connection doesn't close, it will abort the worker but the connection might stuck inside it,

Why don't you make it simple? Just call

connection.Close();
connection.Dispose();

After you call cancelAsync

answered Sep 30 '15 at 02:19

Ryan Chong

180
2
13

score 0 · Answer 2 · answered Sep 30 '15 at 02:10

Your background worker never tests for cancellation so it will always run to completion and close the connection.

However, if you encounter an Exception while the connection is open it will not close until garbage collection. I suggest the following improvement

using (var connection = new OdbcConnection(connectionString))
{
   var adapter = new OdbcDataAdapter(masterquery, connection);
   connection.Open();
   adapter.Fill(superset);
   superset.Merge(superset);
}

score 0 · Answer 3 · edited May 23 '17 at 11:58

Remember that in the case of a background worker is your responsibility to check for the cancellation request in the worker method (see BackgroundWorker.CancellationPending). If you do not check for a cancellation request inside backgroundWorker1_DoWork then the background worker will not magically terminate early. Since the decision to cancel is under your control, just make sure that the worker method will close the connection when it is done, regardless if it is done because it finished what was supposed to do or because it was cancelled.

In this particular case, you may not have a lot of good points where to check for a cancellation request (it is not like you do a lot of operations in a long loop). You could call connection.Close after you call CancelAsync but if you do that you must understand that the cancellation request will be ignored. It may be that the worker method will at that point complete but not because you requested cancellation but because adapter.Fill may fail since you closed the connection.

A few more comments:

You do not need to call connection.Close if you call connection.Dispose. See this stackoverflow question.
Inside backgroundWorker1_DoWork you should use a "using" statement and forgo the call to connection.Close(). Like this:

using(connection = new OdbcConnection(connectionString) { ... }

It is safer in case there is an exception.
If you do intend to use cakncellation you will have to set: backgroundWorker1.WorkerSupportsCancellation = true;
Otherwise CancelAsync will throw an exception.
I am not sure you can set dataGridView1.DataSource from the background thread. See if it works but you may have to change the code that sets dataGridView1.DataSource.

Ensure DB connection is closed on Background worker Cancel Async

3 Answers3