• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

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
0
Mr_Fulano
Asked:
Mr_Fulano
1 Solution
 
käµfm³d 👽Commented:
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

0
 
käµfm³d 👽Commented:
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.
0
 
Jeff CertainCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
CodeCruiserCommented:
One approach would be to find the value of the cell in first row and then apply a filter on that value. If the returned number of rows for that filter is equal to the original row count then the value is same for all rows.


Dim emp1 As String = dTable.Rows(0).Item("Emp")
dTable.DefaultView.RowFilter = "Emp='" & emp1 & "'"
If dTable.DefaultView.Count = dTable.Rows.Count Then
   'Log is based on employee
Else 
   dTable.DefaultView.RowFilter = ""
   Dim v1 As String = dTable.Rows(0).Item("Vehicle")
   dTable.DefaultView.RowFilter = "Vehicle='" & v1 & "'"
   If dTable.DefaultView.Count = dTable.Rows.Count Then
      'Log is based on vehicle
   Else
      'Log is neither based on emp nor vehicle
   End If
End If

Open in new window

0
 
Mr_FulanoAuthor Commented:
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
0
 
Mr_FulanoAuthor Commented:
Excellent, thank you for the advice.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now