How to store Database single column rows in to an array using Windows Forms(vb.net)

问题: I am trying to select string values from MySql database and then place them into an array of strings so that I can perform a loop statement on the array. However I don't...

问题:

I am trying to select string values from MySql database and then place them into an array of strings so that I can perform a loop statement on the array.

However I don't know how to place the result of the query into an array. I know how to query the database but all I need is how to put the result in an array.

My select statement is Select ItemName from menuitem. I want to put ItemName in an text box array.

The whole code to read the data is:

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Public Property TBMenuItemNameArray As TextBox
Private Sub Cashier_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    GetItemName()
End Sub
Public Function GetItemName() As String()
    TBMenuItemNameArray = New TextBox
    Dim output As New List(Of String)()
    con.Open("Dsn=Invintory;uid=root")
    rs.Open("Select ItemName From menuitem", con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
    output.Add(rs.GetString())
    rs.Close()
    con.Close()
    Dim ItemNameArray = Me.GetItemName()
    For Each item As String In ItemNameArray
        TBMenuItemNameArray.Text = item
        TableLayoutPanel1.Controls.Add(TBMenuItemNameArray)
    Next
    Return output.ToArray()
End Function

回答1:

Here is a quick example of one way to use ADO.net. Your will need to add the MySql nuget package to your project. Then add Imports MySql.Data.MySqlClient at the top of the code file.

The Using...End Using blocks ensure that your database objects are closed and disposed even if there is an error.

Your code creates a single TextBox with the New keyword and it overwrites the .Text property on each iteration; adding the same text box to the panel on each iteration.

For display, I simply added all the ItemName strings to a ListBox.

Since you don't use the return value of the function, I changed it to a Sub.

Private Sub Cashier_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    GetItemName()
End Sub

Public Sub GetItemName()
    Dim dt As New DataTable
    Using cn As New MySqlConnection("Your connection string")
        Using cmd As New MySqlCommand("Select ItemName From menuitem", cn)
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    Dim arr As String() = dt.AsEnumerable().Select(Function(x) x.Field(Of String)("ItemName")).ToArray()
    ListBox2.Items.AddRange(arr)
End Sub

回答2:

I am trying to select string values from MySql database and then place them into an array of strings

Okay

so that I can perform a loop statement on the array.

You don't need an array to write a loop. You could loop over the output list in that code just as easy, or you could do this:

Public Iterator Function GetItemName() As IEnumerable(Of String)
    Dim sql As String = "Select ItemName From menuitem"

    Using con As New MySqlConnection("Dsn=Invintory;uid=root"), _
          cmd As New MySqlCommand(sql, con)
        con.Open()
        Using rdr As MySqlDataReader = cmd.ExecuteReader()
             While rdr.Read()
                 Yield DirectCast(rdr("ItemName"), String)
             End While
        End Using
    End Using
End Function

And then loop over it like this:

Private Sub Cashier_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    For Each item As String In GetItemName()
        Dim itemBox As New TextBox()
        itemBox.Name = item
        itemBox.Text = item
        'You may also want to set a location here

        TableLayoutPanel1.Controls.Add(itemBox)
    Next
End Sub

Or like this:

Private Sub Cashier_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim boxes = GetItemName().Select(Function(n) New TextBox With {.Text = n})
    TableLayoutPanel1.Controls.AddRange(boxes.ToArray())
End Sub

Even better if you can use a control that supports data binding, like a DataGridView.

  • 发表于 2019-03-16 05:18
  • 阅读 ( 22 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除