ASP.NET VB网站循环遍历数据库行

Question

ASP.NET VB网站循环遍历数据库行

3

我正在制作我的第一个网站，需要帮助使用循环。我有一个包含名为Menu的食品项目的数据库表，其中有8个类别（例如汉堡，开胃菜）。我还在网站上有一个菜单页面，其中有8个不同的图片来显示每个类别的项目。我需要循环遍历数据库的行。目前的情况是只循环遍历列，并一遍又一遍地重复第一行。我知道我需要一个循环，但出于某种原因无法正确实现。

这是后台代码：

Partial Class Burger
    Inherits System.Web.UI.Page

    'String Used to build the necessary markup and product information
    Dim str As String = ""
    'Var used to interact with SQL database
    Dim db As New Interaction

    'Adds the necessary markup for each menu item, using its productName
    Protected Sub printMenuBlock(ByVal productName As String)
        'Set up variable storing the product
        Dim product As Product
        'Pull the product in from our database using the productName
        product = db.ReadProduct(productName)
        'Add necessary markup to str variable, with products information within
        str += "<div class='storeItem'>"
        ' str += "    <img alt='Item Picture' class='itemPicture' src='" + product.ImagePath.Substring(3).Replace("\", "/") + "' />"
        ' str += "    <div class='itemInfo'>"
        str += "        <h1 class='itemName'>"
        str += "            " + product.Name + "</h1>"
        str += "        <h3 class='itemDescription'>"
        str += "            " + product.Description + "</h3>"
        str += "        <p class='itemPrice'>"
        str += "            " + product.Price.ToString("c") + "</p>"
        str += "        "
        str += "        </div>"
        str += "    </div>"
    End Sub

    'Uses
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim productNames As New List(Of String)
        'Pull the product names using the database
        productNames = db.getProductNames
        'Loop through all product names
        For Each name As String In productNames

            'Add necessary markup and product info to str variable
            printMenuBlock(name)
        Next
        'Print the str variable in our menuPlace div
        menuPlace.InnerHtml = str
    End Sub
End Class

这是交互类中的函数：

Private Sub GetProduct(ByVal CatIn As String)
    ' SQL String
    Dim strSelect As String
    strSelect = "SELECT * "
    strSelect &= " FROM Menu "
    ' strSelect &= " WHERE (ProductCat = 'Burgers')"
    ' Set up the connection to the datebase
    cmdSelect.Connection = conIn.Connect
    ' Add the SQL string to the connection
    cmdSelect.CommandText = strSelect
    ' Add the parameters to the connection
    cmdSelect.Parameters.Add("@CatIn", SqlDbType.NVarChar).Value = CatIn
End Sub

'Executes the SQL statement to find a Product by ProductId
Public Function ReadProduct(ByVal CatIn As String) As Product
    ' Product object initalized to nothing
    Dim prod As Product = Nothing
    Try
        Call GetProduct(CatIn)
        Dim dbr As SqlDataReader
        Dim strCat As String
        Dim strName As String
        Dim strDesc As String
        Dim decPrice As Decimal
        Dim strPath As String

        ' Execute the created SQL command from GetProduct and set to the SqlDataReader object
        dbr = cmdSelect.ExecuteReader
        dbr.Read()
        ' Check if there are any returned values
        If dbr.HasRows Then

            ' Assign the value in column two to strName
            strCat = dbr.GetString(1)

            ' Assign the value in column two to strName
            strName = dbr.GetString(2)
            ' Assign the value in column three to strDesc
            strDesc = dbr.GetString(3)
            ' Assing the value in column four to intPrice
            decPrice = ToDecimal(dbr.GetValue(4))
            'Assign the value in column five to strPath
            'strPath = dbr.GetString(3)
            ' Create the new Product object from the returned values
            prod = New Product(strName, strDesc, decPrice, strCat, strPath)
        End If
        ' Clear the SQL parameters and close the connection
        cmdSelect.Parameters.Clear()
        dbr.Close()
    Catch ex As SqlException
        Dim strOut As String
        strOut = ex.Message
        Console.WriteLine(strOut)
    End Try
    ' Return the Product object
    Return prod
