VB.NET中TextBox的TextChanged事件自动完成功能卡顿问题排查

问题描述 #

在VB.NET中通过TextBox的TextChanged事件实现自动完成功能时，出现严重卡顿。即使数据库仅有6条记录，输入“KBE”“KBT”等字符时仍有明显延迟，需等待自动完成建议加载完成才能继续操作。

演示效果：

原始代码 #

Public Class Form3
    Private iService As New ItemService()
    Private bindingSource1 As BindingSource = Nothing
    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        lblcodeproduct.Visible = False
        TextBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
        TextBox1.AutoCompleteCustomSource.AddRange(iService.GetByCodeProduct().Select(Function(n) n.CodeProduct).ToArray())
    End Sub
    Private Sub GetItemData2(ByVal iditem As String)
        Dim item = iService.GetByCodeProductOrBarcode(iditem)
        If item IsNot Nothing Then
            If String.Equals(iditem, item.CodeProduct, StringComparison.CurrentCultureIgnoreCase) Then
                TextBox1.Text = item.CodeProduct
            End If
            TextBox2.Text = item.Barcode
        Else
            TextBox2.Clear()
            Return
        End If
    End Sub
    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        If TextBox1.Text = "" Then
            lblcodeproduct.Visible = False
            ErrorProvider1.SetError(TextBox1, "")
        Else
            bindingSource1 = New BindingSource With {.DataSource = New BindingList(Of Stock)(CType(iService.GetByCodeProductlike(TextBox1.Text), IList(Of Stock)))}

            If bindingSource1.Count > 0 Then

                lblcodeproduct.Visible = False

                ErrorProvider1.SetError(TextBox1, "")
            Else
                lblcodeproduct.Visible = True
            End If
            GetItemData2(TextBox1.Text)
        End If
    End Sub
End Class
Public Class Stock
    Public Property Id() As Integer
    Public Property CodeProduct() As String
    Public Property Barcode() As String
End Class
Public Class ItemService
    Public Function GetOledbConnectionString() As String

        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL.accdb;Persist Security Info=False;"
    End Function
    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()
    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub
    Public Function GetByCodeProduct() As IEnumerable(Of Stock)
        Dim sql = "SELECT CodeProduct AS CodeProduct FROM Items"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Stock)(sql).ToList()
        End Using
    End Function
    Public Function GetByCodeProductlike(ByVal CodeProduct As String) As IEnumerable(Of Stock)
        Dim sql = $"SELECT CodeProduct FROM Items WHERE CodeProduct LIKE '%{CodeProduct}%'"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Stock)(sql).ToList()
        End Using
    End Function
    Public Function GetByCodeProductOrBarcode(ByVal code As String) As Stock
        Dim sql = $"SELECT * FROM Items WHERE CodeProduct = '{code}' or Barcode = '{code}'"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Stock)(sql).FirstOrDefault()
        End Using
    End Function
End Class

卡顿原因分析 #

1. TextChanged递归触发：GetItemData2中直接修改TextBox1.Text，会再次触发TextChanged事件，导致同一输入多次重复执行数据库查询和UI操作，这是卡顿的核心原因。
2. 频繁数据库连接开销：每次TextChanged事件都会新建数据库连接，执行两次独立查询，即使数据量小，频繁的连接创建与销毁也会产生额外性能损耗。
3. 冗余逻辑：已通过AutoCompleteCustomSource配置自动完成源，TextChanged事件内的bindingSource1相关操作无实际作用，反而增加不必要的计算负担。
4. SQL注入风险：原始代码使用字符串拼接生成SQL语句，存在安全隐患，同时会影响查询解析效率。

修复方案与优化代码 #

核心修改点 #

• 添加标志位防止TextChanged递归触发
• 移除冗余的bindingSource1逻辑
• 使用参数化查询避免SQL注入并优化查询效率
• 减少不必要的数据库请求，新增存在性查询替代全量列表查询
• 优化文本更新逻辑，避免无意义的Text修改

修改后的代码 #

Public Class Form3
    Private iService As New ItemService()
    ' 标志位：防止TextChanged事件递归触发
    Private isUpdatingText As Boolean = False

    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        lblcodeproduct.Visible = False
        TextBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
        ' 预加载所有自动完成项到内存
        Dim codeProducts = iService.GetByCodeProduct().Select(Function(n) n.CodeProduct).ToArray()
        TextBox1.AutoCompleteCustomSource.AddRange(codeProducts)
    End Sub

    Private Sub GetItemData2(ByVal iditem As String)
        If isUpdatingText Then Return

        Dim item = iService.GetByCodeProductOrBarcode(iditem)
        If item IsNot Nothing Then
            ' 仅当文本不一致时才修改，避免触发TextChanged
            If Not String.Equals(TextBox1.Text, item.CodeProduct, StringComparison.CurrentCultureIgnoreCase) Then
                isUpdatingText = True
                TextBox1.Text = item.CodeProduct
                ' 保持光标在文本末尾
                TextBox1.SelectionStart = TextBox1.Text.Length
                isUpdatingText = False
            End If
            TextBox2.Text = item.Barcode
        Else
            TextBox2.Clear()
        End If
    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        If isUpdatingText Then Return

        If String.IsNullOrEmpty(TextBox1.Text) Then
            lblcodeproduct.Visible = False
            ErrorProvider1.SetError(TextBox1, "")
            TextBox2.Clear()
        Else
            ' 用存在性查询替代全量列表查询，减少数据传输
            Dim hasMatch = iService.IsCodeProductExists(TextBox1.Text)
            If hasMatch Then
                lblcodeproduct.Visible = False
                ErrorProvider1.SetError(TextBox1, "")
            Else
                lblcodeproduct.Visible = True
            End If
            GetItemData2(TextBox1.Text)
        End If
    End Sub
End Class

Public Class Stock
    Public Property Id() As Integer
    Public Property CodeProduct() As String
    Public Property Barcode() As String
End Class

Public Class ItemService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL.accdb;Persist Security Info=False;"
    End Function

    Public Function GetByCodeProduct() As IEnumerable(Of Stock)
        Dim sql = "SELECT CodeProduct FROM Items"
        Using conn = New OleDbConnection(GetOledbConnectionString())
            Return conn.Query(Of Stock)(sql).ToList()
        End Using
    End Function

    ' 新增：仅判断是否存在匹配项，无需返回全量数据
    Public Function IsCodeProductExists(ByVal codeProduct As String) As Boolean
        Dim sql = "SELECT COUNT(*) FROM Items WHERE CodeProduct LIKE @CodeProduct"
        Using conn = New OleDbConnection(GetOledbConnectionString())
            Dim count = conn.QueryFirstOrDefault(Of Integer)(sql, New With {.CodeProduct = $"%{codeProduct}%"})
            Return count > 0
        End Using
    End Function

    ' 参数化查询，避免SQL注入并提升查询效率
    Public Function GetByCodeProductOrBarcode(ByVal code As String) As Stock
        Dim sql = "SELECT * FROM Items WHERE CodeProduct = @Code OR Barcode = @Code"
        Using conn = New OleDbConnection(GetOledbConnectionString())
            Return conn.QueryFirstOrDefault(Of Stock)(sql, New With {.Code = code})
        End Using
    End Function
End Class

额外优化建议 #

• 输入延迟触发：添加300ms左右的延迟计时器，用户停止输入后再执行查询，避免每输入一个字符就触发数据库操作。
• 全量数据缓存：由于数据库仅6条记录，可在Form_Load时将所有Stock数据缓存到内存集合中，后续直接从内存查询，完全消除数据库操作开销。

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