从MS Access迁移至ASP.NET：VB代码如何获取全部带照片重复行

我正在从MS Access迁移至ASP.NET，现有代码用于查找关联有照片的记录：在MS Access中能返回多条记录（这些记录除关联照片不同外，其余数据完全重复），但ASP.NET版本仅返回一行记录，像是自动对重复数据做了分组，只保留一条记录和对应的一张照片。请问如何修改ASP.NET VB代码，以获取所有关联有照片的记录？

Protected Sub btnGetPhoto_Click(sender As Object, e As EventArgs) Handles btnGetPhoto.Click
    Dim connectionString As String = "myConnectionString"
    Dim sql As String = String.Empty
    Dim myRepDesList As List(Of String) = New List(Of String)()
    Dim myCategoryList As List(Of String) = New List(Of String)()

    ' Handle input fields txtRepDescription and txtCategory
    If Not String.IsNullOrEmpty(txtRepDescription.Text) Then
        If txtRepDescription.Text.Contains(",") Then
            txtRepDescription.Text = txtRepDescription.Text.Replace(", ", ",")
        End If
        myRepDesList = txtRepDescription.Text.Split(","c).ToList()
    End If

    If Not String.IsNullOrEmpty(txtCategory.Text) Then
        If txtCategory.Text.Contains(",") Then
            txtCategory.Text = txtCategory.Text.Replace(", ", ",")
        End If
        myCategoryList = txtCategory.Text.Split(","c).ToList()
    End If

    ' Validate and parse DateFrom and DateTo
    Dim dateFrom As DateTime
    Dim dateTo As DateTime

    If Not DateTime.TryParse(txtMyDateFrom.Text.Trim(), dateFrom) OrElse Not DateTime.TryParse(txtMyDateTo.Text.Trim(), dateTo) OrElse dateFrom > dateTo Then
        lblMessage.Visible = True
        lblMessage.Text = "Invalid date range. Please correct it."
        lblMessage.ForeColor = System.Drawing.Color.Red
        Return
    End If

    ' Construct the base SQL query
    sql = "SELECT RMANo, DateTested, RepairDescription, Category, Model, Rev, SN, DefectivePartNumber, DefectivePartLocation, Location, Customer, RequestCustomer, HasInvParentSN, ParentPartNumber, ParentSerialNumber, SYSModel, SYSSN, AppliedECO, TestResult, ReportID FROM vw_RMAItemView WHERE DateTested BETWEEN @DateFrom AND @DateTo AND ReportID <> -1"

    ' Add conditions dynamically
    If myRepDesList.Any() Then
        sql &= " AND RepairDescription IN (" & String.Join(", ", myRepDesList.Select(Function(x, i) $"@RepDes{i}")) & ")"
    End If

    If myCategoryList.Any() Then
        sql &= " AND Category IN (" & String.Join(", ", myCategoryList.Select(Function(x, i) $"@Category{i}")) & ")"
    End If

    ' Fetch data from the database
    Dim photoTable As New DataTable()

    Using conn As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.AddWithValue("@DateFrom", txtMyDateFrom.Text)
        cmd.Parameters.AddWithValue("@DateTo", txtMyDateTo.Text)

        ' Add parameters for RepairDescription
        For i As Integer = 0 To myRepDesList.Count - 1
            cmd.Parameters.AddWithValue($"@RepDes{i}", myRepDesList(i))
        Next

        ' Add parameters for Category
        For i As Integer = 0 To myCategoryList.Count - 1
            cmd.Parameters.AddWithValue($"@Category{i}", myCategoryList(i))
        Next

        conn.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        photoTable.Load(reader)
        reader.Close()

        ' Add the myPhoto column to the DataTable
        If Not photoTable.Columns.Contains("myPhoto") Then
            photoTable.Columns.Add("myPhoto", GetType(String))
        End If

        ' Add photo links to the DataTable
        Dim rowsToRemove As New List(Of DataRow)()

        For Each row As DataRow In photoTable.Rows
            Dim location As String = row("Location").ToString()
            Dim reportID As Integer = Convert.ToInt32(row("ReportID"))
            Dim repairPhotoURL As String = ""

            ' Retrieve photo URL using stored procedure
            Using photoCmd As New SqlCommand("GetRepairPhoto", conn)
                photoCmd.CommandType = CommandType.StoredProcedure
                photoCmd.Parameters.AddWithValue("@Location", location)
                photoCmd.Parameters.AddWithValue("@ReportID", reportID)

                Dim photoReader As SqlDataReader = photoCmd.ExecuteReader()
                If photoReader.HasRows Then
                    photoReader.Read()
                    repairPhotoURL = photoReader("RepairPhoto").ToString()
                End If
                photoReader.Close()
            End Using

            ' Assign the photo URL to the myPhoto column
            If Not String.IsNullOrEmpty(repairPhotoURL) Then
                row("myPhoto") = repairPhotoURL
            Else
                rowsToRemove.Add(row) ' Mark the row for removal if no photo is available
            End If
        Next

        ' Remove rows with no photos
        For Each row As DataRow In rowsToRemove
            photoTable.Rows.Remove(row)
        Next
    End Using

    ' Bind the filtered DataTable to the GridView
    GridView1.DataSource = photoTable
    GridView1.DataBind()
End Sub

问题核心是当前逻辑先从视图vw_RMAItemView获取主记录，再逐个查询照片，但视图可能默认去重重复数据，且存储过程GetRepairPhoto仅返回单张照片。要获取所有带照片的记录，需调整查询逻辑，直接关联照片数据源：

