making rows distinct and showing all the columns

Question

In my project there are two datatables dtFail and dtFailed (dtFailed has nothing but column names declarations). dtFail has duplicate "EmployeeName" column values. so i took a dataview dvFail and did the process to make them distinct as shown in the below code:

dtFail

I tried the below code:

   DataView dvFail = new DataView(dtFail);
   dtFail = dvFail.ToTable(true, "EmployeeName"); //showing only one column in dtFail

dtFailed (only one column)

If i do like below

   DataView dvFail = new DataView(dtFail);
   dtFail = dvFail.ToTable(true, "EmployeeName","EmployeeRole","Status");

dtFailed (showing but with duplicate rows)

Then the datatable dtFailed is storing duplicate "EmployeeName" also.

Please Help
Thanks in Advance.

Answer 1

Try this query-

DataTable distinctTable = originalTable.DefaultView.ToTable( /*distinct*/ true);

For more info hit below link-

https://social.msdn.microsoft.com/Forums/en-US/ed9c6a6a-a93e-4bf5-a892-d8471b84aa3b/distinct-in-datatable-or-dataview?forum=adodotnetdataset

I hope this would have helped you.

Answer 2

SOLUTION 1:

Based on the question my understanding is, we need to consider duplicates based on EmployeeName and we need not worry about other columns. If that is the case below solution works better.

foreach(DataRow r in dtFail.AsEnumerable())
  {
   if (!dt1.AsEnumerable().Any(r1 => r1["EmployeeName"] == r["EmployeeName"]))
      {
     // if you don't want to copy entire row create new DataRow 
     // with required fields and add that row.
      dt1.Rows.Add(r.ItemArray);
      }
  }

if you want you can put dt1 back to dtFail.

SOLUTION 2:

If we need to consider distinct rows I prefer below solution.

var temp = dtFail.AsEnumerable().Distinct();
dtFail = temp.CopyToDataTable();

Answer 3

I'm not sure it will be helpful or not. As far as I get from your question that you want EmployeeName to be distinct irrelevant to other columns. But if you do ToTable and turn on the distinct flag it will give all the distinct rows, doesn't matter how many columns are involved there. So if you mention only EmployeeName it will obviously give you distinct EmployeeNames, not all the columns associated with it.

So, thats what I did, initially select only the distinct EmployeeName columns and put it into a temp DataTable dtt.

DataTable dtt = dvFail.DefaultView.ToTable(true, "EmployeeName");

Secondly I've created another temp DataTable where we put the segregated rows from the main DataTable dtFail and set the column names manually.

DataTable TempDataTable = new DataTable();
DataTable dtFailed = new DataTable();

Prepare the columns in the dtFailed DataTable.

     if (dtFailed.Columns.Count == 0)
     {
           dtFailed.Columns.Add("EmployeeName");
           dtFailed.Columns.Add("EmployeeRole");
           dtFailed.Columns.Add("Status");
           dtFailed.Columns.Add("Date");
     }

Loop through the distinct EmployeeName dtt DataTable and match the EmployeeName and keep that selected first row in the TempDataTable. Finally all rows transferred into the dtFailed.

     for (int j = 0; j < dtt.Rows.Count; j++)
     {
           string EmployeeName = dtt.Rows[j]["EmployeeName"].ToString();
           TempDataTable = dvFail.Select("EmployeeName = " + EmployeeName).CopyToDataTable();
           dtFailed.Rows.Add(TempDataTable.Rows[0].ItemArray);
     }