Function Return array from Database

Question

I have this function that return specific info of student from database. Here's the code.

Public Function arrInfo(Byval id as String)
    Dim name as String = ""
    DBCon_Open()
    Dim Cmd = New MySqlCommand("SELECT * FROM student WHERE student_id ='" & id & "'", con)
    Cmd.CommandTimeout = 0
    Dim rs = Cmd.ExecuteReader

    Do While rs.Read
        name = rs.Item("firstname")
    Loop
    rs.Close()
    Cmd.Dispose()
    DBCon_Close()

    Return name

End Function

MsgBox(arrInfo("STUD0027"))

Result: Ben

But, I want to return an array of info of a student from the query.

When I call the function it goes something like this:

MsgBox(arrInfo("STUD0027").("lastname"))

I tried this one but not working.

Public Function arrInfo(Byval id as String)

    DBCon_Open()
    Dim Cmd = New MySqlCommand("SELECT * FROM student WHERE student_id ='" & id & "'", con)
    Cmd.CommandTimeout = 0
    Dim rs = Cmd.ExecuteReader
    rs.Close()
    Cmd.Dispose()
    DBCon_Close()

    Return rs.Read

End Function

How can I achieve this array return?

Any help very much appreciated.

Thanks

You should look into parameterized queries.

Joel Coehoorn
– Joel Coehoorn

2017-04-05 04:26:09 +00:00
Commented Apr 5, 2017 at 4:26 — Joel Coehoorn
– Joel Coehoorn, Commented Apr 5, 2017 at 4:26

Joel Coehoorn · Accepted Answer · 2017-04-05 04:33:07Z

Try this:

Public Iterator Function arrInfo(Byval id as String) As IEnumerable(Of IDataRecord)
    'In most cases, the best practice in .Net is to use a new connection object each time, to take advantage of connection pooling
    Using cn  As New MySqlConnection(DBCon.ConnectionString), _
          cmd As New MySqlCommand("SELECT * FROM student WHERE student_id = @id", cn)

        cmd.Parameters.Add("@id", MySqlDbType.VarChar, 8).Value = id
        cn.Open()

        Using rdr = cmd.ExecuteReader()
           While rdr.Read()
              Yield Return rdr
           End While
           rdr.Close()
        End Using 
    End Using 'Using block will close and dispose your connection, **even if an exception is thrown**. The existing code would have left it hanging open. 
End Function

It's not exactly an array, but it has some similar properties. You can use it like this:

For Each record As IDataRecord In arrInfo("STUD0027")
    MsgBox(String.Format("Name: {0} {1}", record("firstname"), record("lastname")))
Next record

You should also look into the DataTable and DataSet objects.

Collectives™ on Stack Overflow

Function Return array from Database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related