C# / LINQ(?) - Joining two DataTables (without SQL!)

Question

I have two Datatables that (inside the SQLS DB) have a relationship (let's call them dtStock and dtSpec, where dtSpec.ItemTypeID = dtStock.ID). I am pulling these from the DB separately (using SqlClient) so I can update them both with CommandBuilders.

I have a control that wants to see a single Datatable (dtJob).

How do I join them so that when I update dtJob using the control, dtSpec and dtStock get updated as well? I'm guessing this will involve LINQ...

This sounds like it should be a pretty simple thing to do, but most of the examples I've found seem to COPY the data to a new table rather than refer to the original data, or require a lot of code to accomplish (e.g. rewriting the CopyToDataTable method to deal with joins). Maybe my Google-fu is just rubbish.

Thanks in advance

Enrico Campidoglio · Accepted Answer · 2011-02-22 19:36:24Z

1

Assuming that the schema of the dtJob DataTable is the sum of the dtStock and dtSpec schemas you might want to look at the DataTable.Merge method to propagate the changes from dtJob to the two other tables.

Here's an example:

dtStock.Merge(dtJob, preserveChanges: false, MissingSchemaAction.Ignore);
dtSpec.Merge(dtJob, preserveChanges: false, MissingSchemaAction.Ignore);

Note that in this sample I've used named arguments, which are only available in C# 4.0, in order to make the method call more readable.

Here's a more complete example:

var firstTable = new DataTable();
firstTable.Columns.Add(new DataColumn("FirstTableId", typeof(int)));
firstTable.Columns.Add(new DataColumn("FirstTableName", typeof(string)));
firstTable.Columns.Add(new DataColumn("SecondTableId", typeof(int)));

var secondTable = new DataTable();
secondTable.Columns.Add(new DataColumn("SecondTableId", typeof(int)));
secondTable.Columns.Add(new DataColumn("SecondTableName", typeof(string)));

var joinedTable = new DataTable();
joinedTable.Columns.Add(new DataColumn("FirstTableId", typeof(int)));
joinedTable.Columns.Add(new DataColumn("FirstTableName", typeof(string)));
joinedTable.Columns.Add(new DataColumn("SecondTableId", typeof(int)));
joinedTable.Columns.Add(new DataColumn("SecondTableName", typeof(string)));

joinedTable.Rows.Add(1, "FirstTableRow1", 1, "SecondTableRow1");
joinedTable.Rows.Add(2, "FirstTableRow2", 1, "SecondTableRow1");
joinedTable.Rows.Add(3, "FirstTableRow3", 2, "SecondTableRow2");

firstTable.Merge(joinedTable, false, MissingSchemaAction.Ignore);
secondTable.Merge(joinedTable, false, MissingSchemaAction.Ignore);

Related resources:

DataTable.Merge Method (DataTable, Boolean, MissingSchemaAction)

edited Feb 22, 2011 at 19:36

answered Feb 22, 2011 at 12:05

Enrico Campidoglio

60.8k13 gold badges137 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jez Clark Over a year ago

I can't get this to work - I get "Target table dtJoined missing definition for column ItemType_ID" at the first merge line, but ItemType_ID IS definitely there (I looped through the source tables' columns and populated the joined table's columns from them)...or am I misunderstanding the error? (I changed the 'MissingSchemaAction' so I could see the error, previously it was returning a blank table)

Enrico Campidoglio Over a year ago

@Jez Clark Can you post the schemas of the three tables?

Jez Clark · Accepted Answer · 2011-02-23 15:01:05Z

0

In the end I canned this DataTable idea and went with a List (even with the higher quantity of code required) - after some thought I discovered that joining two Datatables isn't what I really wanted to do anyway, things were somewhat more complex. Thanks for the help though.

answered Feb 23, 2011 at 15:01

Jez Clark

2,9594 gold badges29 silver badges31 bronze badges

Collectives™ on Stack Overflow

C# / LINQ(?) - Joining two DataTables (without SQL!)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related