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

The Reader will be returned with the first row, you'll need to increment a progress bar as you enumerate the rows by calling myReader.Read. The SQL part is already asycnronous. However, if you want the changes to the progress bar to be visible to the user while the data is read you'll need to run executeMyQuery in a background thread to leave the main GUI thread free to update the progress bar as you trigger the change events by incrementing the progress. — Jodrell
– Jodrell, Commented May 22, 2013 at 10:17
Thanks, But I dont know how to use threads... Tried them and get errors of my controls not being part of thread etc. — Dean Hart
– Dean Hart, Commented May 22, 2013 at 10:22
You won't know how many rows will be returned until the query is finished so you won't be able to show actual progress. You could show estimated progress or just indicate activity. — Jodrell
– Jodrell, Commented May 22, 2013 at 10:23
Side note: Please do not build SQL queries by building a string. Use parameters instead! tblname = "something; drop table users; --"; — PhonicUK
– PhonicUK, Commented May 22, 2013 at 10:27

Mark Schultheiss · Accepted Answer · 2013-08-29 17:54:00Z

9

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.

edited Aug 29, 2013 at 17:54

Mark Schultheiss

34.3k13 gold badges75 silver badges116 bronze badges

answered May 22, 2013 at 11:53

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dean Hart Over a year ago

Thanks for the effort guys :)

Jodrell Over a year ago

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

Marius · Accepted Answer · 2013-05-22 10:35:33Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related