Link to home
Start Free TrialLog in
Avatar of Mr_Fulano
Mr_FulanoFlag for United States of America

asked on

Filtering a DataTable in a quick manner.

Hi, I'm using VB.NET 2010 (VS2010), WinForms. - I have a TXT file (a Log) in the format below, which I read into a StreamReader Object, split at the commas, and then put those values into a DataTable. -- That all works well.

Format:
Employee Name    Vehicle     Date/Time

The problem I've encountered is that this log can be run in two ways. One is based on "Employee" and that will yield a list of each employee and which vehicles they've used (Example 1). The other way is based on "Vehicle" and that will yield a log with the same format, but one that is based on a specific vehicle (Example 2).


Example 1 -- Log based on Employee:
Employee_1,Vehicle_1,Date, Time
Employee_1,Vehicle_2,Date, Time
Employee_1,Vehicle_2,Date, Time
Employee_1,Vehicle_7,Date, Time
Employee_1,Vehicle_2,Date, Time
Employee_1,Vehicle_3,Date, Time
Employee_1,Vehicle_1,Date, Time
Employee_1,Vehicle_4,Date, Time


Example 2 -- Log based on Vehicle:
Employee_1,Vehicle_2,Date, Time
Employee_2,Vehicle_2,Date, Time
Employee_1,Vehicle_2,Date, Time
Employee_3,Vehicle_2,Date, Time
Employee_3,Vehicle_2,Date, Time
Employee_1,Vehicle_2,Date, Time
Employee_3,Vehicle_2,Date, Time
Employee_2,Vehicle_2,Date, Time


Is there a quick way (other than looping through each line in the log) to determine what the log is based on? In other words, if each of the values in the Vehicle column are the same, the report was run based on "Vehicle", if all the "Employee Names" are the same, then the report was run based on employee.

Given that I'm using a DataTable, is there any feature of a datatable that will look at the values in a specific column and tell me that they are all the same, or not all the same?

Thanks,
Fulano
Avatar of kaufmed
kaufmed
Flag of United States of America image

How about some Linq/lambda action? You would need to change the "Of String" to "Of [Type]" if the types of the columns in your DataTable aren't strings.

The first query finds the value of the first cell in the first row of the table. The second query then checks all columns against the first value and if any column doesn't match, you have, based on the example above, a file that is based on vehicle.
Dim firstRowFirstCol As String = dataTbl.Rows.OfType(Of DataRow)() _
                                             .First() _
                                             .Field(Of String)(0)
Dim isFirstColumnUnique = dataTbl.Rows.OfType(Of DataRow) _
                                      .Any(Function(x) x.Field(Of String)(0) <> firstRowFirstCol)

Open in new window

In hindsight, I guess it's just as simple to access the cell directly rather than do Linq on it:

Dim firstRowFirstCol As String = dataTbl.Rows(0).Field(Of String)(0)

Open in new window


The second query is still OK.
If you have a large number of records, you're better off to get away from DataTable. Here's a link to a discussion of the scalability issues:
http://msdn.microsoft.com/library/dd364983.aspx
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mr_Fulano

ASKER

Hi Chaosian, thanks for the link, but its too late in the game for me to get away from the DataTable approach. However, I'll keep it in mind for the next round.

Thanks,
Fulano
Excellent, thank you for the advice.