How to compare 2 data tables and return duplicate values in C#

Question

0.00/5 (No votes)

See more:

I have a requirement in which i have two datatables, both with different sets of columns, rows are dynamic. I want to compare the values in 5th column of one datatable with the 4th column on the 2nd Datatable and return a new data table on which only the matched values are present.

The data tables are system.Data.Datatable

I am a complete beginner and your help will be great for me.

Data Table 1 looks like below

xxyy    2   Enjoy 2000 MB                      xxyy 88035
yyxx    2   Enjoy 250 Minutes                  yyxx 88039
yyxx    2   Get 1 GB Data, valid for 3 day.    yyxx 88549
zzyy    2   10 GB Data   valid for 30 days.    zzyy 88535

Data table 2 like below

2   Enjoy 2000 MB                      xxyy 88035 john
2   Enjoy 250 Minutes                  yyxx 88039 george
2   Get 1 GB Data, valid for 3 day.    yyxx 88612 anil
2   10 GB Data   valid for 30 days.    zzyy 88992 peter

Here, i am trying to compare the values on column 5 of data table1 and column 4 of data table 2, the result should be like below

xxyy    2   Enjoy 2000 MB        xxyy   88035
yyxx    2   Enjoy 250 Minutes    yyxx   88039

i.e if values are common, return datatable1 with removing the unique values.

What I have tried:

I'm a complete beginner and have no idea where to start

Posted 17-Jun-18 10:29am

aswin kumar

Updated 18-Jun-18 4:10am

Maciej Los

v3

Add a Solution

3 solutions

Solution 1

Use an "IN" sub-query:

IN (Transact-SQL) | Microsoft Docs[^]

Posted 17-Jun-18 10:37am

Gerry Schmitz

Comments

aswin kumar 18-Jun-18 1:31am

Hi Gerry,
Sorry, i forgot to mention that i was looking at System.Data.Datatable, not a DB table.

[no name] 18-Jun-18 12:05pm

Then use LINQ.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/creating-a-datatable-from-a-query-linq-to-dataset

Solution 3

C#

//clone structure of dt1 into new table to store the result
DataTable dt3 = dt1.Clone();
//find index numbers in dt1 not present in dt2
var idsExceptDt2 = dt1.AsEnumerable().Select(r => (long)r.ItemArray[4])
    .Except(dt2.AsEnumerable().Select(r => (long)r.ItemArray[3])).ToArray();

foreach (DataRow r in dt1.Rows)
  {
    //does the row index number match any of the non-duplicate index numbers?
    bool isMatched = idsExceptDt2.Any(id => (long)r.ItemArray[4] == id);
    if (!isMatched)
       {
         dt3.Rows.Add(r.ItemArray);
       }
  }

Posted 18-Jun-18 4:11am

George Swan

Updated 18-Jun-18 4:15am

v2

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)

Maciej Los · Accepted Answer · 2018-06-17T21:38:00

Solution 2

If you would like to get matched records, use join clause (C# Reference) | Microsoft Docs[^]

For example:

C#

DataTable dt1 = new DataTable();
dt1.Columns.AddRange(new DataColumn[]
	{
		new DataColumn(),
		new DataColumn(),
		new DataColumn(),
		new DataColumn(),
		new DataColumn()
	});
dt1.Rows.Add(new object[]{"xxyy", 2, "Enjoy 2000 MB", "xxyy", 88035});
dt1.Rows.Add(new object[]{"yyxx", 2, "Enjoy 250 Minutes", "yyxx", 88039});
dt1.Rows.Add(new object[]{"yyxx", 2, "Get 1 GB Data, valid for 3 day.", "yyxx", 88549});
dt1.Rows.Add(new object[]{"zzyy", 2, "10 GB Data   valid for 30 days.", "zzyy", 88535});

DataTable dt2 = new DataTable();
dt2.Columns.AddRange(new DataColumn[]
	{
		new DataColumn(),
		new DataColumn(),
		new DataColumn(),
		new DataColumn(),
		new DataColumn()
	});
dt2.Rows.Add(new object[]{2, "Enjoy 2000 MB", "xxyy", 88035, "john"});
dt2.Rows.Add(new object[]{2, "Enjoy 250 Minutes", "yyxx", 88039, "george"});
dt2.Rows.Add(new object[]{2, "Get 1 GB Data, valid for 3 day.", "yyxx", 88612, "anil"});
dt2.Rows.Add(new object[]{2, "10 GB Data   valid for 30 days.", "zzyy", 88992, "peter"});

var result = from d1 in dt1.AsEnumerable()
	join d2 in dt2.AsEnumerable() on new {A = d1[1], B = d1[4]} equals new {A = d2[0], B = d2[3]}
	select d1;
	
foreach (DataRow dr in result)
{
	Console.WriteLine("{0} {1} {2} {3} {4}", dr[0], dr[1], dr[2], dr[3], dr[4]);
}

Good luck!

Posted 17-Jun-18 21:38pm

Maciej Los

Updated 17-Jun-18 23:59pm

v2

Comments

aswin kumar 18-Jun-18 4:17am

Dear Maciej,
Thanks for the help, but both these data tables doesnot have any header, can we do it based on index?

Maciej Los 18-Jun-18 4:34am

Yes, you can. Use d1[index] instead of d1.Field<string>("ColumnName1"), but i'm pretty sure that there's a list of columns with their names.

aswin kumar 18-Jun-18 5:27am

Thanks Iosmac,
when i tried with index, i am getting an error as below
"Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access."

i used the below code

public DataTable CheckTable(DataTable dt1, DataTable dt2)
{
var result = from d1 in dt1.AsEnumerable()
join d2 in dt2.AsEnumerable() on new {d1[6]} equals new {d2[2]}
select d1;

return dt1;
}

Maciej Los 18-Jun-18 5:43am

When you use only one field, you don't need a [new] statement: Hide Copy Code

d1[0] equals d2[2]

[EDIT]
See updated answer.

aswin kumar 18-Jun-18 9:15am

hi isomac,

the script got validated, but when i try to return datatable1 after the joining , its giving me the same previous Datatable1 only, not the joined one.

public DataTable CheckTable(DataTable dt1, DataTable dt2)
{
var result = from d1 in dt1.AsEnumerable()
join d2 in dt2.AsEnumerable() on d1[1].ToString() equals d2[6].ToString()
select d1;

return dt1;
}

Maciej Los 18-Jun-18 9:40am

You didn't mention you want to return common data for dt1 and dt2. Look at your "output" too... Doesn't it looks like dt1 data?

aswin kumar 18-Jun-18 10:06am

yes iosmac, i found out the mistake and used the below code to output data.

return result.Any()?result.CopyToDataTable():new DataTable();

and it worked, but if there are duplicate values, its also coming on the output, anyway i can filter it out, ?

sorry for the annoying questions, im in the learning phase only . please help me here.

Maciej Los 19-Jun-18 2:07am

If you would like to remove "duplicates", you can use:

var result = yourActuallQuery.Distinct()