扫描条码后SQL返回单条记录，如何向DataGridView追加新行而非替换？

开发了一款条码扫描应用，流程如下：

• 用户通过条码扫描器将条码输入文本框（扫描器自动发送CR/LF，无需手动提交）
• 扫描后通过SQL查询验证条码，查询每次仅返回数据库中一行数据
• 需要将该行数据添加到DataGridView，最终DataGridView中每行对应一个扫描的条码

当前实现的VB.NET代码片段如下：

Public Function GetResults() As DataTable

        Dim dtResults As New DataTable
        Dim dtScanned As New DataTable
        Dim connString As String = "Data Source=SQL03\SQL2019;Initial Catalog=aa"

        Using conn As New SqlConnection(connString)
            Using cmd As New SqlCommand("
                SELECT requests.request_id AS 'Request ID', patients.first_name AS GivenName, patients.surname AS Surname,
                concat(patients.day_birth, '/', patients.month_birth, '/', patients.year_birth) AS DOB, patients.sex AS 'Sex',
                services.service_abbrev AS 'Ref.Service', specimens.specimen AS 'Specimen', referral_details.referral_dt AS 'Referral Date'
                
                FROM
                { insert a bunch of joins here }

                WHERE
                    referral_details.referral_dt >= @StartDate AND
                    referral_details.referral_dt <= @EndDate AND
                    labs.lab_id = '46' AND
                    requests.request_id = @LabID
                
                ORDER BY
                    labs.lab_name ASC,
                    requests.request_id ASC,
                    services.service_abbrev ASC

                ", conn)
                cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dateFrom.Value
                cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DateTo.Value
                cmd.Parameters.Add("@LabID", SqlDbType.Int).Value = txtBarcode.Text
                cmd.CommandTimeout = 0

                Try
                    conn.Open()
                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    dtResults.Load(reader)

                    If dtResults.Rows.Count = 0 Then
                        MessageBox.Show("No referral found for this Lab ID.", "No Referral", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        Return dtResults
                    End If

                Catch Ex As Exception
                    MessageBox.Show("Unable to connect to SQL Server. Please check your network connection and if required, report the issue to IT." & vbCrLf & vbCrLf & Ex.Message, "No Connection!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using

        End Using

        For Each dr As DataRow In dtResults.Rows
            dtScanned.ImportRow(dr)

        Next

        Return dtScanned

    End Function

目前遇到的问题：每次扫描条码时，DataGridView中的第一行都会被新数据替换。已知原因是每次扫描都重新创建数据表，但不清楚如何持久存储每次扫描的数据，让新数据作为新增行显示在DataGridView中。

核心问题分析 #

当前代码每次调用GetResults()时，都会在函数内部新建dtScanned和dtResults，返回的始终是单条数据的新表，绑定到DataGridView自然会覆盖之前的内容。要实现累加行的效果，需要把存储扫描历史的DataTable提升为类级成员变量，而非每次在函数内重新创建。

具体实现步骤 #

1. 定义类级DataTable
   在窗体类的顶部（所有方法之外）声明用于存储所有扫描数据的DataTable，确保它在窗体生命周期内持续存在：

Private dtScanned As DataTable ' 类级变量，保存所有扫描到的数据

2. 初始化DataTable结构并绑定数据源
   在窗体的Load事件中初始化结构，并绑定到DataGridView：

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    dtScanned = New DataTable()
    ' 可以手动匹配SQL查询的列结构，也可以在第一次查询后自动复制结构
    DataGridView1.DataSource = dtScanned
End Sub

3. 修改GetResults()函数，仅返回单条查询结果
   调整函数逻辑，不再返回整个DataTable，而是返回查询到的单条DataRow：

Public Function GetResults() As DataRow
    Dim dtResults As New DataTable
    Dim connString As String = "Data Source=SQL03\SQL2019;Initial Catalog=aa"

    Using conn As New SqlConnection(connString)
        Using cmd As New SqlCommand("
            SELECT requests.request_id AS 'Request ID', patients.first_name AS GivenName, patients.surname AS Surname,
            concat(patients.day_birth, '/', patients.month_birth, '/', patients.year_birth) AS DOB, patients.sex AS 'Sex',
            services.service_abbrev AS 'Ref.Service', specimens.specimen AS 'Specimen', referral_details.referral_dt AS 'Referral Date'
            
            FROM
            { insert a bunch of joins here }

            WHERE
                referral_details.referral_dt >= @StartDate AND
                referral_details.referral_dt <= @EndDate AND
                labs.lab_id = '46' AND
                requests.request_id = @LabID
            
            ORDER BY
                labs.lab_name ASC,
                requests.request_id ASC,
                services.service_abbrev ASC

            ", conn)
            cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dateFrom.Value
            cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DateTo.Value
            cmd.Parameters.Add("@LabID", SqlDbType.Int).Value = txtBarcode.Text
            cmd.CommandTimeout = 0

            Try
                conn.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtResults.Load(reader)

                If dtResults.Rows.Count = 0 Then
                    MessageBox.Show("No referral found for this Lab ID.", "No Referral", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Return Nothing
                Else
                    Return dtResults.Rows(0) ' 返回单条查询结果
                End If

            Catch Ex As Exception
                MessageBox.Show("Unable to connect to SQL Server. Please check your network connection and if required, report the issue to IT." & vbCrLf & vbCrLf & Ex.Message, "No Connection!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return Nothing
            End Try
        End Using
    End Using
End Function

4. 处理扫描事件，添加行到类级DataTable
   监听文本框的Enter键（扫描器自动发送的CR/LF），调用查询并将结果添加到dtScanned：

Private Sub txtBarcode_KeyDown(sender As Object, e As KeyEventArgs) Handles txtBarcode.KeyDown
    If e.KeyCode = Keys.Enter Then
        Dim newRow As DataRow = GetResults()
        If newRow IsNot Nothing Then
            ' 首次扫描时自动复制查询结果的表结构
            If dtScanned.Columns.Count = 0 Then
                dtScanned = newRow.Table.Clone()
                DataGridView1.DataSource = dtScanned
            End If
            dtScanned.ImportRow(newRow) ' 将新行添加到历史表
            txtBarcode.Clear() ' 清空文本框准备下一次扫描
        End If
        e.Handled = True ' 阻止Enter键的默认行为
    End If
End Sub

更优实现建议 #

• 防重复扫描：添加行前检查dtScanned中是否已存在相同的Request ID，避免重复数据。
• 使用BindingSource：用BindingSource作为中间层绑定数据源，能更灵活地处理数据更新，无需手动刷新DataGridView：

Private bsScanned As New BindingSource()

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    bsScanned.DataSource = dtScanned
    DataGridView1.DataSource = bsScanned
End Sub

• 异步查询：使用ExecuteReaderAsync实现异步查询，避免UI卡顿，提升扫描体验。

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