Trouble with Filtering a .NET DataTable

Question

I have the following code:

myDataSet.myTable.DefaultView.RowFilter = "Code <> 'X'"

After the line executes, I can see the updated result where no rows contain an X for the Code column, if I hover over DefaultView. But, when hovering over myDataSet.myTable the table still contains records where Code = 'X'.

What would I need to do in order to make the myDataSet.myTable be updated with the results of the filter applied?

Nothing, because you can't. You cannot sort or filter the DataTable itself, other than actually editing each individual row as needed. When you sort and filter, you do it to the DefaultView. That's why, when you bind a DataTable, e.g. to a DataGridView, the data displayed in the UI actually comes from the DefaultView. Why can't you just get the data from the DefaultView yourself? If you really must have a DataTable for some reason, the DefaultView has a ToTable method, which will create a new DataTable containing the data exposed by the DefaultView. — jmcilhinney
– jmcilhinney, Commented Jan 17 at 15:35

Albert D. Kallal · Accepted Answer · 2025-01-17 17:54:22Z

1

Well, as pointed out, the "view" is in effect another "view" into that data table. Rather similar as to when you use a SQL query against the database. The result of that query is a "view" into the database.

So, you are free to set the "default" view on a data table, and it will filter to that view. And thus you can get BOTH a count of table rows, and a count of filter rows.

And, you can "copy" the results into a new table object if you wish.

This example shows this in action:

    Dim dtHotels As DataTable

    Dim strSQL As String =
        "SELECT * FROM tblHotelsA
        WHERE Active = 1
        ORDER BY HotelName"

    dtHotels = MyRst(strSQL)

    dtHotels.DefaultView.RowFilter = "[City] = 'Edmonton'"

    Dim MyFilterTable As DataTable = dtHotels.DefaultView.ToTable()

    Debug.Print($"dtHotels rows = {dtHotels.Rows.Count}")
    Debug.Print($"dtHotels view filter rows = {dtHotels.DefaultView.Count}")
    Debug.Print($"Filter table (copy) = {MyFilterTable.Rows.Count}")

Output:

dtHotels rows = 16
dtHotels view filter rows = 8
New Filter table (copy) = 8

So, the view is much in effect like a "query" against that data table, and the row count, and original rows still exist in that data table.

answered Jan 17 at 17:54

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rich Jan 17 at 19:16

Okay, so you couldn't alter the original table, but you could filter a view on it then copy the results into a new table. I guess my issue is that the DataSet that contains the table is being passed as an argument into a method in another class that is shared. I don't really want to change the method in the other class to use a different table. In order to use the same table I could instead delete records from the original table that match a condition.

Albert D. Kallal Jan 17 at 19:41

Well, I suppose you could execute a .delete method on the data table, but that makes little sense. How a table view works is, well how they always worked. As my above example shows, you are able to do a count of table rows (regardless of the view filter), OR YOU are allowed to do a count of the view filter rows. This allows sorting, filtering, and allows you to NOT have to re-query the database. So, views have a valuable use case. But, no surprise how they work. It not clear why the target routines can't use a view, or use a copy of the table filter to another datatable....

jmcilhinney Jan 18 at 3:36

@Rich, if the data in your DataTable is not what you want passed to this method then that data shouldn't be there in the first place. You should have used a WHERE clause on the SQL that populated it in the first place, then there'd be no need for another filter. The alternative would be to change that other method to use the DefaultView of each DataTable instead of the Rows. If the view isn't sorted or filtered then the results will be the same, so other callers will be unaffected, unless they have applied sorting and/or filtering but didn't intend for them to affect that call.

Collectives™ on Stack Overflow

Trouble with Filtering a .NET DataTable

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related