Solved

C# join two DataTable together with inner join

Posted on 2013-11-03
3
21,999 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
  • 2
3 Comments
 
LVL 62

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 62

Expert Comment

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now