VB.NET Mimic GetRows()

I am trying to mimic the old ADO .GetRows method in a VB.NET class, can someone help me figure out the right way to fill the 2d array tmpArr?

I would like the column names to be the first dimension, and the values to be the second dimension

Here is my current code:


objConn.Open()
Execute DataReader and make ExecuteQuery an Array of returned values
objDR = objCmd.ExecuteReader
If (objDR.Read()) Then
  objDS = New DataSet
  Dim objDT As DataTable = objDS.Tables(0)
  TotalRecords = objDS.Tables(0).Rows.Count
  For Me.a = 0 To objDT.Columns.Count - 1
    tmpArr(a, ) = objDT.Columns(a).ColumnName
    MsgBox(objDT.Rows(a)(a).ToString)
  Next
End If

Open in new window

LVL 25
kevp75Asked:
Who is Participating?
 
kevp75Connect With a Mentor Author Commented:
I was able to accomplish what I need for myself.  code attached if anyone would like it:

Public Sub ExecuteDataReader()
            Dim objConn As New SqlConnection(ConnString)
            Dim objCmd As New SqlCommand(Query, objConn)
            Dim objDA As New SqlDataAdapter()
            Dim objDS As New DataSet()
            If IsArray(Values) And IsArray(ParamNames) Then
                If UBound(Values) = UBound(ParamNames) Then
                    'Prepare our parameters
                    For Me.i = 0 To UBound(Values)
                        objCmd.Parameters.AddWithValue(ParamNames(i), Values(i))
                    Next
                    objCmd.CommandType = CmdType
                    objCmd.Connection.Open()
                    objDA.SelectCommand = objCmd
                    objDA.Fill(objDS, "Tbl")
                    objCmd = Nothing
                    objConn.Close() : objConn = Nothing
                    Dim DT As DataTable = objDS.Tables("Tbl")
                    FieldCount = DT.Columns.Count
                    TotalRecords = DT.Rows.Count
                    Dim c As Integer
                    Dim r As Integer
                    Dim tmpRetVals(FieldCount - 1, TotalRecords - 1) As Array
                    For c = 0 To FieldCount - 1
                        For r = 0 To TotalRecords - 1
                            tmpRetVals(c, r) = New String() {DT.Columns.Item(c).ColumnName, DT.Rows(r)(c).ToString()}
                        Next
                    Next
                    ReturnValues = tmpRetVals
                    CleanUp()
                Else
                    'ErrorHandler(ErrNumber, ErrDescription, ErrLine, Which, ExtraNotes)
                    Call ErrorHandler("", "Upper Boundaries Do Not Match", "", "ExecuteDataReader()", "")
                End If
            Else
                Call ErrorHandler("", "Values And ParamNames are not arrays.", "", "ExecuteDataReader()", "")
            End If
        End Sub

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Why do you need that on the array? What db are you using?
0
 
