How to use dataset -- vb.net data

vb.net data : How to use dataset

Robin
7/23/2004 9:20:37 PM

In VB.Net I have returned a dataset. How do I loop through a named table for
all fields for each row returned?

Scott M.
7/23/2004 10:53:27 PM

Each table has a Rows collection and each collection has a count property
(starting from 1). Each Row object in the Rows collection has an Items
collection, which can be iterated by the index (starting from zero) or the
column name.

[quoted text, click to view]

Samuel L Matzen
7/24/2004 4:49:17 PM

Robin,

Here is a spot of code that creates a datatable, puts it into a dataset and
shows how to iterate the rows and columns:

The real heart of the iteration is:

Dim dr As DataRow
Dim dc As DataColumn
For Each dr In ds.Tables("Root").Rows ' or ds.tables(0) if you don't know
the name

''' iterate columns with column names
For Each dc In ds.Tables("Root").Columns
Console.WriteLine(dr(dc.ColumnName).ToString)
Next

''' iterate columns with objects
Dim o As Object
For Each o In dr.ItemArray
Console.WriteLine(o.ToString)
Next

Next

---------- full source text -----------

<code (VB.NET) >
''' create table
Dim rootTable As New DataTable("Root")
With rootTable
.Columns.Add(New DataColumn("rootGUID", GetType(System.String)))
.Columns.Add(New DataColumn("string", GetType(System.String)))
.Columns.Add(New DataColumn("int32", GetType(System.Int32)))
.Columns.Add(New DataColumn("date", GetType(System.DateTime)))
.Columns.Add(New DataColumn("boolean", GetType(System.Boolean)))
End With

''' add rows
Dim intPtr As Integer
For intPtr = 1 To 10
Dim dra As DataRow = rootTable.NewRow
dra("rootGUID") = System.Convert.ToBase64String(Guid.NewGuid.ToByteArray)
dra("string") = "String Value for " + intPtr.ToString
dra("int32") = intPtr * 10
dra("date") = Now.AddDays(intPtr)
dra("boolean") = IIf(intPtr Mod 2 = 0, True, False)
rootTable.Rows.Add(dra)
Next

Dim ds As New DataSet
ds.Tables.Add(rootTable)
Dim dr As DataRow
Dim dc As DataColumn
For Each dr In ds.Tables("Root").Rows ' or ds.tables(0) if you don't know
the name

''' iterate columns with column names
For Each dc In ds.Tables("Root").Columns
Console.WriteLine(dr(dc.ColumnName).ToString)
Next

''' iterate columns with objects
Dim o As Object
For Each o In dr.ItemArray
Console.WriteLine(o.ToString)
Next

Next
</code>

-Sam Matzen

[quoted text, click to view]

Samuel L Matzen
7/24/2004 7:46:40 PM

Scott,

I must appologize in advance if I am missing something here. I don't
usually care much about how fast something runs, but something didn't sound
right about your last post. I may not be running these loops the way you
would, but I found the For Each loop to be consistently 5 times faster than
the For Next loop when passing 500,000 records in a datatable with 5
columns.

Here is my test:

1. Create a new project and add the following declaration for a datatable:

<code (VB.NET) >
Dim rootTable As New DataTable("Root"
</code>

2. Add the following code to Page Load to create 500000 records with 5
columns:

<code (VB.NET) >
''' create table
With rootTable
.Columns.Add(New DataColumn("rootGUID", GetType(System.String)))
.Columns.Add(New DataColumn("string", GetType(System.String)))
.Columns.Add(New DataColumn("int32", GetType(System.Int32)))
.Columns.Add(New DataColumn("date", GetType(System.DateTime)))
.Columns.Add(New DataColumn("boolean", GetType(System.Boolean)))
End With

''' add rows
Dim intPtr As Integer
For intPtr = 1 To 500000
Dim dr As DataRow = rootTable.NewRow
dr("rootGUID") = System.Convert.ToBase64String(Guid.NewGuid.ToByteArray)
dr("string") = "String Value for " + intPtr.ToString
dr("int32") = intPtr * 10
dr("date") = Now.AddDays(intPtr)
dr("boolean") = IIf(intPtr Mod 2 = 0, True, False)
rootTable.Rows.Add(dr)
Next
</code>

3. Add a button and a textbox to display the elapsed time for the For Each
test and put the following code in the Button Click event:

<code (VB.NET) >
Dim aStartTime As DateTime = Now
Dim dr As DataRow
Dim o As Object
For Each dr In rootTable.Rows
For Each o In dr.ItemArray

Next
Next
Dim aEndTime As DateTime = Now
Me.TextBox1.Text = aEndTime.Subtract(aStartTime).ToString
</code>

4. Add another button and textbox to display the elapsed time for the For
Next test and put the following code in the Button Click event:

<code (VB.NET) >
Dim aStartTime As DateTime = Now
Dim dr As DataRow
Dim o As Object
Dim drPtr As Integer
Dim oPtr As Integer
For drPtr = 0 To rootTable.Rows.Count - 1
dr = rootTable.Rows(drPtr)
For oPtr = 0 To dr.ItemArray.Length - 1
o = dr.ItemArray(oPtr)
Next
Next
Dim aEndTime As DateTime = Now
Me.TextBox2.Text = aEndTime.Subtract(aStartTime).ToStrin
</code>

5. Run the project and click on the two buttons. On my machine I am seeing
the For Each loop running in about .70xxx seconds and the For Next loop
running in around 3.60xxx seconds.

-Sam Matzen

[quoted text, click to view]

Scott M.
7/24/2004 8:10:40 PM

I would suggest setting the loop(s) to count their way through the columns
and rows using the .count property that every collection has. It can be
slightly more efficient that a For...Each loop.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about vb.net data

home · blog · groups

Questions? Comments? Contact the dn