BackgroundWorkers (which I will refer to as bgWorker) are great for doing background processing in your application, thus preventing your program from going into a “Not Responding” status. One downfall, however, is that you cannot pass or reference data in a separate thread from the bgWorker. The following code shows how to get around this by utilizing Global/Public variables because they are referenced between all threads of the application.
Global/Public Variables
[vbnet] ‘ public variables
Public dr As OdbcDataReader
Public conn As OdbcConnection
Dim miliSecond As Long = 1000
Dim timerSeconds As Long
Dim startDttm As Date
Dim endDttm As Date
Dim sqlComm As String
Dim dt As New DataTable
Dim da As New OdbcDataAdapter
Dim strConnString As String
Dim comm As OdbcCommand
[/vbnet]
How to call the BackgroundWorker
[vbnet]
bgWorker = New BackgroundWorker
bgWorker.WorkerReportsProgress = True
bgWorker.WorkerSupportsCancellation = True
bgWorker.RunWorkerAsync() ‘ run the background worker
[/vbnet]
Run the BackgroundWorker and fetch the data
[vbnet]
Private Sub FetchData(ByVal sender As Object, ByVal e As DoWorkEventArgs) Handles bgWorker.DoWork
Try
Dim conn As New OdbcConnection(strConnString)
startDttm = Now()
comm = New OdbcCommand(sqlComm)
comm.Connection = conn
comm.Connection.Open()
dt = New DataTable
da = New OdbcDataAdapter
da.SelectCommand = comm ‘ Utilize the SELECT statement
da.Fill(dt) ‘ Fill the Data Table
Catch ex As Exception
MsgBox(“ERROR: ” & ex.Message, MsgBoxStyle.Critical, “Error running SQL”)
dbClose()
End Try
End Sub
[/vbnet]
What to do when the BackgroundWorker is finished
[vbnet]
Private Sub bgWorkerCompleted(ByVal sender As Object, ByVal e As RunWorkerCompletedEventArgs) Handles bgWorker.RunWorkerCompleted
Try
UpdateDataGrid()
Catch ex As Exception
bgWorker.CancelAsync()
End Try
End Sub
[/vbnet]
Update the DataGridView (dg)
[vbnet]
Private Sub UpdateDataGrid()
Try
Dim intCount As Integer
intCount = dt.Rows.Count ‘ number of rows returned by SQL
Select Case intCount
Case Is < 1000 ‘ fill if less than 1000 rows
Me.dg.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
dg.DataSource = dt ‘ Populate the Data Grid with the Data Table values
dr = comm.ExecuteReader
Case Else
If MsgBox(“This will display ” & Format(dt.Rows.Count, “###,###,##0″) & ” rows. Would you like to continue?”, MsgBoxStyle.YesNo, “Continue?”) = MsgBoxResult.Yes Then
Me.dg.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
dg.DataSource = dt ‘ Populate the Data Grid with the Data Table values
dr = comm.ExecuteReader
End If
End Select
dbClose()
dg.AutoResizeColumns()
If comm.ExecuteNonQuery >= 0 Then
intCount = comm.ExecuteNonQuery
End If
AddToSummary(Format(intCount, “###,###,##0″) & IIf(intCount = 1, ” record found”, ” records found”), False)
Dim dur As Global.System.TimeSpan = Now.Subtract(startDttm)
AddToSummary(” – ” & dur.Seconds & “.” & dur.Milliseconds & ” seconds”, True)
Catch ex As Exception
MessageBox.Show(“Error updating data grid”, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
[/vbnet]
Close the Database Connection
[vbnet]
Private Sub dbClose()
Try ‘ Close the Data Reader
dr.Close()
dr = Nothing
Catch ex As Exception
‘MsgBox(“Error closing the data reader” & vbCrLf & ex.Message, MsgBoxStyle.Critical, “Data Reader Close Issue”)
End Try
Try ‘ Close the Connection
conn.Dispose()
conn = Nothing
Catch ex As Exception
MsgBox(“Error closing the connection” & vbCrLf & ex.Message, MsgBoxStyle.Critical, “ODBC Connection Close Issue”)
End Try
End Sub
[/vbnet]
Add to Summary method – I use a TextBox (txt_Status) on my Form for displaying application status to the user
[vbnet]
Private Sub AddToSummary(ByVal strText As String, ByVal Append As Boolean)
If Append Then
Me.txt_Status.Text += strText
Else
Me.txt_Status.Text = strText
End If
End Sub
[/vbnet]
Codepad 