How to display progress bar while executing big SQLCommand VB.Net

Question

I have this big SQL command that usually returns 20 000 - 100 000 rows of data. But as soon as i call the executeMyQuery function, the program hangs for a few seconds depending on how large the return is.

I only return one column.

How can I display a progress bar while this command is running?

Maybe in a Thread or something(I have NO experience with threads)

Here is my code(The arguments are sent from 3 different combobox.selectedItem) :

    Public Function executeMyQuery(dbname As String, colname As String, tblname As String)
    Try
        ListBox1.Items.Clear()
        If Not String.IsNullOrWhiteSpace(connString) Then
            Using cn As SqlConnection = New SqlConnection(connString)
                cn.Open()
                Using cmd As SqlCommand = New SqlCommand()
                    cmd.Connection = cn
                    Dim qry As String
                    qry = String.Format("select distinct [{0}] from {1}.dbo.{2} where [{0}] is not null", colname, dbname, tblname)
                    cmd.CommandText = qry
                    cmd.CommandTimeout = 0

                    Dim count As Integer
                    Using myReader As SqlDataReader = cmd.ExecuteReader()
                        While (myReader.Read())
                            count += 1
                            ListBox1.Items.Add(count.ToString & ". " & myReader.GetString(0))
                        End While
                    End Using
                End Using
            End Using
        End If
         cn.Close()
    Catch ex As Exception
        MsgBox("Error Occured : " & ex.Message)
        cn.Close()
    End 
End Function         

Solution 1

Here is a cut down example of how to do Asychrounous Work with VB.Net 4.0.

Lets imagine you have a form that has the following imports,

Imports System.Windows.Forms
Imports System.Threading
Imports System.Threading.Tasks

That form has two controls

Private WithEvents DoSomthing As Button
Private WithEvents Progress As ProgressBar

Somewhere in your application we have a Function called ExecuteSlowStuff, this function is the equivalent of your executeMyQuery. The important part is the Action parameter which the function uses to show it is making progress.

Private Shared Function ExecuteSlowStuff(ByVal progress As Action) As Integer
    Dim result = 0
    For i = 0 To 10000
        result += i
        Thread.Sleep(500)
        progress()
    Next

    Return result
End Function

Lets say this work is started by the click of the DoSomething Button.

Private Sub Start() Handled DoSomething.Click
    Dim slowStuff = Task(Of Integer).Factory.StartNew(
        Function() ExceuteSlowStuff(AddressOf Me.ShowProgress))
End Sub

You're probably wondering where ShowProgress comes from, that is the messier bit.

Private Sub ShowProgress()
    If Me.Progress.InvokeRequired Then
        Dim cross As new Action(AddressOf Me.ShowProgress)
        Me.Invoke(cross)
    Else 
        If Me.Progress.Value = Me.Progress.Maximum Then
            Me.Progress.Value = Me.Progress.Minimum
        Else
            Me.Progress.Increment(1)
        End If

        Me.Progress.Refresh()
    End if
End Sub

Note that because ShowProgress can be invoked from another thread, it checks for cross thread calls. In that case it invokes itself on the main thread.

Solution 2

During the query execution you cannot show a real progress bar. MySQL do not deliver any estimation how long the query will take to be finsihed. You can estimate the time by measuring your old runs and "fake" the progress bar with this informations. But this is kind of overkill. In most cases it is enough to show the user "something". Like a wheel spinning or a progress bar filling up every 2-3 seconds.

If you want a progress bar while filling the items, this is possible without changing much. Just add a progress bar control and increment it inside your "While(myReader.Reader())" loop. I even suspect this takes the longer time then the query. If you query takes long, check if you have an index on column!

If you want to show the user that something is happening you can use a thread. .NET has a nice BackgroundWorker().

It is faily easy to start a BackgroundWorker

        Dim bgw As New BackgroundWorker
        bgw.WorkerReportsProgress = true
        bgw.RunWorkerAsync()

Now you have to do the two events of the backgroundworker:

Dim WithEvents bgw As New BackgroundWorker
Dim progressBar As New progressbar

Sub start()
    bgw.WorkerReportsProgress = true
    bgw.RunWorkerAsync()
End Sub

Sub bgw_DoWork(sender As Object, e As DoWorkEventArgs) Handles bgw.DoWork

    ' put your sql code here
    For i As Integer = 0 To 10000
        If i Mod 1000 Then
            bgw.ReportProgress(i / 100)
        End If
    Next

End Sub

Sub bgw_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles bgw.ProgressChanged

    ' put your progress changed events here
    myProgressBar.Value = e.ProgressPercentage

End Sub

Remeber, inside the DoWork function you cannot access any GUI stuff. Do NOT put message boxes here, do NOT directly change the progressBar. ALWAYS use the bgw.progressChanged event. If you want to give messages from the bgw.doWork to the GUI you can use the reportProgress custom object to do that. Plz read further documentation for this. Do not raise the progressChanged event too often. It is quiet heavy and if you change something in the GUI every time your application might even get VERY slow. I try to call it not more then 10 times per second, if it does not do GUI stuff. And at most 2 time per second, if it does GUI stuff. (Updating a progress bar every second is fine for the user.)

Comments

[Dean Hart]

Thanks for the effort guys :)

[Jodrell]

@DeanHart, note, this is essentially what the BackgroundWorker in Marius's answer does for you. When you fire ProgressChanged it invokes the handler back on the thread that instantiated it.