Results 1 to 3 of 3

Thread: IEnumerable to DataTable

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    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
    Last edited by dday9; Mar 10th, 2023 at 06:01 PM.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: IEnumerable to DataTable

    Code updated to include support for nullable data types.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: IEnumerable to DataTable

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width