ExtensionMethod.NET Home of 816 C#, Visual Basic, F# and Javascript extension methods

CopyToEntityList

Copy DataTable rows to IEnumerable(Of T)

Source

<Extension()> _
Public Function CopyToEntityList(Of T As New)(ByVal dt As DataTable) As IEnumerable(Of T)
    Dim myPpts = New T().GetType().GetProperties()
    Dim colList = From col In dt.Columns.Cast(Of DataColumn)() _
          Select col.ColumnName, col.DataType
    Dim pptList = (From ppt In myPpts _
                   Where colList.Select(Function(p) p.ColumnName).Contains(ppt.Name) _
                   And colList.Select(Function(p) p.DataType).Contains( _
                                    If(Nullable.GetUnderlyingType(ppt.PropertyType) Is Nothing, _
                                       ppt.PropertyType, Nullable.GetUnderlyingType(ppt.PropertyType))) _
                   Select ppt)

    Dim NewList As New List(Of T)
    For Each drSourceItem As DataRow In dt.Rows
        Dim NewT As New T()
        For Each entityItem As PropertyInfo In pptList
            entityItem.SetValue(NewT, If(IsDBNull(drSourceItem(entityItem.Name)), Nothing, drSourceItem(entityItem.Name)), Nothing)
        Next
        NewList.Add(NewT)
    Next

    Return NewList
End Function

Example

Sub Main()
Dim dt As New DataTable
dt.Columns.AddRange(New DataColumn() {New DataColumn("ColName", GetType(System.String)), _
                                      New DataColumn("ColDataType", GetType(System.String)), _
                                      New DataColumn("ColType", GetType(System.String)), _
                                      New DataColumn("IntCol", GetType(System.Int32)), _
                                      New DataColumn("DateCol", GetType(System.DateTime)) _
                                      })
dt.Rows.Add("Leo", "DataType", "IntType", 50, Today)
dt.Rows.Add("Rose", "StrDataType", "StrType", 90, Now)
dt.Rows.Add("Alice", "StrDataType", DBNull.Value, 150, DateAdd(DateInterval.Day, 55, Today))
dt.Rows.Add("Emy", "StrDataType", "StrType", DBNull.Value, DateAdd(DateInterval.Day, 55, Today))
dt.Rows.Add("Amy", "StrDataType", "StrType", 150, DBNull.Value)

Dim rtnList = dt.CopyToEntityList(Of test)()

For Each item In rtnList
    Console.WriteLine(String.Format("ColName = {0}, ColDataType = {1}, ColType = {2}, IntCol = {3}, DateCol = {4}", _
                                    item.ColName, item.ColDataType, item.ColType, item.IntCol, item.DateCol))
Next

Console.ReadLine()
    End Sub

''' Test is test class, have 5 properties, 2 properties allow nullable, like below: 
Private _ColName As String
Private _ColDataType As String
Private _ColType As String
Private _IntCol As Integer?
Private _DateCol As DateTime?

Author: Leo Shih

Submitted on: 5 apr 2012

Language: VB

Type: System.Data.DataTable

Views: 3515