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

I have two Datatables that (inside the SQLS DB) have a relationship (let's call them dtStock and dtSpec, where dtSpec.ItemTy开发者_运维技巧peID = 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

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)

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.