使用VB.Net和SQL Server循环时无法获得所需的输出

debugcn 发表于 Dev

H4rdstyler

我有以下代码，在该代码中，我试图遍历数据库，提取用户输入的特定年份的公寓号和电表读数以供其输出热量。

输出必须像...

公寓号{apartmentNo}-{TotalReading}

公寓编号{apartmentNo}-{Totalreading}'第二公寓编号，依此类推

该查询似乎只读取最后一个数据，而不读取先前的数据。

这是我的VB.Net代码。

    Imports System.Data.Sql
    Imports System.Data.SqlClient

    Public Class AssociationHeating

        Private Sub GetAssociationButton_Click(sender As Object, e As EventArgs) Handles GetAssociationButton.Click
            Dim con As New SqlConnection
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader
            Dim totalHeating As Integer = 0
            Dim apt_no As Integer = 1

            con.ConnectionString = "Server=10.176.165.29,1433;Database=EnergyDB;User=Clerk;Pwd=12345"
            cmd.Connection = con
            con.Open()
            cmd.CommandText = "SELECT apartment_no,reading FROM HeatMeasurement WHERE association_no = '" & AssociationField.Text 

& "' and year(reading_date) = '" & YearField.Text & "'"
            reader = cmd.ExecuteReader
            If reader.HasRows Then
                Do While reader.Read()
                    If Convert.ToInt32(reader.GetString(0)) = apt_no Then
                        totalHeating += reader.GetInt32(1)
                        'Console.WriteLine("Read data from apartment: " & reader.GetString(0))
                    Else
                        Console.WriteLine("Apartment " & apt_no & " totals: " & totalHeating)
                        apt_no += 1
                        Console.WriteLine("Incremented apt_no by one")
                        totalHeating = 0
                    End If
                Loop
                Console.WriteLine("Apartment " & apt_no & " totals: " & totalHeating)
            Else
                Console.WriteLine("No data found for the year " & YearField.Text)
            End If
            con.Close()
        End Sub
    End Class

打印输出看起来像这样：公寓1合计：110 apt_no递增1公寓2合计：0

（有2间带米的公寓）

乔尔·科洪

这称为控制/中断逻辑。每当您有很多按某个字段排序且具有相同值的行（在本例中为apt_no），并且想为该字段中的每个值打印页眉或页脚条目时，就会看到此信息。

您可以通过嵌套两个while循环来正确处理此问题。两个循环的条件都会检查您是否位于数据末尾，但是内部循环还会检查组何时更改。然后，您仅在内部循环中增加阅读器。这种结构避免了重复复制除初始读取记录以外的任何代码。它还将代码按逻辑顺序放置，首先处理组的标题和初始化，然后处理各个记录，最后处理组的页脚。

该逻辑还可以轻松扩展到多个组级别...只需为每个其他组级别添加另一个内部循环。

最后，下面的代码还修复了原始代码中一个非常讨厌的sql注入漏洞。

Private Sub GetAssociationButton_Click(sender As Object, e As EventArgs) Handles GetAssociationButton.Click
    Dim Sql As String = "SELECT apartment_no,reading FROM HeatMeasurement WHERE association_no = @Association AND year(reading_date) = @Year ORDER BY apartment_no" 

    Using con As New SqlConnection("Server=10.176.165.29,1433;Database=EnergyDB;User=Clerk;Pwd=12345"), _
          cmd As New SqlCommand(Sql, con)

        'Had to guess at column types and lengths here
        cmd.Parameters.Add("@Association", SqlDbType.NVarChar, 10).Value = AssociationField.Text 
        cmd.Parameters.Add("@Year", SqlDbType.Int).Value = CInt( YearField.Text)

        con.Open()
        Using reader As SqlDataReader = cmd.ExecuteReader()
           Dim rows As Boolean = reader.Read()
           If Not rows Then Console.WriteLine("No data found for the year {0}", YearField.Text)

           '------------
           'Control/Break logic
           '------------
           'Outer loop to check for end of records
           While rows 
               'Group initialization and header
               Dim apt_no As Integer = CInt(reader("apartment_no"))
               Dim totalHeating As Integer = 0
               Console.WriteLine("Read data from apartment: {0}", apt_no)

               'Inner loop checks for end of records and change of group
               While rows AndAlso aptNo = reader("apartment_no")
                    'process individual records
                    totalHeating += reader("reading")

                    'Increment record pointer
                    rows = reader.Read()
               End While
               'Group trailer/footer
               Console.WriteLine("Apartment {0} totals: {1}",apt_no, totalHeating)
           End While

           reader.Close()
        End Using
    End Using
End Sub

通常嵌套两个循环是O（n ²）或至少是O（n log n），但这仍然是O（n），因为您只在内部循环中读取，就像只有一个循环一样。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。