VB.NET || How To Convert An Array/List/IEnumerable To A DataTable & Convert A DataTable To A List Using VB.NET

November 13, 2020 admin No comments

The following is a module with functions which demonstrates how to convert an Array/List/IEnumerable to a DataTable, and how to convert a DataTable to a List using VB.NET.

Contents

1. Overview 2. Convert To DataTable - Integer Array 3. Convert To DataTable - List Of Strings 4. Convert To DataTable - List Of Objects 5. Convert To List - Integer DataTable 6. Convert To List - String DataTable 7. Convert To List - Multi-Column DataTable 8. Utils Namespace 9. More Examples

1. Overview

The functions demonstrated on this page are generic extension methods which uses reflection to convert a DataTable to a List(Of T) and an Array/List/IEnumerable to a DataTable.

These functions work on primitive data types, custom structure and class object fields and properties, as well as DBNull.Value, and nullable arrays and lists.

Note: Don’t forget to include the ‘Utils Namespace‘ before running the examples!

2. Convert To DataTable – Integer Array

The example below demonstrates the use of ‘Utils.Collections.ToDataTable‘ to convert a nullable integer array to a datatable.

When converting nullable types, their value is represented as DBNull in the returned table.





		
		
			2. Convert To DataTable - Integer Array
			
Visual Basic .NET
			
			' Convert To DataTable - Integer Array

Imports Utils.Collections

' Declare array of integers 
Dim numbers = New Integer?() {Nothing, 1987, 19, 22, 2009, 2019, 1991, 28, 31}

' Convert array to a datatable
Dim tableNumbers = numbers.ToDataTable

' For primitive types, access data using the 'value' key
For Each row As DataRow In tableNumbers.Rows
    Debug.Print($"{If(IsDBNull(row("value")), "[DBNull.Value]", row("value"))}")
Next

' expected output:
'    [DBNull.Value]
'    1987
'    19
'    22
'    2009
'    2019
'    1991
'    28
'    31
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
				
						' Convert To DataTable - Integer Array
 
Imports Utils.Collections
 
' Declare array of integers 
Dim numbers = New Integer?() {Nothing, 1987, 19, 22, 2009, 2019, 1991, 28, 31}
 
' Convert array to a datatable
Dim tableNumbers = numbers.ToDataTable
 
' For primitive types, access data using the 'value' key
For Each row As DataRow In tableNumbers.Rows
    Debug.Print($"{If(IsDBNull(row("value")), "[DBNull.Value]", row("value"))}")
Next
 
' expected output:
'    [DBNull.Value]
'    1987
'    19
'    22
'    2009
'    2019
'    1991
'    28
'    31

3. Convert To DataTable – List Of Strings

The example below demonstrates the use of ‘Utils.Collections.ToDataTable‘ to convert a list of strings to a datatable.

When converting nullable types, their value is represented as DBNull in the returned table.





		
		
			3. Convert To DataTable - List Of Strings
			
Visual Basic .NET
			
			' Convert To DataTable - List Of Strings

Imports Utils.Collections

' Declare list of strings
Dim names = New List(Of String) From {
    "Kenneth",
    "Jennifer",
    "Lynn",
    "Sole",
    Nothing
}

' Convert list to a datatable
Dim tableNames = names.ToDataTable

' For primitive types, access data using the 'value' key
For Each row As DataRow In tableNames.Rows
    Debug.Print($"{If(IsDBNull(row("value")), "[DBNull.Value]", row("value"))}")
Next

' expected output:
'    Kenneth
'    Jennifer
'    Lynn
'    Sole
'    [DBNull.Value]
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
				
						' Convert To DataTable - List Of Strings
 
Imports Utils.Collections
 
' Declare list of strings
Dim names = New List(Of String) From {
    "Kenneth",
    "Jennifer",
    "Lynn",
    "Sole",
    Nothing
}
 
' Convert list to a datatable
Dim tableNames = names.ToDataTable
 
' For primitive types, access data using the 'value' key
For Each row As DataRow In tableNames.Rows
    Debug.Print($"{If(IsDBNull(row("value")), "[DBNull.Value]", row("value"))}")
Next
 
' expected output:
'    Kenneth
'    Jennifer
'    Lynn
'    Sole
'    [DBNull.Value]

4. Convert To DataTable – List Of Objects

The example below demonstrates the use of ‘Utils.Collections.ToDataTable‘ to convert a list of custom objects to a datatable.

A class is used in this example, but a custom structure also works here as well.

When converting nullable types, their value is represented as DBNull in the returned table.





		
		
			4. Convert To DataTable - List Of Objects
			
Visual Basic .NET
			
			' Convert To DataTable - List Of Objects

Imports Utils.Collections

Public Class Part
    Public Property PartName As String
    Public PartId As Integer?
End Class

' Declare list of objects
Dim parts = New List(Of Part) From {
    New Part With {
     .PartName = "crank arm",
     .PartId = 1234
},
    New Part With {
     .PartName = "chain ring",
     .PartId = 1334
},
    New Part With {
     .PartName = "regular seat",
     .PartId = 1434
},
    New Part With {
     .PartName = "banana seat",
     .PartId = 1444
},
    New Part With {
     .PartName = "cassette",
     .PartId = 1534
},
    New Part With {
     .PartName = "shift lever",
     .PartId = 1634
},
    New Part With {
     .PartName = Nothing,
     .PartId = Nothing
}}

' Convert list to a datatable
Dim tableParts = parts.ToDataTable

' For non primitive types, access data using the class property names
For Each row As DataRow In tableParts.Rows
    Debug.Print($"{If(IsDBNull(row("PartId")), "[DBNull.Value]", row("PartId"))} - {If(IsDBNull(row("PartName")), "[DBNull.Value]", row("PartName"))}")
