VB.Net Function Return MySql Results Two Dimensional Array

Question

The following code pulls data from a MySql connection and returns the array (output). This two dimensional array only works when the number of rows and columns are specified before running the query.

Is there a way to get around this? A non-fixed array size? Please help!

    Public Function GetUsers() As String(,)
        Dim GetCommand As MySqlCommand
        Dim SQL As String = "Select * From users"
        Dim output(,) As String
        Dim intRowCount As Integer = 2
        Dim intColumnCount As Integer = 3 ' Users Count
        ReDim output(intColumnCount - 1, intRowCount - 1)
        Dim GetMyConn As New MySqlConnection
        GetMyConn.ConnectionString = "server = mysql.com;user id=rtest;password=test1234;database=rtestdb;"

        GetMyConn.Open()
        GetCommand = New MySqlCommand(SQL, GetMyConn)
        Dim counter As Integer = 0
        Try
            Dim getResult As Object = GetCommand.ExecuteReader()
            While getResult.Read()
                output(counter, 0) = getResult("username").ToString()
                output(counter, 1) = getResult("game_ip").ToString()
                counter = counter + 1
            End While
        Catch e As MySqlException
            MessageBox.Show("There was an error accessing your data. DETAIL: " & e.ToString())
        End Try

        GetMyConn.Close()
        Return output

    End Function
'''

Answer 1

The easiest way is:

 Friend Function getDataAsArray() As Array
        Try

            Dim SQLConnection = New SqlConnection("server = mysql.com;user id=rtest;password=test1234;database=rtestdb;")

            Dim DtTable As New DataTable

            Dim mSqlAdapter As New SqlClient.SqlDataAdapter With {
                    .SelectCommand = New SqlCommand("Select * From yourtable", SQLConnection)
                }

            mSqlAdapter.Fill(DtTable)

            Dim output As Array = (From twoColumns As DataRow In DtTable
                                   Select col1 = twoColumns.Item("username"),
                             col2 = twoColumns.Item("game_ip")).ToList.ToArray

            Return output

        Catch ex As Exception
            Console.WriteLine("Ops ops ops something wrong: " + ex.ToString)
        End Try

        Return Nothing

    End Function

Answer 2

I have given three different options using different data structures. Most databse objects need to be closed and disposed. Using...End Using blocks take care of this. You want to do as little as possible while the connection is open.

'Option 1 Using DataTable
Public Function GetUsers() As DataTable
    Dim dt As New DataTable
    Using GetMyConn As New MySqlConnection("server = mysql.com;user id=rtest;password=test1234;database=rtestdb;"),
        GetCommand As New MySqlCommand("Select * From users", GetMyConn)
        GetMyConn.Open()
        dt.Load(GetCommand.ExecuteReader)
    End Using
    Return dt
End Function

'Option 2 Using List(Of User)
Public Class User
    Public Property ID As Integer
    Public Property Name As String
    Public Property GameIP As String

    Public Sub New(UserID As Integer, UName As String, UGame As String)
        ID = UserID 'Assumed there was a primary key ID of some sort
        Name = UName
        GameIP = UGame
    End Sub
End Class

Public Function GetUsers() As List(Of User)
    Dim lst As New List(Of User)
    Using GetMyConn As New MySqlConnection("server = mysql.com;user id=rtest;password=test1234;database=rtestdb;"),
            GetCommand As New MySqlCommand("Select * From users", GetMyConn)
        GetMyConn.Open()
        Using r = GetCommand.ExecuteReader
            While r.Read
                Dim u = New User(r.GetInt32(0), r.GetString(1), r.GetString(2))
                lst.Add(u)
            End While
        End Using
    End Using
    Return lst
End Function

'Option 3 Using 2D array
Public Function GetUsers() As String(,)
    Dim Users(,) As String = Nothing
    Dim dt As New DataTable
    Using GetMyConn As New MySqlConnection("server = mysql.com;user id=rtest;password=test1234;database=rtestdb;"),
                GetCommand As New MySqlCommand("Select username, game_ip From users", GetMyConn)
        GetMyConn.Open()
        dt.Load(GetCommand.ExecuteReader)
    End Using
    ReDim Users(dt.Rows.Count - 1, 1)
    For r = 0 To dt.Rows.Count - 1
        Users(r, 0) = dt(r)(0).ToString
        Users(r, 1) = dt(r)(1).ToString
    Next
    Return Users
End Function

Answer 3

Use this instead

            output(counter, 0) = getResult.GetString(0)
            output(counter, 1) = getResult.GetString(1)

When you have integer use

reader.GetInt32(0)

and so on

To get the correct dimensions for your array

Change your select statement like below

 Dim SQL As String = "Select (SElECT Count(*) rowcount FROM users),* From users"

So you have in your result table a column more with the row count in every row.

Now to redim your array. the Columncount you should know. Else you have to add also

,(SELECT count(*) FROM information_schema.columns
WHERE table_name ='users') columncount

So and then you have to adept your datareader

    Try
        Dim getResult As Object = GetCommand.ExecuteReader()
        If getResult.Read() Then            
          ReDim output(intColumnCount - 1, Integer.Parse(getResult("rowcount ")) - 1)

          output(0, 0) = getResult("username").ToString()
          output(0, 1) = getResult("game_ip").ToString(
          counter += 1
          While getResult.Read()
            output(counter, 0) = getResult("username").ToString()
            output(counter, 1) = getResult("game_ip").ToString()
            counter = counter + 1
          End While
        END IF

    Catch e As MySqlException
        MessageBox.Show("There was an error accessing your data. DETAIL: " & e.ToString())
    End Try

The idea is to get the first row, catch rowcount and redimension the array properly with the right diemnsions. As described you can if you want do the same with with thecoumnsnumber, if you want to be even more flexible.