I have a dataset objds. objds contains a table named Table1. Table1 contains column named ProcessName. This ProcessName contains repeated names.So i want to select only distinct names.Is this possible.
intUniqId[i] = (objds.Tables[0].Rows[i]["ProcessName"].ToString());
DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...);
var distinctRows = (from DataRow dRow in dtInventory.Rows
select dRow["column_name"] ).Distinct();
var distinctRows = (from DataRow dRow in dtInventory.Rows
select dRow["col1"], dRow["col2"].. ).Distinct();
var ValuetoReturn = (from Rows in YourDataTable.AsEnumerable()
select Rows["ColumnName"]).Distinct().ToList();
Following works. I have it working for me with .NET 3.5 SP1
// Create the list of columns
String[] szColumns = new String[data.Columns.Count];
for (int index = 0; index < data.Columns.Count; index++)
{
szColumns[index] = data.Columns[index].ColumnName;
}
// Get the distinct records
data = data.DefaultView.ToTable(true, szColumns);
You can use like that:
data
is DataTable
data.DefaultView.ToTable(true, "Id", "Name", "Role", "DC1", "DC2", "DC3", "DC4", "DC5", "DC6", "DC7");
but performance will be down. try to use below code:
data.AsEnumerable().Distinct(System.Data.DataRowComparer.Default).ToList();
For Performance ; http://onerkaya.blogspot.com/2013/01/distinct-dataviewtotable-vs-linq.html
DataTable dt = new DataTable();
dt.Columns.Add("IntValue", typeof(int));
dt.Columns.Add("StringValue", typeof(string));
dt.Rows.Add(1, "1");
dt.Rows.Add(1, "1");
dt.Rows.Add(1, "1");
dt.Rows.Add(2, "2");
dt.Rows.Add(2, "2");
var x = (from r in dt.AsEnumerable()
select r["IntValue"]).Distinct().ToList();
objds.Table1.Select(r => r.ProcessName).AsEnumerable().Distinct();
The most simple solution is to use LINQ and then transform the result to a DataTable
//data is a DataTable that you want to change
DataTable result = data.AsEnumerable().Distinct().CopyToDataTable < DataRow > ();
This is valid only for asp.net 4.0 ^ Framework and it needs the reference to System.Data.DataSetExtensions as Ivan Ferrer Villa pointed out
it is easy
DataView view = new DataView(dt);
DataTable dt2 = view.ToTable(true, "Column1", "Column2","Column3", ...,"ColumnNth");
and dt2 datatable contain column1,Column2..ColumnNth unique data.
Following single line of code will avoid the duplicate rows of a DataTable
:
dataTable.DefaultView.ToTable(true, "employeeid");
Where:
first parameter in ToTable()
is a boolean which indicates whether you want distinct rows or not.
second parameter in the ToTable()
is the column name based on which we have to select distinct rows. Only these columns will be in the returned datatable.
The same can be done from a DataSet
, by accessing a specific DataTable
:
dataSet.Tables["Employee"].DefaultView.ToTable(true, "employeeid");
I just happened to find this: http://support.microsoft.com/default.aspx?scid=kb;en-us;326176#1
While looking for something similar, only, specifically for .net 2.0
Im assuming the OP was looking for distinct while using DataTable.Select(). (Select() doesn't support distinct)
So here is the code from the above link:
class DataTableHelper
{
public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
{
DataTable dt = new DataTable(TableName);
dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);
object LastValue = null;
foreach (DataRow dr in SourceTable.Select("", FieldName))
{
if ( LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])) )
{
LastValue = dr[FieldName];
dt.Rows.Add(new object[]{LastValue});
}
}
return dt;
}
private bool ColumnEqual(object A, object B)
{
// Compares two values to see if they are equal. Also compares DBNULL.Value.
// Note: If your DataTable contains object fields, then you must extend this
// function to handle them in a meaningful way if you intend to group on them.
if ( A == DBNull.Value && B == DBNull.Value ) // both are DBNull.Value
return true;
if ( A == DBNull.Value || B == DBNull.Value ) // only one is DBNull.Value
return false;
return ( A.Equals(B) ); // value type standard comparison
}
}
To improve the above answer: The ToTable function on dataview has a "distinct" flag.
//This will filter all records to be distinct
dt = dt.DefaultView.ToTable(true);
Syntax:-
DataTable dt = ds.Tables[0].DefaultView.ToTable(true, "ColumnName");
EX:-
DataTable uniqueCols = dsUDFlable.Tables[0].DefaultView.ToTable(true, "BorrowerLabelName");
DataTable dtbs = new DataTable();
DataView dvbs = new DataView(dt);
dvbs.RowFilter = "ColumnName='Filtervalue'";
dtbs = dvbs.ToTable();
sthing like ?
SELECT DISTINCT .... FROM table WHERE condition
http://www.felixgers.de/teaching/sql/sql_distinct.html
note: Homework question ? and god bless google..
DataTable dt = new DataTable("EMPLOYEE_LIST");
DataColumn eeCode = dt.Columns.Add("EMPLOYEE_CODE", typeof(String));
DataColumn taxYear = dt.Columns.Add("TAX_YEAR", typeof(String));
DataColumn intData = dt.Columns.Add("INT_DATA", typeof(int));
DataColumn textData = dt.Columns.Add("TEXT_DATA", typeof(String));
dt.PrimaryKey = new DataColumn[] { eeCode, taxYear };
It filters data table with eecode and taxyear combinedly considered as unique
string[] TobeDistinct = {"Name","City","State"};
DataTable dtDistinct = GetDistinctRecords(DTwithDuplicate, TobeDistinct);
//Following function will return Distinct records for Name, City and State column.
public static DataTable GetDistinctRecords(DataTable dt, string[] Columns)
{
DataTable dtUniqRecords = new DataTable();
dtUniqRecords = dt.DefaultView.ToTable(true, Columns);
return dtUniqRecords;
}
With LINQ (.NET 3.5, C# 3)
var distinctNames = ( from row in DataTable.AsEnumerable()
select row.Field<string>("Name")).Distinct();
foreach (var name in distinctNames ) { Console.WriteLine(name); }
Source: Stackoverflow.com