8

I have read many posts on this topic; among them and most recently .NET - Convert Generic Collection to Data Table. Unfortunately, all to no avail.

I have a generic collection of structures :

Private Structure MyStruct
Dim sState as String
Dim lValue as Long
Dim iLayer as Integer
End Structure

Dim LOStates As New List(Of MyStruct)

I need to fill a DataTable with this list of structures but have no idea how to go about doing this. I am using vb.net in Visual Studio 2008.

Any insights will be greatly appreciated

Community
  • 1
  • 1
8thWonder
  • 171
  • 1
  • 3
  • 16

3 Answers3

20

The code you linked assumes the members are declared as properties. You didn't declare properties. You can make it work with Reflection:

Imports System.Reflection
...

      Public Shared Function ConvertToDataTable(Of T)(ByVal list As IList(Of T)) As DataTable
        Dim table As New DataTable()
        Dim fields() As FieldInfo = GetType(T).GetFields()
        For Each field As FieldInfo In fields
          table.Columns.Add(field.Name, field.FieldType)
        Next
        For Each item As T In list
          Dim row As DataRow = table.NewRow()
          For Each field As FieldInfo In fields
            row(field.Name) = field.GetValue(item)
          Next
          table.Rows.Add(row)
        Next
        Return table
      End Function
Hans Passant
  • 922,412
  • 146
  • 1,693
  • 2,536
  • nobugz, thank you for such a prompt response! I added this function to my class, then passed it the list of structures (oTable = ConvertToDataTable(LOStates)) but no rows were retruned - the table count = 0 before being returned to where it was called from. I'm wondering if there's something else that I'm missing or doing incorrectly... – 8thWonder Nov 27 '09 at 16:57
  • Debug it. Do the for each loops loop? Does the table have any columns? – Hans Passant Nov 27 '09 at 17:03
  • It was in debug that I was able to determine that the table count = 0. The for each loops loop indeed. There are 3 coumns but 0 rows right before the return table statement is executed. – 8thWonder Nov 27 '09 at 18:36
  • I noticed that as soon as I finished typing my last response so I included the add statement. BUT after adding the first row to the DataTable, it craps out citing a "System.Reflection.TargetInvocationException" befor going on to add any more rows to teh DataTable. – 8thWonder Nov 27 '09 at 19:05
  • Sorry for being such a pain in the tail! I really appreciate your help and patience :) – 8thWonder Nov 27 '09 at 19:06
  • Oh nvm, I effed up w/ the declarations! It's all good. You're the man nobugz! – 8thWonder Nov 27 '09 at 19:12
  • @HansPassant `GetType(T).GetFields()` returns an empty set for me, I had to use `GetProperties` which doesn't return a type castable to `FieldInfo`. I'm using ADO Entity models. – Sam Selikoff Nov 25 '13 at 15:28
  • How to pass the list to this function in vb.net – Amarnath Balasubramanian Apr 04 '14 at 05:51
  • @SamSelikoff, snipped translated to GetProperties http://stackoverflow.com/a/24739862/842935 – dani herrera Jul 14 '14 at 15:17
8

I have same issue than @SamSelikoff, moved to GetProperties:

Public Shared Function ConvertToDataTable(Of t)(
                                                  ByVal list As IList(Of t)
                                               ) As DataTable
    Dim table As New DataTable()
    If Not list.Any Then
        'don't know schema ....
        Return table
    End If
    Dim fields() = list.First.GetType.GetProperties
    For Each field In fields
        table.Columns.Add(field.Name, field.PropertyType)
    Next
    For Each item In list
        Dim row As DataRow = table.NewRow()
        For Each field In fields
            dim p = item.GetType.GetProperty(field.Name)
            row(field.Name) = p.GetValue(item, Nothing)
        Next
        table.Rows.Add(row)
    Next
    Return table
End Function
dani herrera
  • 48,760
  • 8
  • 117
  • 177
0

Following @Hans Passant function if anyone is dealing with nullable types:

For Each field As FieldInfo In fields
' Extra check for nullable
If field.FieldType.AssemblyQualifiedName.Contains("System.Nullable") Then
    ' Insert proper type
    If field.FieldType.AssemblyQualifiedName.Contains("System.DateTime") Then
       table.Columns.Add(field.Name, Type.GetType("System.DateTime"))
    End If
Else
    table.Columns.Add(field.Name, field.FieldType)
End If
Next

Values:

For Each item As T In list
Dim row As DataRow = table.NewRow()
For Each field As FieldInfo In fields
    ' Check if value is null
    If field.GetValue(item) is nothing Then
        Continue For
    End If
    row(field.Name) = field.GetValue(item)
Next
table.Rows.Add(row)
Next
Azelski
  • 155
  • 1
  • 2
  • 10