End Function
'Returns a list of Product Names
Public Function getProductNames() As List(Of String)
    Dim list As New List(Of String)

    Dim sql As String = "SELECT  ProductName FROM Menu " +
   "WHERE (ProductCat) = 'Burgers'"
    '"DISTINCT 'ProductName'"
    cmdSelect.CommandText = sql
    cmdSelect.Connection = conIn.Connect
    Dim dbr As SqlDataReader

    dbr = cmdSelect.ExecuteReader




    If dbr.HasRows Then
        Do While dbr.Read()
            list.Add(dbr.GetString(0))
        Loop
    End If





    dbr.Close()
    Return list
End Function

显然有一个产品类，但不认为必须在此展示。同时，请忽略字符串路径，那将用于稍后的图像。感谢任何帮助。我很确定我需要一个for each而不是do while，但只是做不到。提前致谢。

产品类：

Public Class Product

    Private pName As String
    Private pDescription As String
    Private pPrice As Integer
    Private pPath As String
    Private pCat As String

    'Constructor, uses database to populate properties based on productName
    Public Sub New(ByVal productName As String)
        Dim data As New Interaction
        Dim work As Product
        work = data.ReadProduct(productName)
        pCat = work.Cat
        pName = work.Name
        pDescription = work.Description
        pPrice = work.Price

    End Sub

    'Constructor, populates properties from passed in values
    Public Sub New(ByVal NameIn As String,
                   ByVal DescriptionIn As String, ByVal PriceIn As Integer, ByVal CatIn As String, ByVal ImagePathIn As String)

        pName = NameIn
        pDescription = DescriptionIn
        pPrice = PriceIn
        pPath = ImagePathIn
        pCat = CatIn
    End Sub


    'Stores name of product
    Public ReadOnly Property Name() As String
        Get
            Return pName
        End Get
    End Property
    'Stores a description of the product
    Public ReadOnly Property Description() As String
        Get
            Return pDescription
        End Get
    End Property
    'Stores the price of the product
    Public ReadOnly Property Price() As Integer
        Get
            Return pPrice
        End Get
    End Property
    'Stores the path to the image associated with this product
    Public ReadOnly Property ImagePath() As String
        Get
            Return pPath
        End Get
    End Property

    'Stores name of product
    Public ReadOnly Property Cat() As String
        Get
            Return pCat
        End Get
    End Property

End Class

- Lolo

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user1382306 · Answer 1

请使用这个替代方案

Public Function ReadProduct(ByVal CatIn As String) As List(Of Dictionary(String, Of String))
    Dim ReturnProducts As New List(Of Dictionary(String, Of String))
    Try
        Call GetProduct(CatIn)
        Dim dbr As SqlDataReader
        ' Execute the created SQL command from GetProduct and set to the SqlDataReader object
        dbr = cmdSelect.ExecuteReader
        Dim FieldCount = dbr.FieldCount()
        Dim ColumnList as New List(Of String)
        For i as Integer = 0 to FieldCount - 1
            ColumnList.Add(dbr.GetName(i))
        Next
        While dbr.Read()
            Dim ReturnProduct As New Dictionary(String, Of String)
            For i as Integer = 0 to FieldCount - 1
                ReturnProduct.Add(ColumnList(i), dbr.GetValue(i).toString())
            Next
            ReturnProducts.Add(ReturnProduct)
        End While
        cmdSelect.Parameters.Clear()
        dbr.Close()
    Catch ex As SqlException
        Dim strOut As String
        strOut = ex.Message
        Console.WriteLine(strOut)
    End Try
    ' Return the Product object
    Return ReturnProducts
End Function

然后，在printMenuBlock内部，您使用以下代码声明了product

Dim product = db.ReadProduct(productName)

然后，您可以像这样访问它

For i as Integer = 0 to product.Count - 1
    'do everything normally for building str except, for example, if you want 
    'to acccess product.Name as before, access it with product(i).Item("Name"), 
    'assuming that your column name/alias for "Name" is in fact "Name"
    'i personally like to align column names to variable names for laziness's sake
    'bad obfuscation practice tho if you don't use aliases
Next