Access the contents of a column in a DataTable in code

Question

I've done research on this but none of results found does what I'm looking for.

Suppose I have a DataTable that was filled from a MySql database, in another function, I want to access one of the columns and assign the values to a variable (one at time in a loop)

Some code to illustrate what I'm trying to do:

Dim adapter As New MySqlDataAdapter
Dim dt As New DataTable
Dim intList As New List(Of Integer)
.......
.......
adapter.fill(dt)
.......
.......
dim col = dt.Columns(1)
populate the list here with the contents of the column

EDIT I am not entirely sure that Columns(1) is correct syntax

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2017-02-24T23:37:41.213

1

(Referring to the other answer) First, note that there is no need to separately declare scratch variables used to loop thru something. You can declare the type as part of the For...:

For Each row As DataRow In Dt.Rows

This is a more than a matter of coding style. The row variable above exists only between the For Each and Next. Declared as in the other answer, a more lengthy method can result in tmp, tmp1 etc each of a different type which were used just once for different loops.

Then, there are a number of linq methods that can get your list for you without you writing a loop at all:

Dim carbs = dt.AsEnumerable().
                  Select(Function(q) q.Field(Of Int32)("Carbs")).
                  ToList()

With Option Infer, you don't even need to declare carbs as a List(of Int32), but you can:

Dim carbs As List(Of Int32) = dt.AsEnumerable(). ...

edited Feb 24 '17 at 23:37

answered Feb 24 '17 at 20:04

Ňɏssa Pøngjǣrdenlarp

38,411
12
59
178

This works very, very well. I just don't understand how the select function works by using a function in the argument. Any sources you recommend on reading to understand that further? – robotHamster Feb 24 '17 at 20:49
1

It is a [Lambda Expression](https://msdn.microsoft.com/en-us/library/bb531253.aspx). It provides the actual logic for *what* to select. Many, many of the linq methods will take/or require a lambda to supply the actual logic. Note that `q.Field(Of ...)` is yet another linq extension to cast the column selected to a specific type. Together they can reduce simple loops to almost nothing or perform much more complex tasks with very little code. [This is a c# example](http://stackoverflow.com/a/39082249/1070452) to remove dupes in 2 CSV files ( `q =>` is the c# form of `Function (q)`). – Ňɏssa Pøngjǣrdenlarp Feb 24 '17 at 21:27
Thanks a lot @Plutonix , I'll make sure I go over all of this this weekend, it looks like I can reduce a lot of my code using this – robotHamster Feb 24 '17 at 21:30
1

The starting point is probably [Extension Methods](https://msdn.microsoft.com/en-us/library/system.linq.enumerable(v=vs.110).aspx), then lambdas as needed to implement them. – Ňɏssa Pøngjǣrdenlarp Feb 24 '17 at 21:36

score 0 · Answer 2 · edited May 23 '17 at 12:25

0

I found what I was looking for here, I don't know how I missed that in my seach.

The code in the end is the following:

    Dim carbsInvolved As New List(Of Integer)
    Dim row As DataRow
    For Each row In dt.Rows
        carbsInvolved.Add(row.Item(1))
    Next

And then the numbers in the list can be accessed by carbsInvolved.Item(index) for later use :)

edited May 23 '17 at 12:25

Community

1
1

answered Feb 24 '17 at 19:41

robotHamster

609
1
7
24

Access the contents of a column in a DataTable in code

2 Answers2