Next    

' expected output:
'    1234 - crank arm
'    1334 - chain ring
'    1434 - regular seat
'    1444 - banana seat
'    1534 - cassette
'    1634 - shift lever
'    [DBNull.Value] - [DBNull.Value]
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
				
						' Convert To DataTable - List Of Objects
 
Imports Utils.Collections
 
Public Class Part
    Public Property PartName As String
    Public PartId As Integer?
End Class
 
' Declare list of objects
Dim parts = New List(Of Part) From {
    New Part With {
     .PartName = "crank arm",
     .PartId = 1234
},
    New Part With {
     .PartName = "chain ring",
     .PartId = 1334
},
    New Part With {
     .PartName = "regular seat",
     .PartId = 1434
},
    New Part With {
     .PartName = "banana seat",
     .PartId = 1444
},
    New Part With {
     .PartName = "cassette",
     .PartId = 1534
},
    New Part With {
     .PartName = "shift lever",
     .PartId = 1634
},
    New Part With {
     .PartName = Nothing,
     .PartId = Nothing
}}
 
' Convert list to a datatable
Dim tableParts = parts.ToDataTable
 
' For non primitive types, access data using the class property names
For Each row As DataRow In tableParts.Rows
    Debug.Print($"{If(IsDBNull(row("PartId")), "[DBNull.Value]", row("PartId"))} - {If(IsDBNull(row("PartName")), "[DBNull.Value]", row("PartName"))}")
Next    
 
' expected output:
'    1234 - crank arm
'    1334 - chain ring
'    1434 - regular seat
'    1444 - banana seat
'    1534 - cassette
'    1634 - shift lever
'    [DBNull.Value] - [DBNull.Value]

5. Convert To List – Integer DataTable

The example below demonstrates the use of ‘Utils.Collections.ToList‘ to convert a datatable to a List(Of Integer?).

When converting DBNull types, their value is represented as ‘Nothing‘ in the returned list.





		
		
			5. Convert To List - Integer DataTable
			
Visual Basic .NET
			
			' Convert To List - Integer DataTable

Imports Utils.Collections

' Declare datatable of integers 
Dim tableNumbers = New DataTable
tableNumbers.Columns.Add("Number", GetType(Integer))

tableNumbers.Rows.Add(System.DBNull.Value)
tableNumbers.Rows.Add(1987)
tableNumbers.Rows.Add(19)
tableNumbers.Rows.Add(22)
tableNumbers.Rows.Add(2009)
tableNumbers.Rows.Add(2019)
tableNumbers.Rows.Add(1991)
tableNumbers.Rows.Add(28)
tableNumbers.Rows.Add(31)

' Convert datatable to a list.
' When converting to a primitive type, only the first column is returned
Dim listNumbers = tableNumbers.ToList(Of Integer?)

' Loop through the items in the returned list
For Each item In listNumbers
    Debug.Print($"{If(item Is Nothing, "[Nothing]", item.ToString)}")
Next

' expected output:
'    [Nothing]
'    1987
'    19
'    22
'    2009
'    2019
'    1991
'    28
'    31
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
				
						' Convert To List - Integer DataTable
 
Imports Utils.Collections
 
' Declare datatable of integers 
Dim tableNumbers = New DataTable
tableNumbers.Columns.Add("Number", GetType(Integer))
 
tableNumbers.Rows.Add(System.DBNull.Value)
tableNumbers.Rows.Add(1987)
tableNumbers.Rows.Add(19)
tableNumbers.Rows.Add(22)
tableNumbers.Rows.Add(2009)
tableNumbers.Rows.Add(2019)
tableNumbers.Rows.Add(1991)
tableNumbers.Rows.Add(28)
tableNumbers.Rows.Add(31)
 
' Convert datatable to a list.
' When converting to a primitive type, only the first column is returned
Dim listNumbers = tableNumbers.ToList(Of Integer?)
 
' Loop through the items in the returned list
For Each item In listNumbers
    Debug.Print($"{If(item Is Nothing, "[Nothing]", item.ToString)}")
Next
 
' expected output:
'    [Nothing]
'    1987
'    19
'    22
'    2009
'    2019
'    1991
'    28
'    31

6. Convert To List – String DataTable

The example below demonstrates the use of ‘Utils.Collections.ToList‘ to convert a datatable to a List(Of String).

When converting DBNull types, their value is represented as ‘Nothing‘ in the returned list.





		
		
			6. Convert To List - String DataTable
			
Visual Basic .NET
			
			' Convert To List - String DataTable

Imports Utils.Collections

' Declare datatable of strings
Dim tableNames = New DataTable
tableNames.Columns.Add("Name", GetType(String))

tableNames.Rows.Add("Kenneth")
tableNames.Rows.Add("Jennifer")
tableNames.Rows.Add("Lynn")
tableNames.Rows.Add("Sole")
tableNames.Rows.Add(System.DBNull.Value)

' Convert datatable to a list.
' When converting to a primitive type, only the first column is returned
Dim listNames = tableNames.ToList(Of String)

' Loop through the items in the returned list
For Each item In listNames
    Debug.Print($"{If(item Is Nothing, "[Nothing]", item.ToString)}")
Next

' expected output:
'    Kenneth
'    Jennifer
'    Lynn
'    Sole
'    [Nothing]
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
				
						' Convert To List - String DataTable
 
Imports Utils.Collections
 
' Declare datatable of strings
Dim tableNames = New DataTable
tableNames.Columns.Add("Name", GetType(String))
 
tableNames.Rows.Add("Kenneth")
tableNames.Rows.Add("Jennifer")
tableNames.Rows.Add("Lynn")
tableNames.Rows.Add("Sole")
tableNames.Rows.Add(System.DBNull.Value)
 
