C# Corner: How to get distinct rows from a DataSet or DataTable

Not without its typos, but quite a helpful article nonetheless… 😉

One of the common issues in data layer is avoiding duplicate rows from dataset or datatable. I saw many people are writing separate function and looping through the datatable to avoid the duplicates. There is more simple ways available in .Net but people are unaware about this. I thought of writing a blog about this because I saw many blogs which mislead the people from right path. Thers is no need of looping or no need of logic are required to avoid the duplicates.

Following single line of code will avoid the duplicate rows.


ds – Dataset object

dt.DefaultView.ToTable( true, “employeeid”);

dt – DataTable object

First option in ToTable is a boolean which indicates, you want distinct rows or not?

Second option in the ToTable is the column name based on which we have to select distinct rows.

Simple right, this option is the last one so most of the people didn’t got the time to find it. Now your code will look much cleaner without those junk codes.

via How to get [distinct] rows from a DataSet or Datatable? by shyju.


  1. Arsalan says:

    Plz help me I want to add rows in dataset, How can I add rows in dataset using FOR loop ?

    1. willwm says:

      I just found this site that might help you out: http://www.pcreview.co.uk/forums/thread-1238950.php

      The fact is that you can’t create a DataRow using its constructor. You must call the NewRow() method on the table for which you want to create a row. This ensures that the created row has the right schema.

      1. Anant says:

        Thank you very much, it recuded the waste of code… 🙂

  2. Cool. thanks. it works.

  3. Mithun says:

    Thanks, Great!!!

  4. omar says:

    great code!

  5. Daniel says:

    When I do this it clears my table and when looking at the watch it says “‘dtPieTable.Rows[0]’ threw an exception of type ‘System.IndexOutOfRangeException'”
    after the totable() function is used.

  6. neil says:

    Great trick!

    You can also do:

    dt.DefaultView.ToTable( true, “employeeid”,”anothercol”,”yetanothercol”);

    and it will return distinct rows if all the columns included are identical… the problem with all this is that the resulting table ONLY includes the identical columns and ditches any others…

    I’m looking for this same functionality but which would keep the rest of the data intact (for example, a unique ID column) – any ideas?

    Basically I’d like to create a “duplicates” datatable from a maindatatable where duplicates are defined as several columns being the same – but I still need to keep the columns which do not contain the same data :-/

    I’ve discovered that a datatable can have multiple primary keys and that the find method can find a data row based on a primary key based on more than one column (http://bytes.com/topic/visual-basic-net/answers/368804-datatable-row-find-method), but I’m having trouble implementing this – any ideas?

  7. Narsing says:

    This works fine, but in the second option of totable is column array right, ok in this if we give column name it retreives the distinct values ok but it is retreiving only that one column which we specifies in the column array.
    How to get all columns based on that column.

  8. Jayanth says:

    Who ever had posted the above post i.e. Selecting distinct rows from a datatable,man you ROCK.I Love You for sharing this post to all the IT guys out there.

    Cheers Dude keep it rolling,

  9. Rinosh says:

    Hai a very useful post,
    Rinosh K Sasidharan

  10. adde says:

    thanx for it

  11. nare says:

    I have one datable which has 60cloumns and many rows now,
    i need to compare the each row and get the distinct columns and values from that datatable and create a new table and in this new table i need to add these distinct columns values

  12. Sonali Sabnam says:

    it works !!

  13. Cool. thanks. it works……..

  14. Not working for me using more than one column.

Leave a Reply to omar Cancel reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s