Click here to Skip to main content
14,580,516 members
Rate this:
Please Sign up or sign in to vote.
I have two datatables(i don't have any database table, just simple datatable) as shown in screenshot (datatable1 and datatable2).

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
Updated 2-Dec-19 2:34am
v3
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

First of all, i agree with OriginalGriff.

You can create "merged table" by using Linq. See:
//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});
}
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100