' Convert datatable to a list.
' When converting to a primitive type, only the first column is returned
Dim listNames = tableNames.ToList(Of String)
 
' Loop through the items in the returned list
For Each item In listNames
    Debug.Print($"{If(item Is Nothing, "[Nothing]", item.ToString)}")
Next
 
' expected output:
'    Kenneth
'    Jennifer
'    Lynn
'    Sole
'    [Nothing]

7. Convert To List – Multi-Column DataTable

The example below demonstrates the use of ‘Utils.Collections.ToList‘ to convert a datatable to a List(Of Object).

A class is used in this example, but a custom structure also works here as well.

When converting DBNull types, their value is represented as ‘Nothing‘ in the returned list.





		
		
			7. Convert To List - Multi-Column DataTable
			
Visual Basic .NET
			
			' Convert To List - Multi-Column DataTable

Imports Utils.Collections

Public Class Part
    Public Property PartName As String
    Public PartId As Integer?
End Class

' Declare multi column datatable
Dim tableParts = New DataTable
tableParts.Columns.Add("PartName", GetType(String))
tableParts.Columns.Add("PartId", GetType(Integer))

tableParts.Rows.Add({"crank arm", 1234})
tableParts.Rows.Add({"chain ring", 1334})
tableParts.Rows.Add({"regular seat", 1434})
tableParts.Rows.Add({"banana seat", 1444})
tableParts.Rows.Add({"cassette", 1534})
tableParts.Rows.Add({"shift lever", 1634})
tableParts.Rows.Add({System.DBNull.Value, System.DBNull.Value})

' Convert datatable to a list.
' When converting to an object, all matching columns are returned
Dim listParts = tableParts.ToList(Of Part)

' Access properties like normal
For Each item In listParts
    Debug.Print($"{If(item.PartId Is Nothing, "[Nothing]", item.PartId.ToString)} - {If(item.PartName Is Nothing, "[Nothing]", item.PartName.ToString)}")
Next

' expected output:
'    1234 - crank arm
'    1334 - chain ring
'    1434 - regular seat
'    1444 - banana seat
'    1534 - cassette
'    1634 - shift lever
'    [Nothing] - [Nothing]
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
				
						' Convert To List - Multi-Column DataTable
 
Imports Utils.Collections
 
Public Class Part
    Public Property PartName As String
    Public PartId As Integer?
End Class
 
' Declare multi column datatable
Dim tableParts = New DataTable
tableParts.Columns.Add("PartName", GetType(String))
tableParts.Columns.Add("PartId", GetType(Integer))
 
tableParts.Rows.Add({"crank arm", 1234})
tableParts.Rows.Add({"chain ring", 1334})
tableParts.Rows.Add({"regular seat", 1434})
tableParts.Rows.Add({"banana seat", 1444})
tableParts.Rows.Add({"cassette", 1534})
tableParts.Rows.Add({"shift lever", 1634})
tableParts.Rows.Add({System.DBNull.Value, System.DBNull.Value})
 
' Convert datatable to a list.
' When converting to an object, all matching columns are returned
Dim listParts = tableParts.ToList(Of Part)
 
' Access properties like normal
For Each item In listParts
    Debug.Print($"{If(item.PartId Is Nothing, "[Nothing]", item.PartId.ToString)} - {If(item.PartName Is Nothing, "[Nothing]", item.PartName.ToString)}")
Next
 
' expected output:
'    1234 - crank arm
'    1334 - chain ring
'    1434 - regular seat
'    1444 - banana seat
'    1534 - cassette
'    1634 - shift lever
'    [Nothing] - [Nothing]

8. Utils Namespace

The following is the Utils Namespace. Include this in your project to start using!





		
		
			Utils Namespace
			
Visual Basic .NET
			
			' ============================================================================
'    Author: Kenneth Perkins
'    Date:   Nov 13, 2020
'    Taken From: http://programmingnotes.org/
'    File: Utils.vb
'    Description: Handles general utility functions
' ============================================================================
Option Strict On
Option Explicit On

