VB.NET：点击按钮用BackgroundWorker显示加载动画并调用Oracle存储过程

Hey there! Let's tackle this step by step. You're already off to a great start with the BackgroundWorker for CSV imports—we'll adapt that structure to work with your Oracle stored procedure, plus add a friendly wait dialog to keep users in the loop so they don't think the app is frozen.

1. First, Fix & Refine the Oracle Stored Procedure Call #

Your existing RESA_Checker function has some undefined variables and missing setup. Let's clean that up so it reliably returns a DataTable from the stored procedure:

Imports System.Data.OracleClient ' Use Oracle.ManagedDataAccess.Client for modern ODP.NET

Public Function GetSalesCheckerData(ByVal stores As String, ByVal bus_date As String, ByVal warehouse As String) As DataTable
    Dim dt As New DataTable()
    Dim connStr As String = "Your_Oracle_Connection_String_Here" ' Replace with your actual connection string
    
    ' Using blocks ensure resources are cleaned up automatically
    Using ora_conn As New OracleConnection(connStr)
        Using cmd As New OracleCommand("SALES_CHECKER.procedure_checker", ora_conn)
            cmd.CommandType = CommandType.StoredProcedure
            
            ' Add input parameters
            cmd.Parameters.Add(New OracleParameter("stores", OracleDbType.Varchar2)).Value = stores
            cmd.Parameters.Add(New OracleParameter("bus_date", OracleDbType.Varchar2)).Value = bus_date
            cmd.Parameters.Add(New OracleParameter("warehouse", OracleDbType.Varchar2)).Value = warehouse
            
            ' Configure output ref cursor
            Dim outputCursor As New OracleParameter("O_OUTPUT", OracleDbType.RefCursor)
            outputCursor.Direction = ParameterDirection.Output
            cmd.Parameters.Add(outputCursor)
            
            ' Fill DataTable with results
            Using adap_or As New OracleDataAdapter(cmd)
                ora_conn.Open()
                adap_or.Fill(dt)
            End Using
        End Using
    End Using
    
    Return dt
End Function

Note: If you're using the newer Oracle Managed Driver, swap System.Data.OracleClient with Oracle.ManagedDataAccess.Client and update your connection string to match the driver's requirements.

2. Adapt the BackgroundWorker to Use the Stored Procedure #

We'll replace the CSV-reading logic in your BackgroundWorker with calls to our refined stored procedure function, and adjust progress reporting to work with the DataTable rows.

First, update your form-level variables (remove CSV-specific ones, add new ones):

Private dtResult As DataTable ' Holds data fetched from Oracle
Private blnReported As Boolean = True ' Tracks if progress report is complete

Modify the bgWorker_DoWork event to fetch data and process rows:

Private Sub bgWorker_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bgWorker.DoWork
    ' Replace these with actual user input (e.g., from textboxes on your form)
    Dim storesParam As String = "Your_Stores_Value"
    Dim busDateParam As String = "Your_Bus_Date_Value"
    Dim warehouseParam As String = "Your_Warehouse_Value"
    
    Try
        ' Fetch data from Oracle (this is the time-consuming part)
        dtResult = GetSalesCheckerData(storesParam, busDateParam, warehouseParam)
        
        If bgWorker.CancellationPending Then
            e.Cancel = True
            Exit Sub
        End If
        
        ' Process each row to add to the DataGridView
        For rowIndex As Integer = 0 To dtResult.Rows.Count - 1
            If bgWorker.CancellationPending Then
                e.Cancel = True
                Exit Sub
            End If
            
            ' Wait for previous progress update to finish
            While Not blnReported
                System.Threading.Thread.Sleep(10) ' Avoid tight loop
            End While
            
            ' Calculate progress percentage
            Dim progressPercent As Integer = CInt((rowIndex + 1) / dtResult.Rows.Count * 100)
            
            ' Report progress with the current row data
            bgWorker.ReportProgress(progressPercent, dtResult.Rows(rowIndex))
            
            blnReported = False
        Next
    Catch ex As Exception
        ' Pass errors to the completion event
        e.Result = ex
    End Try
