how to remove duplicate records from a datatable

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

0	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

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

0	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

0	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

Latest Posts