Remove or delete duplicate records from datatable/ dataset in asp.net

By: Suresh Dasari Feb 15, 2012

Introduction:

In this article I will explain how to remove duplicate records from datatable or dataset in asp.net.

Description:

In previous posts I explained many articles regarding Asp.net, Gridview, Ajax, JQuery and many more. During write articles on those concepts generally we bind data to datatable or dataset that data contains duplicate records of data also that would be like this

My data appears like this with duplicate records but in one situation I got requirement like to display only unique records of data to achieve this I made some code changes in code behind for that check below code

Write the following code in your aspx page

<title>Delete Duplicate Records of Data</title>

</head>

<body>

<div>

<asp:GridView ID="gvDetails" runat="server"/>

</div>

</form>

</body>

</html>

After completion of aspx page design add following namespaces in code behind

C# Code

using System;

using System.Collections;

using System.Data;

using System.Data.SqlClient;

After add namespace write the following code

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

{

SqlConnection con = new SqlConnection("Data Source=SureshDasari; Initial Catalog=MySampleDB; Integrated Security=true");

con.Open();

SqlCommand cmd = new SqlCommand("select * from SampleTable", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds,"UserDetails");

DataTable dt = ds.Tables["UserDetails"];

RemoveDuplicateRows(dt, "UserName"); // Here UserName is Column name of table

gvDetails.DataSource = ds;

gvDetails.DataBind();

}

// This method is used to delete duplicate rows of table

public DataTable RemoveDuplicateRows(DataTable dTable, string colName)

{

Hashtable hTable = new Hashtable();

ArrayList duplicateList = new ArrayList();

foreach (DataRow dtRow in dTable.Rows)

{

if (hTable.Contains(dtRow[colName]))

duplicateList.Add(dtRow);

else

hTable.Add(dtRow[colName], string.Empty);

}

foreach (DataRow dtRow in duplicateList)

dTable.Rows.Remove(dtRow);

return dTable;

}

VB.NET Code

Imports System.Collections

Imports System.Data

Imports System.Data.SqlClient

Partial Class Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

If Not IsPostBack Then

Dim con As New SqlConnection("Data Source=SureshDasari; Initial Catalog=MySampleDB; Integrated Security=true")

con.Open()

Dim cmd As New SqlCommand("select * from SampleTable", con)

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet()

da.Fill(ds, "UserDetails")

Dim dt As DataTable = ds.Tables("UserDetails")

RemoveDuplicateRows(dt, "UserName")

' Here UserName is Column name of table

gvDetails.DataSource = ds

gvDetails.DataBind()

End If

End Sub

Public Function RemoveDuplicateRows(ByVal dTable As DataTable, ByVal colName As String) As DataTable

Dim hTable As New Hashtable()

Dim duplicateList As New ArrayList()

For Each dtRow As DataRow In dTable.Rows

If hTable.Contains(dtRow(colName)) Then

duplicateList.Add(dtRow)

Else

hTable.Add(dtRow(colName), String.Empty)

End If

dTable.Rows.Remove(dtRow)

End Function

End Class

Now run in your application and check output that would be like this

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS

Subscribe by Email

8 comments :

Anonymous said...: Hi Suresh great blog. Keep up the good work.

Manik; September 28, 2012 at 2:26 AM
Dorababu said...: How can I sum the salary and display instead of removing the row; November 27, 2012 at 10:33 PM
javeed said...: From the above example i will be having same name and i need different location and salary. i.e, if the username is same location ans salary should nto be same.. how can i do that...

please help me this is very urgent....; January 17, 2013 at 7:22 AM
Anonymous said...: Nice oe; April 4, 2013 at 11:28 PM
Anonymous said...: its a good functionality
but instead of removing duplicate rows i need to make the cell of the column conatining duplicate value blank; August 6, 2013 at 1:33 AM
alino said...: tq SO MUCH...HELPS ME A LOT; March 3, 2014 at 7:51 PM
Anonymous said...: very very help full, when ever i search in google for my query, my eyes search for you blog link in that search results, manojk832@gmail.com; April 11, 2014 at 1:32 AM
Anonymous said...: HI Suresh
Great Job ! Keep it Up; October 28, 2015 at 1:44 AM