Filling an Array with Items from a Table

Question

Dear All,

I have a small requirement in VB.NET and that is to fill an array with values retrieved from a table. That is i have a table called "user_roles" and that is having columns called "role_id" and "role_name". In the load event of the form, i want to execute a procedure and populate all the items (role_id) from the table "user_roles" into an array.

Can anyone please help me on this requirement.

Regards, George

Answer 1

I assume that you better use a generic list instead of an array. Correct me if i'm wrong.

If you have filled your table in your codebehind, you can add the rolw_id's by iterating through all rows.

Dim allRoleIDs As New List(Of Int32)
For Each row As DataRow In user_roles.Rows
    allRoleIDs.Add(CInt(row("role_id)")))
Next

Consider that its better to use a Datareader here because of performance reasons.

When you are using a strong typed dataset and want to avoid the extra roundtrip after filling the Datatable to add the ID's to the list, you have to extend the auto-generated Dataset DataAdapter Class(f.e. called user_rolesTableAdapter).

Don't use the Dataset's designer.vb class for that, because it will be overridden on every Dataset change. Use its codebehind class(without designer.vb) and add first the same namespace from your auto-generated TableAdapter(f.e. DatasetNameTableAdapter where DatasetName is the name of your Dataset). Then add following class(replace correct commands,column-index,class name of the partial class):

Namespace DatasetNameTableAdapters

    Partial Public Class user_rolesTableAdapter

        Public Function getListOfUserRolesID() As System.Collections.Generic.List(Of System.Int32)
            Dim list As New System.Collections.Generic.List(Of System.Int32)
            Dim command As System.Data.SqlClient.SqlCommand = Me.CommandCollection(0)

            Dim previousConnectionState As System.Data.ConnectionState = command.Connection.State
            If ((command.Connection.State And System.Data.ConnectionState.Open) _
                        <> System.Data.ConnectionState.Open) Then
                command.Connection.Open()
            End If
            Try
                Using reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader
                    While reader.Read
                        list.Add(reader.GetInt32(0))
                    End While
                End Using
            Finally
                If (previousConnectionState = System.Data.ConnectionState.Closed) Then
                    command.Connection.Close()
                End If
            End Try

            Return list
        End Function
    End Class

End NameSpace

Now you can get the user_roles ID's as generic List directly from the Dataadapter without iterating twice(first on filling the datatable and second on addind the ID's to an List).

Of course you can also use this Datareader approach in Page.Load without using a Dataset.