Remove duplicate column values from a datatable without using LINQ

Question

Consider my datatable,

Id  Name  MobNo
1   ac    9566643707
2   bc    9944556612
3   cc    9566643707

How to remove the row 3 which contains duplicate MobNo column value in c# without using LINQ. I have seen similar questions on SO but all the answers uses LINQ.

Answer 1

You might want to look up the inner workings on DISTINCT before running this on your sharp DB (be sure to back up!), but if it works as I think it does (grabbing the first value) you should be able to use (something very similar to) the following SQL:

DELETE FROM YourTable WHERE Id NOT IN (SELECT DISTINCT Id, MobNo FROM YourTable);

Answer 2

CREATE TABLE  MyTable
(
    Id int,
    Name  varchar(50),
    MobNo  varchar(50)
)
GO

INSERT INTO MyTable
VALUES
(1,   'ac'   , '9566643707') ,
(2,   'bc'   , '9944556612'), 
(3,   'cc'   , '9566643707') 
GO



;WITH cteRows(Id, Name, MobNo, rownum) AS
(
    SELECT 
        Id, Name, MobNo, 
        ROW_NUMBER() OVER (PARTITION BY MobNo Order BY Id) rownum
    FROM
        MyTable
)
select * from cteRows 
where rownum = 1

Answer 3

As you are reading your CSV file ( a bit of pseudo code, but you get the picture ):

List<String> uniqueMobiles = new List<String>();

String[] fileLines = readYourFile();

for (String line in fileLines) {
   DataRow row = parseLine(line);
   if (uniqueMobiles.Contains(row["MobNum"])
   {
       continue;
   }
   uniqueMobiles.Add(row["MobNum"]);
   yourDataTable.Rows.Add(row);       
}

This will only load the records with unique mobiles into your data table.

Answer 4

The following method did what i want....

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;
    }

Answer 5

You can use "IEqualityComparer" in C#

Answer 6

Pandiya, this is very similar to a problem I have. Have you figured out how I would go about deleting rows 1 AND 3 (all duplicates), and returning only row 2?