Merge two datatables based on one primary column in C#

Question

0.00/5 (No votes)

See more:

I have two datatables(i don't have any database table, just simple datatable) as shown in screenshot (datatable1 and datatable2).

C#

DataTable datatable1= new DataTable();
 new_Table.Columns.Add("Name");
 new_Table.Columns.Add("Number_A");
 new_Table.Columns.Add("Comment");
 new_Table.Columns.Add("ID");

DataTable datatable2= new DataTable();
 new_Table.Columns.Add("Name");
 new_Table.Columns.Add("Number_B");
 new_Table.Columns.Add("Age");
 new_Table.Columns.Add("City");

screenshot

after binding them I want to compare two datatable and merge rows with similar "Name's column" value and add other rows also as you see in screenshot, and create new datatable named result datatable...

Can you please explain to me how to achieve this?

Thanks

What I have tried:

i don't have any idea for merge this tables

Posted 1-Dec-19 22:12pm

pcprogrammer9

Updated 2-Dec-19 2:34am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-12-01T22:25:00

Basically, your tables are badly designed, and there is no "guaranteed" link between them - the only column which exists in both tables is "Name" and that is not unique, even in small groups. Remember your schooldays - how many children just in your class shared a common forename? As the sample size increases, the chance of duplicates also rises and a single duplicate will mess up your results horribly. Think about it: even with Birthdays, the change of a duplicate reaches 99.9% probability with just 70 people, and 50% probability with 23 people - so names are a real risk!

You need to redesign your data structures before you start "merging tables": Name should be in a third table with a unique ID column to allow duplicates, and the other two tables should reference the ID value as a foreign key.
Then it's trivial: a simple JOIN will do what you want.

Maciej Los · Answer 2 · 2019-12-02T02:34:00

First of all, i agree with OriginalGriff.

You can create "merged table" by using Linq. See:

C#

//first
DataTable dt1= new DataTable();
dt1.Columns.Add("Name", typeof(string));
dt1.Columns.Add("Number_A", typeof(int));
dt1.Columns.Add("Comment", typeof(string));
dt1.Columns.Add("ID", typeof(int));
dt1.Rows.Add(new object[]{"John", 25800, "text", 12});
dt1.Rows.Add(new object[]{"Jack", 35801, "text", 10});
dt1.Rows.Add(new object[]{"Tom", 92412, "text", 3});
dt1.Rows.Add(new object[]{"Max", 92415, "text", 6});
dt1.Rows.Add(new object[]{"Bob", 68752, "text", 19});
//second
DataTable dt2= new DataTable();
dt2.Columns.Add("Name", typeof(string));
dt2.Columns.Add("Number_B", typeof(int));
dt2.Columns.Add("Age", typeof(int));
dt2.Columns.Add("City", typeof(string));
dt2.Rows.Add(new object[]{"Rose", 92610, 24, "London"});
dt2.Rows.Add(new object[]{"Jack", 25813, 21, "Paris"});
dt2.Rows.Add(new object[]{"Tom", 65291, 26, "Newyork"});
dt2.Rows.Add(new object[]{"John", 76241, 31, "Rome"});
dt2.Rows.Add(new object[]{"Mat", 38516, 27, "Paris"});
//result table
DataTable dt3 = new DataTable();
dt3.Columns.Add("Name", typeof(string));
dt3.Columns.Add("Number_A", typeof(int));
dt3.Columns.Add("Comment", typeof(string));
dt3.Columns.Add("ID", typeof(int));
dt3.Columns.Add("Number_B", typeof(int));
dt3.Columns.Add("Age", typeof(int));
dt3.Columns.Add("City", typeof(string));

//get common data and insert into result datatable
dt3 = (from d1 in dt1.AsEnumerable()
	join d2 in dt2.AsEnumerable() on d1.Field<string>("Name") equals d2.Field<string>("Name")
	select dt3.LoadDataRow(new object[]
	{
		d1.Field<string>("Name"),
		d1.Field<int>("Number_A"),
		d1.Field<string>("Comment"),
		d1.Field<int>("ID"),
		d2.Field<int>("Number_B"),
		d2.Field<int>("Age"),
		d2.Field<string>("City")
	}, false))
	.CopyToDataTable();

//get dt1 specific data (not in common)
var dt1spec = (from d1 in dt1.AsEnumerable()
	select new
	{
		Name = d1.Field<string>("Name"),
		Number_A = d1.Field<int>("Number_A"),
		Comment = d1.Field<string>("Comment"),
		ID = d1.Field<int>("ID")
	})
	.ToList()
	.Except(from d3 in dt3.AsEnumerable()
		select new
		{
			Name = d3.Field<string>("Name"),
			Number_A = d3.Field<int>("Number_A"),
			Comment = d3.Field<string>("Comment"),
			ID = d3.Field<int>("ID")
		})
	.ToList();

//get dt2 specific data (not in common)
var dt2spec = (from d2 in dt2.AsEnumerable()
	select new
	{
		Name = d2.Field<string>("Name"),
		Number_B = d2.Field<int>("Number_B"),
		Age = d2.Field<int>("Age"),
		City = d2.Field<string>("City")
	})
	.ToList()
	.Except(from d3 in dt3.AsEnumerable()
		select new
		{
			Name = d3.Field<string>("Name"),
			Number_B = d3.Field<int>("Number_B"),
			Age = d3.Field<int>("Age"),
			City = d3.Field<string>("City")
		})
	.ToList();
	
//add dt1spec data
foreach(var data in dt1spec)
{
	dt3.Rows.Add(new object[]{data.Name, data.Number_A, data.Comment, data.ID, null, null, null});
}
//add dt2spec data
foreach(var data in dt2spec)
{
	dt3.Rows.Add(new object[]{data.Name, null, null, null, data.Number_B, data.Age, data.City});
}

Merge two datatables based on one primary column in C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0