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


Copy DataTable rows to IEnumerable(Of T)


<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)

    Return NewList
End Function


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))

    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: 4646