.net - 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         

Answer 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.