locked
Datatable to Class objects? RRS feed

  • Question

  • Hallo,

    I am trying to convert data table to class objects . I have a following code which works for commented data table but it does not work for the data table created from the XML import why is that?

    XML

    <?xml version="1.0" encoding="UTF-8"?>
    
    -<dataroot generated="2020-04-20T16:36:48" xmlns:od="urn:schemas-microsoft-com:officedata">
    
    
    -<Tb1>
    
    <Name>Name1</Name>
    
    <Address>Address1</Address>
    
    <City>City1</City>
    
    <Zip>Zip1</Zip>
    
    </Tb1>
    
    
    -<Tb1>
    
    <Name>Name2</Name>
    
    <Address>Address2</Address>
    
    <City>City2</City>
    
    <Zip>Zip2</Zip>
    
    </Tb1>
    
    
    -<Tb1>
    
    <Name>Name3</Name>
    
    <Address>Address3</Address>
    
    <City>City3</City>
    
    <Zip>Zip3</Zip>
    
    </Tb1>
    
    
    -<Tb1>
    
    <Name>Name4</Name>
    
    <Address>Address4</Address>
    
    <City>City4</City>
    
    <Zip>Zip4</Zip>
    
    </Tb1>
    
    </dataroot>
    Imports System.IO
    Imports System.Reflection
    Public Class Form1
        Public XML_DS As New DataSet
        Public DS As New DataSet
        Public dt As New DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Call ImportXMLs()
    
            dt = XML_DS.Tables("Tb1")
    
            'dt.Columns.Add(New DataColumn("Name"))
            'dt.Columns.Add(New DataColumn("Address"))
            'dt.Columns.Add(New DataColumn("City"))
            'dt.Columns.Add(New DataColumn("Zipcode"))
    
            'dt.Rows.Add("Name1", "Address1", "City1", "Zipcode1")
            'dt.Rows.Add("Name2", "Address2", "City2", "Zipcode2")
            'dt.Rows.Add("Name3", "Address3", "City3", "Zipcode3")
            'dt.Rows.Add("Name4", "Address4", "City4", "Zipcode4")
    
    
        End Sub
    
        Public Class Equipment
            Public Property Name As String
            Public Property Address As String
            Public Property City As String
            Public Property Zipcode As Integer
        End Class
    
        Public Shared Function ConvertDT2List(Of T)(tabel As DataTable) As IList(Of T)
    
            If tabel Is Nothing Then
                Return Nothing
            End If
    
            Dim lines As New List(Of DataRow)()
            For Each row As DataRow In tabel.Rows
                lines.Add(row)
            Next
    
            Return ConvertDataRow2List(Of T)(lines)
    
        End Function
    
        Public Shared Function ConvertDataRow2List(Of T)(lines As IList(Of DataRow)) As IList(Of T)
            Dim list As IList(Of T) = Nothing
            If lines IsNot Nothing Then
                list = New List(Of T)()
                For Each line As DataRow In lines
                    Dim item As T = CreateItem(Of T)(line)
                    list.Add(item)
                Next
            End If
            Return list
        End Function
        Public Shared Function CreateItem(Of T)(row As DataRow) As T
    
            Dim ColName As String
            Dim [object] As T = Nothing
    
            If row IsNot Nothing Then
                [object] = Activator.CreateInstance(Of T)()
    
                For Each column As DataColumn In row.Table.Columns
                    ColName = column.ColumnName
    
                    Dim prop As PropertyInfo = [object].[GetType]().GetProperty(ColName)
                    Try
                        Dim value As Object = If((row(ColName).[GetType]() = GetType(DBNull)), Nothing, row(ColName))
                        prop.SetValue([object], value, Nothing)
                    Catch
                        Throw
                    End Try
    
                Next
    
            End If
    
            Return [object]
        End Function
    
        Sub ImportXMLs()
    
            Dim XMLPath As ArrayList = New ArrayList
            Dim root As String = "C:\Temp\xml"  '//Folder Name in Project folder
    
            For Each filePath As String In Directory.EnumerateFiles(root)
                XMLPath.Add(filePath.ToString)
            Next
    
            If XMLPath.Count > 0 Then
    
                Dim tempDS() As DataSet = New DataSet((XMLPath.Count) - 1) {}
                Dim impFiles As Integer = 0
    
                For Each ob As Object In XMLPath
                    Dim impDS As DataSet = New DataSet
                    impDS.ReadXml(ob.ToString)
                    tempDS(impFiles) = impDS
                    impFiles = (impFiles + 1)
                Next
    
                For Each DS As DataSet In tempDS
                    For Each table As DataTable In DS.Tables
                        XML_DS.Merge(table)
                    Next
                Next
    
                XMLPath = Nothing
                tempDS = Nothing
    
            Else
    
                MessageBox.Show("No XML files were found!")
    
            End If
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim EQlist As IList(Of Equipment) = ConvertDT2List(Of Equipment)(dt)
            DataGridView1.DataSource = EQlist
    
        End Sub
    
    End Class
    

    or any other simple method to do?

    Monday, April 20, 2020 2:41 PM

