Hi, I have two DataTables from two SQL queries (of two databases).
Both have the same 1st column, the persons name, but the second column in both DataTables are different. I want to try Join them together to make one table.
DataTable 1 looks like...
FeeEarnerName, Value1
John Smith, 1
Jane Doe, 2
Tom Jones, 3
DataTable 2 looks like...
FeeEarnerName, Value2
John Smith, A
Jane Doe, B
Tom Jones, C
Is there anyway I could join the two on the FeeEarnerName, make the DataTable like below, so I can use it in a GridView...
FeeEarnerName, Value1, Value2
John Smith, 1, A
Jane Doe, 2, B
Tom Jones, 3, C
this is what I am using to create the two data tables...
'1st DataTable'
dbcomm = New OdbcDataAdapter(sql, MyConnection)
Dim dt As DataTable = New DataTable()
dbcomm.Fill(dt)
'2nd DataTable'
dbcomm2 = New OdbcDataAdapter(sql2, MyConnection2)
Dim dt2 As DataTable = New DataTable()
dbcomm2.Fill(dt2)
querying the datatables is going to be tricky. why now do the join in your sql query or write a linq query that will join the data before placing it in a datatable
"He who would learn to fly one day must first learn to stand and walk and run and climb and dance; one cannot fly into flying."
What would the LINQ query look like to join my two tables
Hello：）
As far as I see——Since your DataTable's structures look same and you've filled them into different kinds of DataTables but in the same DataSet，no need for you to use LINQ to fetch them
but just use Merge
method for the DataTable when meeting with the same structures of DataTables……
【Sample】
private static void DemonstrateMergeTable()
{
DataTable table1 = new DataTable("Items");
// Add columns
DataColumn column1 = new DataColumn("id", typeof(System.Int32));
DataColumn column2 = new DataColumn("item", typeof(System.Int32));
table1.Columns.Add(column1);
table1.Columns.Add(column2);
// Set the primary key column.
table1.PrimaryKey = new DataColumn[] { column1 };
// Add RowChanged event handler for the table.
table1.RowChanged +=
new System.Data.DataRowChangeEventHandler(Row_Changed);
// Add some rows.
DataRow row;
for (int i = 0; i <= 3; i++)
{
row = table1.NewRow();
row["id"] = i;
row["item"] = i;
table1.Rows.Add(row);
}
// Accept changes.
table1.AcceptChanges();
PrintValues(table1, "Original values");
// Create a second DataTable identical to the first.
DataTable table2 = table1.Clone();
// Add three rows. Note that the id column can't be the
// same as existing rows in the original table.
row = table2.NewRow();
row["id"] = 14;
row["item"] = 774;
table2.Rows.Add(row);
row = table2.NewRow();
row["id"] = 12;
row["item"] = 555;
table2.Rows.Add(row);
row = table2.NewRow();
row["id"] = 13;
row["item"] = 665;
table2.Rows.Add(row);
// Merge table2 into the table1.
Console.WriteLine("Merging");
table1.Merge(table2);
PrintValues(table1, "Merged With table1");
}
private static void Row_Changed(object sender,
DataRowChangeEventArgs e)
{
Console.WriteLine("Row changed {0}\t{1}",
e.Action, e.Row.ItemArray[0]);
}
private static void PrintValues(DataTable table, string label)
{
// Display the values in the supplied DataTable:
Console.WriteLine(label);
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
Console.Write("\t " + row[col].ToString());
}
Console.WriteLine();
}
}
Thanks Decker Dong, that nearly works, I've used...
dbcomm = New OdbcDataAdapter(sql, MyConnection)
Dim dt As DataTable = New DataTable()
dbcomm.Fill(dt)
dbcomm = New OdbcDataAdapter(sql2, MyConnection)
Dim dt2 As DataTable = New DataTable()
dbcomm.Fill(dt2)
dt.Merge(dt2, False, MissingSchemaAction.Add)
Employment.DataSource = dt
Employment.DataBind()
The results are like this though
Parson1, 1, null
Person2, 2, null
Person3, 3, null
Person1, null, A
Person2, null, B
Person3, null, C
danieldunn10
Member
389 Points
383 Posts
LINQ query to Join two DataTables
Apr 23, 2012 04:17 PM|LINK
Hi, I have two DataTables from two SQL queries (of two databases).
Both have the same 1st column, the persons name, but the second column in both DataTables are different. I want to try Join them together to make one table.
DataTable 1 looks like...
FeeEarnerName, Value1
John Smith, 1
Jane Doe, 2
Tom Jones, 3
DataTable 2 looks like...
FeeEarnerName, Value2
John Smith, A
Jane Doe, B
Tom Jones, C
Is there anyway I could join the two on the FeeEarnerName, make the DataTable like below, so I can use it in a GridView...
FeeEarnerName, Value1, Value2
John Smith, 1, A
Jane Doe, 2, B
Tom Jones, 3, C
this is what I am using to create the two data tables...
Thanks very much
rickjames961
Participant
775 Points
174 Posts
Re: LINQ query to Join two DataTables
Apr 23, 2012 06:23 PM|LINK
querying the datatables is going to be tricky. why now do the join in your sql query or write a linq query that will join the data before placing it in a datatable
Friedrich Nietzsche
danieldunn10
Member
389 Points
383 Posts
Re: LINQ query to Join two DataTables
Apr 23, 2012 08:35 PM|LINK
Thanks Rick, i think you're right.
What would the LINQ query look like to join my two tables
Many Thanks
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ query to Join two DataTables
Apr 25, 2012 01:53 AM|LINK
Hello：）
As far as I see——Since your DataTable's structures look same and you've filled them into different kinds of DataTables but in the same DataSet，no need for you to use LINQ to fetch them but just use Merge method for the DataTable when meeting with the same structures of DataTables……
【Sample】
private static void DemonstrateMergeTable() { DataTable table1 = new DataTable("Items"); // Add columns DataColumn column1 = new DataColumn("id", typeof(System.Int32)); DataColumn column2 = new DataColumn("item", typeof(System.Int32)); table1.Columns.Add(column1); table1.Columns.Add(column2); // Set the primary key column. table1.PrimaryKey = new DataColumn[] { column1 }; // Add RowChanged event handler for the table. table1.RowChanged += new System.Data.DataRowChangeEventHandler(Row_Changed); // Add some rows. DataRow row; for (int i = 0; i <= 3; i++) { row = table1.NewRow(); row["id"] = i; row["item"] = i; table1.Rows.Add(row); } // Accept changes. table1.AcceptChanges(); PrintValues(table1, "Original values"); // Create a second DataTable identical to the first. DataTable table2 = table1.Clone(); // Add three rows. Note that the id column can't be the // same as existing rows in the original table. row = table2.NewRow(); row["id"] = 14; row["item"] = 774; table2.Rows.Add(row); row = table2.NewRow(); row["id"] = 12; row["item"] = 555; table2.Rows.Add(row); row = table2.NewRow(); row["id"] = 13; row["item"] = 665; table2.Rows.Add(row); // Merge table2 into the table1. Console.WriteLine("Merging"); table1.Merge(table2); PrintValues(table1, "Merged With table1"); } private static void Row_Changed(object sender, DataRowChangeEventArgs e) { Console.WriteLine("Row changed {0}\t{1}", e.Action, e.Row.ItemArray[0]); } private static void PrintValues(DataTable table, string label) { // Display the values in the supplied DataTable: Console.WriteLine(label); foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { Console.Write("\t " + row[col].ToString()); } Console.WriteLine(); } }danieldunn10
Member
389 Points
383 Posts
Re: LINQ query to Join two DataTables
Apr 25, 2012 06:28 AM|LINK
Thanks Decker Dong, that nearly works, I've used...
dbcomm = New OdbcDataAdapter(sql, MyConnection) Dim dt As DataTable = New DataTable() dbcomm.Fill(dt) dbcomm = New OdbcDataAdapter(sql2, MyConnection) Dim dt2 As DataTable = New DataTable() dbcomm.Fill(dt2) dt.Merge(dt2, False, MissingSchemaAction.Add) Employment.DataSource = dt Employment.DataBind()The results are like this though
Parson1, 1, null
Person2, 2, null
Person3, 3, null
Person1, null, A
Person2, null, B
Person3, null, C
Is there anyway to combine them?
Thanks!
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ query to Join two DataTables
Apr 25, 2012 06:51 AM|LINK
What do you mean？Combine means to remove duplicated ones？Show us what you expect……:-)
danieldunn10
Member
389 Points
383 Posts
Re: LINQ query to Join two DataTables
Apr 25, 2012 06:59 AM|LINK
Sorry i was meaning instead of...
Person1, 1, null
Person2, 2, null
Person3, 3, null
Person1, null, A
Person2, null, B
Person3, null, C
It would appear like...
Parson1, 1, A
Person2, 2, B
Person3, 3, C
Thank you!
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ query to Join two DataTables
Apr 25, 2012 07:34 AM|LINK
Hello danieldunn10：）
Sorry the default method cannot do that——You have to do this（suppose you have two tables called Table1 and Table2……）：
DataTable destination = DataTable1.Clone(); for(int i=0;i<DataTable1.Rows.Count;++i) { destination.Rows.Add(DataTable1.Rows[i][0].ToString(),DataTable1.Rows[i][1].ToString(),DataTable2.Rows[i][2].ToString()); }danieldunn10
Member
389 Points
383 Posts
Re: LINQ query to Join two DataTables
Apr 25, 2012 08:00 AM|LINK
Thanks Deker DOng, nearly there...
I get the error
A field or property with the name 'Value2' was not found on the selected data source.
I tried changing datatable2.Rows(i)(2).ToString() to datatable2.Rows(i)(1).ToString() but it didnt help sorry
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ query to Join two DataTables
Apr 25, 2012 08:04 AM|LINK
Your DataTable1 and DataTable2 have the same structure？Plz list them for us.
Many Thx！