Remove Delete Duplicate Rows/Records From DataTable

Remove/Delete Duplicate Rows/Records From Datatable. 

Here i'm describing how to remove or delete duplicate rows or duplicate 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

1DataView dView = new DataView(dtRemoveDuplicate);

2        string[] arrColumns = { "Id", "Name", "Location" };

3        dtRemoveDuplicate = dView.ToTable(true, arrColumns);

VB.NET CODE

1Dim dView As New DataView(dtRemoveDuplicate)

2Dim arrColumns As String() = {"Id", "Name", "Location"}

3dtRemoveDuplicate = 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

01protected DataTable DeleteDuplicateFromDataTable(DataTable dtDuplicate, string columnName)

02    {

03        Hashtable hashT = new Hashtable();

04        ArrayList arrDuplicate = new ArrayList();

05        foreach (DataRow row in dtDuplicate.Rows)

06        {

07            if (hashT.Contains(row[columnName]))

08                arrDuplicate.Add(row);

09            else

10                hashT.Add(row[columnName], string.Empty);

11        }

12        foreach (DataRow row in arrDuplicate)

13            dtDuplicate.Rows.Remove(row);

14 

15        return dtDuplicate;

16    }

Write this code in Page_Load event of page

C# CODE

01protected void Page_Load(object sender, EventArgs e)

02    {

03        string strConnection = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

04        SqlConnection con = new SqlConnection();

05        con.ConnectionString = strConnection;

06        SqlCommand cmd = new SqlCommand();

07        cmd.CommandType = CommandType.Text;

08        cmd.CommandText = "SELECT * FROM Details";

09        cmd.Connection = con;

10        SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);

11        DataTable dtRemoveDuplicate = new DataTable();

12        dAdapter.Fill(dtRemoveDuplicate);

13        dtRemoveDuplicate = DeleteDuplicateFromDataTable(dtRemoveDuplicate, "Id");

14        GridView1.DataSource = dtRemoveDuplicate;

15        GridView1.DataBind();

16    }

VB.NET CODE

01Protected Sub Page_Load(sender As Object, e As EventArgs)

02 Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString

03 Dim con As New SqlConnection()

04 con.ConnectionString = strConnection

05 Dim cmd As New SqlCommand()

06 cmd.CommandType = CommandType.Text

07 cmd.CommandText = "SELECT * FROM Details"

08 cmd.Connection = con

09 Dim dAdapter As New SqlDataAdapter(cmd)

10 Dim dtRemoveDuplicate As New DataTable()

11 dAdapter.Fill(dtRemoveDuplicate)

12 dtRemoveDuplicate = DeleteDuplicateFromDataTable(dtRemoveDuplicate, "Id")

13 GridView1.DataSource = dtRemoveDuplicate

14 GridView1.DataBind()

15End Sub

16Protected Function DeleteDuplicateFromDataTable(dtDuplicate As DataTable, columnName As String) As DataTable

17 Dim hashT As New Hashtable()

18 Dim arrDuplicate As New ArrayList()

19 For Each row As DataRow In dtDuplicate.Rows

20  If hashT.Contains(row(columnName)) Then

21   arrDuplicate.Add(row)

22  Else

23   hashT.Add(row(columnName), String.Empty)

24  End If

25 Next

26 For Each row As DataRow In arrDuplicate

27  dtDuplicate.Rows.Remove(row)

28 Next

29 

30 Return dtDuplicate

31End Function

And result will be as shown in the image.

All duplicate records have been removed from DataTable.

enj..............happy