Join two datatables populated from different sources?

[Guest]

Hi,

I've managed to populate a dataset with two different datatables from
different webservices. They each contain a different number of columns but
both have the Ticker symbol as a common key.

Is there a way to "join" or combine these the two tables based on the ticker
into one? I only want to return rows from each where there is a match on
ticker between the two (like an inner join).

I've tried creating a DataRelation but the datatable I had setup as the
parent returns all it's rows when I bind it to a datagrid even though there
are no matching children. So, I'm not sure if this object is the way to go.

If I'm able to combine the datatables into a single one, I want to filter it
at that point.

Thanks, Dave

 

[Alex Passos]

Yes you can merge using the DataSets merge method. Here is the MSDN
documentation for it:

http://msdn.microsoft.com/library/d...tml/frlrfSystemDataDataSetClassMergeTopic.asp

 

[Sahil Malik [MVP]]

Okay so if I understood your question correctly,

You have two tables. They have a common column. And you want to find all
rows, where that particular column value is the same in either table. Right?

Hmm ... Merge is not what you need then .. IMO.

You can however use DataTable.Select. The where clause you formualte for the
the Select method, will have to be formed over a loop from the first table.

So something like ..

Table2.Select("Ticker In (1,2,3,4)")

Where 1,2,3,4 are formed over a loop from Table1.Ticker.

BTW - I must add - DataTable.Select isn't the fastest pony in the stable, so
hopefully you don't have a lot of rows.

There are other ways to do this too - but the above is the most
straightforward.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Guest]

Thanks, but this doesn't seem to work for me. The only column in common
between the two is ticker. How does it know to join on it? I tried merging
the second datatable into the first but nothing different was displayed when
I bound it.

dst.Merge(dst.Tables["Company2"], false,MissingSchemaAction.Add);

DataGrid1.DataSource = dst.Tables[0];
DataGrid1.DataBind(); --still shows just the original "Compan1"...

Dave

 

[Guest]

Thanks! You're right. It would be just like a SQL Join on ticker, except I
have these two datatables loaded with XML from external separate sources. I
want to join them into one result set somehow.

I'd be interested in other ideas too. When I started this, I thought the
hard part was getting the datatables loaded but now I'm kind of stuck getting
getting them joined/combined on the common key.

Thanks, Dave

 

[Sahil Malik [MVP]]

Another approach could be to have a seperate table with all the unique
ticker values, and then setup a relationship with the two tables.

TickerMapId, TIcker1, Ticker2.

And once you setup this relation, (This'd be somewhat like a nullable FK),
you can find where Ticker1 = Ticker2 - and find childrows based on that.

This approach has an initial setup cost - but will work MUCH faster than
DataTable.Select, and is more suitable to a situation where you have
multiple requests to the same two datatables you've prepared.

I am sure there are even other approaches - it totally depends on how
creative you wanna be.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Guest]

Ok, thanks, but if you mean a DataRelation, I've already tried that but it
nests the datatables in a hierarchial manner. Where I want to make a "flat"
resultset between the two. I guess I don't quite understand how I would do
the find you describe.

 

[Sahil Malik [MVP]]

Not just a datarelation. You will have to add a brand new table, and two
datarelations per what I described. It should work I think.


--

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/