Delete Duplicate Rows from Database using c# code

Delete Duplicate Rows from DataTable

October 29, 2012

Tags: DataTabe Modifications, Remove Duplicate Rows

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 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

And result will be as shown in the image. All duplicate records have been removed from DataTable.

Happy Coding !!