Namespace Global.Utils
    Namespace Collections
        Public Module modCollections
            ''' <summary>
            ''' Returns a List(Of T) of the items contained in the DataTable
            ''' </summary>
            ''' <param name="source">The DataTable to convert to a List(Of T)</param> 
            ''' <returns>A List(Of T) with the contents of the DataTable</returns>
            <Runtime.CompilerServices.Extension()>
            Public Function ToList(Of T)(source As DataTable) As List(Of T)
                Dim type = GetType(T)
                Dim list = New List(Of T)
                Dim isPrimitive = IsPrimitiveType(type)
                Dim members = New List(Of System.Reflection.MemberInfo)

                If Not isPrimitive Then
                    members.AddRange(type.GetProperties)
                    members.AddRange(type.GetFields)
                End If

                ' Convert data rows to the object
                For Each row As DataRow In source.Rows
                    Dim obj = CreateObject(Of T)(isPrimitive, row, members)
                    list.Add(obj)
                Next
                Return list
            End Function

            ''' <summary>
            ''' Returns a DataTable of the items contained in the IEnumerable(Of T)
            ''' </summary>
            ''' <param name="source">The IEnumerable list to convert to a DataTable</param> 
            ''' <returns>A DataTable with the contents of the IEnumerable(Of T)</returns>
            <Runtime.CompilerServices.Extension()>
            Public Function ToDataTable(Of T)(source As IEnumerable(Of T)) As DataTable
                Dim type = GetType(T)
                Dim table = New DataTable(type.Name)
                Dim isPrimitive = IsPrimitiveType(type)
                Dim primitiveColumnName = "value"
                Dim members = New List(Of System.Reflection.MemberInfo)

                If Not isPrimitive Then
                    Dim flags = System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.Public
                    members.AddRange(type.GetProperties(flags))
                    members.AddRange(type.GetFields(flags))
                End If

                ' Build the header rows
                BuildColumns(Of T)(isPrimitive, primitiveColumnName, table, members)

                ' Add the data rows
                For Each item In source
                    Dim row = CreateRow(isPrimitive, primitiveColumnName, item, table, members)
                    table.Rows.Add(row)
                Next
                Return table
            End Function

            Private Sub BuildColumns(Of T)(isPrimitive As Boolean,
                                         primitiveColumnName As String,
                                         table As DataTable,
                                         members As IEnumerable(Of System.Reflection.MemberInfo))
                If isPrimitive Then
                    AddColumn(table, primitiveColumnName, GetType(T))
                Else
                    For Each member In members
                        AddColumn(table, member.Name, GetMemberDataType(member))
                    Next
                End If
            End Sub

            Private Sub AddColumn(table As DataTable, columnName As String, type As System.Type)
                table.Columns.Add(columnName, If(IsNullable(type), System.Nullable.GetUnderlyingType(type), type))
            End Sub

            Private Function CreateRow(Of T)(isPrimitive As Boolean,
                                   primitiveColumnName As String,
                                   obj As T,
                                   table As DataTable,
                                   members As IEnumerable(Of System.Reflection.MemberInfo)) As DataRow
                Dim row = table.NewRow
                If isPrimitive Then
                    SetRowValue(row, primitiveColumnName, obj)
                Else
                    For Each member In members
                        Dim value = If(CanRead(member), GetMemberValue(obj, member), Nothing)
                        SetRowValue(row, member.Name, value)
                    Next
                End If
                Return row
            End Function

            Private Sub SetRowValue(row As DataRow, columnName As String, value As Object)
                row(columnName) = If(value Is Nothing, System.DBNull.Value, value)
            End Sub

            Private Function CreateObject(Of T)(isPrimitive As Boolean, row As DataRow, members As IEnumerable(Of System.Reflection.MemberInfo)) As T
                Dim obj As T
                If isPrimitive Then
                    SetObjectValue(isPrimitive, obj, row(0))
                Else
                    obj = System.Activator.CreateInstance(Of T)()
                    For Each column As DataColumn In row.Table.Columns
                        Dim member = members.FirstOrDefault(Function(x) x.Name.ToLower = column.ColumnName.ToLower)
                        If member Is Nothing _
                            OrElse Not CanWrite(member) Then
                            Continue For
                        End If
                        Dim value = row(column.ColumnName)
                        SetObjectValue(isPrimitive, obj, value, member)
                    Next
                End If
                Return obj
            End Function

            Private Sub SetObjectValue(Of T)(isPrimitive As Boolean, ByRef obj As T, value As Object, Optional member As System.Reflection.MemberInfo = Nothing)
                If IsDBNull(value) Then
                    Dim type = If(isPrimitive, GetType(T), GetMemberDataType(member))
                    Dim nullValue = If(IsNullable(type), Nothing, GetDefaultValue(type))
                    If isPrimitive Then
                        obj = CType(nullValue, T)
                    Else
                        SetObjectValue(obj, member, nullValue)
                    End If
                Else
                    If isPrimitive Then
                        obj = CType(value, T)
                    Else
                        SetObjectValue(obj, member, value)
                    End If
                End If
            End Sub

            Private Sub SetObjectValue(Of T)(ByRef obj As T, member As System.Reflection.MemberInfo, value As Object)
                ' Boxing method used for modifying structures
                Dim boxed = If(obj.GetType.IsValueType, CType(obj, Object), obj)
                SetMemberValue(boxed, member, value)
                obj = CType(boxed, T)
            End Sub

            Private Sub SetMemberValue(Of T)(ByRef obj As T, member As System.Reflection.MemberInfo, value As Object)
                If IsProperty(member) Then
                    Dim prop = CType(member, System.Reflection.PropertyInfo)
                    If prop.SetMethod IsNot Nothing Then
                        prop.SetValue(obj, value)
                    End If
                ElseIf IsField(member) Then
                    Dim field = CType(member, System.Reflection.FieldInfo)
                    field.SetValue(obj, value)
                End If
            End Sub

            Private Function GetMemberValue(obj As Object, member As System.Reflection.MemberInfo) As Object
                Dim result As Object = Nothing
                If IsProperty(member) Then
                    Dim prop = CType(member, System.Reflection.PropertyInfo)
                    result = prop.GetValue(obj, If(prop.GetIndexParameters.Count = 1, New Object() {Nothing}, Nothing))
                ElseIf IsField(member) Then
                    Dim field = CType(member, System.Reflection.FieldInfo)
                    result = field.GetValue(obj)
                End If
                Return result
            End Function

            Private Function GetDefaultValue(type As System.Type) As Object
                Return Conversion.CTypeDynamic(Nothing, type)
            End Function

            Private Function IsPrimitiveType(type As System.Type) As Boolean
                Return type.IsPrimitive _
                    OrElse type.IsEnum _
                    OrElse (type.IsValueType AndAlso Not IsCustomStructure(type)) _
                    OrElse IsType(type, GetType(String))
            End Function

            Private Function IsCustomStructure(type As System.Type) As Boolean
                Dim result = type.IsValueType AndAlso Not type.IsPrimitive _
                    AndAlso (String.IsNullOrWhiteSpace(type.Namespace) _
                        OrElse Not type.Namespace.StartsWith("System") _
                        OrElse Not type.FullName.StartsWith("System.")) _
                    AndAlso Not System.Reflection.Assembly.GetAssembly(type).Location.ToLower.Contains("microsoft")
                Return result
            End Function

            Private Function IsNullable(type As System.Type) As Boolean
                Return System.Nullable.GetUnderlyingType(type) IsNot Nothing
            End Function

            Private Function GetMemberDataType(member As System.Reflection.MemberInfo) As System.Type
                Dim result As System.Type = Nothing
                If IsProperty(member) Then
                    result = CType(member, System.Reflection.PropertyInfo).PropertyType
                ElseIf IsField(member) Then
                    result = CType(member, System.Reflection.FieldInfo).FieldType
                End If
                Return result
            End Function

            Private Function CanWrite(member As System.Reflection.MemberInfo) As Boolean
                Return If(IsProperty(member), CType(member, System.Reflection.PropertyInfo).CanWrite, IsField(member))
            End Function

            Private Function CanRead(member As System.Reflection.MemberInfo) As Boolean
                Return If(IsProperty(member), CType(member, System.Reflection.PropertyInfo).CanRead, IsField(member))
            End Function

            Private Function IsProperty(member As System.Reflection.MemberInfo) As Boolean
                Return IsType(member.GetType, GetType(System.Reflection.PropertyInfo))
            End Function

            Private Function IsField(member As System.Reflection.MemberInfo) As Boolean
                Return IsType(member.GetType, GetType(System.Reflection.FieldInfo))
            End Function

            Private Function IsType(type As System.Type, targetType As System.Type) As Boolean
                Return type.Equals(targetType) OrElse type.IsSubclassOf(targetType)
            End Function
        End Module
    End Namespace
