What is the correct way to iterate through rows in a DataTable?

[T.C.]

My application uses a DataSet to store data. Whenever I must loop through a DataTable, I use code that looks something like this:

For Each ElementRow As DataRow In ElementTable.Select("TRUE", "", DataViewRowState.CurrentRows)
...

That works, but I suspect it isn't the best technique. It bothers me that I must specify FilterExpression and Sort, even though I don't want to filter and I don't care about the sort order. Have I overlooked a more appropriate way to iterate through rows?

-TC

 

[jmcilhinney]

You can just loop through all rows like so:

For Each row As DataRow In table.Rows

but then, if there are any deleted rows, you'll have to test the RowState before accessing the data or an exception will be thrown.

That said, if you want to use the Select method to exclude deleted rows as you are, if you don't want to specify a filetrExpression and sort then don't:

For Each row As DataRow In table.Select(Nothing, Nothing, DataViewRowState.CurrentRows)

If you were using .NET 3.5 or later then you'd have LINQ at your disposal:

For Each row In table.AsEnumerable().Where(Function(r) r.RowState <> DataRowState.Deleted)

 

[Budius]

I might be a bit old school, but since I migrate to vb.net2010 (just a few months ago) I just been using what the old ols:

For i = 0 to rowcount-1
FOR J = 0 to columnscount-1
   table.row(i).cell(j).value
next 
next

and it didn't gave me problems.

--------------
Edit:

I know the other presented methods works, and give more options (such as filtering, etc) but if' I'm doing something terribly wrong or if there are some pros and cons I'm not seeing, let me know...

 

[jmcilhinney]

I might be a bit old school, but since I migrate to vb.net2010 (just a few months ago) I just been using what the old ols:

For i = 0 to rowcount-1
FOR J = 0 to columnscount-1
   table.row(i).cell(j).value
next 
next

and it didn't gave me problems.

--------------
Edit:

I know the other presented methods works, and give more options (such as filtering, etc) but if' I'm doing something terribly wrong or if there are some pros and cons I'm not seeing, let me know...

That's looping through the cells in a DataGridView, not a DataTable. It does make me think though, another option would be to loop through the DataTable's DefaultView, which will naturally exclude and deleted rows:

For Each row As DataRowView In myDataTable.DefaultView
    '...
Next

Note that each row is then a DataRowView, rather than a DataRow. For most purposes the two are interchangeable, but if you really need the DataRow then you can get it from the Row property:

For Each view As DataRowView In myDataTable.DefaultView
    Dim row As DataRow = view.Row

    '...
Next

 

[Budius]

That's looping through the cells in a DataGridView, not a DataTable.

it works on both (with small syntax differences), this time I'll copy from my code instead of just typing by heart:

'Datatable (from inside a Dataset)
' Filling an Excel object using late binding
            For Each Tbl In DS.Tables
                    oWS = oWB.Worksheets.Add(, oWB.Worksheets(oWB.Worksheets.Count)) ' Add the table
                    oWS.Name = Tbl.TableName
                    For i = 1 To Tbl.Columns.Count
                        oWS.Cells(1, i) = Tbl.Columns(i - 1).ColumnName
                    Next

                    For j = 1 To Tbl.Columns.Count
                        For i = 1 To Tbl.Rows.Count
                            oWS.Cells(i + 1, j) = Tbl.Rows(i - 1).Item(j - 1).ToString ' Fill it up
                        Next i
                    Next j
              Next Tbl


'DatagridView
      DatagridView1.Rows(i).Cells(j).Value  ' Do something with the value

I'm re-reading the original question, I know it specific says "iterate through rows" and on your 1st answer the

For Each row As DataRow In table.Rows

is the most straight forward method, but the method I'm presenting still goes through the rows and I'm just trying to enrich the discussion with more alternatives.

and as I asked before, even thou I'm aware of the extra options that the other methods give (such as filtering) is there any explicit reason not to use the method I'm using???

 

[jmcilhinney]

and as I asked before, even thou I'm aware of the extra options that the other methods give (such as filtering) is there any explicit reason not to use the method I'm using???

Generally speaking, if you have bound controls then you should be working with the data source and not the controls in code. As such, if you have a DataTable bound to a DataGridView then you would generally work with the DataTable in preference to the DataGridView in code. Even better would be to work with a BindingSource.

As for loops, a For Each loop is a bit for natural when you want to do something with each item in a list. I'd only choose a For loop over a For Each loop if I specifically wanted to use the loop counter for another purpose as well.

 

[cjard]

Note that you cannot modify the contents of a collection if you are enumerating it, i.e. If using "For Each row As DataRow in table.Rows" you cannot remove the row from the table. If you plan on modifying the collection, use indexed access instead of enumerating (and working backwards will make things easier)

