Delete (Remove) duplicate rows from DataTable in C# and VB.Net

mahjoubi
 
on Dec 01, 2020 09:45 PM
3118 Views

how to remove duplicate value in asp.net vb.net

If you see in my code there is a test when it reach I want in that place begin to see if there is a duplicate value like this

a   1     10

b   2     20

c   3      30

     3      0

     4      0

I want if there no value in first column begin test from second column if duplicate should delete the row that has no value in 1st column

Dim conter As Integer = tab.Rows.Count - 1
Dim conter2 As Integer = 0
Dim ref As String
For i = 0 To conter
    If tab.Rows(i).Item(0) = Nothing And tab.Rows(i).Item(4) = Nothing And tab.Rows(i).Item(5) = Nothing And tab.Rows(i).Item(6) = Nothing And tab.Rows(i).Item(7) = Nothing And tab.Rows(i).Item(8) = Nothing Then
        ref = tab.Rows(i).Item(2)
        For j = 0 To conter
            If ref = tab.Rows(j).Item(2) Then
                tab.Rows(i).Delete()
        Exit For
    End If
Next

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Dec 02, 2020 10:30 PM

Hi mahjoubi,

Refer below code.

C#

DataTable dt = new DataTable();
dt.Columns.Add("Item");
dt.Columns.Add("Qty");
dt.Columns.Add("Price");
dt.Rows.Add("a", 1, 10);
dt.Rows.Add("b", 2, 20);
dt.Rows.Add("c", 3, 30);
dt.Rows.Add("", 3, 0);
dt.Rows.Add("", 4, 0);
dt.Rows.Add("a", 4, 0);

List<string> duplicates = (from dr in dt.AsEnumerable()
                            group dr by dr["Qty"] into groups
                            where groups.Count() > 1
                            select groups.Key.ToString()).ToList();

for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
    if (string.IsNullOrEmpty(dt.Rows[i][0].ToString()))
    {
        if (duplicates.Contains(dt.Rows[i][1].ToString()))
        {
            dt.Rows[i].Delete();
        }
    }
}

VB.Net

Dim dt As DataTable = New DataTable()
dt.Columns.Add("Item")
dt.Columns.Add("Qty")
dt.Columns.Add("Price")
dt.Rows.Add("a", 1, 10)
dt.Rows.Add("b", 2, 20)
dt.Rows.Add("c", 3, 30)
dt.Rows.Add("", 3, 0)
dt.Rows.Add("", 4, 0)
dt.Rows.Add("a", 4, 0)

Dim duplicates = (From dr In dt
                    Group dr By Qty = dr("Qty") Into Group, Count
                    Where Count > 1
                    Select New With {Key Qty}.Qty).ToList()

For i As Integer = dt.Rows.Count - 1 To 0 Step -1
    If String.IsNullOrEmpty(dt.Rows(i)(0).ToString()) Then
        If duplicates.Contains(dt.Rows(i)(1).ToString()) Then
            dt.Rows(i).Delete()
        End If
    End If
Next

Output

ItemQtyPrice
a 1 10
b 2 20
c 3 30
a 4 0