End Namespace ' http://programmingnotes.org/
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
				
						' ============================================================================
'    Author: Kenneth Perkins
'    Date:   Nov 13, 2020
'    Taken From: http://programmingnotes.org/
'    File: Utils.vb
'    Description: Handles general utility functions
' ============================================================================
Option Strict On
Option Explicit On
 
Namespace Global.Utils
    Namespace Collections
        Public Module modCollections
            ''' <summary>
            ''' Returns a List(Of T) of the items contained in the DataTable
            ''' </summary>
            ''' <param name="source">The DataTable to convert to a List(Of T)</param> 
            ''' <returns>A List(Of T) with the contents of the DataTable</returns>
            <Runtime.CompilerServices.Extension()>
            Public Function ToList(Of T)(source As DataTable) As List(Of T)
                Dim type = GetType(T)
                Dim list = New List(Of T)
                Dim isPrimitive = IsPrimitiveType(type)
                Dim members = New List(Of System.Reflection.MemberInfo)
 
                If Not isPrimitive Then
                    members.AddRange(type.GetProperties)
                    members.AddRange(type.GetFields)
                End If
 
                ' Convert data rows to the object
                For Each row As DataRow In source.Rows
                    Dim obj = CreateObject(Of T)(isPrimitive, row, members)
                    list.Add(obj)
                Next
                Return list
            End Function
 
            ''' <summary>
            ''' Returns a DataTable of the items contained in the IEnumerable(Of T)
            ''' </summary>
            ''' <param name="source">The IEnumerable list to convert to a DataTable</param> 
            ''' <returns>A DataTable with the contents of the IEnumerable(Of T)</returns>
            <Runtime.CompilerServices.Extension()>
            Public Function ToDataTable(Of T)(source As IEnumerable(Of T)) As DataTable
                Dim type = GetType(T)
                Dim table = New DataTable(type.Name)
                Dim isPrimitive = IsPrimitiveType(type)
                Dim primitiveColumnName = "value"
                Dim members = New List(Of System.Reflection.MemberInfo)
 
                If Not isPrimitive Then
                    Dim flags = System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.Public
                    members.AddRange(type.GetProperties(flags))
                    members.AddRange(type.GetFields(flags))
                End If
 
                ' Build the header rows
                BuildColumns(Of T)(isPrimitive, primitiveColumnName, table, members)
 
                ' Add the data rows
                For Each item In source
                    Dim row = CreateRow(isPrimitive, primitiveColumnName, item, table, members)
                    table.Rows.Add(row)
                Next
                Return table
            End Function
 
            Private Sub BuildColumns(Of T)(isPrimitive As Boolean,
                                         primitiveColumnName As String,
                                         table As DataTable,
                                         members As IEnumerable(Of System.Reflection.MemberInfo))
                If isPrimitive Then
                    AddColumn(table, primitiveColumnName, GetType(T))
                Else
                    For Each member In members
                        AddColumn(table, member.Name, GetMemberDataType(member))
                    Next
                End If
            End Sub
 
            Private Sub AddColumn(table As DataTable, columnName As String, type As System.Type)
                table.Columns.Add(columnName, If(IsNullable(type), System.Nullable.GetUnderlyingType(type), type))
            End Sub
 
            Private Function CreateRow(Of T)(isPrimitive As Boolean,
                                   primitiveColumnName As String,
                                   obj As T,
                                   table As DataTable,
                                   members As IEnumerable(Of System.Reflection.MemberInfo)) As DataRow
                Dim row = table.NewRow
                If isPrimitive Then
                    SetRowValue(row, primitiveColumnName, obj)
                Else
                    For Each member In members
                        Dim value = If(CanRead(member), GetMemberValue(obj, member), Nothing)
                        SetRowValue(row, member.Name, value)
                    Next
                End If
                Return row
            End Function
 
            Private Sub SetRowValue(row As DataRow, columnName As String, value As Object)
                row(columnName) = If(value Is Nothing, System.DBNull.Value, value)
            End Sub
 
            Private Function CreateObject(Of T)(isPrimitive As Boolean, row As DataRow, members As IEnumerable(Of System.Reflection.MemberInfo)) As T
                Dim obj As T
                If isPrimitive Then
                    SetObjectValue(isPrimitive, obj, row(0))
                Else
                    obj = System.Activator.CreateInstance(Of T)()
                    For Each column As DataColumn In row.Table.Columns
                        Dim member = members.FirstOrDefault(Function(x) x.Name.ToLower = column.ColumnName.ToLower)
                        If member Is Nothing _
                            OrElse Not CanWrite(member) Then
                            Continue For
                        End If
                        Dim value = row(column.ColumnName)
                        SetObjectValue(isPrimitive, obj, value, member)
                    Next
                End If
                Return obj
            End Function
 
            Private Sub SetObjectValue(Of T)(isPrimitive As Boolean, ByRef obj As T, value As Object, Optional member As System.Reflection.MemberInfo = Nothing)
                If IsDBNull(value) Then
                    Dim type = If(isPrimitive, GetType(T), GetMemberDataType(member))
                    Dim nullValue = If(IsNullable(type), Nothing, GetDefaultValue(type))
                    If isPrimitive Then
                        obj = CType(nullValue, T)
                    Else
                        SetObjectValue(obj, member, nullValue)
                    End If
                Else
                    If isPrimitive Then
                        obj = CType(value, T)
                    Else
                        SetObjectValue(obj, member, value)
                    End If
                End If
            End Sub
 
            Private Sub SetObjectValue(Of T)(ByRef obj As T, member As System.Reflection.MemberInfo, value As Object)
                ' Boxing method used for modifying structures
                Dim boxed = If(obj.GetType.IsValueType, CType(obj, Object), obj)
                SetMemberValue(boxed, member, value)
                obj = CType(boxed, T)
            End Sub
 
            Private Sub SetMemberValue(Of T)(ByRef obj As T, member As System.Reflection.MemberInfo, value As Object)
                If IsProperty(member) Then
                    Dim prop = CType(member, System.Reflection.PropertyInfo)
                    If prop.SetMethod IsNot Nothing Then
                        prop.SetValue(obj, value)
                    End If
                ElseIf IsField(member) Then
                    Dim field = CType(member, System.Reflection.FieldInfo)
                    field.SetValue(obj, value)
                End If
            End Sub
 
            Private Function GetMemberValue(obj As Object, member As System.Reflection.MemberInfo) As Object
                Dim result As Object = Nothing
                If IsProperty(member) Then
                    Dim prop = CType(member, System.Reflection.PropertyInfo)
                    result = prop.GetValue(obj, If(prop.GetIndexParameters.Count = 1, New Object() {Nothing}, Nothing))
                ElseIf IsField(member) Then
                    Dim field = CType(member, System.Reflection.FieldInfo)
                    result = field.GetValue(obj)
                End If
                Return result
            End Function
 
            Private Function GetDefaultValue(type As System.Type) As Object
                Return Conversion.CTypeDynamic(Nothing, type)
            End Function
 
            Private Function IsPrimitiveType(type As System.Type) As Boolean
                Return type.IsPrimitive _
                    OrElse type.IsEnum _
                    OrElse (type.IsValueType AndAlso Not IsCustomStructure(type)) _
                    OrElse IsType(type, GetType(String))
            End Function
 
            Private Function IsCustomStructure(type As System.Type) As Boolean
                Dim result = type.IsValueType AndAlso Not type.IsPrimitive _
                    AndAlso (String.IsNullOrWhiteSpace(type.Namespace) _
                        OrElse Not type.Namespace.StartsWith("System") _
                        OrElse Not type.FullName.StartsWith("System.")) _
                    AndAlso Not System.Reflection.Assembly.GetAssembly(type).Location.ToLower.Contains("microsoft")
                Return result
            End Function
 
            Private Function IsNullable(type As System.Type) As Boolean
                Return System.Nullable.GetUnderlyingType(type) IsNot Nothing
            End Function
 
            Private Function GetMemberDataType(member As System.Reflection.MemberInfo) As System.Type
                Dim result As System.Type = Nothing
                If IsProperty(member) Then
                    result = CType(member, System.Reflection.PropertyInfo).PropertyType
                ElseIf IsField(member) Then
                    result = CType(member, System.Reflection.FieldInfo).FieldType
                End If
                Return result
            End Function
 
            Private Function CanWrite(member As System.Reflection.MemberInfo) As Boolean
                Return If(IsProperty(member), CType(member, System.Reflection.PropertyInfo).CanWrite, IsField(member))
            End Function
 
            Private Function CanRead(member As System.Reflection.MemberInfo) As Boolean
                Return If(IsProperty(member), CType(member, System.Reflection.PropertyInfo).CanRead, IsField(member))
            End Function
 
            Private Function IsProperty(member As System.Reflection.MemberInfo) As Boolean
                Return IsType(member.GetType, GetType(System.Reflection.PropertyInfo))
            End Function
 
            Private Function IsField(member As System.Reflection.MemberInfo) As Boolean
                Return IsType(member.GetType, GetType(System.Reflection.FieldInfo))
            End Function
 
            Private Function IsType(type As System.Type, targetType As System.Type) As Boolean
                Return type.Equals(targetType) OrElse type.IsSubclassOf(targetType)
            End Function
        End Module
    End Namespace
