CODEWITHSUNDEEP
c#datatabledatasetdataview
Mr_Green
Mr_Green

Reputation: 41832

making rows distinct and showing all the columns

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

enter image description here

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)

enter image description here

If i do like below

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

dtFailed (showing but with duplicate rows)

enter image description here

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

Upvotes: 12

Views: 16020

Answers (3)

ShaileshDev
ShaileshDev

Reputation: 1136

Try this query from Mike at this 2007 TechNet forum link:

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

Upvotes: 7

Raj Karri
Raj Karri

Reputation: 551

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();

Upvotes: 1

Mahib
Mahib

Reputation: 4063

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

Upvotes: 0

Related Questions