Note 2 that when using typed datatables, the syntax is: For Each row as MyDataSetName.MyDataTableName in myDataSetInstance.MyDataTable
Do not use the .Rows property

 

[jmcilhinney]

Note that you cannot modify the contents of a collection if you are enumerating it, i.e. If using "For Each row As DataRow in table.Rows" you cannot remove the row from the table.

That is definitely another reason to use a For loop, but it would rarely apply to a DataTable because, while you might Delete a row, you would rarely Remove one. Deleting a row doesn't actually remove it from the table, so the collection isn't modified.

 

[T.C.]

Thanks for the replies. In my original post, I did not do a good job of expressing the essence of my question, so I'll try again:

I'm confused by deleted rows. Several times, I have assumed there would be no deleted rows in a table, but later experienced esoteric bugs in my code when that assumption proved to be wrong. As a result, I've concluded that the best practice is to always filter out deleted rows except in those rare, hypothetical situations where deleted rows are wanted.

In fact, this conclusion seems so obvious to me, I can't understand why it isn't reflected by the syntax of the language. To omit deleted rows, we must either add If statements or explicitly specify row states using a rather awkward overload of the Select method. When I find myself writing syntax like this

For Each row As DataRow In table.Select(Nothing, Nothing, DataViewRowState.CurrentRows)

it makes me think the creators of the programming language did not expect the language to be used this way. I can't help but wonder if they didn't realize the importance of omitting deleted rows (which seems unlikely), or if they provided another method which they expected to be used instead.

What I'm really looking for, in other words, is a good, clean syntax for iterating through the undeleted rows in a DataTable. From the discussion so far, I expect the "Select(Nothing, Nothing, DataViewRowState.CurrentRows)" solution is as good as it gets.

-TC

 

[jmcilhinney]

Of course you can't omit deleted rows by default. If you did, how then would you delete the corresponding records from the database when saving? What you should probably be doing is looping through the table's DefaultView, which is a DataView and doesn't expose deleted rows. The DefaultView property is similar to the Rows property, except that it supports sorting and filtering. Each item is a DataRowView, rather than a DataRow. In most cases you can treat them as though they were the same thing, although there are some differences. If you really need the DataRow then you can get it from the Row property of the DataRowView.

By the way, when you bind a DataTable to a DataGridView or some other control(s), it's the contents of the DefaultView that you see. That's why a row disappears from the grid when you delete it. It's also how you're able to sort a DataGridView by column.

 

[T.C.]

jmcilhinney,

Thank you for the excellent reply. In particular, I want to thank you for telling me that the DefaultView does not expose deleted rows. I did research on DefaultView before posting here, but I never discovered that fact.

I have one concern about using DefaultView. My understanding is that the DefaultView can be changed. Therefore, it is possible for one section of code to change the DefaultView for its purposes and break another section of code which expects a different DefaultView. Because of this concern, and because it is so easy to create custom DataView objects, I've concluded that using DefaultView is unnecessarily dangerous.

I understand that DataViews are useful for binding, but I am not aware of any benefit they offer for data processing. I've tried using DataViews with syntax like this:

Dim ElementView as DataView
ElementView = New DataView(ElementTable, Nothing, Nothing, DataViewRowState.CurrentRows)
For Each ElementRow as DataRowView in ElementView
...

However, that code just seems wordier than the approach which uses the DataTable directly, without offering any benefits.


-TC

 

[cjard]

Perhaps you should tell us what programming problem youre trying to solve, rather than asking for help with some broken solution?

Why are you iterating the table?

 

[jmcilhinney]

I have one concern about using DefaultView. My understanding is that the DefaultView can be changed. Therefore, it is possible for one section of code to change the DefaultView for its purposes and break another section of code which expects a different DefaultView.

You seem to be confused between a DataView and the DefaultView. DataView is a class. You can create as many DataViews as you like for as many DataTables as you like, but that doesn't make any of them the DefaultView. DefaultView is a property of the DataTable class and it exposes a DataView object that the DataTable creates itself. Each DataTable never has more than one object exposed via its DefaultView in its lifetime. Code to use the DefaultView would look like this:

For Each row As DataRowView In myDataTable.DefaultView
    'Use row here
Next

 

[T.C.]

jmcilhinney,

Are you saying I can be confident DefaultView will always give me the view I expect?

What if someone writes something like this in an unrelated section of code?

myDataTable.DefaultView.RowFilter = "MyIDColumn = 123"

Wouldn't that really mess me up if I were using this syntax in my code?

For Each row As DataRowView In myDataTable.DefaultView

That is what I meant when I said DefaultView strikes me as dangerous. If you meant to rebut that concern by explaining the difference between a DataView and a DefaultView, I'm afraid I don't understand.


-TC