IN clause in datatable.select

Hi all
How to use an IN clause in Select method of DataTable.
I have a table with list of entities and parent relation as columns. I populated the rows from database to DataTable. I'm able to use query like below.

Dim Rows as DataRow()
Rows = DataTable.Select("ParentRelation = 'ID1'  OR ParentRelation = 'ID2'")

But ultimatly I want to use something like what we do in SQL query

SELECT * FROM table1 WHERE ParentRelation IN ('ID1','ID2')

Any help would be much appreciated.
Thanks in advance
mudharanAsked:
Who is Participating?
 
Jeff CertainCommented:
1. Why don't you just populate the list from a SQL query as you mentioned, instead of doing it in your application?
2. You could probably use a dataview. As a general rule, the RowFilter property can be set to whatever SQL WHERE clause you'd like...
dim dv as dataview = new dataview(datatable)
dv.RowFilter = "ParentRelation IN ('ID1','ID2')"
0
 
toddhdCommented:
You are limited by the DataTable.Select's own requirements. It expects a filter expression, which is what you did above.

My only suggestion would be to write a Helper function of some type, roughly:

Public Function IN(ByVal ColName As String, Byval Values As String()) As String
Dim sbAs New StringBuilder
Dim Terms() As String = Split(values,",")
For Each s As String in Terms
sb.Append (ColName & "=" & s & " OR ")
Next
sb.Remove(4) ' I forget the syntax, but we are just stripping off the extra " OR " at the end
Return sb.ToString
End Function

That's off the top off my head, so sorry if any errors - but you get the idea. Then just

DataTable.Select(IN("ParentRelation","ID1,ID2,ID3"))
0
 
mudharanAuthor Commented:
Tod
We are already using a helper class, but would like to avoid constructing string etc...

Chaosian
This would be fetched dynamically everytime user selects some options. To avoid the round trips to database we are using a datatable and helper class to implement it.
0
 
Jeff CertainCommented:
Sounds like the dataview approach would work just fine...
0
 
SanclerCommented:
I have used it successfully in an array of DataRows such as you are using.  The only difference between my code and yours is that I put the filter - just in the form "ParentRelation IN ('ID1','ID2')" - in a String variable. I also had a sorting clause in a String variable, and I then used  Rows = DataTable.Select(inFilter, sortExp).

Roger
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.

All Courses

From novice to tech pro — start learning today.