Forums » .NET » .NET »

How will get distinct values from datatable


Posted Date: 27 Feb 2004      Posted By:: Martin Mathew     Member Level: Bronze    Member Rank: 0     Points: 2   Responses: 10



Hi

how i will get disctinct values , max and min from datatable

the code is below

dim dt as datatable
Dim drt() As DataRow
drt = dt.Select("distinct Roomtype")


getting error. says that 'System.Data.SyntaxErrorException: Syntax error: Missing operand after 'Roomtype' operator'

datatable contins values
i want the distinct roomtype from datatable
how i will get these values

thanks
Martin






Responses

#49    Author: Mohan      Member Level: Silver      Member Rank: 0     Date: 01/Mar/2004   Rating: 2 out of 52 out of 5     Points: 2

<code>
use these two functions to select distinct

private bool ColumnEqual(object A, object B)
{

if ( A == DBNull.Value && B == DBNull.Value )
return true;
if ( A == DBNull.Value || B == DBNull.Value )
return false;
return ( A.Equals(B) );

}


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});
}
}
if (ds != null)
ds.Tables.Add(dt);
return dt;
}


calling code

// your dataset code

DataTable dt=SelectDistinct("[tbl name]", ds.tables["table name"],"[distinct field name]")

//datatable dt now will contain distinct values


</code>

refer to link http://support.microsoft.com/default.aspx?scid=kb;EN-US;326176


 
#79    Author: Cuckoo John Joseph      Member Level: Gold      Member Rank: 0     Date: 05/Mar/2004   Rating: 2 out of 52 out of 5     Points: 2

A Simple Method to Get the Distinct Values.

Use this method.

Parameters :

Datatable from which the distinct values has to be fetched
colName is the name of the column for the distinct values.

so u can call like this.

<font style="FONT-SIZE: 12px; FONT-FAMILY: 'Courier New'" >
<B>object[] distinctRoomType = GetDistinctValues(dt,"Roomtype");</B>
</font>

Here is the method definition.


<font style="FONT-SIZE: 12px; FONT-FAMILY: 'Courier New'" >
public object[] GetDistinctValues(DataTable dtable,string colName)
{
Hashtable hTable = new Hashtable();
foreach(DataRow drow in dtable.Rows)
{
try
{
hTable.Add(drow[colName],string.Empty);
}
catch{}
}
object[] objArray = new object[hTable.Keys.Count];
hTable.Keys.CopyTo(objArray,0);
return objArray;
}

</font>


Cheers !
John


 
#34222    Author: geekman      Member Level: Bronze      Member Rank: 0     Date: 15/Nov/2005   Rating: 2 out of 52 out of 5     Points: 2

I found this posting very helpful but the function supplied is too slow because it is designed to throw errors so I thought I would add my own varaition of the c# code:

public object[] GetDistinctValues(DataTable dtable, string colName)
{
Hashtable hTable = new Hashtable();
foreach (DataRow drow in dtable.Rows)
if(!hTable.ContainsKey(drow[colName]))
hTable.Add(drow[colName], string.Empty);
object[] objArray = new object[hTable.Keys.Count];
hTable.Keys.CopyTo(objArray, 0);
return objArray;
}





 
#146635    Author: suresh      Member Level: Bronze      Member Rank: 0     Date: 24/Aug/2007   Rating: 2 out of 52 out of 5     Points: 2

Hi friends you may find this useful....

public DataRow[] GetDistinctRows(DataTable drows, string colName)
{
DataRow[] objArray = null;
if (drows != null && colName != null && colName != string.Empty)
{
Hashtable hTable = new Hashtable();
List<DataRow> distinctRows = new List<DataRow>();

foreach (DataRow drow in drows.Rows)
if (!hTable.ContainsKey(drow[colName].ToString()))
{
hTable.Add(drow[colName].ToString(), string.Empty);
distinctRows.Add(drow);
}
objArray = new DataRow[distinctRows.Count];
distinctRows.CopyTo(objArray);
}
return objArray;
}


 
#188572    Author: Ananda Babu Karthikeyan      Member Level: Silver      Member Rank: 4814     Date: 11/Feb/2008   Rating: 2 out of 52 out of 5     Points: 2

hello Martin,
Try this code. u'll get distinct table from ur original table(dtTemp).
 dim dt as datatable
Dim dtTemp As DataTable
dt=dtTemp.DefaultView.ToTable(true,"Roomtype")


Regards,
ANANDA BABU KARTHIKEYAN,
Calippus Tech (India) Pvt. Ltd.


 
#190197    Author: chinnu      Member Level: Bronze      Member Rank: 0     Date: 14/Feb/2008   Rating: 2 out of 52 out of 5     Points: 2

I think the above answer holds true only for framework 2.0 onwards.

People on earlier frameworks can use the following logic. Loop thru the table using-
for each dr as dataRow in dt.select("",columnName)
Keep adding the row to a dummy table(clone of original) when you see a change in the row(columnName) value.

Simple! I think using a hashtable will only slow up things.


 
#192580    Author: Mr.Rajesh      Member Level: Gold      Member Rank: 93     Date: 21/Feb/2008   Rating: 2 out of 52 out of 5     Points: 2

Query:
"Select Distinct from Table";
this query will give u distinct table;

Regards
Mr.Rajesh
Software Engg.


 
#192660    Author: Ananda Babu Karthikeyan      Member Level: Silver      Member Rank: 4814     Date: 21/Feb/2008   Rating: 2 out of 52 out of 5     Points: 2

hello rajesh ur code will not help. for this

 
#288526    Author: Sujit Kumar      Member Level: Gold      Member Rank: 220     Date: 28/Aug/2008   Rating: 2 out of 52 out of 5     Points: 4

you can get distinct rows from dataset/datatable
ex:
DataSet ds=new DataSet();
//code to fill dataset from database.

DataTable dt=new DataTable();
dt=ds.Tables["0"].DefaultView.ToTable(true,”columnName”);
//here true means you want distict result.

Regards,
Sujit Kumar


 
#347483    Author: Gopikrishna      Member Level: Bronze      Member Rank: 0     Date: 16/Feb/2009   Rating: 2 out of 52 out of 5     Points: 3

The above codes need to be slightly modified. They all specify the column name as a string, where as the 'ToTable' method accepts a array of column names.

The code should be,
DataTable dt1=new DataTable();
DataTable dt2=new DataTable();

dt2=dt1.DefaultView.ToTable(true,new string [1]{"columnName"});


 
Post Reply

 This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.



Return to Discussion Forum
Start new thread

Subscribe to Subscribers
Active Members
Today
    Last 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Aswini Aluri
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India