Using multiple Backgroundworker controls on one WinForm

Hello,
My developing environment is :
Win 10 64
Visual Basic 2010
WinForm application Front-End, MS-Access 2016 Database

My Issue :
I have a Form that contains 4 ComboBox Controls, each ComboBox is supposed to be filled with Data From a different table within the Database. I'm using BackgroundWorker Control to fill one ComboBox control on Form_Load() event.

I'm wondering, how to do the same with the rest 3 ComboBox controls? and if there are a better way to achieve such task !!

Here is my code for filling one of the combobox controls:

Code:

---

Imports System.Data.OleDb
Public Class ThisForm

    Private ComboItems As New Dictionary(Of String, String)()

Private Sub PurchaseFrm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        If BackgroundWorker1.IsBusy Then
            BackgroundWorker1.CancelAsync()
        End If
        BackgroundWorker1.RunWorkerAsync()
    End Sub

Private Sub BackgroundWorker1_DoWork(sender As System.Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim SqlStr As String =
            ("SELECT * FROM Types;")
        Using CN As New OleDbConnection With {.ConnectionString = My.Settings.MainConnection},
                              M_Cmd As New OleDbCommand(SqlStr, CN)
            CN.Open()
            Using ComboReader As OleDbDataReader = M_Cmd.ExecuteReader
                While ComboReader.Read
                    If ComboReader.HasRows Then
                        'Almost 29.8k records of string.
                        ComboItems.Add(ComboReader!TypeID.ToString, ComboReader!TypeNm)
                    Else
                        TypCbo.DataSource = Nothing
                    End If
                End While
            End Using
        End Using
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        With TypCbo
            .DataSource = Nothing
            .Items.Clear()
            .BeginUpdate()
            .DataSource = New BindingSource(ComboItems, Nothing)
            .DisplayMember = "Value"
            .ValueMember = "key"
            .SelectedIndex = -1
            .EndUpdate()
        End With
    End Sub

End Class

---

You might look at Tasks, but ultimately that's probably not going to be the biggest issue.

If you run multiple simultaneous threads to get data, then each will have a connection. So, you'd be creating multiple connections to an Access database. That may or may not cause a problem. Access has not historically dealt all that well with multiple simultaneous connections because it locks more than it absolutely needs to, which can mean that one thread may block another till it's done, in which case you won't be gaining much from having multiple threads.

Another points is that the Load event may be too late. It may also be all you can do. If that's your startup form, you don't have a lot of good alternatives. If it isn't the startup form, then you have some flexibility, since you could move starting the BGW or the Tasks, or whatever route you take, into the constructor. The constructor is called when you create the form, while the Load event is called when you show the form. You can create the form a long time before you show it, so you might have the data being gathered in the background such that it is ready by the time you need it. Of course, that won't work if the form is your startup form such that there is no change of a delay prior to needing the form (unless you add a splash screen).

Firstly, there is no BackgroundWorker control. A control is any class that inherits, either directly or indirectly, the Control class. The BackgroundWorker class does NOT inherit Control, thus it is not a control. Everything you see in the Toolbox when using the WinForms designer is a component. Strictly speaking, anything that implements the IComponent interface is a component. There is a Component class that provides a default implementation and most components actually inherit that class. The Control class inherits the Component class and thus all controls are components. The BackgroundWorker class does inherit Component but does not via Control, so it is a component but not a control.

Every class you use from the Framework is documented and, among other things, that documentation provides the inheritance hierarchy, so it's easy to see what's a control and what is only a component. If it has no UI then you can be pretty sure it's not a control. If you have encountered context-sensitive Help in Windows over the last few decades, you can open the documentation for any class by clicking the class name in code and pressing the F1 key. It works the same way if you select a control or component in the WinForms designer.

As for the question, I agree with Shaggy that, as your database is Access, you should not try to query it multiple times on multiple threads. Using a single background thread to perform all four queries in series is perfectly fine but your code could be cleaned up a bit.

The code you have seems to be overly verbose. For one thing, you check whether the BackgroundWorker is busy in the Load event handler. How could it be? Where exactly would you have started it for it to be busy? Secondly, you are clearing both the DataSource and Items collection before binding the ComboBox. Why? Can either or both of them actually contain something? I can imagine that you would have a dummy item in there to start that indicates that data is loading and then you run this routine multiple times to load and reload data. In that case, clearing both might be worthwhile. Otherwise, you could only possibly need to clear one and possibly neither. Finally, you should always set the DisplayMember and ValueMember before setting the DataSource. If you know what those columns will be and they won't change, though, then you should be setting the DisplayMember and ValueMember in the designer and only setting the DataSource in code.

As for loading the data into four ComboBoxes, I would suggest two possible approaches:

1. Execute all four queries first and then bind all four ComboBoxes in the RunWorkerCompleted event handler.
2. Execute one query at a time and then bind the corresponding ComboBox in the ProgressChanged event handler. The last one could be done in the ProgressChanged or RunWorkerCompleted event handler. I'd probably use the former for consistency.

You should decide which way you want to go and then I can help you with it if required.

OT, you have this in your DoWork event handler:

vb.net Code:

TypCbo.DataSource = Nothing

Either the DataSource is already Nothing and that line of code is completely useless or else the DataSource is currently set and clearing will throw a cross-thread exception. DO NOT access controls AT ALL on secondary threads, other than to test InvokeRequired or to call Invoke, BeginInvoke or EndInvoke. Some operations are legal but you don't know which they are so don't even try. If you never try then you can never get it wrong.

Quote:

---

Originally Posted by Shaggy Hiker

You might look at Tasks, but ultimately that's probably not going to be the biggest issue.

If you run multiple simultaneous threads to get data, then each will have a connection. So, you'd be creating multiple connections to an Access database. That may or may not cause a problem. Access has not historically dealt all that well with multiple simultaneous connections because it locks more than it absolutely needs to, which can mean that one thread may block another till it's done, in which case you won't be gaining much from having multiple threads.

Another points is that the Load event may be too late. It may also be all you can do. If that's your startup form, you don't have a lot of good alternatives. If it isn't the startup form, then you have some flexibility, since you could move starting the BGW or the Tasks, or whatever route you take, into the constructor. The constructor is called when you create the form, while the Load event is called when you show the form. You can create the form a long time before you show it, so you might have the data being gathered in the background such that it is ready by the time you need it. Of course, that won't work if the form is your startup form such that there is no change of a delay prior to needing the form (unless you add a splash screen).

---

Thank you. No this is not the Startup Form.

Quote:

---

Originally Posted by jmcilhinney

As for the question, I agree with Shaggy that, as your database is Access, you should not try to query it multiple times on multiple threads. Using a single background thread to perform all four queries in series is perfectly fine but your code could be cleaned up a bit.

The code you have seems to be overly verbose. For one thing, you check whether the BackgroundWorker is busy in the Load event handler. How could it be? Where exactly would you have started it for it to be busy? Secondly, you are clearing both the DataSource and Items collection before binding the ComboBox. Why? Can either or both of them actually contain something? I can imagine that you would have a dummy item in there to start that indicates that data is loading and then you run this routine multiple times to load and reload data. In that case, clearing both might be worthwhile. Otherwise, you could only possibly need to clear one and possibly neither. Finally, you should always set the DisplayMember and ValueMember before setting the DataSource. If you know what those columns will be and they won't change, though, then you should be setting the DisplayMember and ValueMember in the designer and only setting the DataSource in code.

As for loading the data into four ComboBoxes, I would suggest two possible approaches:

1. Execute all four queries first and then bind all four ComboBoxes in the RunWorkerCompleted event handler.
2. Execute one query at a time and then bind the corresponding ComboBox in the ProgressChanged event handler. The last one could be done in the ProgressChanged or RunWorkerCompleted event handler. I'd probably use the former for consistency.

You should decide which way you want to go and then I can help you with it if required.

OT, you have this in your DoWork event handler:

vb.net Code:

TypCbo.DataSource = Nothing

Either the DataSource is already Nothing and that line of code is completely useless or else the DataSource is currently set and clearing will throw a cross-thread exception. DO NOT access controls AT ALL on secondary threads, other than to test InvokeRequired or to call Invoke, BeginInvoke or EndInvoke. Some operations are legal but you don't know which they are so don't even try. If you never try then you can never get it wrong.

---

Thank you. BackgroundWorker is a component not a Control, thank you for making that so clear.
This is not a Startup Form.
Now, I cleared the Form_Load event from useless Block.... as suggested.
I removed clear items.
I re-ordered displaymember and ValueMember to be after setting DataSource. Though, doing this caused the combobox to display Data like this [ID,Value] with the brackets, but when I use my code, it gives only [Value] without brackets.
I dunno how to set both in the designer since I'm not using the designer to connect to the Database,.

I think I would use the first method [Execute all four queries first and then bind all four ComboBoxes in the RunWorkerCompleted event handler], and yes I need your help.

My code now is, as you suggested:

Code:

---

Imports System.Data.OleDb
Public Class ThisForm

    Private ComboItems As New Dictionary(Of String, String)()

Private Sub PurchaseFrm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        BackgroundWorker1.RunWorkerAsync()
    End Sub

Private Sub BackgroundWorker1_DoWork(sender As System.Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim SqlStr As String =
            ("SELECT * FROM Types;")
        Using CN As New OleDbConnection With {.ConnectionString = My.Settings.MainConnection},
                              M_Cmd As New OleDbCommand(SqlStr, CN)
            CN.Open()
            Using ComboReader As OleDbDataReader = M_Cmd.ExecuteReader
                While ComboReader.Read
                    If ComboReader.HasRows Then
                        'Almost 29.8k records of string.
                        ComboItems.Add(ComboReader!TypeID.ToString, ComboReader!TypeNm)
                    End If
                End While
            End Using
        End Using
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        With TypCbo
            .DataSource = Nothing
            .BeginUpdate()
            .DataSource = New BindingSource(ComboItems, Nothing)
            .DisplayMember = "Value"
            .ValueMember = "key"
            .SelectedIndex = -1
            .EndUpdate()
        End With
    End Sub

End Class

---

I also forgot to mention that, the ComboBox controls are bound to 4 tables, those tables are being filled from different Forms, so When I leave the Form that contains the ComboBox controls to add new item to one table, I want the combobox to display the new inserted item, and that won't happen if the ComboBox controls are populated in Form_Load event. The ComboBox Controls DropDown Style is SIMPLE, ANY IDEAS ?!!