Answers

  • Shan, 

    Somehow many want to do what you want. But a DataTable is a Class.

    Which probably implements all the things you need. 

    https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?view=netframework-4.8

    In fact if the XML file has also a description part you can make strongly named datasets from it. 

    Which means that you simply can use as 

    MyDataTable.ClientField (names are then automatic generated)

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/generating-strongly-typed-datasets


    Success
    Cor

    • Marked as answer by Shan1986 Monday, April 20, 2020 6:25 PM
    Monday, April 20, 2020 5:29 PM
  • See if this might work for you, the DataTable in this example is there but not setup

    Imports System.Reflection
    
    Public Class Form1
        Private someTable As New DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim equipmentList As List(Of Equipment) =
                    someTable.DataTableToList(Of Equipment)
    
        End Sub
    End Class
    ''' <summary>
    ''' Place in a class file
    ''' </summary>
    Public Class Equipment
        Public Property Name As String
        Public Property Address As String
        Public Property City As String
        Public Property Zipcode As Integer
    End Class
    ''' <summary>
    ''' Place in a code module file
    ''' </summary>
    Public Module LanguageExtensions
        ''' <summary>
        ''' Converts a DataTable to a list with generic objects
        ''' </summary>
        ''' <typeparam name="T">Generic object</typeparam>
        ''' <param name="table">DataTable</param>
        ''' <returns>List with generic objects</returns>
        <Runtime.CompilerServices.Extension>
        Public Function DataTableToList(Of T As {Class, New})(table As DataTable) As List(Of T)
            Try
                Dim list As New List(Of T)()
    
                For Each row In table.AsEnumerable()
                    Dim container As New T()
    
                    For Each prop In container.GetType().GetProperties()
                        Try
                            Dim propertyInfo As PropertyInfo = container.GetType().GetProperty(prop.Name)
                            propertyInfo.SetValue(container,
                                                  Convert.ChangeType(row(prop.Name),
                                                                     propertyInfo.PropertyType),
                                                  Nothing)
                        Catch
                            Continue For
                        End Try
                    Next prop
    
                    list.Add(container)
                Next row
    
                Return list
            Catch
                Return Nothing ' or Throw
            End Try
        End Function
    End Module


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Shan1986 Monday, April 20, 2020 6:25 PM
    Monday, April 20, 2020 5:52 PM

All replies

  • Shan, 

    Somehow many want to do what you want. But a DataTable is a Class.

    Which probably implements all the things you need. 

    https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?view=netframework-4.8

    In fact if the XML file has also a description part you can make strongly named datasets from it. 

    Which means that you simply can use as 

    MyDataTable.ClientField (names are then automatic generated)

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/generating-strongly-typed-datasets


    Success
    Cor

    • Marked as answer by Shan1986 Monday, April 20, 2020 6:25 PM
    Monday, April 20, 2020 5:29 PM
  • See if this might work for you, the DataTable in this example is there but not setup

    Imports System.Reflection
    
    Public Class Form1
        Private someTable As New DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim equipmentList As List(Of Equipment) =
                    someTable.DataTableToList(Of Equipment)
    
        End Sub
    End Class
    ''' <summary>
    ''' Place in a class file
    ''' </summary>
    Public Class Equipment
        Public Property Name As String
        Public Property Address As String
        Public Property City As String
        Public Property Zipcode As Integer
    End Class
    ''' <summary>
    ''' Place in a code module file
    ''' </summary>
    Public Module LanguageExtensions
        ''' <summary>
        ''' Converts a DataTable to a list with generic objects
        ''' </summary>
        ''' <typeparam name="T">Generic object</typeparam>
        ''' <param name="table">DataTable</param>
        ''' <returns>List with generic objects</returns>
        <Runtime.CompilerServices.Extension>
        Public Function DataTableToList(Of T As {Class, New})(table As DataTable) As List(Of T)
            Try
                Dim list As New List(Of T)()
    
                For Each row In table.AsEnumerable()
                    Dim container As New T()
    
                    For Each prop In container.GetType().GetProperties()
                        Try
                            Dim propertyInfo As PropertyInfo = container.GetType().GetProperty(prop.Name)
                            propertyInfo.SetValue(container,
                                                  Convert.ChangeType(row(prop.Name),
                                                                     propertyInfo.PropertyType),
                                                  Nothing)
                        Catch
                            Continue For
                        End Try
                    Next prop
    
                    list.Add(container)
                Next row
    
                Return list
            Catch
                Return Nothing ' or Throw
            End Try
        End Function
    End Module


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Shan1986 Monday, April 20, 2020 6:25 PM
    Monday, April 20, 2020 5:52 PM