database - 如何使用 VB.NET 在 DataGridView 中执行“按类型搜索”文本框

Question

我已将 DataGridView 连接到数据库，但无法实现搜索功能。

程序的流程是当我单击 DataGridView 的一列并在搜索框中键入时，我只能从同一列而不是旁边的其他列中获取结果。

它还应该一个字母一个字母地搜索，所以基本上是一个 TextChanged 事件。

Answer 1

这就是我的做法

首先，有两个变量来存储来自数据库的原始数据表，还有一个字符串变量来存储您选择的 dgv 列标题文本(稍后将用于执行过滤器)。

Private oriDataTable As New DataTable
Private columnToFilter As String = String.Empty

我对一些虚拟数据的测试

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'dummy datatable
    oriDataTable.Columns.Add(New DataColumn("ID"))
    oriDataTable.Columns.Add(New DataColumn("FirstName"))
    oriDataTable.Columns.Add(New DataColumn("LastName"))
    For i = 0 To 5
        Dim dr As DataRow = oriDataTable.NewRow()
        dr.Item("ID") = i
        dr.Item("FirstName") = "fn type1 " & i
        dr.Item("LastName") = "ln type1 " & i
        oriDataTable.Rows.Add(dr)
    Next
    For i = 6 To 10
        Dim dr As DataRow = oriDataTable.NewRow()
        dr.Item("ID") = i
        dr.Item("FirstName") = "fn type2" & i
        dr.Item("LastName") = "ln type2" & i
        oriDataTable.Rows.Add(dr)
    Next

    'Since you already connected to database 
    'i assume that you could fill a datatable and bind to dgv
    dgvToFilter.DataSource = oriDataTable
    columnToFilter = "ID" 'Assign any default column name
End Sub

然后添加一个ColumnHeaderMouseClick dgv 上的事件处理程序，每次用户单击时更新 columnToFilter。

Private Sub dgvToFilter_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgvToFilter.ColumnHeaderMouseClick
    Dim clickedColumn As DataGridViewColumn = dgvToFilter.Columns(e.ColumnIndex)

    'Note:HeaderText must match with your datatable column name
    columnToFilter = clickedColumn.HeaderText
    lblHeaderSelected.Text = columnToFilter
End Sub

最后是 TextChaged 事件。使用 DataTable.Select过滤数据表并将结果(如果有)更新到 dgv 的方法。

Private Sub txtFilterText_TextChanged(sender As Object, e As EventArgs) Handles txtFilterText.TextChanged
    If txtFilterText.Text.Length <= 0 Then dgvToFilter.DataSource = oriDataTable

    Dim filterString = String.Format("{0} LIKE '{1}%'", columnToFilter, txtFilterText.Text)

    Dim dataRows As DataRow() = oriDataTable.Select(filterString)
    'Choose what you wan to do if no row is found. I bind back the oriDataTable.
    dgvToFilter.DataSource = If(dataRows.Count > 0, dataRows.CopyToDataTable(), oriDataTable)
End Sub