IN clause in

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
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')"
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 ")
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

mudharanAuthor Commented:
We are already using a helper class, but would like to avoid constructing string etc...

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.
Jeff CertainCommented:
Sounds like the dataview approach would work just fine...
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).

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.