End Sub

Update bgWorker_ProgressChanged to add rows to the DataGridView:

Private Sub bgWorker_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bgWorker.ProgressChanged
    Dim currentRow As DataRow = DirectCast(e.UserState, DataRow)
    
    ' Add row to DataGridView (replace column names with your actual DB column names)
    Dim dgvRowIndex As Integer = dgvCSVData.Rows.Add()
    With dgvCSVData.Rows(dgvRowIndex)
        .Cells("colNo").Value = dgvRowIndex + 1
        .Cells("colDate").Value = currentRow("BUS_DATE")
        .Cells("colItem").Value = currentRow("ITEM_NAME")
        .Cells("colQty").Value = currentRow("QUANTITY")
        .Cells("colUnit").Value = currentRow("UNIT")
        .Cells("colAmt").Value = currentRow("AMOUNT") ' Or calculate like you did for CSV
    End With
    
    ' Update progress bar
    pgbCopyProgress.Value = e.ProgressPercentage
    
    ' Mark progress report as complete
    blnReported = True
End Sub

Update bgWorker_RunWorkerCompleted to handle cleanup and the wait dialog:

Private Sub bgWorker_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgWorker.RunWorkerCompleted
    ' Close and dispose the wait dialog
    If WaitForm IsNot Nothing AndAlso WaitForm.Visible Then
        WaitForm.Close()
        WaitForm.Dispose()
    End If
    
    ' Handle different completion states
    If e.Cancelled Then
        lblStatus.Text = "Operation cancelled. Press Start again."
        dgvCSVData.Rows.Clear()
    ElseIf e.Result IsNot Nothing AndAlso TypeOf e.Result Is Exception Then
        Dim ex As Exception = DirectCast(e.Result, Exception)
        lblStatus.Text = $"Error: {ex.Message}"
        MessageBox.Show($"An error occurred: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        dgvCSVData.Rows.Clear()
    Else
        lblStatus.Text = "Data loaded successfully!"
        pgbCopyProgress.Value = 100
    End If
    
    ' Reset button states
    btnStart.Enabled = True
    btnStop.Enabled = False
End Sub

3. Create the Wait Dialog (With Loading GIF or Progress Bar) #

Let's build a simple wait form to show while the background task runs:

1. Add a new Windows Form to your project, name it WaitForm.
2. Add a Label (set Text to "正在搜索，请稍候...") and a PictureBox (to hold your loading GIF).
3. Set these form properties:
   • FormBorderStyle = None
   • StartPosition = CenterParent
   • ShowInTaskbar = False
   • BackColor = Match your main form's background
4. Load a loading GIF into the PictureBox (set SizeMode = AutoSize).

Add a form-level variable for the wait dialog in your main form:

Private WaitForm As WaitForm

Update btnStart_Click to show the dialog before starting the BackgroundWorker:

Private Sub btnStart_Click(sender As Object, e As EventArgs) Handles btnStart.Click
    btnStart.Enabled = False
    btnStop.Enabled = True
    lblStatus.Text = "Loading data from database..."
    
    ' Initialize and show the wait dialog (non-modally so the worker can run)
    WaitForm = New WaitForm()
    WaitForm.Show(Me)
    
    ' Start the background task
    bgWorker.RunWorkerAsync()
End Sub

4. Quick Tips for Smooth Operation #

• Thread Safety: We use ProgressChanged to update the UI because it runs on the main thread—never modify UI controls directly from DoWork.
• User Input: Replace the hardcoded parameters in bgWorker_DoWork with values from textboxes or other input controls on your form.
• Cancellation: The existing cancel logic still works—when the user clicks Stop, we check CancellationPending in the loop to exit early.

内容的提问来源于stack exchange，提问作者Rash Meme