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.

19 thoughts on “C# Corner: How to get distinct rows from a DataSet or DataTable

  1. 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.

  2. 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?

  3. Hi,
    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.

  4. 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,

  5. 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

Leave a Reply to neil 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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s