Delete Duplicate Rows from DataTable

October 29, 2012

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

Remove Duplicate Rows Records from datatable

My table in database contains duplicate records as shown in image and I want to remove those duplicate records temporarily before 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 parameterand returns DataTable after removing duplicate records.

 

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();
    }
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;
    }
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
Delete Duplicate Records from datatable
And result will be as shown in the image. All duplicate records have been removed from DataTable.

Happy Coding !!

Leave a Reply

You must be logged in to post a comment.