End Namespace ' http://programmingnotes.org/

9. More Examples

Below are more examples demonstrating the use of the ‘Utils‘ Namespace. Don’t forget to include the module when running the examples!





		
		
			More Examples
			
Visual Basic .NET
			
			' ============================================================================
'    Author: Kenneth Perkins
'    Date:   Nov 13, 2020
'    Taken From: http://programmingnotes.org/
'    File: Program.vb
'    Description: The following demonstrates the use of the Utils Namespace
' ============================================================================
Option Strict On
Option Explicit On
Imports System
Imports Utils.Collections

Module Program
    Public Class Part
        Public Property PartName As String
        Public PartId As Integer?
    End Class

    Sub Main(args As String())
        Try
            ' Declare array of integers 
            Dim numbers = New Integer?() {Nothing, 1987, 19, 22, 2009, 2019, 1991, 28, 31}

            ' Convert array to a datatable
            Dim tableNumbers = numbers.ToDataTable

            ' For primitive types, access data using the 'value' key
            For Each row As DataRow In tableNumbers.Rows
                Display($"{If(IsDBNull(row("value")), "[DBNull.Value]", row("value"))}")
            Next

            Display("")

            ' Declare list of strings
            Dim names = New List(Of String) From {
                "Kenneth",
                "Jennifer",
                "Lynn",
                "Sole",
                Nothing
            }

            ' Convert list to a datatable
            Dim tableNames = names.ToDataTable

            ' For primitive types, access data using the 'value' key
            For Each row As DataRow In tableNames.Rows
                Display($"{If(IsDBNull(row("value")), "[DBNull.Value]", row("value"))}")
            Next

            Display("")


            ' Declare list of objects
            Dim parts = New List(Of Part) From {
                New Part With {
                 .PartName = "crank arm",
                 .PartId = 1234
            },
                New Part With {
                 .PartName = "chain ring",
                 .PartId = 1334
            },
                New Part With {
                 .PartName = "regular seat",
                 .PartId = 1434
            },
                New Part With {
                 .PartName = "banana seat",
                 .PartId = 1444
            },
                New Part With {
                 .PartName = "cassette",
                 .PartId = 1534
            },
                New Part With {
                 .PartName = "shift lever",
                 .PartId = 1634
            },
                New Part With {
                 .PartName = Nothing,
                 .PartId = Nothing
            }}


            ' Convert list to a datatable
            Dim tableParts = parts.ToDataTable

            ' For non primitive types, access data using the class property names
            For Each row As DataRow In tableParts.Rows
                Display($"{If(IsDBNull(row("PartId")), "[DBNull.Value]", row("PartId"))} - {If(IsDBNull(row("PartName")), "[DBNull.Value]", row("PartName"))}")
            Next

            Display("'==========================")

            '==========================


            ' Declare datatable of integers 
            Dim dtblNumbers = New DataTable
            dtblNumbers.Columns.Add("Number", GetType(Integer))

            dtblNumbers.Rows.Add(System.DBNull.Value)
            dtblNumbers.Rows.Add(1987)
            dtblNumbers.Rows.Add(19)
            dtblNumbers.Rows.Add(22)
            dtblNumbers.Rows.Add(2009)
            dtblNumbers.Rows.Add(2019)
            dtblNumbers.Rows.Add(1991)
            dtblNumbers.Rows.Add(28)
            dtblNumbers.Rows.Add(31)

            ' Convert datatable to a list.
            ' When converting to a primitive type, only the first column is returned
            Dim listNumbers = dtblNumbers.ToList(Of Integer?)

            ' Loop through the items in the returned list
            For Each item In listNumbers
                Display($"{If(item Is Nothing, "[Nothing]", item.ToString)}")
            Next

            Display("")

            ' Declare datatable of strings 
            Dim dtblNames = New DataTable
            dtblNames.Columns.Add("Name", GetType(String))

            dtblNames.Rows.Add("Kenneth")
            dtblNames.Rows.Add("Jennifer")
            dtblNames.Rows.Add("Lynn")
            dtblNames.Rows.Add("Sole")
            dtblNames.Rows.Add(System.DBNull.Value)

            ' Convert datatable to a list.
            ' When converting to a primitive type, only the first column is returned
            Dim listNames = dtblNames.ToList(Of String)

            ' Loop through the items in the returned list
            For Each item In listNames
                Display($"{If(item Is Nothing, "[Nothing]", item.ToString)}")
            Next

            Display("")

            ' Declare multi column datatable
            Dim dtblParts = New DataTable
            dtblParts.Columns.Add("PartName", GetType(String))
            dtblParts.Columns.Add("PartId", GetType(Integer))

            dtblParts.Rows.Add({"crank arm", 1234})
            dtblParts.Rows.Add({"chain ring", 1334})
            dtblParts.Rows.Add({"regular seat", 1434})
            dtblParts.Rows.Add({"banana seat", 1444})
            dtblParts.Rows.Add({"cassette", 1534})
            dtblParts.Rows.Add({"shift lever", 1634})
            dtblParts.Rows.Add({System.DBNull.Value, System.DBNull.Value})

            ' Convert datatable to a list.
            ' When converting to an object, all matching columns are returned
            Dim listParts = dtblParts.ToList(Of Part)

            ' Access properties like normal
            For Each item In listParts
                Display($"{If(item.PartId Is Nothing, "[Nothing]", item.PartId.ToString)} - {If(item.PartName Is Nothing, "[Nothing]", item.PartName.ToString)}")
            Next

            Display("")
        Catch ex As Exception
            Display(ex.ToString)
        Finally
            Console.ReadLine()
        End Try
    End Sub

    Public Sub Display(message As String)
        Console.WriteLine(message)
        Debug.Print(message)
    End Sub
