Pathetic Plea for help


A number of people have asked me for a VB version of the CopyToDataTable<T> sample I wrote a few months back.   Unfortunately, between getting some skiing in (lots of snow in Washington this year!) and getting Astoria ready for Mix, I have not had very little free time.  Hence, if anyone else wants to take a crack at this – or knows of an existing port of the code to VB, let me know.


Comments (7)

  1. Matthew says:

    Imports Microsoft.VisualBasic

    Imports System

    Imports System.Collections.Generic

    Imports System.Linq

    Imports System.Text

    Imports System.Data

    Imports System.Reflection

    Namespace WindowsFormsApplication1

    Friend Class Sample
    
        Shared Sub Main(ByVal args() As String)
    
            ' create sequence 
    
            Dim items() As Item = New Item() { New Book With {.Id = 1, .Price = 13.50, .Genre = &quot;Comedy&quot;, .Author = &quot;Jim Bob&quot;}, New Book With {.Id = 2, .Price = 8.50, .Genre = &quot;Drama&quot;, .Author = &quot;John Fox&quot;}, New Movie With {.Id = 1, .Price = 22.99, .Genre = &quot;Comedy&quot;, .Director = &quot;Phil Funk&quot;}, New Movie With {.Id = 1, .Price = 13.40, .Genre = &quot;Action&quot;, .Director = &quot;Eddie Jones&quot;}}
    
            Dim query1 = From i In items _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Where i.Price &gt; 9.99 _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Order By i.Price _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Select i
    
            ' load into new DataTable
    

               Dim table1 As DataTable = query1.CopyToDataTable()

               ‘ load into existing DataTable – schemas match            

            Dim table2 As DataTable = New DataTable()
    
            table2.Columns.Add(&quot;Price&quot;, GetType(Integer))
    
            table2.Columns.Add(&quot;Genre&quot;, GetType(String))
    
            Dim query2 = From i In items _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Where i.Price &gt; 9.99 _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Order By i.Price _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Select New With { i.Price, i.Genre }
    
            query2.CopyToDataTable(table2, LoadOption.PreserveChanges)
    
            ' load into existing DataTable - expand schema + autogenerate new Id.
    
            Dim table3 As DataTable = New DataTable()
    
            Dim dc As DataColumn = table3.Columns.Add(&quot;NewId&quot;, GetType(Integer))
    
            dc.AutoIncrement = True
    
            table3.Columns.Add(&quot;ExtraColumn&quot;, GetType(String))
    
            Dim query3 = From i In items _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Where i.Price &gt; 9.99 _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Order By i.Price _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Select New With { i.Price, i.Genre }
    
            query3.CopyToDataTable(table3, LoadOption.PreserveChanges)
    
            ' load sequence of scalars.
    
            Dim query4 = From i In items _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Where i.Price &gt; 9.99 _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Order By i.Price _
    
             &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Select i.Price
    
            Dim DataTable4 = query4.CopyToDataTable()
    
        End Sub
    
        Public Class Item
    
            Private privateId As Integer
    
            Public Property Id() As Integer
    
                Get
    
                    Return privateId
    
                End Get
    
                Set(ByVal value As Integer)
    
                    privateId = value
    
                End Set
    
            End Property
    
            Private privatePrice As Double
    
            Public Property Price() As Double
    
                Get
    
                    Return privatePrice
    
                End Get
    
                Set(ByVal value As Double)
    
                    privatePrice = value
    
                End Set
    
            End Property
    
            Private privateGenre As String
    
            Public Property Genre() As String
    
                Get
    
                    Return privateGenre
    
                End Get
    
                Set(ByVal value As String)
    
                    privateGenre = value
    
                End Set
    
            End Property
    
        End Class
    
        Public Class Book
    
            Inherits Item
    
            Private privateAuthor As String
    
            Public Property Author() As String
    
                Get
    
                    Return privateAuthor
    
                End Get
    
                Set(ByVal value As String)
    
                    privateAuthor = value
    
                End Set
    
            End Property
    
        End Class
    
        Public Class Movie
    
            Inherits Item
    
            Private privateDirector As String
    
            Public Property Director() As String
    
                Get
    
                    Return privateDirector
    
                End Get
    
                Set(ByVal value As String)
    
                    privateDirector = value
    
                End Set
    
            End Property
    
        End Class
    
    End Class
    

       Public Module DataSetLinqOperators

           <System.Runtime.CompilerServices.Extension()> _

       Public Function CopyToDataTable(Of T)(ByVal source As IEnumerable(Of T)) As DataTable

               Return New ObjectShredder(Of T)().Shred(source, Nothing, Nothing)

           End Function

           <System.Runtime.CompilerServices.Extension()> _

           Public Function CopyToDataTable(Of T)(ByVal source As IEnumerable(Of T), ByVal table As DataTable, ByVal options As Nullable(Of LoadOption)) As DataTable

               Return New ObjectShredder(Of T)().Shred(source, table, options)

           End Function

       End Module

       Public Class ObjectShredder(Of T)

           Private _fi() As FieldInfo

           Private _pi() As PropertyInfo

           Private _ordinalMap As Dictionary(Of String, Integer)

           Private _type As Type

           Public Sub New()

               _type = GetType(T)

               _fi = _type.GetFields()

               _pi = _type.GetProperties()

               _ordinalMap = New Dictionary(Of String, Integer)()

           End Sub

           Public Function Shred(ByVal source As IEnumerable(Of T), ByVal table As DataTable, ByVal options As Nullable(Of LoadOption)) As DataTable

               If GetType(T).IsPrimitive Then

                   Return ShredPrimitive(source, table, options)

               End If

               If table Is Nothing Then

                   table = New DataTable(GetType(T).Name)

               End If

               ‘ now see if need to extend datatable base on the type T + build ordinal map

               table = ExtendTable(table, GetType(T))

               table.BeginLoadData()

               Using e As IEnumerator(Of T) = source.GetEnumerator()

                   Do While e.MoveNext()

                       If options.HasValue Then

                           table.LoadDataRow(ShredObject(table, e.Current), CType(options, LoadOption))

                       Else

                           table.LoadDataRow(ShredObject(table, e.Current), True)

                       End If

                   Loop

               End Using

               table.EndLoadData()

               Return table

           End Function

           Public Function ShredPrimitive(ByVal source As IEnumerable(Of T), ByVal table As DataTable, ByVal options As Nullable(Of LoadOption)) As DataTable

               If table Is Nothing Then

                   table = New DataTable(GetType(T).Name)

               End If

               If (Not table.Columns.Contains("Value")) Then

                   table.Columns.Add("Value", GetType(T))

               End If

               table.BeginLoadData()

               Using e As IEnumerator(Of T) = source.GetEnumerator()

                   Dim values() As Object = New Object(table.Columns.Count – 1) {}

                   Do While e.MoveNext()

                       values(table.Columns("Value").Ordinal) = e.Current

                       If options.HasValue Then

                           table.LoadDataRow(values, CType(options, LoadOption))

                       Else

                           table.LoadDataRow(values, True)

                       End If

                   Loop

               End Using

               table.EndLoadData()

               Return table

           End Function

           Public Function ExtendTable(ByVal table As DataTable, ByVal type As Type) As DataTable

               ‘ value is type derived from T, may need to extend table.

               For Each f As FieldInfo In type.GetFields()

                   If (Not _ordinalMap.ContainsKey(f.Name)) Then

                       Dim dc As DataColumn

                       dc = If(table.Columns.Contains(f.Name), table.Columns(f.Name), table.Columns.Add(f.Name, f.FieldType))

                       _ordinalMap.Add(f.Name, dc.Ordinal)

                   End If

               Next f

               For Each p As PropertyInfo In type.GetProperties()

                   If (Not _ordinalMap.ContainsKey(p.Name)) Then

                       Dim dc As DataColumn

                       dc = If(table.Columns.Contains(p.Name), table.Columns(p.Name), table.Columns.Add(p.Name, p.PropertyType))

                       _ordinalMap.Add(p.Name, dc.Ordinal)

                   End If

               Next p

               Return table

           End Function

           Public Function ShredObject(ByVal table As DataTable, ByVal instance As T) As Object()

               Dim fi() As FieldInfo = _fi

               Dim pi() As PropertyInfo = _pi

               If instance.GetType() IsNot GetType(T) Then

                   ExtendTable(table, instance.GetType())

                   fi = instance.GetType().GetFields()

                   pi = instance.GetType().GetProperties()

               End If

               Dim values() As Object = New Object(table.Columns.Count – 1) {}

               For Each f As FieldInfo In fi

                   values(_ordinalMap(f.Name)) = f.GetValue(instance)

               Next f

               For Each p As PropertyInfo In pi

                   values(_ordinalMap(p.Name)) = p.GetValue(instance, Nothing)

               Next p

               Return values

           End Function

       End Class

    End Namespace

  2. Fabrice says:

    We have a VB version of this source code in the samples’ source code of the LINQ in Action book (http://LinqInAction.net).

    Here is the complete archive: http://linqinaction.net/files/folders/linqinaction/entry1952.aspx

    See the files DataSetLinqOperators.vb and ObjectShreder.vb in Chapter14.VBChapter14.

    Note: In our version, we kept the original names of the query operators for clarity (ToDataTable and LoadSequence).

  3. Walther says:

    Anyone found about about the nullable problem in the VB version yet?

    When a nullable column is added in this line:

    Dim dc As DataColumn = If(table.Columns.Contains(p.Name), table.Columns(p.Name), table.Columns.Add(p.Name, p.PropertyType))

    a NotSupportedException is thrown:

    DataSet does not support System.Nullable<>.

    Fabrice’s implementation suffers the same problem.

  4. Walther says:

    VB implementation to handle nullable types as well :

    Public Function ExtendTable(ByVal table As DataTable, ByVal type As Type) As DataTable

     For Each f As FieldInfo In type.GetFields()

       If (Not _ordinalMap.ContainsKey(f.Name)) Then

         Dim dc As DataColumn

         dc = If(table.Columns.Contains(f.Name), table.Columns(f.Name), table.Columns.Add(f.Name, f.FieldType))

         _ordinalMap.Add(f.Name, dc.Ordinal)

       End If

     Next f

     For Each p As PropertyInfo In type.GetProperties()

       If Not _ordinalMap.ContainsKey(p.Name) Then

         Dim colType As Type = p.PropertyType

         If (colType.IsGenericType) AndAlso (colType.GetGenericTypeDefinition() Is GetType(Nullable(Of ))) Then

           colType = colType.GetGenericArguments()(0)

         End If

         Dim dc As DataColumn = IIf(table.Columns.Contains(p.Name), table.Columns(p.Name), table.Columns.Add(p.Name, colType))

         _ordinalMap.Add(p.Name, dc.Ordinal)

       End If

     Next

     Return table

    End Function

  5. hmmm says:

    a small problem guys…this function does not set the primarykey on the datatable.

    which is needed in case you wish to update the table using an sqlcommand.

    has anyone been able to get this to work?

  6. 罗爱军 says:

    IntheoriginalLinqCTPandthefirstOrcasBeta,weincludedaDataSetspecificLinqoperatorcall…