datatable.select and DISTINCT

In 1.1 of the Framework, which as you know I am still working with as my employer users I am on has not made the decision to upgrade the framework yet, we dont have the advantage of using LINQ as users of 3.5 have had the good fortune of doing so. I became adept at using datatable.select to work with currently loaded data. It did have its limitations though. You could not use the DISTINCT keyword that was available in SQL. So I came up with a solution to handle that. Here it is. If it helps please do let me know.
 
Imports System.Data

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
#End Region

    Dim dsSource As DataSet
    Dim dsDest As DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dsSource = New DataSet()
        dsDest = New DataSet()

        ‘ Create source table
        ‘
        Dim dt As New DataTable("Orders")
        dt.Columns.Add("EmployeeID", GetType(String))
        dt.Columns.Add("OrderID", GetType(Integer))
        dt.Columns.Add("Amount", GetType(Decimal))
        dt.Rows.Add(New Object() {"Sam", 5, 25.0})
        dt.Rows.Add(New Object() {"Tom", 7, 50.0})
        dt.Rows.Add(New Object() {"Sue", 9, 11.0})
        dt.Rows.Add(New Object() {"Tom", 12, 7.0})
        dt.Rows.Add(New Object() {"Sam", 14, 512.0})
        dt.Rows.Add(New Object() {"Sue", 15, 17.0})
        dt.Rows.Add(New Object() {"Sue", 22, 2.5})
        dt.Rows.Add(New Object() {"Tom", 24, 3.0})
        dt.Rows.Add(New Object() {"Tom", 33, 78.75})
        dsSource.Tables.Add(dt)
        dg.DataSource = dsSource

    End Sub
 
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dg.DataSource = Me.SelectDistinct("DistinctEmployees", dsSource.Tables("Orders"), "EmployeeID")
    End Sub

    ‘This method copies unique values of the field that you select into a new DataTable. If the field contains
   ‘ NULL values, a record in the destination table will also contain NULL values.
    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 dsSource Is Nothing Then dsDest.Tables.Add(dt)
        Return dt
    End Function

    Private Function ColumnEqual(ByVal A As Object, ByVal B As Object) As Boolean
        ‘
        ‘ Compares two values to determine if they are equal. Also compares DBNULL.Value.
        ‘
        ‘ NOTE: If your DataTable contains object fields, you must extend this
        ‘ function to handle the fields in a meaningful way if you intend to group on them.
        ‘
        If A Is DBNull.Value And B Is DBNull.Value Then Return True ‘ Both are DBNull.Value.
        If A Is DBNull.Value Or B Is DBNull.Value Then Return False ‘ Only one is DBNull.Value.
        Return A = B                                                ‘ Value type standard comparison
    End Function

End Class

Technorati Tags: ,,,,,,,,,,,,,

Windows Live Tags: vb.net,.NET,csharp,DISTINCT,Framework,LINQ,data,limitations,code,DataSet,DataTable,DataSource,Select,DBNULL

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: