Printable View
I have a Dataset that contains two data tables and I now need to merge them based on a "key" column which they both share.
I'm wondering what the best way to do this is. I've researched a few methods (Merge, LINQ) but I'm relatively new to .NET and I'd like to know what the preferred method is.
I have the two data tables all set - just need to join them based on a field - ideally leaving behind all records in data table 1 and only the matches from data table 2.
"I have the two data tables all set " -- in what manner? Typed data tables, in a dataset (ala the designer)? or through code?
Personally I'd have done it through SQL and not have to deal with it in code. But, eh... the key is going to be creating a DataRelation between the two tables, then creating a datatable based on that, adding some fields, which should be Expression fields, and the expression is "DataRelation.Table.FieldName" ... if I remember right...
So let's say I have a Parent dataTable and a Child datatable... and a DataRelation set as "ParentChild"
so when I add the column, the expression value is "ParentChild.Parent.Name" for the ParentName... and "ParentChild.Child.Name" for the Child name...
Admittedly though it's been some time since I've done that so I may be remembering it wrong... but I'd swear I've done this before.
-tg
I've uncovered a way of doing it - I think. My first data table contains 99% of the fields I need, I just had to run a second query to grab another column.
When add my rows to a datagridview, I have a statement in there that finds the key value from the parent table in the child table. Once found, it then grabs the value needed and inserts it into the datagridview.
I'm testing it now. Speed is okay surprisingly but is it right - that's the next question.
only you can answer that... like I said, I'd have pulled everything from thje database in the first place... but that's how I am... I'm very object-oriented when it comes to data... I think of in terms of "I need a frame, chassis, an engine, doors, shell, tires, gas, etc for a car..." and get it all at once... you're thinking in terms of "I've got a table of chassis, and a table of wheels and a table of engines... now I need to join them to build a car" ... there's nothing wrong with that. both ways ultimately get us there, they're just different ways... Again, I think what you need to do is create a DataRelation between the two, add a new column to your 99% table, and set it's expression to pull the field from the related table through the relationship.
-tg
I agree with you, I'm just not sure how to get the data I need from the database in one fell swoop.
The problem is, I need to run two queries and join them together. It can't be done in a single query because I use the same column in both.
Lets say I have a column called "Order Number". I currently have two queries - one that that returns the customer's FIRST order, and another that returns their LAST orders.
I looked at Union queries but you appear to require the same columns in both queries. Since my first query contains a lot of columns I was hoping to limit my second query to just the key field and the additional column that I need - nothing more.
Union wouldn't be appropriate... a JOIN would be...
-tgCode:Select t1.Field1, T1.Field2, T1.Field3, T2.Field99
from table1 T1
inner join table2 T2 on T1.Field47 = T2.Field1
Thing about joins, is that they don't even need to be true tables... they can be sub-queries, views, functions, etc...
-tg