How to update progressbar when retrieve data from database to datagridview VB.NET

Question

i have VB.net code to retrieve data from SQL server (stored proc) to Datagridview (dgMC). everything worked fine, but the progressbar1 not update. i'd like to progressbar1 shows update in percentage for user knows the status of data retrieve. the data is around 1000K.

  Friend Delegate Sub SetDataSourceDelegate(table As DataTable)
    Private Sub setDataSource(table As DataTable)
        ' Invoke method if required:
        If Me.InvokeRequired Then
            Me.Invoke(New SetDataSourceDelegate(AddressOf setDataSource), table)
        Else
            dgMC.DataSource = table
            ProgressBar1.Visible = False
        End If
    End Sub
    Private Sub loadTable()
        Dim cnn As SqlConnection = GetConnection()
        Dim cmdSearch As New SqlCommand("MC_Display", cnn)
        cmdSearch.CommandType = CommandType.StoredProcedure
        Try
            cnn.Open()
            Dim readerSearch = cmdSearch.ExecuteReader
            If readerSearch.HasRows Then
                Dim dt = New DataTable()
                dt.Load(readerSearch)
                setDataSource(dt)
            Else
                Me.Cursor = Cursors.Default
                MsgBox("No Data Found.", MsgBoxStyle.Exclamation)
                dgMC.DataSource = Nothing
            End If
            readerSearch.Close()
        Catch ex As Exception
            Throw ex
        Finally
            cnn.Close()
        End Try
    End Sub

    Private Sub btnGoMC_Click(sender As Object, e As EventArgs) Handles btnGoMC.Click
        ProgressBar1.Visible = True
        ProgressBar1.Style = ProgressBarStyle.Marquee
        Dim thread As New System.Threading.Thread(New System.Threading.ThreadStart(AddressOf loadTable))
        thread.Start()
    End Sub

Answer 1

To know exactly the number of data retrieve, you have to create a query with count(*) of your data.

Then, when you are retrieving data, you have to know which row is being retrieve, because you have to calculate the percentage.

And finally, you refresh your progressBar :

Dim percentage As Double = (currentRow / totalRows) * 100
ProgressBar.Value = Int32.Parse(Math.Truncate(percentage).ToString())

I hope it helps you

Answer 2

Since you're not performing anything that is quantifiable (you're issuing a sql query against a database, blocking processing for that thread until the database gives you the data you requested) you're not going to be able to update the progress bar with anything meaningful. In cases like this a Marquee scroll is usually sufficient. Also, you have the progress bar on the UI thread so it can be responsive and kick off a new thread to keep the UI thread from being blocked. At this moment you can't access the progress bar on the UI thread easily.

Some other thoughts though... If you have access to the Task Parallel Library, I would advise using that instead of creating a raw Thread and starting the process execution. You can utilize a type in the TPL called Task, which is an abstraction of Thread and takes care of some details you probably don't need to concern yourself with in this particular application/scenario. It also yields powerful asynchronous programming through the Async/Await paradigm in .NET 4.5. There is an excellent blog series by a guy named Stephen Cleary who has great expertise on this paradigm: Stephen Cleary Async/Await

A brief Example:

Private Sub btnGoMC_Click(sender As Object, e As EventArgs) Handles btnGoMC.Click ProgressBar1.Visible = True ProgressBar1.Style = ProgressBarStyle.Marquee Dim thread As New System.Threading.Thread(New System.Threading.ThreadStart(AddressOf loadTable)) thread.Start() End Sub

Can become:

`Private Async Sub btnGoMC_Click(sender As Object, e As EventArgs) Handles btnGoMC.Click
    ProgressBar1.Visible = True
    ProgressBar1.Style = ProgressBarStyle.Marquee
    Await Task.Run(Sub() loadTable)
End Sub`