0

I'm trying to use a BackgroudWorker to load data into a DataTable and then return it to a DataGridView.

Public Sub Reload_Selected_Invoice()
    bgwLoadOldInvoices.RunWorkerAsync()
End Sub

Private Sub bgwLoadOldInvoices_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bgwLoadOldInvoices.DoWork
    Dim DataTable As New DataTable
    Using SQLSERVER_Connection As New SqlConnection("Server = localhost;Database=test;Integrated Security=SSPI;")
        Using SQLQuery As New SqlCommand("SQLQuery that takes a few seconds;", SQLSERVER_Connection)
            SQLSERVER_Connection.Open()
            Using SqlDataAdapter As New SqlDataAdapter(SQLQuery)
                SqlDataAdapter.Fill(DataTable)
            End Using
            SQLSERVER_Connection.Close()
        End Using
    End Using
    e.Result = DataTable
End Sub

Private Sub bgwLoadOldInvoices_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles bgwLoadOldInvoices.RunWorkerCompleted
    dgvOldInvoices.DataSource = e.Result
End Sub

When I push the button the form stops responding until the query is finished. This is my first attempt with Threading and I don't see where I'm messing up.

Edit 1

Private Sub bgwLoadOldInvoices_DoWork(sender As Object, e As DoWorkEventArgs) Handles bgwLoadOldInvoices.DoWork
    Dim DataGridView1 As New DataGridView
    Dim DataTable As New DataTable
    Using SQLSERVER_Connection As New SqlConnection("Server = localhost;Database=test;Integrated Security=SSPI;")
        Using SQLQuery As New SqlCommand("SQLQuery that takes a few seconds;", SQLSERVER_Connection)
            Using SqlDataAdapter As New SqlDataAdapter(SQLQuery)
                SqlDataAdapter.Fill(DataTable)
            End Using
        End Using
    End Using
    DataGridView1.DataSource = DataTable
    e.Result = DataGridView1
End Sub

Private Sub bgwLoadOldInvoices_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles bgwLoadOldInvoices.RunWorkerCompleted
    dgvOldInvoices = DirectCast(e.Result, DataGridView)
    dgvOldInvoices.Refresh()
    MessageBox.Show("Finished!")
End Sub
Kayot
  • 582
  • 2
  • 20
  • If there is a large amount of data, it may take some time to draw the DGV. And normally the connection string would have something like `Server=.\SQLEXPRESS` rather than `Server=localhost`. (Also, you don't need to open and close the connection as the `.Fill` will do that automatically, but that is nothing to do with the problem.) – Andrew Morton Apr 12 '16 at 20:12
  • @AndrewMorton I'm using SQL Server 2008. I've tried to feed the data into a DataGridView, but that isn't working as a return. It just shows the message box instantly and the DataGridView on the form doesn't update. See Edit 1. – Kayot Apr 13 '16 at 12:34
  • Have you got it working without using a BGW? I suggest returning a DataTable (as you did first) rather than a DGV. – Andrew Morton Apr 14 '16 at 18:39
  • The problem seems to be that the DGV just takes a long time to draw. It's only showing 8,000 rows with maybe eight columns but it takes ten seconds to draw the DGV. I'll try using BGW on another area that does take a long time to query and then post back. – Kayot Apr 14 '16 at 23:28
  • In that case, you might be interested in [How can we do pagination in datagridview in winform](http://stackoverflow.com/questions/2825771/how-can-we-do-pagination-in-datagridview-in-winform). – Andrew Morton Apr 15 '16 at 08:07

0 Answers0