多数据源读取至DataTable后GridView显示行合并问题求助

现有一段VB.NET代码，可从文件及SQL表读取数据至DataTable，并在GridView中展示。目前遇到数据显示问题：各数据源的数据会被写入DataTable的新行，希望将它们合并至同一行。请问是否是写入DataTable的逻辑导致该问题？该如何修复？

注：文件中的值位于不同行，且可能重复出现。当前输出为每行仅包含单个字段数据（如单独的Year行、单独的Cost行、单独的Name行），期望输出为同一行包含Year、Cost、Name三个字段的完整数据。

原代码 #

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Path As String = TextBox1.Text
    Dim fileReader As String = My.Computer.FileSystem.ReadAllText(Path)
    Dim lines As List(Of String) = IO.File.ReadLines(Path).ToList
    Dim idx As Integer = 0
    Dim dt As New DataTable()

    dt.Columns.Add("Year")
    dt.Columns.Add("Cost")

    Do While idx < lines.Count - 1

        Dim line As String = lines(idx)

        '--YEAR
        If line.Substring(11, 2) = "14" Then

            Dim Year = "20" & line.Substring(16, 6).ToString()
            Dim R As DataRow = dt.NewRow

            R("Year") = Year
            dt.Rows.Add(R)

        End If

        '--Cost
        If line.Substring(5, 2) = "50" Then


            Dim Cost As Decimal = (line.Substring(75, 4) & "." & line.Substring(80, 2))

            '-- ID FROM FILE
            Dim ID As String = (line.Substring(18, 3))

            Dim R As DataRow = dt.NewRow
            R("Cost") = Cost
            dt.Rows.Add(R)

            Dim conConn As New SqlConnection
            Dim comComm As SqlCommand = Nothing
            conConn = New SqlConnection(Con)

            '--GET NAME FROM DATABASE
            comComm = New SqlCommand
            With comComm
                .Connection = conConn
                .CommandType = CommandType.Text
                .CommandText = "SELECT NAME FROM [TEST].[dbo].[tblTest] WHERE ID = @ID"
                .Parameters.AddWithValue("@ID", ID)

                Using sda As New SqlDataAdapter(comComm)
                    sda.Fill(dt)
                End Using

            End With
        End If

        DataGridView1.Visible = True
        DataGridView1.DataSource = dt

        idx += 1

    Loop
End Sub

完全是写入DataTable的逻辑问题：

• 识别到Year行时，直接新建一行只写入Year，生成独立行
• 识别到Cost行时，又新建一行写入Cost，同时用SqlDataAdapter.Fill(dt)把查询到的Name插入新行，导致Cost和Name也各自成独立行
• 没有任何逻辑把同一条记录的Year、Cost、Name关联到同一行

核心思路：用字典跟踪每个ID对应的行，把同一条记录的所有字段都写入同一行；暂存年份，让后续Cost行能关联正确的年份。修复后的代码如下：

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Path As String = TextBox1.Text
    Dim lines As List(Of String) = IO.File.ReadLines(Path).ToList
    Dim dt As New DataTable()
    
    ' 补充Name列
    dt.Columns.Add("Year")
    dt.Columns.Add("Cost")
    dt.Columns.Add("Name")
    
    ' 用字典存储ID对应的行，处理重复ID情况
    Dim rowDict As New Dictionary(Of String, DataRow)
    Dim currentYear As String = "" ' 暂存当前读取到的年份

    For Each line As String In lines
        ' 读取年份：先存起来，后面Cost行要关联该年份
        If line.Length >= 22 AndAlso line.Substring(11, 2) = "14" Then
            currentYear = "20" & line.Substring(16, 6).ToString()
        End If

        ' 读取成本和ID，关联年份并查询Name
        If line.Length >= 82 AndAlso line.Substring(5, 2) = "50" Then
            Dim Cost As Decimal = Convert.ToDecimal(line.Substring(75, 4) & "." & line.Substring(80, 2))
            Dim ID As String = line.Substring(18, 3)
            
            ' 查找对应ID的行，不存在则新建
            Dim targetRow As DataRow
            If Not rowDict.ContainsKey(ID) Then
                targetRow = dt.NewRow()
                targetRow("Year") = currentYear
                rowDict.Add(ID, targetRow)
                dt.Rows.Add(targetRow)
            Else
                targetRow = rowDict(ID)
                ' 若已有行但未赋值年份，补上年份
                If String.IsNullOrEmpty(targetRow("Year").ToString()) Then
                    targetRow("Year") = currentYear
                End If
            End If
            
            ' 将Cost写入当前行
            targetRow("Cost") = Cost
            
            ' 查询Name并写入当前行，用ExecuteScalar避免新增行
            Using conConn As New SqlConnection(Con)
                Using comComm As New SqlCommand("SELECT NAME FROM [TEST].[dbo].[tblTest] WHERE ID = @ID", conConn)
                    comComm.Parameters.AddWithValue("@ID", ID)
                    conConn.Open()
                    Dim nameResult As Object = comComm.ExecuteScalar()
                    targetRow("Name") = If(nameResult Is DBNull.Value, "", nameResult.ToString())
                End Using
            End If
        End If
    Next

    ' 最后统一绑定数据源
    DataGridView1.Visible = True
    DataGridView1.DataSource = dt
End Sub

• 新增rowDict字典，通过ID跟踪已存在的行，避免重复创建，同时处理文件中重复出现的ID
• 用currentYear暂存年份，确保后续Cost行能关联到正确的年份
• 所有字段都写入同一行，不再单独创建新行
• 用ExecuteScalar直接获取Name值，替代SqlDataAdapter.Fill，避免插入新行
• 增加字符串长度判断，防止Substring时出现索引越界错误

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