How will get distinct values from datatable

Mramesh · #1 (**permalink**) 02-07-2008, 04:28 AM

Hi

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

the code is below

Code:

 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

Thanx in Advance
Ramesh Kumar M

SaravananJ · #2 (**permalink**) 02-07-2008, 04:29 AM

use these two functions to select distinct

Code:

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

}

Code:

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

Code:

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

//datatable dt now will contain distinct values

By
Saravanan.J

a.deeban · #3 (**permalink**) 02-07-2008, 04:31 AM

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.

Code:

object[] distinctRoomType = GetDistinctValues(dt,"Roomtype");

Here is the method definition.

Code:

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

Cheers !
Deeban

Sundaram · #4 (**permalink**) 02-07-2008, 04:33 AM

hi...

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:

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

By
Sundaram

Sathish Kumar · #5 (**permalink**) 02-07-2008, 04:38 AM

Hi friends you may find this useful....

Code:

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

by
sathish kumar

GDevakii · #6 (**permalink**) 02-14-2008, 08:51 PM

private static DataTable SelectDistinct(DataTable SourceTable, params string[] FieldNames)
{
object[] lastValues;
DataTable newTable;
DataRow[] orderedRows;

if (FieldNames == null || FieldNames.Length == 0)
throw new ArgumentNullException("FieldNames");

lastValues = new object[FieldNames.Length];
newTable = new DataTable();

foreach (string fieldName in FieldNames)
newTable.Columns.Add(fieldName, SourceTable.Columns[fieldName].DataType);

orderedRows = SourceTable.Select("", string.Join(", ", FieldNames));

foreach (DataRow row in orderedRows)
{
if (!fieldValuesAreEqual(lastValues, row, FieldNames))
{
newTable.Rows.Add(createRowClone(row, newTable.NewRow(), FieldNames));

setLastValues(lastValues, row, FieldNames);
}
}

return newTable;
}
private static bool fieldValuesAreEqual(object[] lastValues, DataRow currentRow, string[] fieldNames)
{
bool areEqual = true;

for (int i = 0; i < fieldNames.Length; i++)
{
if (lastValues[i] == null || !lastValues[i].Equals(currentRow[fieldNames[i]]))
{
areEqual = false;
break;
}
}

return areEqual;
}
private static DataRow createRowClone(DataRow sourceRow, DataRow newRow, string[] fieldNames)
{
foreach (string field in fieldNames)
newRow[field] = sourceRow[field];

return newRow;
}

private static void setLastValues(object[] lastValues, DataRow sourceRow, string[] fieldNames)
{
for (int i = 0; i < fieldNames.Length; i++)
lastValues[i] = sourceRow[fieldNames[i]];
}

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to sort Datatable	oxygen	C# Programming	2	04-22-2008 10:25 PM
How to get distinct records from datatable	oxygen	C# Programming	1	03-05-2008 02:47 AM
How can i use the sql distinct	itbarota	Database Support	2	02-23-2008 01:40 AM
Convert Dataview to Datatable	it.wily	C# Programming	2	01-23-2008 02:51 AM
How to create Datatable which is very useful in binding..	Archer	C# Programming	1	07-21-2007 01:12 AM