How to get the intersection of two DataTables in a DataSet

[Bob]

I'm sure this can be done but I just can't figure out how. I have two
DataTables in a DataSet with a relation between the two. Table 1 contains a
full list of product IDs and names and Table 2 contains the list of product
IDs a user has access to, how do I do an "inner join" type of operation to
output a DataView or DataRows (or anyting bindable) that has a list of
product names the user has access to based on the product IDs? I can go
through Table 2 row by row and use DataRow.GetChildRows() to get the rows in
Table 1 but this creates multiple DataRows objects (one for each loop).

Any suggestion is appreciated.
Bob

 

[William Ryan eMVP]

Bob:

There are a few ways but the easiest I can think of is to Create a DataView
with the second table.

Dim dv as DataView = DataTable2.DefaultView

Now, all you have to do is set the rowfilter and then walk through the
filtered items. So, assuming ID's in table1 are unique (that's what the
relation is on) and Table2 has 2 fields (you can adjust this) ID,
ProductName

Then you can do this

For i as Integer = 0 to DataTable2.Rows.Count-1
dv.RowFilter = "ID = '" & DataTable2.Rows(i)("ID") & "'"
'Now the filter is set so the dataview will only have the rows in it that
match the filter. You can walk through the view now and grab those values
for your reference . Also, I reference the column by name above, but you'd
want to use the Numeric index of the column b/c it runs a lot faster...or
you could create an enum so that if ID is the first column, the first value
of your enum would be ID = 0



Next

Here's the code to enumerate a dataview (this is in C# and the other code is
in VB.NET but it's easy to convert

IEnumerator viewCounter = dv.GetEnumerator();
DataRowView drv;
while(viewCounter.MoveNext)
{
drv = (DataRowView) viewCounter.Current;
Debug.WriteLine((string)drv(1) ) ///or you could add the second value
to an array list (this would give you the productname)
MyArrayList.Add(drv(1))
}
If you put this in the other loop where the view's rowfilter was set, you'd
only get the values corresponding to that particular ID number.

HTH,

Bill

P.S. I have a full example here
http://www.knowdotnet.com/articles/dataviews1.html

 

[Bob]

Hi Bill, thanks a lot for your reply. Yeah this does what I need. I was
hoping there would be a structural way (as opposed to a procedureal way) to
do this, but I guess not. There is no need to set DataRelation between the
two tables when using RowFilter like in your example below then.

Bob

 

[William Ryan eMVP]

Bob:

I don't know of any direct way and this is the simplest I can think of other
than what you mentioned earlier. As far as teh DataRelation, I'd still set
it unless I was 200% sure that there were no values in the child that didn't
exist in the parent . While it will still work you won't know that
everything corresponds correctly unless you use the relation but it's not
technically necessary for it to function.

HTH,

Bill