Remove Delete Duplicate Rows/Records From DataTable Asp.Net

This post explains how to Remove/Delete Duplicate Rows Records From Datatable using C# or VB.NET in Asp.Net.

My table in database contains duplicate records as shown in image and i want to remove those duplicate records temporaryly efore displaying them in gridview.

For this purpose i'm filling records in DataTable and deleting duplicates.

I have placed one gridview on the page and populating it from DataTable in Page_Load event in code behind.

There are two ways to remove duplicates from datatable.



Method 1:

In this method we create a DataView on DataTable and pass the column names to check for duplicates as array in ToTable method of DataView.

C# CODE

DataView dView = new DataView(dtRemoveDuplicate);
        string[] arrColumns = { "Id", "Name", "Location" };
        dtRemoveDuplicate = dView.ToTable(true, arrColumns);

VB.NET CODE

Dim dView As New DataView(dtRemoveDuplicate)
Dim arrColumns As String() = {"Id", "Name", "Location"}
dtRemoveDuplicate = dView.ToTable(True, arrColumns)

Method 2 :

Create a method which takes datatable and column name to look for duplicates as parameter and returns DataTable after removing duplicate records.

C# CODE

protected DataTable DeleteDuplicateFromDataTable(DataTable dtDuplicate, string columnName)
    {
        Hashtable hashT = new Hashtable();
        ArrayList arrDuplicate = new ArrayList();
        foreach (DataRow row in dtDuplicate.Rows)
        {
            if (hashT.Contains(row[columnName]))
                arrDuplicate.Add(row);
            else
                hashT.Add(row[columnName], string.Empty);
        }
        foreach (DataRow row in arrDuplicate)
            dtDuplicate.Rows.Remove(row);

        return dtDuplicate;
    }

Write this code in Page_Load event of page

C# CODE

protected void Page_Load(object sender, EventArgs e)
    {
        string strConnection = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        SqlConnection con = new SqlConnection();
        con.ConnectionString = strConnection;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT * FROM Details";
        cmd.Connection = con;
        SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
        DataTable dtRemoveDuplicate = new DataTable();
        dAdapter.Fill(dtRemoveDuplicate);
        dtRemoveDuplicate = DeleteDuplicateFromDataTable(dtRemoveDuplicate, "Id");
        GridView1.DataSource = dtRemoveDuplicate;
        GridView1.DataBind();
    }

VB.NET CODE

Protected Sub Page_Load(sender As Object, e As EventArgs)
 Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
 Dim con As New SqlConnection()
 con.ConnectionString = strConnection
 Dim cmd As New SqlCommand()
 cmd.CommandType = CommandType.Text
 cmd.CommandText = "SELECT * FROM Details"
 cmd.Connection = con
 Dim dAdapter As New SqlDataAdapter(cmd)
 Dim dtRemoveDuplicate As New DataTable()
 dAdapter.Fill(dtRemoveDuplicate)
 dtRemoveDuplicate = DeleteDuplicateFromDataTable(dtRemoveDuplicate, "Id")
 GridView1.DataSource = dtRemoveDuplicate
 GridView1.DataBind()
End Sub
Protected Function DeleteDuplicateFromDataTable(dtDuplicate As DataTable, columnName As String) As DataTable
 Dim hashT As New Hashtable()
 Dim arrDuplicate As New ArrayList()
 For Each row As DataRow In dtDuplicate.Rows
  If hashT.Contains(row(columnName)) Then
   arrDuplicate.Add(row)
  Else
   hashT.Add(row(columnName), String.Empty)
  End If
 Next
 For Each row As DataRow In arrDuplicate
  dtDuplicate.Rows.Remove(row)
 Next

 Return dtDuplicate
End Function

And result will be as shown in the image.

All duplicate records have been removed from DataTable.

Download Sample Code

Tweet

If you like this post than join us or share