End Module ' http://programmingnotes.org/
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
				
						' ============================================================================
'    Author: Kenneth Perkins
'    Date:   Nov 13, 2020
'    Taken From: http://programmingnotes.org/
'    File: Program.vb
'    Description: The following demonstrates the use of the Utils Namespace
' ============================================================================
Option Strict On
Option Explicit On
Imports System
Imports Utils.Collections
 
Module Program
    Public Class Part
        Public Property PartName As String
        Public PartId As Integer?
    End Class
 
    Sub Main(args As String())
        Try
            ' Declare array of integers 
            Dim numbers = New Integer?() {Nothing, 1987, 19, 22, 2009, 2019, 1991, 28, 31}
 
            ' Convert array to a datatable
            Dim tableNumbers = numbers.ToDataTable
 
            ' For primitive types, access data using the 'value' key
            For Each row As DataRow In tableNumbers.Rows
                Display($"{If(IsDBNull(row("value")), "[DBNull.Value]", row("value"))}")
            Next
 
            Display("")
 
            ' Declare list of strings
            Dim names = New List(Of String) From {
                "Kenneth",
                "Jennifer",
                "Lynn",
                "Sole",
                Nothing
            }
 
            ' Convert list to a datatable
            Dim tableNames = names.ToDataTable
 
            ' For primitive types, access data using the 'value' key
            For Each row As DataRow In tableNames.Rows
                Display($"{If(IsDBNull(row("value")), "[DBNull.Value]", row("value"))}")
            Next
 
            Display("")
 
 
            ' Declare list of objects
            Dim parts = New List(Of Part) From {
                New Part With {
                 .PartName = "crank arm",
                 .PartId = 1234
            },
                New Part With {
                 .PartName = "chain ring",
                 .PartId = 1334
            },
                New Part With {
                 .PartName = "regular seat",
                 .PartId = 1434
            },
                New Part With {
                 .PartName = "banana seat",
                 .PartId = 1444
            },
                New Part With {
                 .PartName = "cassette",
                 .PartId = 1534
            },
                New Part With {
                 .PartName = "shift lever",
                 .PartId = 1634
            },
                New Part With {
                 .PartName = Nothing,
                 .PartId = Nothing
            }}
 
 
            ' Convert list to a datatable
            Dim tableParts = parts.ToDataTable
 
            ' For non primitive types, access data using the class property names
            For Each row As DataRow In tableParts.Rows
                Display($"{If(IsDBNull(row("PartId")), "[DBNull.Value]", row("PartId"))} - {If(IsDBNull(row("PartName")), "[DBNull.Value]", row("PartName"))}")
            Next
 
            Display("'==========================")
 
            '==========================
 
 
            ' Declare datatable of integers 
            Dim dtblNumbers = New DataTable
            dtblNumbers.Columns.Add("Number", GetType(Integer))
 
            dtblNumbers.Rows.Add(System.DBNull.Value)
            dtblNumbers.Rows.Add(1987)
            dtblNumbers.Rows.Add(19)
            dtblNumbers.Rows.Add(22)
            dtblNumbers.Rows.Add(2009)
            dtblNumbers.Rows.Add(2019)
            dtblNumbers.Rows.Add(1991)
            dtblNumbers.Rows.Add(28)
            dtblNumbers.Rows.Add(31)
 
            ' Convert datatable to a list.
            ' When converting to a primitive type, only the first column is returned
            Dim listNumbers = dtblNumbers.ToList(Of Integer?)
 
            ' Loop through the items in the returned list
            For Each item In listNumbers
                Display($"{If(item Is Nothing, "[Nothing]", item.ToString)}")
            Next
 
            Display("")
 
            ' Declare datatable of strings 
            Dim dtblNames = New DataTable
            dtblNames.Columns.Add("Name", GetType(String))
 
            dtblNames.Rows.Add("Kenneth")
            dtblNames.Rows.Add("Jennifer")
            dtblNames.Rows.Add("Lynn")
            dtblNames.Rows.Add("Sole")
            dtblNames.Rows.Add(System.DBNull.Value)
 
            ' Convert datatable to a list.
            ' When converting to a primitive type, only the first column is returned
            Dim listNames = dtblNames.ToList(Of String)
 
            ' Loop through the items in the returned list
            For Each item In listNames
                Display($"{If(item Is Nothing, "[Nothing]", item.ToString)}")
            Next
 
            Display("")
 
            ' Declare multi column datatable
            Dim dtblParts = New DataTable
            dtblParts.Columns.Add("PartName", GetType(String))
            dtblParts.Columns.Add("PartId", GetType(Integer))
 
            dtblParts.Rows.Add({"crank arm", 1234})
            dtblParts.Rows.Add({"chain ring", 1334})
            dtblParts.Rows.Add({"regular seat", 1434})
            dtblParts.Rows.Add({"banana seat", 1444})
            dtblParts.Rows.Add({"cassette", 1534})
            dtblParts.Rows.Add({"shift lever", 1634})
            dtblParts.Rows.Add({System.DBNull.Value, System.DBNull.Value})
 
            ' Convert datatable to a list.
            ' When converting to an object, all matching columns are returned
            Dim listParts = dtblParts.ToList(Of Part)
 
            ' Access properties like normal
            For Each item In listParts
                Display($"{If(item.PartId Is Nothing, "[Nothing]", item.PartId.ToString)} - {If(item.PartName Is Nothing, "[Nothing]", item.PartName.ToString)}")
            Next
 
            Display("")
        Catch ex As Exception
            Display(ex.ToString)
        Finally
            Console.ReadLine()
        End Try
    End Sub
 
    Public Sub Display(message As String)
        Console.WriteLine(message)
        Debug.Print(message)
    End Sub
End Module ' http://programmingnotes.org/

QUICK NOTES:
The highlighted lines are sections of interest to look out for.

The code is heavily commented, so no further insight is necessary. If you have any questions, feel free to leave a comment below.

Was this article helpful?

👍 YesNo

Posted in: Console, VB.NET Tags: datatable, datatable to list, list, list to datatable, vb.net

Leave a ReplyCancel reply

Search

Compile Your Code Now

Translate Page

Popular Pages

Recent

Archives

Categories