1. 修改SQL查询，直接关联所有照片数据 #

放弃逐行查询照片的方式，改用CROSS APPLY关联存储过程（或直接关联照片表），确保每条照片对应一条主记录：

SELECT 
    R.RMANo, R.DateTested, R.RepairDescription, R.Category, 
    R.Model, R.Rev, R.SN, R.DefectivePartNumber, R.DefectivePartLocation, 
    R.Location, R.Customer, R.RequestCustomer, R.HasInvParentSN, 
    R.ParentPartNumber, R.ParentSerialNumber, R.SYSModel, R.SYSSN, 
    R.AppliedECO, R.TestResult, R.ReportID, P.RepairPhoto AS myPhoto
FROM vw_RMAItemView R
CROSS APPLY GetRepairPhoto(R.Location, R.ReportID) P
WHERE R.DateTested BETWEEN @DateFrom AND @DateTo AND R.ReportID <> -1

注：若存储过程GetRepairPhoto不支持表值返回，可改为直接关联照片表，例如INNER JOIN RepairPhotos P ON R.Location = P.Location AND R.ReportID = P.ReportID

2. 调整代码逻辑，移除冗余的逐行查询 #

修改后的代码直接通过SQL获取完整的主记录+照片数据集，无需再循环处理：

Protected Sub btnGetPhoto_Click(sender As Object, e As EventArgs) Handles btnGetPhoto.Click
    Dim connectionString As String = "myConnectionString"
    Dim sql As String = String.Empty
    Dim myRepDesList As List(Of String) = New List(Of String)()
    Dim myCategoryList As List(Of String) = New List(Of String)()

    ' 处理输入字段 txtRepDescription 和 txtCategory
    If Not String.IsNullOrEmpty(txtRepDescription.Text) Then
        If txtRepDescription.Text.Contains(",") Then
            txtRepDescription.Text = txtRepDescription.Text.Replace(", ", ",")
        End If
        myRepDesList = txtRepDescription.Text.Split(","c).ToList()
    End If

    If Not String.IsNullOrEmpty(txtCategory.Text) Then
        If txtCategory.Text.Contains(",") Then
            txtCategory.Text = txtCategory.Text.Replace(", ", ",")
        End If
        myCategoryList = txtCategory.Text.Split(","c).ToList()
    End If

    ' 验证并解析日期范围
    Dim dateFrom As DateTime
    Dim dateTo As DateTime

    If Not DateTime.TryParse(txtMyDateFrom.Text.Trim(), dateFrom) OrElse Not DateTime.TryParse(txtMyDateTo.Text.Trim(), dateTo) OrElse dateFrom > dateTo Then
        lblMessage.Visible = True
        lblMessage.Text = "日期范围无效，请修正。"
        lblMessage.ForeColor = System.Drawing.Color.Red
        Return
    End If

    ' 构造关联照片的SQL查询
    sql = "SELECT " &
          "R.RMANo, R.DateTested, R.RepairDescription, R.Category, " &
          "R.Model, R.Rev, R.SN, R.DefectivePartNumber, R.DefectivePartLocation, " &
          "R.Location, R.Customer, R.RequestCustomer, R.HasInvParentSN, " &
          "R.ParentPartNumber, R.ParentSerialNumber, R.SYSModel, R.SYSSN, " &
          "R.AppliedECO, R.TestResult, R.ReportID, P.RepairPhoto AS myPhoto " &
          "FROM vw_RMAItemView R " &
          "CROSS APPLY GetRepairPhoto(R.Location, R.ReportID) P " &
          "WHERE R.DateTested BETWEEN @DateFrom AND @DateTo AND R.ReportID <> -1"

    ' 动态添加条件
    If myRepDesList.Any() Then
        sql &= " AND R.RepairDescription IN (" & String.Join(", ", myRepDesList.Select(Function(x, i) $"@RepDes{i}")) & ")"
    End If

    If myCategoryList.Any() Then
        sql &= " AND R.Category IN (" & String.Join(", ", myCategoryList.Select(Function(x, i) $"@Category{i}")) & ")"
    End If

    ' 获取数据
    Dim photoTable As New DataTable()

    Using conn As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.AddWithValue("@DateFrom", dateFrom) ' 使用解析后的DateTime对象，避免格式问题
        cmd.Parameters.AddWithValue("@DateTo", dateTo)

        ' 添加RepairDescription参数
        For i As Integer = 0 To myRepDesList.Count - 1
            cmd.Parameters.AddWithValue($"@RepDes{i}", myRepDesList(i))
        Next

        ' 添加Category参数
        For i As Integer = 0 To myCategoryList.Count - 1
            cmd.Parameters.AddWithValue($"@Category{i}", myCategoryList(i))
        Next

        conn.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        photoTable.Load(reader)
        reader.Close()
    End Using

    ' 绑定到GridView
    GridView1.DataSource = photoTable
    GridView1.DataBind()
End Sub

3. 关键优化说明 #

• 关联查询替代逐行查询：通过CROSS APPLY直接关联所有照片，确保每条照片对应一条主记录，不会丢失重复主记录+不同照片的组合。
• 日期参数优化：改用解析后的DateTime对象传递日期，避免文本格式导致的潜在问题。
• 移除冗余逻辑：无需手动添加myPhoto列和逐行处理照片查询，SQL直接返回完整数据集。

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