How do I removeduplicate rows from a
data table based upon a two column values;
I want to pass in a Data Table and a column names and get the data table back with data rows where that columns value are unique.
I have a Data Table with Duplicate Rows i want to delete the duplicates rows based upon two column values if both columns values are there then Delete the row.
Any Idea?
Best reagrds
life is name of learning!
Mark as an answer if it helps
I Know the above line for getting the Distinct Records but above table only contains those columns which iam passing like in above statment Value1 and Value2 but i have to need complete row columns values is it possible?
Best Regards
life is name of learning!
Mark as an answer if it helps
In the end you have to loop within the rows,either using for loops and temp tables or arraylists(the link I posted earlier contains all the ways mentioned here by the members) or use Linq(it will loop within the rows too)
I'm confused,you don't want to loop within the rows using for loop or Linq,there are no other solutions left,there is no simple solution for this to get it done,you are asking for a car without wheels, though you know that cars need wheels to move but you
don't want them.
Best Regards,
Ala'a Alnajjar
----------------------------------------------------
My Webblog
Rameezwaheed
Contributor
3730 Points
1595 Posts
How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 05:51 AM|LINK
HI all,
How do I remove duplicate rows from a data table based upon a two column values;
I want to pass in a Data Table and a column names and get the data table back with data rows where that columns value are unique.
I have a Data Table with Duplicate Rows i want to delete the duplicates rows based upon two column values if both columns values are there then Delete the row.
Any Idea?
Best reagrds
Mark as an answer if it helps
alaa9jo
Star
11375 Points
2036 Posts
Re: How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 07:37 AM|LINK
Take a look at this link:
http://geekswithblogs.net/ajohns/archive/2004/06/24/7191.aspx
Ala'a Alnajjar
----------------------------------------------------
My Webblog
karthicks
All-Star
31382 Points
5424 Posts
Re: How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 07:51 AM|LINK
hi, you can use below code
DataTable dataTable = new DataTable(); dataTable.Columns.Add("Value1"); dataTable.Columns.Add("Value2"); dataTable.Rows.Add(new object[] { "1", "2" }); dataTable.Rows.Add(new object[] { "2", "2" }); dataTable.Rows.Add(new object[] { "3", "2" }); dataTable.Rows.Add(new object[] { "3", "2" }); dataTable.Rows.Add(new object[] { "5", "2" }); dataTable.Rows.Add(new object[] { "4", "2" }); dataTable.Rows.Add(new object[] { "1", "2" }); DataTable dtDistinct = dataTable.DefaultView.ToTable(true, new string[] { "Value1", "Value2" });(Or)
private DataTable RemoveDuplicates(DataTable tbl, String[] keyColumns)
{
int rowNdx = 0;
try
{
while (rowNdx < tbl.Rows.Count - 1)
{
DataRow[] dups = FindDups(tbl, rowNdx, keyColumns);
if (dups.Length > 0)
{
foreach (DataRow dup in dups)
{
tbl.Rows.Remove(dup);
}
}
else
{
rowNdx++;
}
}
}
catch (Exception ex)
{
}
return tbl;
}
private DataRow[] FindDups(DataTable tbl, int sourceNdx, String[] keyColumns)
{
ArrayList retVal = new ArrayList();
try
{
DataRow sourceRow = tbl.Rows[sourceNdx];
for (int i = sourceNdx + 1; i < tbl.Rows.Count; i++)
{
DataRow targetRow = tbl.Rows[i];
if (IsDup(sourceRow, targetRow, keyColumns))
{
if (retVal.Count.Equals(0))
retVal.Add(sourceRow);
retVal.Add(targetRow);
}
}
}
catch (Exception ex)
{
}
return (DataRow[])retVal.ToArray(typeof(DataRow));
}
private bool IsDup(DataRow sourceRow, DataRow targetRow, String[] keyColumns)
{
bool retVal = true;
try
{
foreach (String column in keyColumns)
{
if (sourceRow.Table.Columns.Contains(column))
{
retVal = retVal && sourceRow[column].Equals(targetRow[column]);
if (!retVal) break;
}
}
}
catch (Exception ex)
{
}
return retVal;
}
hi,
you need to give all the required column names in the string array
DataTable dtDistinct = dataTable.DefaultView.ToTable(true, new string[]
{ "Value1", "Value2","Column1","Column2" });
else you can use second the way
Karthick S
Rameezwaheed
Contributor
3730 Points
1595 Posts
Re: How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 08:24 AM|LINK
Thanks for reply,
I Know the above line for getting the Distinct Records but above table only contains those columns which iam passing like in above statment Value1 and Value2 but i have to need complete row columns values is it possible?
Best Regards
Mark as an answer if it helps
fayaz_3e
Star
9332 Points
1744 Posts
Re: How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 08:42 AM|LINK
If your framework is 3.0 or higher, then apply LINQ
DataTable dtb2 = oldDataTable.AsEnumerable().Distinct().CopyToDataTable();
Rameezwaheed
Contributor
3730 Points
1595 Posts
Re: How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 08:46 AM|LINK
Yes i am using Framwork 3.5 VS 2008 but i dont have to to use LINQ Is there any alternate way to get the full distinct row columns??
Best Regards
Rameez
Mark as an answer if it helps
fayaz_3e
Star
9332 Points
1744 Posts
Re: How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 08:56 AM|LINK
Dont know why not LINQ. But think this should work. You have overloaded method of ToTable, accepting distinct boolean flag and column names. Try this
oldDataTable = oldDataTable.DefaultView.ToTable(true, "column1", "column2", "column3", "column-n");
alaa9jo
Star
11375 Points
2036 Posts
Re: How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 09:17 AM|LINK
Rameez,
In the end you have to loop within the rows,either using for loops and temp tables or arraylists(the link I posted earlier contains all the ways mentioned here by the members) or use Linq(it will loop within the rows too)
I'm confused,you don't want to loop within the rows using for loop or Linq,there are no other solutions left,there is no simple solution for this to get it done,you are asking for a car without wheels, though you know that cars need wheels to move but you don't want them.
Ala'a Alnajjar
----------------------------------------------------
My Webblog
cnranasinghe
Star
8885 Points
1798 Posts
Re: How to delete the Duplicate Rows from Data table ??
Mar 04, 2010 11:20 AM|LINK
You can achieve this by SQL Server.
http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx