VB.NET中DataGridView/DataTable替换内容并去重实现方案

我开发的VB.NET应用中，操作员扫描条码后从SQL数据库拉取对应记录并展示在DataGridView中。需求是：当多条记录除Ref. Service列外其余列均相同时，将这些行合并为一行，并将Ref. Service列统一设置为Organic Acids。当前填充DataTable/DataGridView的代码如下：

Public Function GetResults() As DataTable

        Dim dtResults As New DataTable
        Dim connString As String = "Data Source=NP-SQ03\SQL;Initial Catalog=asl;User Id=;Password="

        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
                { oj (((((((asl.dbo.requests requests INNER JOIN asl.dbo.requests_services requests_services ON
                requests.request_id = requests_services.request_id)
                INNER JOIN asl.dbo.referral_details referral_details ON
                    requests.request_id = referral_details.referring_request_id)
                INNER JOIN asl.dbo.patients patients ON
                    requests.patient_id = patients.patient_id)
                INNER JOIN asl.dbo.specimens specimens ON
                    requests_services.specimen_id = specimens.specimen_id)
                INNER JOIN asl.dbo.referral_details_services referral_details_services ON
                    requests_services.request_id = referral_details_services.request_id AND
                requests_services.service_id = referral_details_services.service_id)
                
                INNER JOIN asl.dbo.labs labs ON referral_details.to_lab_id = labs.lab_id)
                INNER JOIN asl.dbo.services services ON referral_details_services.service_id = services.service_id)
                LEFT OUTER JOIN asl.dbo.addresses addresses ON labs.lab_address_id = addresses.address_id}

                WHERE
                    referral_details.referral_dt >= @StartDate AND
                    referral_details.referral_dt <= @EndDate AND
                    labs.lab_id = '55' 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)

Process:
                    If dtResults.Rows.Count = 0 Then
                        MessageBox.Show("This Lab ID cannot be added to the manifest because it is not in the referrals table. Please resolve and try again.", "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
    End Function

Private Sub txtBarcode_KeyDown(sender As Object, e As KeyEventArgs) Handles txtBarcode.KeyDown
        If e.KeyCode = Keys.Enter Then
            If txtBarcode.Text.Length < 7 Or txtBarcode.Text.Length > 7 Then
                My.Computer.Audio.Play("specierror.wav")
                MessageBox.Show("Scanned Lab ID is invalid. Please try again.", "Invalid Lab ID", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                If GetResults() Is Nothing Then
                Else
                    For Each dr As DataRow In GetResults().Rows
                        dgScanned.Rows.Insert(0, dr.ItemArray)
                    Next dr
                    txtBarcode.Text = ""
                    txtBarcode.Focus()
                End If
            End If
        End If
    End Sub

提供两种可行方案，优先推荐SQL层面处理，效率更高且逻辑更简洁。

方案一：SQL查询层面直接合并（推荐） #

修改SQL语句，通过GROUP BY按除Ref.Service外的所有列分组，直接将该列设置为固定值Organic Acids，从数据库拿到的就是合并好的数据：

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',
    'Organic Acids' AS 'Ref.Service',
    specimens.specimen AS 'Specimen', 
    referral_details.referral_dt AS 'Referral Date'
FROM
    { oj (((((((asl.dbo.requests requests INNER JOIN asl.dbo.requests_services requests_services ON
    requests.request_id = requests_services.request_id)
    INNER JOIN asl.dbo.referral_details referral_details ON
        requests.request_id = referral_details.referring_request_id)
    INNER JOIN asl.dbo.patients patients ON
        requests.patient_id = patients.patient_id)
    INNER JOIN asl.dbo.specimens specimens ON
        requests_services.specimen_id = specimens.specimen_id)
    INNER JOIN asl.dbo.referral_details_services referral_details_services ON
        requests_services.request_id = referral_details_services.request_id AND
    requests_services.service_id = referral_details_services.service_id)
    
    INNER JOIN asl.dbo.labs labs ON referral_details.to_lab_id = labs.lab_id)
    INNER JOIN asl.dbo.services services ON referral_details_services.service_id = services.service_id)
    LEFT OUTER JOIN asl.dbo.addresses addresses ON labs.lab_address_id = addresses.address_id}
WHERE
    referral_details.referral_dt >= @StartDate AND
    referral_details.referral_dt <= @EndDate AND
    labs.lab_id = '55' AND
    requests.request_id = @LabID
GROUP BY
    requests.request_id,
    patients.first_name,
    patients.surname,
    concat(patients.day_birth, '/', patients.month_birth, '/', patients.year_birth),
    patients.sex,
    specimens.specimen,
    referral_details.referral_dt
ORDER BY
    labs.lab_name ASC,
    requests.request_id ASC

注意：GROUP BY必须包含SELECT中所有非固定值的列，确保分组逻辑准确。

方案二：VB.NET代码层面处理DataTable #

如果无法修改SQL语句，可在获取DataTable后手动合并行：

修改GetResults函数，在dtResults.Load(reader)后、Process标签前添加合并逻辑：

' 新增：合并符合条件的行
If dtResults.Rows.Count > 0 Then
    Dim mergedDt As DataTable = dtResults.Clone()
    Dim groupDict As New Dictionary(Of String, DataRow)
    
    For Each dr As DataRow In dtResults.Rows
        ' 生成分组键：拼接除Ref.Service外的所有列值
        Dim key As String = String.Join("|", 
            dr("Request ID").ToString(),
            dr("GivenName").ToString(),
            dr("Surname").ToString(),
            dr("DOB").ToString(),
            dr("Sex").ToString(),
            dr("Specimen").ToString(),
            dr("Referral Date").ToString())
        
        If Not groupDict.ContainsKey(key) Then
            Dim newRow As DataRow = mergedDt.NewRow()
            newRow.ItemArray = dr.ItemArray
            newRow("Ref.Service") = "Organic Acids"
            mergedDt.Rows.Add(newRow)
            groupDict.Add(key, newRow)
        End If
    Next
    
    dtResults = mergedDt
End If

同时优化txtBarcode_KeyDown事件，避免重复调用数据库查询：

Private Sub txtBarcode_KeyDown(sender As Object, e As KeyEventArgs) Handles txtBarcode.KeyDown
    If e.KeyCode = Keys.Enter Then
        If txtBarcode.Text.Length < 7 Or txtBarcode.Text.Length > 7 Then
            My.Computer.Audio.Play("specierror.wav")
            MessageBox.Show("Scanned Lab ID is invalid. Please try again.", "Invalid Lab ID", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Dim resultsDt As DataTable = GetResults()
            If resultsDt IsNot Nothing Then
                For Each dr As DataRow In resultsDt.Rows
                    dgScanned.Rows.Insert(0, dr.ItemArray)
                Next dr
                txtBarcode.Text = ""
                txtBarcode.Focus()
            End If
        End If
    End If
End Sub

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