[RESOLVED] Form takes time to load, implement BackgroundWorker?

Hello,

I have form that loads data from a remote server. In the Form_load event I have close to 15 queries that populate various combo boxes and Data Grid Views. It usually takes up to 30 seconds to display a form. All that time, the UI is unresponsive.

Here's how a typical query looks like:

vb.net Code:

Dim connection as New SqlConnection(connectionString) 
Try
             connection.Open()
 
            Dim sqlQuery As String = "SELECT scheduleStatusID,scheduleStatusName FROM scheduleStatuses"
            Dim myCommand As New SqlCommand(sqlQuery, connection)
 
            Dim scheduleStatusTable As New DataTable
            Dim scheduleStatusAdapter As New SqlDataAdapter
 
            scheduleStatusAdapter.SelectCommand = myCommand
            scheduleStatusAdapter.Fill(scheduleStatusTable)
 
            scheduleStatusAdapter.Dispose()
            myCommand.Dispose()
 
            If scheduleStatusTable.Rows.Count > 0 Then
                cmbScheduleStatuses.DataSource = scheduleStatusTable
                cmbScheduleStatuses.DisplayMember = "scheduleStatusName"
                cmbScheduleStatuses.ValueMember = "scheduleStatusID"
                cmbScheduleStatuses.AutoCompleteMode = AutoCompleteMode.Suggest
            Else
                cmbScheduleStatuses.DataSource = Nothing
 
            End If
 
 
 
        Catch ex As Exception
              MessageBox.Show ("Test error")
              connection.Close()
        Finally
              connection.Close()
        End Try

Having the UI unresponsive just looks bad, so I wanted to move all the queries to a separate thread. And while the data is being loaded, maybe display a Loading message.

Using the BackgroundWorker component I can place all the queries in the DoWork event. BUT,I cannot access the controls that are in the UI thread. So I can't set the DataSources of the controls being populated.

So my main question is how do I do this? How do I load the data for one Combo Box, set the Data Source and move to the next control?

Thank you!

The BackGroundworker has two major Event-Routines, they are DoWork and RunWorkerCompleted. The first is called (started) by .RunWorkerAsync(), which will start the BackgroundWorker in its thread. The RunWorkerCompleted is raised when the DoWork is completed is is done on the GUI-Thread, in other words in this routine you can access the GUI.

I understand that. In my form load I call .RunWorkerAsync, which starts the BackgroundWorker. But if I place the above query in the DoWork event, it will not be good, because I have cmbScheduleStatuses.DataSource in it. And cmbScheduleStatuses is in the UI thread. So how do I remove all the parts of the above query that deal with UI controls to the RunWorkerCompleted?

Hi,

All you need to do is make sure that you pass the right information between the events. Here is an example flow of what you could do. You add all the results to a dataset and then pass the resulting dataset back to the UI thread when its built. The process of then binding that information to the controls is then almost instantaneous.

vb.net Code:

Public Class Form1
  Private controlDS As DataSet
 
  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.Cursor = Cursors.WaitCursor
 
    With BackgroundWorker1
      .WorkerReportsProgress = True
      .RunWorkerAsync()
    End With
  End Sub
 
  Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
    Dim tempDS As New DataSet
 
    'Add the Tables you need to a Dataset
    With tempDS.Tables
      .Add("comboProducts")
      .Add("comboPrices")
    End With
 
    'Populate each Table with your SQL Fill Statements
 
    'After each table is filled report some progress to the UI Thread if you want to
    BackgroundWorker1.ReportProgress(1, "Products Table")
    'Continue for each table to be loaded
 
    'Finally pass the Dataset back to the UI Thread and populate the Controls that you need
    e.Result = tempDS
  End Sub
 
  Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
    MsgBox(String.Format("{0} percent completed and {1} has been Loaded", e.ProgressPercentage, e.UserState.ToString))
  End Sub
 
  Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
    controlDS = DirectCast(e.Result, DataSet)
 
    With ComboBox1
      .DisplayMember = "SomeFieldName"
      .ValueMember = "SomeFieldName"
      .DataSource = controlDS.Tables("comboProducts")
    End With
 
    'Add to the rest of the controls as you need to
 
    Me.Cursor = Cursors.Default
  End Sub
End Class

You should be able to build what you actually need from here.

Hope that helps.

Cheers,

Ian

Hello Ian,

Thank you for your explanation. That looks like exactly what I need. Will try it out when I get to office tomorrow ;)

I would agree with that. I have a similar problem in a working app. In my case, the form that will show the data is not the startup form, nor is it even a form that the user will get to in the first several seconds, but once they try to open that form, it can take more than 10 seconds to populate the controls. The solution I used was to start a thread as soon as the app started. The thread would populate a dataset in the background while the user was doing other things. If they did manage to reach the key form before the thread had finished filling the dataset, they would see a temporary message asking them to wait, but I've only seen that message one time, since it's hard to get to that form very quickly.

In any case, once the datasets have been populated, binding them to the controls takes no noticeable time at all. The price is paid in all the database activity, the binding is nearly free. Therefore, the solution is to fill the datatables in the background, then bind in the foreground.

One thing you might also consider is whether or not you can start filling those tables even earlier. Technically, you could start the BGW or a thread or Task, in the constructor, but unless you are doing other things in the constructor, it won't help at all to move from the Load event to the constructor. Also, if the form is not the startup form, can you load all, or even some, of the datatables prior to showing the form?

Off head comment: Surely if getting data from a server the SqlConnection etc has some sort of async, or callback method?

http://msdn.microsoft.com/en-us/libr...vs.110%29.aspx

http://msdn.microsoft.com/en-us/libr...vs.110%29.aspx

Well, I used Ians suggestion and it really worked nicely. There was quite a lot of work because I needed to separate the UI elements from the actual code the only loads the data. I even managed to use ReportProgress to update a label on a semi-transparent loading form, so it looks nice really.

I did try moving all the code to the constructor, but that made no significant difference in loading time.

I also looked at ident's example, but didn't use it, because honestly I didn't really understand the point :)

To conclude, this is an example why planning your application is very important. While developing it, it never crossed my mind that there could be problems with all those queries. So now, I had to spend an entire day rewriting code only because I was "in a hurry" :)

Thank you guys!