2

I have a data table which has duplicate row as follow.

|     |                    |            |           |       |
|cid  |    usrnme          |   pname    |   prate   | cabin |
|-----------------------------------------------------------|
|c11  | demo1@gmail.com    |   sample1  |    2000   | B2    | *******
|c14  | demo2@live.com     |   sample2  |    5000   | B3    |
|c15  | demo3@yahoo.com    |   sample3  |    8000   | B2    |
|c11  | demo1@gmail.com    |   sample1  |    2000   | B2    | *******
|c18  | demo4@gmail.com    |   sample4  |    3000   | L1    |
|c11  | demo5@gmail.com    |   sample5  |    7400   | B4    | &&&&&&&
============================================================

NOTE : there are different data for same ID ,see &&&&&&& row

How to get one row for above duplicate two rows.I have tried This

this is the code I used.

public DataTable RemoveduplicateRows(DataTable dTable,string colName)
{
    colName = "cabin";
    Hashtable hTable = new Hashtable();
    ArrayList duplicateArrayList = new ArrayList();


    foreach(DataRow drow in dTable.Rows)
    {
        if (hTable.Contains(drow[colName]))
            duplicateArrayList.Add(drow);
        else
        {
            hTable.Add(drow[colName], string.Empty);
        }
    }

    foreach (DataRow dRow in duplicateArrayList)
        dTable.Rows.Remove(dRow);

    return dTable;
}

if I used above code it avoid duplicate according to cabin then it removes all records which its cabin is B2 and keep the first one only.what I want is to remove only the full row(keep one and delete others).how can I do that.

Community
  • 1
  • 1
bill
  • 854
  • 3
  • 17
  • 41
  • Why You are getting duplicate row from database. Make restriction for duplicate row in database. Use `DISTINCT`,`GROUP BY` clause in your query – Mahesh Bhosale Apr 25 '16 at 06:37

6 Answers6

0

You can use IEnumerable.Distinct

DataTable dataTable = // from data source

var distinctDataTable = dataTable.AsEnumerable().Distinct(DataRowComparer.Default);

Also see Comparing DataRows (LINQ to DataSet) as reference

jegtugado
  • 5,081
  • 1
  • 12
  • 35
0

Its your cid that decides the uniqueness of the record. In the provided example there are two rows with same cid and the entire row is same too.. That proves that if you use cid to find the duplicates then you will get the desired output

change only this line of code

colName = "cabin"; to colName = "cid";

Rajshekar Reddy
  • 18,647
  • 3
  • 40
  • 59
  • I have edited the question.sorry for that there can be same `cid` but other data are different.that is the problem – bill Apr 25 '16 at 06:38
0

try to use DataView and do this

DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...);

in your case put all the columns

rashfmnb
  • 9,959
  • 4
  • 33
  • 44
0

The problem with your solution is that it removes all duplicates instead of keeping one row from each duplicate group.

To do so, you'll need to group the rows by duplicate column and from each group of duplicates delete all rows except the first one.

I haven't tested it in Visual Studio but the code below should give you a hint in the right direction.

var duplicates = dataTable.Rows
    .Cast<DataRow>()
    .GroupBy(r => r["cabin"])
    .SelectMany(g => g.Skip(1))
    .ToList();

duplicates.ForEach(dataTable.Rows.Remove);
RePierre
  • 9,358
  • 2
  • 20
  • 37
0

Its very simple.

You can try this below snippet:

DataTable.DefaultView.ToTable(bool distinct, string[] ColumnNames)
Santosh Kokatnur
  • 357
  • 3
  • 9
  • 19
0

This may seem like a lot of code but it gets the Distinct Method to work on a DataTable row. So you only need on instruction in the main code. This method uses standard built in library methods.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("cid", typeof(string));
            dt.Columns.Add("usrnme", typeof(string));
            dt.Columns.Add("pname", typeof(string));
            dt.Columns.Add("prate", typeof(int));
            dt.Columns.Add("cabin", typeof(string));
            dt.Rows.Add(new object[] { "c11", "demo1@gmail.com", "sample1", 2000, "B2" });
            dt.Rows.Add(new object[] { "c14", "demo2@live.com", "sample2", 5000, "B3" });
            dt.Rows.Add(new object[] { "c15", "demo3@yahoo.com", "sample3", 8000, "B2" });
            dt.Rows.Add(new object[] { "c11", "demo1@gmail.com", "sample1", 2000, "B2" });
            dt.Rows.Add(new object[] { "c18", "demo4@gmail.com", "sample4", 3000, "L1" });
            dt.Rows.Add(new object[] { "c11", "demo5@gmail.com", "sample5", 7400, "B4" });
            dt = dt.AsEnumerable().Select(x => new UniqueColumns(x)).Distinct().Select(y => y.row).CopyToDataTable();
        }
    }
    public class UniqueColumns : EqualityComparer<UniqueColumns>
    {
        public DataRow row { get; set; }
        public UniqueColumns(DataRow row)
        {
            this.row = row;
        }
        public override int GetHashCode(UniqueColumns _this)
        {
            int hash = 0;
            foreach(var x in _this.row.ItemArray){hash ^= x.GetHashCode();} ;
            return hash;
        }
        public override int GetHashCode()
        {
            return this.GetHashCode(this);
        }

        public override Boolean Equals(UniqueColumns _this, UniqueColumns other)
        {
            Boolean results = _this.row.ItemArray.Select((x,i) => x.Equals(other.row.ItemArray[i])).All(y => y);
            return results;
        }
        public override bool Equals(object other)
        {
            return this.Equals(this, (UniqueColumns)other);
        }
    }
}
jdweng
  • 33,250
  • 2
  • 15
  • 20