如何将数据库查询结果添加到数组 [英] How to add database query results to an array

问题描述

我试图从Access数据库中选择字符串值，然后将它们放入字符串数组中，以便可以在该数组上执行循环语句。

I am trying to select string values from an Access 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.

我的select语句是从moto中选择motonum 。我想将 motonum 放入一个数组中。

My select statement is Select motonum from moto. I want to put motonum in an array.

读取数据的整个代码为：

The whole code to read the data is:

connect2()
If Not cnn2.State = ConnectionState.Open Then
    'open connection
    cnn2.Open()
    'MessageBox.Show("chk2")
End If
cmd5.Connection = cnn2
cmd5.CommandText = "Select motonum from moto"
myData5 = cmd5.ExecuteReader
While myData5.Read
    'code to return results here
End While`

推荐答案

有任何数字取决于项目的实际需求的不同方法。首先，我要问的是您是否真的需要一个字符串数组作为返回类型。在大多数情况下，数组不如List（Of String）或实现IEnumerable的其他类型有用。

There are any number of different ways to approach this, depending on the actual needs of your project. First and foremost, I would ask if you actually require a string array as the return type. For most cases, an array is less useful that a List(Of String) or other types which implement IEnumerable.

这里有两个选项，都涉及一个List（Of String）。但是，可以将List返回给调用方，然后调用方可以选择使用List类型的许多有用方法来处理数据：

Here are two options, both of which involve a List(Of String). However, one returns the List to the caller, which can then choose to employ the many useful methods of the List type in working with the data:

THIS是我的方式会建议：

THIS is the way I would recommend:

Public Function getListOfMotonum() As List(Of String)
    Dim SQL As String = "SELECT motonum FROM moto"

    Dim output As New List(Of String)()

    ' Set the connection string in the Solutions Explorer/Properties/Settings object (double-click)
    Using cn = New SqlConnection(Properties.Settings.[Default].MyConnectionString)
        Using cmd = New SqlCommand(SQL, cn)
            cn.Open()

            Try
                Dim dr = cmd.ExecuteReader()
                While dr.Read()
                    output.Add(dr("motonum").ToString())
                End While
            Catch e As SqlException
                ' Do some logging or something. 
                MessageBox.Show("There was an error accessing your data. DETAIL: " & e.ToString())
            End Try
        End Using
    End Using

    Return output

End Function

下面是一个简单的示例消耗此函数输出的代码：

Here is a trivial example of code which consumes the output of this function:

Private Sub PrintListToConsole()
    Dim MyMotonumList = Me.getListOfMotonum()

    For Each item As String In MyMotonumList
        Console.WriteLine(item)
    Next
End Sub

如果您的项目需要字符串数组，则方法可能会有所不同。您可以对同一函数进行一些小的修改即可返回一个字符串：

If your project REQUIRES a string array, the approach may vary. You can return a string from the same function with a couple minor modifications:

' Change the return type in the function signature:
Public Function getArrayOfMotonum() As String()
    Dim SQL As String = "SELECT motonum FROM moto"
    Dim output As New List(Of String)()

    '  . . . Same Data Access code as above:

    ' Just use the .ToArray method of the List class HERE:
    Return output.ToArray()

End Function

或者，您可以在客户端代码中使用相同的方法，使用返回列表的原始函数：

Or, you can use the same method in your client code, consuming the original function which returns a list:

Private Sub PrintArrayToConsole()
    Dim MyMotonumArray = Me.getArrayOfMotonum()

    For Each item As String In MyMotonumArray 
        Console.WriteLine(item)
    Next
End Sub

从函数中返回列表提供了更灵活的返回类型，其中包含许多有用的方法。

Returning the List from your function provides a more flexible return type, with many useful methods.

作为补充说明，在消耗数据访问资源时，请允许我推荐使用块。这样可以为您正确处理连接和命令对象。

As a side note, allow me to recommend the Using block when consuming data access resources. This handles the proper tear down and disposal of the Connection and Command objects for you.

这篇关于如何将数据库查询结果添加到数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！