Solved

C# join two DataTable together with inner join

Posted on 2013-11-03
3
25,147 Views
Last Modified: 2016-02-10
I have 2 datatables that I want to merge into 1 in the same way that one merges with an inner join in sql server.

One catch is that some of the field names are the same in both tables, but the values may be different (eg. pricing values). The tables both have a column 'ID' that have the same value and can be joined.
0
Comment
Question by:esak2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39619921
Hi esak2000;

In order to do what you want you will need to create a new DataTable object with all the columns you wish to include from the two original DataTable's. You will need to give those columns with the same name different names in the new table. Once you have that in place you can run a Linq to Object query as follows:

var resultingTable = from t1 in dataTable1.AsEnumerable()
                     join t2 in dataTable2.AsEnumerable() 
                         on t1.Field<int>("ID") equals t2.Field<int>("ID")
                     select new { t1, t2 };
                     
// Now with the results of the query fill in the columns of the new DataTable
foreach(var dr in resultingTable)
{
    DataRow newRow = newDataTable.NewRow();
    // Now fill the row with the value from the query t1
    newRow["ColumnName1"] = dr.t1.Field<DataType of Column>("ColumnName1");
    // ... Continue with all the columns from t1 in the same way
    
    // Now fill the row with the value from the query t2
    // In the same way as above
    
    // When all columns have been filled in then add the row to the table
    newDataTable.Rows.Add(newRow);
} 

Open in new window

0
 

Author Comment

by:esak2000
ID: 39620108
That's amazing, you save me hours of time, thank you!!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39620179
Not a problem, glad to help.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 77
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 60
VS C# how to design libary of functions 7 33
SQL Job Failed 6 29
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question