How to select distinct from a dataset/dataview ?

[trend]

Hello, I need to select distinct rows from a dataset..

 

I have seen this posting:

 

http://www.xtremedotnettalk.com/showthread.php?t=74539&highlight=distinct+dataview

 

http://support.microsoft.com/default.aspx?scid=kb;EN-US;325684

 

and

Public Function SelectDistinct(ByVal TableName As String, _
                              ByVal SourceTable As DataTable, _
                              ByVal FieldName As String) As DataTable
   Dim dt As New DataTable(TableName)
   dt.Columns.Add(FieldName, SourceTable.Columns(FieldName).DataType)
   Dim dr As DataRow, LastValue As Object
   For Each dr In SourceTable.Select("", FieldName)
       If LastValue Is Nothing OrElse Not ColumnEqual(LastValue, dr(FieldName)) Then
           LastValue = dr(FieldName)
           dt.Rows.Add(New Object() {LastValue})
       End If
   Next
   If Not ds Is Nothing Then ds.Tables.Add(dt)
   Return dt
End Function

 

but this just seems like a long way of doing something that should be simple.. even the above code could be simplier... If i have to move through one dataset and create another.. couldn't I just do something like: dataset.Unique = True

 

 

Do you'll have any suggestions?

 

thanks!

Lee

[trend]

Actually.. It looks like the MS code above will search for a word and get a distinct result.. But I need to query a table and get all the distinct results

[trend]

Actually.. I think (?) I just found some C code to do what i want.. am I right.. and any C people out there help me pseudo code it :)

 

DataTable PurgedDataTable = _DataTable.Clone();
               DataColumn []_Columns = new DataColumn[PurgedDataTable.Columns.Count];
               for (int i =0 ; i< _DataTable.Columns.Count ; i++)
               {
                   _Columns[i]= PurgedDataTable.Columns[i];
               }
               UniqueConstraint _UniqueConstraint = new UniqueConstraint(_Columns);
               
               PurgedDataTable.Constraints.Add(_UniqueConstraint);

               for (int i =0 ; i< _DataTable.Rows.Count ; i++)
               {
                   try
                   {
                       PurgedDataTable.ImportRow(_DataTable.Rows[i]);
                   }
                   catch(Exception ex)
                   {
                       // Keep quite
                   }
               }

[trend]

ahh ok.. I found some code that almost works :)

 

Public Function DistinctRows_Int32_Key(ByVal dt As DataTable, ByVal keyfield As String) As DataTable
 Dim newTable As DataTable = dt.Clone
 Dim keyval As Int32 = 0
 Dim dv As DataView = dt.DefaultView
 dv.Sort = keyfield
 If dt.Rows.Count > 0 Then
   For Each dr As DataRow In dt.Rows
     If Not dr.Item(keyfield) = keyval Then
       newTable.ImportRow(dr)
       keyval = dr.Item(keyfield)
     End If
   Next
 Else
   newTable = dt.Clone
 End If
 Return newTable
End Function

 

the only issue is that if the column has data like this:

 

Dog

Cat

Dog

Dog

Dog

Dog

Bear

Rat

 

you will get outputlike:

Dog

Cat

Dog

Bear

Rat

 

 

Maybe if I can sort the dataset from A-Z first.. this will fix the issue.. how can i do this? :)