Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

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


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Delete Duplicate Records of Data</title>
</head>
<body>
<form id="form1" runat="server">
<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
Next
For Each dtRow As DataRow In duplicateList
dTable.Rows.Remove(dtRow)
Next
Return dTable
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 RSS subscribe by email Subscribe by Email

8 comments :

Anonymous said...

Hi Suresh great blog. Keep up the good work.

Manik

Dorababu said...

How can I sum the salary and display instead of removing the row

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

Anonymous said...

Nice oe

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

alino said...

tq SO MUCH...HELPS ME A LOT

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

Anonymous said...

HI Suresh
Great Job ! Keep it Up

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.