BackgroundWorker to return a value from DAL during foreach loop

Question

I have a backgroundworker in my View which runs a sql query in my DAL.

On my view I also have a progress bar which keeps spinning so the user knows work is being done.

The SQL query receives as one of the conditions a List<string>, that I populate in one of the where conditions, so the code is something like this:

using (SqlConnection conn = new SqlConnection(_myHost))
        {
            conn.Open();

            foreach (string item in myList)
            {
                query = "select * from ( " +
                    "//big query with inner joins here" +
                    ") a " 

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    dt.Load(reader);
                }
            }

So as you notice above, I run through all the items in my list querying them individually on the where clause (omitted above) and add the output, if any, to a datatable, which once the foreach is completed, will be returned to my view layer and become the datasource of a gridview.

It occurred to me that while I cannot query the server to tell me which % of the query is completed, maybe I could be able to provide the % completed of the foreach loop, which is some information to the user.

What I am trying to figure out is a way to:

Return to my view, the current iteration of the loop, which I could detected by simple adding a int i counter and increment it on each foreach, or maybe a string item in the beginning to inform what is being queried.

Thing is, I do not know how to return that value to my view as the codes execute. I considered C#4 Tuple, but it will not work because that is returned only once the method is completed and not during the foreach execution.

Any ideas?

You can raise an event or call a delegate from inside the loop. — RQDQ, Jan 13 '17 at 18:34
What do you mean with _view_? Is this a WinForms app, a WPF one or something else entirely? Also, this code is part of the DoWork method of the BackgroundWorker? — Steve, Jan 13 '17 at 18:37
If it is just a matter of you having to know the stepNumber/stepName, why don't you just create a property with private getter in DAL which will be updated from your for loop and have your main thread read that every x number of sec and update the UI. — haku, Jan 13 '17 at 18:41

score 2 · Accepted Answer · edited May 23 '17 at 11:52

In a WinForms app the simplest method to communicate back to your application the state of affairs inside the BackgroundWorker is raising the ProgressChanged event calling the ReportProgress method and catch it where appropriate in your code.

This event is raised in the thread where your forms are (The UI Thread) so there is no problem with trying to access UI components from a non UI thread.

So for example your could write something like this

// this code is the DoWork event of the BackgroundWorker
private void bgw_DoWork(object sender, DoWorkEventArgs e)
{
    BackgroundWorker bkw = sender as BackgroundWorker;
    .....
    using (SqlConnection conn = new SqlConnection(_myHost))
    {
        conn.Open();
        int counter  = 1;
        int total = myList.Count;
        foreach (string item in myList)
        {
            .....

            WorkerItem item = new WorkerItem() { Counter = counter, Item = item};
            int progress = (100 * counter / total);
            bkw.ReportProgress(progress, item);
            counter++;
        }
    }
}

public class WorkerItem
{
    public int Counter {get;set;}
    public string Item {get;set;}
    ... add other properties if you like .....
}

Then your winform code could start the BackgroundWorker and handle the ReportProgress event in this way

BackgroundWorker bkw = new BackgroundWorker();
bkw.WorkerReportsProgress = true;
bkw.WorkerSupportsCancellation = true;
bkw.ProgressChanged += bgw_ProgressChanged;
bkw.DoWork += bgw_DoWork;
bkw.RunWorkerAsync();
...

private void bgw_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
    WorkerItem item = e.UserState as WorkerItem;
    ... update a progress bar, show the current item in a label etc...
    Console.WriteLine($"Completed: {e.ProgressPercentage}% {WorkerItem.Counter} {WorkerItem.Item}");
}

It is worth noting the first argument to ReportProgress is designed to represent percent complete (as an int); typically I see values calculate like `(100 * counter / total)` used for that argument. — Uueerdo, Jan 13 '17 at 19:02
Yes that's true, I was more interested to show the fact that you could pass an arbitrary object back to the caller. This will allow the caller code a lot more of possibilities than a simple progress percentage. — Steve, Jan 13 '17 at 19:04
Yeah, when that is needed I usually just pass a percentage of 0 and include the information in the status object passed; I just figured it was worth mentioning since the property on the ProgressChangedEventArgs is named ProgressPercentage (as your second section of code shows.) — Uueerdo, Jan 13 '17 at 19:14

score -1 · Answer 2 · edited Jun 20 '20 at 09:12

Here is one way - pass a delegate into the method that is running on the background thread.

public void Run(Action<int> update) 
{
    using (SqlConnection conn = new SqlConnection(_myHost))
    {
       conn.Open();

       int index = 0;

       foreach (string item in myList)
       {
          //Perform query

          //Notify the caller of progress
          update(index);

          index++
       }
    }
}

Note that the callback will be executed on the background thread.

EDIT

I forgot about the ReportProgress method built into BackgroundWorker.

https://msdn.microsoft.com/en-us/library/system.componentmodel.backgroundworker.reportprogress(v=vs.110).aspx

BackgroundWorker to return a value from DAL during foreach loop

2 Answers2

EDIT