IEnumerable to DataTable

The following is an extension method (documentation) and custom attribute (documentation) that will take an IEnumerable(Of T) and convert it to a DataTable.

Extension Method:

Code:

---

Imports System.Reflection
Imports System.Runtime.CompilerServices
Module DataTableExtensionModule

    <Extension()>
    Public Function ToDataTable(Of T)(items As IEnumerable(Of T)) As DataTable
        Dim dataTableConverted = New DataTable(GetType(T).Name)
        Dim enumerableProperties = GetType(T).
            GetProperties(BindingFlags.Public Or BindingFlags.Instance).
            Where(Function(prop)
                      Dim dataTableIgnore = prop.GetCustomAttribute(Of DataTableIgnoreAttribute)
                      Return dataTableIgnore Is Nothing OrElse Not dataTableIgnore.IgnoreColumn
                  End Function).
            ToArray()

        For Each prop In enumerableProperties
            Dim propertyType = If(Nullable.GetUnderlyingType(prop.PropertyType), prop.PropertyType)
            dataTableConverted.Columns.Add(prop.Name, propertyType)
        Next

        For Each item In items
            Dim values = New Object(enumerableProperties.Length - 1) {}

            For i = 0 To enumerableProperties.Length - 1
                values(i) = enumerableProperties(i).GetValue(item, Nothing)
            Next

            dataTableConverted.Rows.Add(values)
        Next

        Return dataTableConverted
    End Function

End Module

---

Custom Attribute

Code:

---

Public Class DataTableIgnoreAttribute
    Inherits Attribute

    Public Property IgnoreColumn As Boolean

    Sub New()
        IgnoreColumn = True
    End Sub

End Class

---

Usage

Code:

---

Public Module Module1
    Public Sub Main()
        Dim users = { New UserViewModel() With { .Username = "dday9" } }
        Dim auditLogs = { New AuditLogViewModel() With {.User = users.First(), .Description = "Foo"}, New AuditLogViewModel() With {.Description = "Bar"} }
        Dim dt = auditLogs.ToDataTable()
        Console.WriteLine(dt.ToPrettyPrintedString())
    End Sub
End Module

Public Class UserViewModel
    Public Property Username As String
End Class

Public Class AuditLogViewModel
    <DataTableIgnoreAttribute> Public Property User As UserViewModel
    Public ReadOnly Property Username As String
        Get
            If (User Is Nothing) Then
                Return String.Empty
            End If
            Return User.Username
        End Get
    End Property
    Public Property Description As String
End Class

---

Fiddle: https://dotnetfiddle.net/c4HLt6

Code updated to include support for nullable data types.

Nice.
I have done something similar for a list(of t) a couple of years ago. Might worth putting it here:

Code:

---

Imports System.ComponentModel
Imports System.Reflection

Public Module BulkExtentions
    <System.Runtime.CompilerServices.Extension()>
    Public Function ToDataTable(Of T)(data As IEnumerable(Of T)) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table = New DataTable()
        For Each prop As PropertyDescriptor In properties
            table.Columns.Add(prop.Name, If(Nullable.GetUnderlyingType(prop.PropertyType), prop.PropertyType))
        Next
        For Each item As T In data
            Dim row As DataRow = table.NewRow()
            For Each prop As PropertyDescriptor In properties
                row(prop.Name) = If(prop.GetValue(item), DBNull.Value)
            Next
            table.Rows.Add(row)
        Next
        Return table
    End Function

    <System.Runtime.CompilerServices.Extension()>
    Public Function ConvertDataTableToList(Of T)(ByVal dt As DataTable) As List(Of T)
        Dim data As List(Of T) = New List(Of T)()

        For Each row As DataRow In dt.Rows
            Dim item As T = GetItem(Of T)(row)
            data.Add(item)
        Next

        Return data
    End Function

    Private Function GetItem(Of T)(ByVal dr As DataRow) As T

        Dim temp As Type = GetType(T)
        Dim obj As T = Activator.CreateInstance(Of T)()

        For Each column As DataColumn In dr.Table.Columns

            For Each pro As PropertyInfo In temp.GetProperties()

                If pro.Name.ToLower() = column.ColumnName.ToLower() Then
                    pro.SetValue(obj, dr(column.ColumnName), Nothing)
                Else
                    Continue For
                End If
            Next
        Next

        Return obj
    End Function

End Module

Public NotInheritable Class IEnumerableExtensions
    Private Sub New()
    End Sub
End Class

---