?
Solved

Filtering a DataTable in a quick manner.

Posted on 2011-05-06
6
Medium Priority
?
589 Views
Last Modified: 2012-05-11
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
Comment
Question by:Mr_Fulano
6 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35710693
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35710695
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
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 35710804
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 35716039
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
 

Author Comment

by:Mr_Fulano
ID: 35751748
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
 

Author Closing Comment

by:Mr_Fulano
ID: 35751752
Excellent, thank you for the advice.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month16 days, 10 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question