ADO.NET 2.0: DataView.ToTable DISTINCT!! Hooray Hooray!

Because I had to write a DistinctRows function last night, today I looked at the May bits of VS2005 to see if they had addressed this really obvious need, and indeed they did! Hooray hoorah!

OverLoads Public Function ToTable( _ ByVal distinct As Boolean, _ ByVal columnNames() As String _) As DataTable

Here’s the quick hack I wrote last night if you want to use it. It sorts the table on the keyfield then walks through it grabbing every row where the value of the key field changes and stuffing it into a new datatable that is returned. No rocket science. Just another one of those things I wonder why I had to write the code.

(note – the int32_key is in there as a reminder in case I need to create another one for non-int keys)

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

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

3 thoughts on “ADO.NET 2.0: DataView.ToTable DISTINCT!! Hooray Hooray!

  1. Your function above does have a small mistake. In order to retain the sort order you set, you need to alter your For Each line to use the Dataview and not the original Datatable.

    For Each dr As DataRow In dv.ToTable.Rows

    Thanks for help in framing this out.

  2. hi you can directly get distinct from dataview refer here

    <a href=’http://www.vbknowledgebase.com/WebApp/Guest/Home/Home.aspx?PageCode=70&Desc=Asp-.Net-DataTable-DataSet-Distinct-using-ToTable’>DataSet Distinct using ToTable</a>

Leave a Reply to Vb Reader Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.