Solved

C# join two DataTable together with inner join

Posted on 2013-11-03
3
24,247 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 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

820 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