Member Avatar for DelilahDemented

I have never used a background worker in vb.net, but from what I have read, I think it will help resolve an issue I am having during runtime. I am executing an sql statement, but it takes a bit to process. I think a background worker could help with this but I am unsure how to to implement this correctly. I greatly appreciate any help, tips, or comments. Thanks in advance!

 Private Sub DeparmentButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn3EData.Click, Btn3WData.Click, Btn4EData.Click, BtnCCUData.Click, BtnERData.Click, BtnAllData.Click, BtnMatData.Click
        'Declare variable
        Dim SelectedDeptButton As Button = CType(sender, Button)

        'Sends Department Information Needed to build SQL statement
        FloorInfoNeeded(SelectedDeptButton.Tag)
    End Sub

#Region "SQLInformation"
    Public Sub FloorInfoNeeded(ByVal DepartmentNumber As String)
        'Declare variable
        Dim DepartmentsNeeded As String() = DepartmentNumber.Split("|")

        'Base SQL string statement
        StringNeeded = "SELECT [Patient Account Number], [PV1 Room], [PV1 Financial Class], [First Name] + ' ' + MI + ' ' + [Last Name] [Patient Name], [PV1 Attending Phy Lastname], SUBSTRING([PV1 Admit Date/Time], 5, 2) + '/' + SUBSTRING([PV1 Admit Date/Time], 7, 2) + '/' + SUBSTRING([PV1 Admit Date/Time], 1, 4) [Admit Date], SUBSTRING([PV1 Admit Date/Time], 9, 4) [Admit Time], [PV1 Admit Source], [PV1 Discharge Date/Time] FROM ADT WHERE (("

        'Adds the Departments Needed to the SQL statement
        Dim i As Integer = 0
        For Each dept As String In DepartmentsNeeded
            If i = 0 Then
                'First department
                StringNeeded = StringNeeded & "[PV1 Department] = '" & dept & "' "
            Else
                'Additional departments
                StringNeeded = StringNeeded & "OR [PV1 Department] = '" & dept & "' "
            End If
            i = i + 1
        Next

        'Ending section of SQL statement
        StringNeeded = StringNeeded & ") AND [PV1 Discharge Date/Time] = '') ORDER BY [PV1 Room]"

        'Determines what appears in the label based on the department
        If i = 1 Then
            Select Case DepartmentsNeeded(0)
                Case "030"
                    LblFloor.Text = "Three East"
                Case "032"
                    LblFloor.Text = "Three West"
                Case "028"
                    LblFloor.Text = "Four East"
                Case "042"
                    LblFloor.Text = "Critical Care"
                Case "060"
                    LblFloor.Text = "Emergency Department"
            End Select
        ElseIf i = 2 Then
            LblFloor.Text = "Maternity/Nursery"
        Else
            LblFloor.Text = "All Departments"
        End If

        'Run the built SQL string
        RunSQL(StringNeeded)
    End Sub

    Private Sub RunSQL(ByVal SQLString As String)
        'Declare variables
        Dim myConnStr As String = My.Settings.CPSISQLConnectionString 'Connection string to Database
        Dim table As New DataTable 'Storage for holding data

        'Open and read
        Using con As New SqlConnection(myConnStr)
            con.Open()

            Dim cmd As New SqlCommand(SQLString, con)
            Dim reader As SqlDataReader
            reader = cmd.ExecuteReader
            table.Load(reader)
            With DataGridView1
                .DataSource = table
            End With

            'Close all
            reader.Close()
            con.Close()
            cmd.Dispose()
        End Using
    End Sub
#End Region

    Private Sub bw_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bw.DoWork
        'TODO:  Is this where RunSQL() should go?
    End Sub

    Private Sub bw_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bw.ProgressChanged
        'TODO:  How does it determine if the progress has changed?
    End Sub

    Private Sub bw_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bw.RunWorkerCompleted
        'TODO:  How does it determine if the process is completed?
    End Sub
  • 2 Contributors
  • 1 Reply
  • 586 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nmaillet

Dani AI

Generated

Short, practical pattern to make your SQL call run off the UI thread (builds on 's suggestion): pass the built SQL to the BackgroundWorker with RunWorkerAsync, execute and fill a local DataTable inside DoWork, then return that table via e.Result. Never touch UI controls from DoWork — update LblFloor and any UI state before starting the worker, and bind the DataGridView in RunWorkerCompleted. Enable progress reporting and cancellation so the UI can show status and stop a long query if needed.

' Before starting: enable features once (designer or form load)
bw.WorkerReportsProgress = True
bw.WorkerSupportsCancellation = True

' Start the worker instead of calling RunSQL(...)
bw.RunWorkerAsync(StringNeeded)

' DoWork: run query on background thread, return DataTable
Private Sub bw_DoWork(sender As Object, e As DoWorkEventArgs) Handles bw.DoWork
    Dim worker = CType(sender, ComponentModel.BackgroundWorker)
    Dim sql As String = CStr(e.Argument)
    Dim dt As New DataTable()

    If worker.CancellationPending Then
        e.Cancel = True
        Return
    End If

    worker.ReportProgress(0, "Opening DB")
    Using cn As New SqlConnection(My.Settings.CPSISQLConnectionString)
        Using da As New SqlDataAdapter(sql, cn)
            worker.ReportProgress(50, "Fetching rows")
            da.Fill(dt)
        End Using
    End Using

    If worker.CancellationPending Then
        e.Cancel = True
        Return
    End If

    e.Result = dt
End Sub

' Completed: runs on UI thread — safe to bind controls
Private Sub bw_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles bw.RunWorkerCompleted
    If e.Error IsNot Nothing Then
        MessageBox.Show("Query error: " & e.Error.Message)
    ElseIf e.Cancelled Then
        ' optional: clear or restore UI state
    Else
        DataGridView1.DataSource = CType(e.Result, DataTable)
    End If
End Sub

Troubleshooting tips: check bw.IsBusy before starting a new run (or call CancelAsync), catch large-result performance by paging or using virtual mode, and keep Try/Catch minimal in DoWork so exceptions surface to RunWorkerCompleted. For modern projects consider Async/Await + ExecuteReaderAsync or Task.Run instead of BackgroundWorker.

Member Avatar for nmaillet

Is this where RunSQL() should go?

Yup.

How does it determine if the progress has changed?

You use the BackgroundWorker.ReportProgress() method; that is, you explicitly update progress if you wish. You can get the instance of the BackgroundWorker by using the sender parameter. It is important to note that DoWork is run on a separate thread, but ProgressChanged and RunWorkerCompleted both run on the UI thread (which allows you to update controls and such).

How does it determine if the process is completed?

The work is completed when the DoWork delegate returns (i.e. bw_DoWork returns).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.