how to remove duplicate records from a datatable

Posted by Santosh4u under VB.NET on 9/15/2010 | Points: 10 | Views : 53064 | Status : [Member] | Replies : 6
Hi

can any body plz tell me how to remove duplicate records from datatable.

Ex:
Below is my Datatable.

ID Name Empid Adress DOB Department
1 Santosh c100 KL 11.04.1984 Developer
2 Ramesh c101 B'lore 12.05.1984 Developer
3 Santosh c103 KL 11.04.1984 Developer
4 Subash c104 B'lore 25.12.1985 Developer

i want to remove duplicate Records according to the (Name,Address and DOB) Column.

means if for both the records Name,Address and DOB is same then i want to remove one (no need to check other details like id,Empid and Department)
in the above datatable for ID =1 and 3 having duplicate records according to the (Name,Address and DOB) column.


so my output Datatable should be
ID Name Empid Adress DOB Department
1 Santosh c100 KL 11.04.1984 Developer
2 Ramesh c101 B'lore 12.05.1984 Developer
4 Subash c104 B'lore 25.12.1985 Developer

i am working on vb.net

Thanx in advance.......

Santosh




Responses

Posted by: Peermohamedmydeen on: 9/15/2010 [Member] Bronze | Points: 25

Up
0
Down
Hi Santhosh,

Try this,

Let us consider dtInput is your data table with duplicate records.

I have a new DataTable dtFinal in which I want to filter the duplicate rows.

So my code will be something like below.

I have given in C#, just try the same with VB.Net.


DataTable dtFinal = dtInput.DefaultView.ToTable(true, new string[3] {"Name","Address","DOB"});



I hope it will be helpful.

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Santosh4u on: 9/15/2010 [Member] Bronze | Points: 25

Up
0
Down
hi PMM

ur code is correct for removing records but not giving my output,
i want same this result with all the 6 columns.


Regards
Santosh

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Peermohamedmydeen on: 9/15/2010 [Member] Bronze | Points: 25

Up
0
Down
Hi Santhosh,

I think, Its not possible to remove duplicates directly with particular 3 columns.

If your DataTable having less number of rows, you had better go with loopings as below.


private DataTable RemoveDuplicate(DataTable dtInput)

{
DataTable dtTemp = dtInput.Clone();
foreach(DataRow drInput in dtInput.Rows)
{
if(dtTemp.Rows.Count == 0 )
{
DataRow dr = dtTemp.NewRow();
dr["ID"] = drInput["ID"].ToString();
dr["Name"] = drInput["Name"].ToString();
dr["EMPID"] = drInput["EMPID"].ToString();
dr["Address"] = drInput["Address"].ToString();
dr["DOB"] = drInput["DOB"].ToString();
dr["Dept"] = drInput["Dept"].ToString();
dtTemp.Rows.Add(dr);
}
else
{
bool xIsExist = false;
for(int idxT=0; idxT < dtTemp.Rows.Count;idxT++)
{
if(drInput["Name"].ToString().Equals(dtTemp.Rows[idxT]["Name"].ToString()) && drInput["Address"].ToString().Equals(dtTemp.Rows[idxT]["Address"].ToString()) &&
drInput["DOB"].ToString().Equals(dtTemp.Rows[idxT]["DOB"].ToString()))
{
xIsExist = true;
}
}

if(!xIsExist)
{
DataRow dr = dtTemp.NewRow();
dr["ID"] = drInput["ID"].ToString();
dr["Name"] = drInput["Name"].ToString();
dr["EMPID"] = drInput["EMPID"].ToString();
dr["Address"] = drInput["Address"].ToString();
dr["DOB"] = drInput["DOB"].ToString();
dr["Dept"] = drInput["Dept"].ToString();
dtTemp.Rows.Add(dr);
}
}
}

return dtTemp;

}




Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 9/15/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Santhosh,

http://www.dotnetspider.com/resources/4535-Remove-duplicate-records-from-table.aspx // using C#

This Code is Converted from C# to VB from above Link.

Protected Sub Button1_Click(sender As Object, e As EventArgs)

Dim strConn As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
Dim conn As New SqlConnection(strConn)

Dim da As New SqlDataAdapter("select * from emp", conn)
Dim ds As New DataSet()
da.Fill(ds, "Emp")

' Filling a employee table
Dim dt As DataTable = ds.Tables("Emp")
dt = RemoveDuplicateRows(dt, "empname")

GridView1.DataSource = ds.Tables("Emp").DefaultView
GridView1.DataBind()
End Sub

Public Function RemoveDuplicateRows(dTable As DataTable, colName As String) As DataTable
Dim hTable As New Hashtable()
Dim duplicateList As New ArrayList()

'Add list of all the unique item value to hashtable, which stores combination of key, value pair.
'And add duplicate item value in arraylist.
For Each drow__1 As DataRow In dTable.Rows
If hTable.Contains(drow__1(colName)) Then
duplicateList.Add(drow__1)
Else
hTable.Add(drow__1(colName), String.Empty)
End If
Next

'Removing a list of duplicate items from datatable.
For Each dRow__2 As DataRow In duplicateList
dTable.Rows.Remove(dRow__2)
Next

'Datatable which contains unique records will be return as output.
Return dTable
End Function


Refer this code will helpful to you.

Cheers :)



Thanks,
T.Saravanan

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 4/3/2012 [Member] Starter | Points: 25

Up
0
Down
http://sandeep-chourasia.blogspot.in/2011/12/delete-duplicate-data.html

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saclines on: 9/20/2013 [Member] Starter | Points: 25

Up
0
Down
Here is a simple method explained using Linq.

http://www.dotnetlines.com/Blogs/tabid/85/EntryId/49/Remove-duplicate-rows-from-a-DataTable-using-LINQ.aspx

Sachin N. S.

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response