Removal of Duplicate Rows from Data table Based on Multiple columns

Question

0.00/5 (No votes)

See more:

I have data table which contains many duplicate rows i need to filter those rows from data table based upon multiple columns to get distinct rows in resultant data table....

Barcode Itemid PacktypeId

1      100      1

1      100      2

1      100      3

1      100      1

1      100      3

need only rows which contains packtypeid 1,2,3 remaining 4th and 5th row should be removed

I have tried using two methods but none didn't turns for better result

Data table contains more than 10 columns but unique column's is "Barcode", "ItemID", "PackTypeID"

Method-1:

C#

dt_Barcode = dt_Barcode.DefaultView.ToTable(true, "Barcode", "ItemID", "PackTypeID");

The above method filter's the rows but it returns columns only 3 column values i need entire 10 column values.

Method-2:

C#

List<string> keyColumns = new List<string>();
keyColumns.Add("Barcode");
keyColumns.Add("ItemID");
keyColumns.Add("PackTypeID");   
RemoveDuplicates(DataTable table, List<string> keyColumns)
{
	var uniqueness = new HashSet<string>();
	StringBuilder sb = new StringBuilder();
	int rowIndex = 0;
	DataRow row;
	DataRowCollection rows = table.Rows;             
	int i = rows.Count;
	while (rowIndex < i)
	{
	row = rows[rowIndex];
	sb.Length = 0;
	foreach (string colname in keyColumns)
	{
		sb.Append(row[colname]);
		sb.Append("|");
	}
	
	if (uniqueness.Contains(sb.ToString()))
	{
		rows.Remove(row);
	}
	else
	{
		uniqueness.Add(sb.ToString());
		rowIndex++;
	}
}

The Above Method returns exception like there is no rows at position 5

Posted 10-Nov-14 21:33pm

pradeepbliss

Updated 4-Aug-20 23:10pm

DamithSL

v2

Add a Solution

4 solutions

Solution 4

public void RemoveDuplicatesFromDataTable(ref DataTable table, List<string> keyColumns)
        {

            Dictionary<string, string> uniquenessDict = new Dictionary<string, string>(table.Rows.Count);
            StringBuilder stringBuilder = null;
            int rowIndex = 0;
            DataRow row;
            DataRowCollection rows = table.Rows;
            string error = string.Empty;

            try
            {
                while (rowIndex < rows.Count)
                {

                    row = rows[rowIndex];

                    stringBuilder = new StringBuilder();

                    foreach (string colname in keyColumns)
                    {
                        try
                        {
                            if (row[colname].ToString() != string.Empty)
                            {
                                stringBuilder.Append(((string)row[colname]));
                            }
                            else
                            {
                                //If it comes here, means one of the keys are blank
                                error += "One of the key values is blank.";
                            }
                        }
                        catch (Exception ss)
                        {
                            error += "Error " + ss.Message + ".";
                        }
                    }

                    if (uniquenessDict.ContainsKey(stringBuilder.ToString()))
                    {
                        rows.Remove(row);
                    }
                    else
                    {
                        uniquenessDict.Add(stringBuilder.ToString().Replace(",", ""), string.Empty);
                        rowIndex++;
                    }

                }
            }
            catch (Exception ex)
            {
                error = "Failed - " + ex.Message;
            }

            if(error != string.Empty)
                Show(error);
        }

Posted 4-Aug-20 23:10pm

subho100

Comments

Richard Deeming 5-Aug-20 7:02am

An unexplained code-dump is not a "solution" to this already-solved question.

You need to explain why you think your code is better than the existing accepted solution. (Hint: It's not.)

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)

Rajesh Buddaraju · Accepted Answer · 2014-11-10T23:46:00

Example code for your problem.
List<items> arritems = new List<items>();
items item = new items();
items item1 = new items();
items item2 = new items();
items item3 = new items();
items item4 = new items();
item.barcode = 1;
item.itemid = 100;
item.packtypeid = 1;

item1.barcode = 1;
item1.itemid = 100;
item1.packtypeid = 2;

item2.barcode = 1;
item2.itemid = 100;
item2.packtypeid = 3;

item3.barcode = 1;
item3.itemid = 100;
item3.packtypeid = 1;

item4.barcode = 1;
item4.itemid = 100;
item4.packtypeid = 3;

arritems.Add(item);
arritems.Add(item1);
arritems.Add(item2);
arritems.Add(item3);
arritems.Add(item4);

var distinctList = arritems.Select(x => new{x.itemid , x.packtypeid}).Distinct().ToList();

C#

public class items
    {
        public int barcode;
        public int itemid;
        public int packtypeid;
    }

DamithSL · Accepted Answer · 2014-11-10T21:47:00

Solution 2

try with LINQ

C#

dt_Barcode = dt_Barcode.AsEnumerable()
            .GroupBy(r => new { Itemid = r.Field<int>("Itemid"), PacktypeId = r.Field<int>("PacktypeId")})
            .Select(g => g.First())
            .CopyToDataTable();

Sample Test Code:

C#

void Main()
{
    DataTable dt_Barcode =GetTable();
	dt_Barcode = dt_Barcode.AsEnumerable()
            .GroupBy(r => new { Itemid = r.Field<int>("Itemid"), PacktypeId = r.Field<int>("PacktypeId")})
            .Select(g => g.First())
            .CopyToDataTable();
}

DataTable GetTable()
{
	DataTable table = new DataTable();
	table.Columns.Add("Barcode", typeof(int));
	table.Columns.Add("Itemid", typeof(int));
	table.Columns.Add("PacktypeId", typeof(int));
	table.Rows.Add(1,100,1);
	table.Rows.Add(1,100,2);
	table.Rows.Add(1,100,3);
	table.Rows.Add(1,100,1);
	table.Rows.Add(1,100,3);
	return table;
}

Posted 10-Nov-14 21:47pm

DamithSL

Updated 10-Nov-14 23:35pm

v6

Comments

pradeepbliss 11-Nov-14 4:14am

Hi DamithSL partially u r correct but i need to pass both packtypeid and itemid at the same time via linq how can i accomplish both in where clause...

DamithSL 11-Nov-14 4:48am

check my updated answer

pradeepbliss 11-Nov-14 4:57am

hi i am getting incorrect syntax near , (invalid expression term ,)

DamithSL 11-Nov-14 5:13am

Oh sorry, new keyword need to be used

pradeepbliss 11-Nov-14 5:20am

hi i am sorry i am new to linq i am getting syntax error->invalid anonymous type member declarator........

DamithSL 11-Nov-14 5:28am

I think you take the intermediate version of my update, check the latest code.

pradeepbliss 11-Nov-14 5:33am

checked with latest code but getting runtime exception->Specified cast is not valid.but works for single parameter....

DamithSL 11-Nov-14 5:37am

do you have null values in these columns or do you have any text or characters with any of these column values?
I have used r.Field<int> by assuming you have integer data in your columns, if you have text fields you better change it to r.Field<string>

pradeepbliss 11-Nov-14 6:08am

wow superb it's works great...Great job man

Manas Bhardwaj 11-Nov-14 4:50am

+5!

DamithSL 11-Nov-14 5:16am

thank you, Manas

Manas Bhardwaj · Accepted Answer · 2014-11-10T21:38:00

This has been discussed so many times on other forums and CodeProject itself.

Look at the links below:

http://stackoverflow.com/questions/1199176/how-to-select-distinct-rows-in-a-datatable-and-store-into-an-array[^]

http://stackoverflow.com/questions/17221561/datatable-distinct-rows[^]

http://stackoverflow.com/questions/12723289/linq-datatable-select-distinct-rows[^]

Select DISTINCT records based on specified fields for DataTable [^]