You must Sign In to post a response.
  • Category: .NET

    How to find the duplicate records in datatable?

    How to find the duplicate records in datatable with multiple columns of data.
  • #767581
    var filterGroup = from d in sourceDt.AsEnumerable()
    group d by new { FirstName = d["FirstName"], LastName = d["LastName"], Location =d["Location"] } into tempGrp
    select new
    {
    FirstName = tempGrp.Key.FirstName,
    LastName = tempGrp.Key.LastName,
    Location = tempGrp.Key.Location,
    NameCount = tempGrp.Count(),

    };

    foreach(var item in filterGroup)
    {
    DataRow dr = destDt.NewRow();
    dr["FirstName"] = item.FirstName;
    dr["LastName"] = item.LastName;
    dr["Location"] = item.Location;
    dr["Count"] = item.NameCount;
    destDt.Rows.Add(dr);
    }

  • #767594
    Simple method is to use 'Contains' method of each row and check for another table row and see if it duplicate
    see below snippet

    public DataTable RemoveDuplicateRows(DataTable dTable, string colName)
    {
    Hashtable hTable = new Hashtable();
    ArrayList duplicateList = 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.
    foreach (DataRow drow in dTable.Rows)
    {
    if (hTable.Contains(drow[colName]))
    duplicateList.Add(drow);
    else
    hTable.Add(drow[colName], string.Empty);
    }

    //Removing a list of duplicate items from datatable.
    foreach (DataRow dRow in duplicateList)
    dTable.Rows.Remove(dRow);

    //Datatable which contains unique records will be return as output.
    return dTable;
    }

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767605
    Hi,

    The best and simple and easiest way to remove duplicates is using DefaultView parenthesis.


    Refer below sample code


    DataTable dtDistinct = dt.DefaultView.ToTable(true, "col1", "col2", "col3");


    Hope this helps you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #767607
    Hi
    you can try this code



    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id"), new DataColumn("Name"), new DataColumn("Country") });
    dt.Rows.Add(1, "John", "United States");
    dt.Rows.Add(2, "Hayden", "United States");
    dt.Rows.Add(1, "John", "United States");
    dt.Rows.Add(3, "Williams", "India");

    dt = dt.DefaultView.ToTable(true, "Id", "Name", "Country");


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767696
    Hai Venkata Koti Reddy,
    To find the duplicate records using a key, you can use Linq and Lambda expression.
    Let's say EmpId is the key for the employee datatable. You can use the code as below:

    var duplicateEmployees = dt.AsEnumerable().GroupBy(a => a[0]).Where(b => b.Count() > 1);

    This query will group by the first column and then check that ho many times that value is in the datatable.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #767711
    1. You can try to get it using the sub query using the group by class.
    2. You can create simple method by passing the data table and the column names.In the method you can remove the duplicate records.

    public DataTable RemoveDuplicateRows(DataTable TheDataTableP, string zColumnNameP)
    {
    Hashtable hTable = new Hashtable();
    ArrayList duplicateList = 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.
    foreach (DataRow drow in TheDataTableP.Rows)
    {
    if (hTable.Contains(drow[zColumnNameP]))
    {
    duplicateList.Add(drow);
    }
    else
    {
    hTable.Add(drow[zColumnNameP], string.Empty);
    }
    }

    //Removing a list of duplicate items from datatable.
    foreach (DataRow dRow in duplicateList)
    {
    TheDataTableP.Rows.Remove(dRow);
    }
    //Datatable which contains unique records will be return as output.
    return TheDataTableP;
    }

    By Nathan
    Direction is important than speed


Sign In to post your comments