CODEWITHSUNDEEP
c#datatablerow
ng80092b
ng80092b

Reputation: 667

Method that compares and removes all similar rows in a datatable

I'm trying to create a method that, given a table, will remove the similar rows. What i thought of doing was create a double foreachcycle, so that every single row in the table, will be compared to all others. 

private void comparaeapagarowsiguais(DataTable table1)
    {
        foreach (DataRow row1 in table1.Rows)
        {
            foreach (DataRow row2 in table1.Rows)
            {
                var array1 = row1.ItemArray;
                var array2 = row2.ItemArray;

                if (array1.SequenceEqual(array2))
                {
                    table1.Rows.Remove(row2);
                }

            }
        }


    }

The problem is that at some point, any given row will compare to itself, and, thus, will try to remove itself (and I would end with no rows at all). But I wanna keep at least one of every different row.

How can I cycle trough them, while avoiding any given row to be compared with herself?

EDIT: Partial solution

I've come up with a solution that works in my case. This will work if the similar rows are all immediatly next to eachother. (If you have similar rows spread around the table it won't work)

 private void comparalinhaseapaga(DataTable table1)
    {   //I will explain the try in the end
        try
        {
            //you will run i for as long as you like
            for (int i = 0; ; )
            {
                //you create 2 arrays from i, and i+1, this means you will compare
                //the first 2 lines of the data table
                var array1 = table1.Rows[i].ItemArray;
                var array2 = table1.Rows[i + 1].ItemArray;

                 //if they are similar, it removes row at 1, and will go back to the cycle and 
                 //proceed to compare row at 0 with the previously row at 2
                if (array1.SequenceEqual(array2))
                {
                    table1.Rows.RemoveAt(i + 1);
                }
                else
                {
                    //if they are not equal, it move next to row at 1, and compare it with row at 2
                    //once it gets here, the row 0 and row 1 are already different
                    //that's why it only works when the similar rows are adjacent to another
                    i++;
                }
            }
        }
        catch { }
    }

The try and catch are because at some point he won't have a row at position i+1 to compare, and will produce an error. Using a try/catch will skip the error and proceed, which you can do since all the similar rows are already deleted. Tested and worked ;)

Hope this is useful for someone.

Edit 2: Found a clean solution, just take this code:

 private DataTable RemoveDuplicatesRecords(DataTable dt)
    {
        //Returns just unique rows
        var UniqueRows = dt.AsEnumerable().Distinct(DataRowComparer.Default);
        DataTable dt2 = UniqueRows.CopyToDataTable();
        return dt2;
    }

Upvotes: 1

Views: 349

Answers (3)

McShep
McShep

Reputation: 491

I think this can be simplified to a group by method, this can only be achieved by using linq. Then simply select the first item of each group

this will turn your datatable into an enumerable then allow you to group by the values you dont wont duplicate 

public IEnumerable<DataRow> test(DataTable myTable)
    {
        var results = myTable.AsEnumerable()
            .GroupBy(datarow => datarow .ItemArray[1]).Select(y=> y.First()) ;

        return results;
    }

or if its a match on all fields

public DataTable test(DataTable myTable)
    {
        var results = myTable.AsEnumerable().Distinct().CopyToDataTable() ;

        return results;
    }

Upvotes: 2

oniramarf
oniramarf

Reputation: 903

You could modify your method adding the following check

private void comparaeapagarowsiguais(DataTable table1)
{
    foreach (DataRow row1 in table1.Rows)
    {
        foreach (DataRow row2 in table1.Rows)
        {
            if (row1 != row2) 
            {
                var array1 = row1.ItemArray;
                var array2 = row2.ItemArray;

                if (array1.SequenceEqual(array2))
                {
                    table1.Rows.Remove(row2);
                }
            }
        }
    }
}

In this way, they should not be compared if is the starting row ;)

Or, you could use LINQ (like I wrote in comment) using this code to select only similar rows and then delete them:

private void comparaeapagarowsiguais(DataTable table1)
{
    foreach (DataRow row1 in table1.Rows)
    {
        var toDelete = table1.AsEnumerable().Where(row => row.ItemArray.SequenceEqual(row1.ItemArray));

        foreach (DataRow r in toDelete)
        {
            table1.Rows.Remove(r);
        }
    }
}

Upvotes: 1

Burak Karasoy
Burak Karasoy

Reputation: 1690

private void comparaeapagarowsiguais(DataTable table1)
    { int a=0; int b=0;
        foreach (DataRow row1 in table1.Rows)
        {  a++; 
           b=0;
            foreach (DataRow row2 in table1.Rows)
            {  b++;
                if(a!=b)
                 {
                var array1 = row1.ItemArray;
                var array2 = row2.ItemArray;

                if (array1.SequenceEqual(array2))
                {
                    table1.Rows.Remove(row2);
                }
               } 

            }
        }


    }

Upvotes: 1

Related Questions