Using the BackgroundWorker and a SqlDataReader

[Matthias S.]

Hi,

I've written a simple app which should just fetch some data from a
database and render the results into a ListView.

In order to not freeze the GUI, I'm using a BackgroundWorker. The
arguments for the RunWorkerAsync are the Query to be executed and the
Connectionstring. As a result I hoped I could return a SqlDataReader.

The problem is, if I create the SqlDataReader within the
BackgroundWorker Thread, I can't access it after the Thread has exitted.
If I would create it on the Form (thus within the Forms Thread) and pass
it as an argument to the Thread, I might end up with the risk of two
Threads accessing the same resource (a Forms Property in this case) and
that doesn't sound a nice thing to do.

How can I pass the resulting SqlDataReader from the Thread back to the form?

Any help is greatly appreceated and thanks in advance!

Matthias

 

[Nicholas Paldino [.NET/C# MVP]]

Matthias,

Can you show an example of how you are using the BackgroundWorker class?
AFAIK, you shouldn't have an issue if you create a SqlDataReader and pass it
around (the OleDb provider I can understand, but not the SqlProvider).

 

[Matthias S.]

Hi Nicholas,

I'm trying to copy/paste the most relevant parts. All ErrorHandling has
been removed. Basically, we need to know only the two EventSinks for the
following BackgroundWorker events:

- DoWork
- RunWorkerCompleted


This is the EventSink for the DoWork event:

private void OnWorkerStart(object sender, DoWorkEventArgs args) {

// the DBWorkerArgs contain two public fields which are
// quite selfexplanatory: ConnectionString and Query
DBWorkerArgs workerArgs = (DBWorkerArgs) args.Argument;

// open connection
SqlConnection con = new SqlConnection(workerArgs.ConnectionString);

con.Open();

// run the query again, checks on the SqlConnection ommitted...
SqlCommand cmd = new SqlCommand(workerArgs.Query, con);
SqlDataReader reader = cmd.ExecuteReader();

// to make the Results available in the
// BackgroundWorker.RunWorkerCompleted event, we put them
// into the args.Result

args.Result = reader;

con.Close();

// done...
}

This is the EventSink for the RunWorkerCompleted event:

private void OnWorkerCompleted(object sender,
RunWorkerCompletedEventArgs args) {

// typechecking omitted
SqlDataReader reader = (SqlDataReader) args.Result;

// _ResultList is a ListView Control on the Form
_ResultList.Items.Clear();
_ResultList.Columns.Clear();

// columns first
int nFieldCt = reader.FieldCount;

for (int i = 0; i < nFieldCt; i++) {
_ResultList.Columns.Add(reader.GetName(i), 100);
}
}

Both methods are located withing Form1.

Thanks already for looking into my issue

Matthias