im doing a project where i need to read data from a SQL Table (Called Table_IDs_Name). In that table i have to read the column (Variable_Name) and get every row in an array ( Called in the code as Names_Array). Im trying the following code and i get the values with the reader but how can i put them into an array? Its quite important so hope u can help me with this

        Public SQL_Connection As SqlConnection  
        Public SQL_Command As SqlCommand   
        Public SQL_Connection_String As String    
        Public Names_Array() As String

        SQL_Connection_String = "---------------------------"

        SQL_Connection = New SqlConnection(SQL_Connection_String)  
        SQL_Connection.Open()   

        Dim SQL_Statement_Array As String = "SELECT Variable_Names From Table_IDs_Names"
        SQL_Command = New SqlCommand(SQL_Statement_Array, SQL_Connection)

        Dim Reader As SqlDataReader

        Dim i As Integer

        Reader = SQL_Command.ExecuteReader()

        While Reader.Read()
            Console.WriteLine(Reader("Variable_Name").ToString().ToArray())
            Names_Array(i) = Reader("Variable_Name").ToString().ToArray()
            i = i   1
            Console.WriteLine("PROBANDO {0}", Names_Array(i))
        End While

            SQL_Command.Dispose()

CodePudding user response：

John suggested that you can use List of string which comes from System.Collection.Generic.

Import System.Collection.Generic

.... 'rest of the code

'declare list of string to store stuff
Dim lst as List(Of String) = new List(Of String)

'iterate query result
While Reader.Read()
    'add query result to the list
    lst.Add(Reader("Variable_Name").ToString())
End While

You can use the List of string named lst in a loop or convert it to array using lst.ToArray().

Note: I haven't write vb.net in a very long time

CodePudding user response：

Several things here that can trip up people who are new to this:

1. Do NOT try to re-use the same connection object. It interferes with a feature called connection pooling and will end up creating bottlenecks, making things slower, and causing you to use more memory, not less.
2. Do put your data access into its own class or module, separate from your UI and business logic, but only re-use the connection string within this module, not the full connection. This class/module will have a separate method for each query or operation you want to run.
3. Do put these short-lived connections in a Using block to make sure they are disposed correctly.
4. Arrays have a very specific meaning in formal computer science terms. However, many languages have arrays in the more colloquial sense. .Net is not one of those platforms. When you have an array in .Net, you have a real array in the full formal definition. These forms arrays are rarely appropriate for modern work. You almost always want a generic List instead, or even the raw Data Access Objects (DAOs) like IDataReader or DataTable provided by ADO.Net. Databinding is also an option. Arrays are just bad, m'kay? Don't conflate them with other collections.
5. Be sure to always use parameterized queries, and NEVER string concatenation to build your SQL statements. I don't see evidence you missed on this one, but it's important enough to make sure it's listed.
6. Similar to #5 (too important to overlook, even if it's not relevant to the question), NEVER store passwords in your database. To use your database to support authentication, instead salt new passwords with a unique nonce value, and then hash the result with a secure cryptographic hash a la BCrypt. You can save the resulting hash. When someone tries to login, you do the same operations to their attempted password, and then compare the hashes, NEVER the passwords directly.

All that out of the way, we can look at some code:

Public Module DB

    Private ConnectionString As String = "---------------------------"

    'I'm extending this to filter by TableID, just so I can demonstrate a parameterized query
    Public Shared Iterator Function GetIDVariables(TableID As Integer) As IEnumerable(Of String)
       
        Dim SQL As String = "SELECT Variable_Names From Table_IDs_Names WHERE TableID = @TableID"
        Using cn As New SqlConnection(ConnectionString), _
              cmd As New SqlCommand(SQL, cn)

            cmd.Parameters.Add("@TableID", SqlDbType.Int).Value = Table
            cn.Open()

            Using rdr As SqlDataReader = cmd.ExecuteReader()
                While rdr.Read()
                    Yield DirectCast(rdr("Variable_Names"), String)
                End While
                rdr.Close()
            End Using
        End Using
    End Function
End Module

And then in other code:

Dim data = DB.GetIDVariables(12345)
For Each variable As String in data
   Console.WriteLine($"PROBANDO {variable}")
Next