kevp75Author Commented:
would it be easier to do if I used an ArrayList instead?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kevp75Author Commented:
i need it for any db  (or at least most db's, access, sql, mysql, etc...  depedant on the connection string)
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
But you have already the information on the Datatable? That should be ok for you.
Where are you going to use it?
0
 
kevp75Author Commented:
multiple places, multiple queires.

I will never know what fields, how many rows, etc?  Hence why i am doing this.  SO I don't need to code a DataTable for each and every query that could possibly be run.
0
 
kevp75Author Commented:
I guess basically what I am trying to do is this, but obviously it is not correct:

For Me.x = 0 To TotalRecords
 For Me.a = 0 To objDT.Columns.Count - 1
  tmpArr(a, ) = objDT.Columns(a).ColumnName
  tmpArr(, x) = objDT.Rows(x)(a).ToString
 Next
Next

Open in new window

0
 
kevp75Author Commented:
OR....

Should I make a structure, and then make an array out of the structure?
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Check this example for SQL Server ... you can do the same for other db's , and you can use that for all you need (fill a combobox, a datagridview, show results in a form, etc, etc)
                Dim ds As New DataSet
                Using adapter As New SqlDataAdapter("SELECT * FROM utilizadores", connString)
                    adapter.Fill(ds)
                End Using

                ' Show the columns
                For Each col As DataColumn In ds.Tables(0).Columns
                    Debug.WriteLine(col.ColumnName)
                Next

                ' Show the rows/coluns
                For Each row As DataRow In ds.Tables(0).Rows
                    For Each col As DataColumn In ds.Tables(0).Columns
                        Debug.Write(row.Item(col).ToString & " - ")
                    Next
                    Debug.WriteLine("------")
                Next

                ' Filter data
                Dim dv As DataView = ds.Tables(0).DefaultView
                dv.RowFilter = "age > 20"

Open in new window

0
 
kevp75Author Commented:
That is not answering the question I have.

This may be exactly what I am looking for...    I have to test it still

Public Structure DataStructure
   Private _field As String
   Private _value As String
   Public Property Field() As String
     Get
      Return _field
     End Get
     Set(ByVal value As String)
       _field = value
     End Set
   End Property
   Public Property Value() As String
     Get
      Return _value
     End Get
     Set(ByVal value As String)
       _value = value
     End Set
   End Property
   Public Sub New(ByRef field As String, ByRef value As String)
     _field = field
     _value = value
   End Sub
End Structure

'Change the loop to:
Dim tmpArr As New ArrayList
For Me.x = 0 To TotalRecords
   For Me.a = 0 To objDT.Columns.Count - 1
     tmpArr.Add(New DataStructure(objDT.Columns(a).ColumnName, objDT.Rows(x)(a).ToString))
   Next
Next

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
But if you want to make a structure, I can also show an example, but it's difficult to filter data (for example)
0
 
Jorge PaulinoIT Pro/DeveloperCommented:

If you want to hold the field names and the records, you can do two things:
1 - Create a new array just for the column names/field names

or

2 - Store the column name in the first postion of the array
0
 
kevp75Author Commented:
I want to do #2
0
 
kevp75Author Commented:
well...no

What it is that I am trying to accomplish is

Array(ColumnName, ColumnValue)
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
But that will store only one field of the table! Is that it?
0
 
kevp75Author Commented:
no.

all fields all columns...

Array(ColumnName, ColumnValue)
0
 
CodeCruiserCommented:
You will be best using the DataTable which is meant for exactly this purpose and is db independent. Array would be the worst choice in my opinion. What .NET version are you using?
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Imagine a table like the picture attached ... how do you store that in the array ?
New-Picture.bmp
0
 
kevp75Author Commented:
Array(ColumnName, ColumnValue) as I have said multiple times already

this is to have a common place to do all my database work (a DAL...  Data Access Layer), however, I want it to be common enough so I can re-use it in other applications
0
 
CodeCruiserCommented:
>so I can re-use it in other applications

If other applications would be .NET then DataTable is a common class. If other apps would be non .NET then its not guaranteed that .NET arrays would work with non .NET languages.
0
 
kevp75Author Commented:
I agree DataTable is common, however everytime I would need to use it I would have to code out the DataTable, the Connection, The Command, the Resultset every single time I need to do it...

Hence the Common DAL!!!
0
 
CodeCruiserCommented:
A DataTable does not require all those objects to be populated. It can be created and populated manually without involving any db. For example

Dim dTable as new DataTable
dTable.Columns.Add("FieldName", GetType(String))
dTable.Columns.Add("FieldValue", GetType(String))

Dim dRow as DataRow = dTable.NewRow()
dRow(0)="Name"
dRow(1)="CodeCruiser"
dTable.Rows.Add(dRow)
...
0
 
kevp75Author Commented:
we are off the topic at hand.  The question was how to do a certain thing, and we have now gone off the beaten path.

Please refer to the question at the top of this page.  It is clearly stated what I am trying to accomplish.
0
All Courses

From novice to tech pro — start learning today.