Man vs Code

# » Pathetic Plea for help MSDN Blog Feed @ Friday, January 11, 2008 2:58 PM

PingBack from http://msdn.blogsforu.com/msdn/?p=4718

» Pathetic Plea for help MSDN Blog Feed

# re: Pathetic Plea for help @ Sunday, January 13, 2008 6:28 PM

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 = "Comedy", .Author = "Jim Bob"}, New Book With {.Id = 2, .Price = 8.50, .Genre = "Drama", .Author = "John Fox"}, New Movie With {.Id = 1, .Price = 22.99, .Genre = "Comedy", .Director = "Phil Funk"}, New Movie With {.Id = 1, .Price = 13.40, .Genre = "Action", .Director = "Eddie Jones"}}

Dim query1 = From i In items _

            Where i.Price > 9.99 _

            Order By i.Price _

            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("Price", GetType(Integer))

table2.Columns.Add("Genre", GetType(String))

Dim query2 = From i In items _

            Where i.Price > 9.99 _

            Order By i.Price _

            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("NewId", GetType(Integer))

dc.AutoIncrement = True

table3.Columns.Add("ExtraColumn", GetType(String))

Dim query3 = From i In items _

            Where i.Price > 9.99 _

            Order By i.Price _

            Select New With { i.Price, i.Genre }

query3.CopyToDataTable(table3, LoadOption.PreserveChanges)

' load sequence of scalars.

Dim query4 = From i In items _

            Where i.Price > 9.99 _

            Order By i.Price _

            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

Matthew

# re: Pathetic Plea for help @ Monday, January 14, 2008 9:45 PM

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.VB\Chapter14.

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

Fabrice

# re: Pathetic Plea for help @ Monday, January 21, 2008 3:58 AM

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.

Walther

# re: Pathetic Plea for help @ Monday, January 21, 2008 4:31 AM

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

Walther

# re: Pathetic Plea for help @ Friday, February 15, 2008 8:45 AM

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?

hmmm

# CopyToDataTable @ Wednesday, October 15, 2008 11:23 AM

IntheoriginalLinqCTPandthefirstOrcasBeta,weincludedaDataSetspecificLinqoperatorcall...

罗爱军