VB.NET WinForm多过滤器联动：避免DataTable重置并展示结果

示例数据集 #

需求 #

要筛选出product为TAA12且sampling为Resample的数据，但现有代码里两个筛选下拉框（ComboBox2选产品、ComboBox3选抽样类型）每次触发时都会单独查询数据库，没法实现先选任意一个过滤器后叠加另一个条件的联动效果。

问题分析 #

1. 两个ComboBox的事件处理各自独立查询，只用到当前选择的条件，没结合另一个ComboBox的选择
2. ComboBox2的代码有SQL命令错误：错把searchTerm当命令文本传入，不是定义好的query变量，会导致语法报错

修改后的代码 #

' 全局变量存储原始数据，可选，也可以每次都从数据库查询
Private originalDt As DataTable

Private Sub LoadData()
    Dim query1 As String = "SELECT DISTINCT product FROM ppiDB;"
    Dim query2 As String = "SELECT DISTINCT sampling FROM ppiDB;"
    Dim allDataQuery As String = "SELECT * FROM testingDB.ppiDB;"

    Try
        ' 加载产品下拉选项
        Using conn1 As New MySqlConnection(sqlServer)
            conn1.Open()
            Using cmd1 As New MySqlCommand(query1, conn1)
                Using reader1 As MySqlDataReader = cmd1.ExecuteReader()
                    While reader1.Read()
                        ComboBox2.Items.Add(reader1("product").ToString())
                    End While
                End Using
            End Using
        End Using

        ' 加载抽样类型下拉选项
        Using conn2 As New MySqlConnection(sqlServer)
            conn2.Open()
            Using cmd2 As New MySqlCommand(query2, conn2)
                Using reader2 As MySqlDataReader = cmd2.ExecuteReader()
                    While reader2.Read()
                        ComboBox3.Items.Add(reader2("sampling").ToString())
                    End While
                End Using
            End Using
        End Using

        ' 加载全部原始数据到全局DataTable，后续过滤直接在内存操作，减少数据库访问
        Using conn3 As New MySqlConnection(sqlServer)
            conn3.Open()
            Using cmd3 As New MySqlCommand(allDataQuery, conn3)
                Dim adapter As New MySqlDataAdapter(cmd3)
                originalDt = New DataTable()
                adapter.Fill(originalDt)
                DataGridView1.DataSource = originalDt
            End Using
        End Using

    Catch ex As Exception
        MessageBox.Show("加载数据出错: " & ex.Message)
    End Try
End Sub

' 统一过滤方法，两个ComboBox变化时都调用这个方法
Private Sub ApplyFilters()
    Try
        Dim filteredDt As DataTable = originalDt.Copy()
        Dim view As New DataView(filteredDt)
        Dim filterConditions As List(Of String) = New List(Of String)()

        ' 添加产品过滤条件，如果有选择的话
        If Not String.IsNullOrEmpty(ComboBox2.Text) Then
            Dim productPattern As String = ""
            Select Case ComboBox2.Text
                Case "TAA12", "TAA13"
                    productPattern = "AA"
                Case "TBB12", "TBB13"
                    productPattern = "BB"
                Case "TCC23", "TCC24", "TCC25"
                    productPattern = "CC"
                Case Else
                    productPattern = ComboBox2.Text
            End Select
            ' 用DataView的RowFilter做模糊匹配
            filterConditions.Add($"product LIKE '%{productPattern}%'")
        End If

        ' 添加抽样类型过滤条件，如果有选择的话
        If Not String.IsNullOrEmpty(ComboBox3.Text) Then
            filterConditions.Add($"sampling = '{ComboBox3.Text}'")
        End If

        ' 组合所有过滤条件
        If filterConditions.Count > 0 Then
            view.RowFilter = String.Join(" AND ", filterConditions)
        End If

        ' 绑定到DataGridView
        DataGridView1.DataSource = view
    Catch ex As Exception
        InsertLog("过滤出错: " & ex.ToString, "过滤器错误")
    End Try
End Sub

' 产品下拉框变化事件
Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
    ApplyFilters()
End Sub

' 抽样类型下拉框变化事件
Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox3.SelectedIndexChanged
    ApplyFilters()
End Sub

关键说明 #

1. 全局DataTable存原始数据：程序启动时加载全部数据到originalDt，后续过滤直接在内存操作，减少数据库访问，提升响应速度
2. 统一过滤方法ApplyFilters：两个ComboBox变化时都调用这个方法，同时获取两个控件的选择值，组合成完整过滤条件
3. DataView.RowFilter实现过滤：用DataView的行过滤功能，支持多条件叠加，语法和SQL WHERE子句类似
4. 简化产品匹配逻辑：用Select Case替代原有多分支判断，代码更简洁
5. 处理未选择情况：如果某个ComboBox没选择，就不添加对应过滤条件，默认显示符合其他条件的所有数据

如果不想在内存缓存数据，也可以修改ApplyFilters方法，根据当前选择的条件动态拼接SQL语句，直接从数据库查过滤后的数据，示例如下：

Private Sub ApplyFiltersFromDB()
    Try
        Dim query As String = "SELECT * FROM testingDB.ppiDB WHERE 1=1"
        Dim cmd As New MySqlCommand()
        cmd.Connection = New MySqlConnection(sqlServer)

        ' 添加产品过滤条件
        If Not String.IsNullOrEmpty(ComboBox2.Text) Then
            Dim productPattern As String = ""
            Select Case ComboBox2.Text
                Case "TAA12", "TAA13"
                    productPattern = "%AA%"
                Case "TBB12", "TBB13"
                    productPattern = "%BB%"
                Case "TCC23", "TCC24", "TCC25"
                    productPattern = "%CC%"
                Case Else
                    productPattern = $"%" & ComboBox2.Text & "%"
            End Select
            query += " AND product LIKE @product"
            cmd.Parameters.AddWithValue("@product", productPattern)
        End If

        ' 添加抽样类型过滤条件
        If Not String.IsNullOrEmpty(ComboBox3.Text) Then
            query += " AND sampling = @sampling"
            cmd.Parameters.AddWithValue("@sampling", ComboBox3.Text)
        End If

        cmd.CommandText = query
        Dim adapter As New MySqlDataAdapter(cmd)
        Dim ds As New DataSet()
        adapter.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)
    Catch ex As Exception
        InsertLog("数据库过滤出错: " & ex.ToString, "数据库过滤器错误")
    End Try
End Sub

使用这种方式时，把两个ComboBox的事件处理方法改成调用ApplyFiltersFromDB即可。

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