C# || How To Convert An Array/List/IEnumerable To A DataTable & Convert A DataTable To A List Using C#

May 09, 2021 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 C#.

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 struct 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.Extensions.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
			
C#
			
			// Convert To DataTable - Integer Array

using Utils.Collections;

// Declare array of integers 
var numbers = new int?[] { null, 1987, 19, 22, 2009, 2019, 1991, 28, 31 };

// Convert array to a datatable
var tableNumbers = numbers.ToDataTable();

// For primitive types, access data using the 'value' key
foreach (DataRow row in tableNumbers.Rows) {
    Console.WriteLine($"{(Convert.IsDBNull(row["value"]) ? "[DBNull.Value]" : row["value"])}");
}

// 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
26
27
				
						// Convert To DataTable - Integer Array
 
using Utils.Collections;
 
// Declare array of integers 
var numbers = new int?[] { null, 1987, 19, 22, 2009, 2019, 1991, 28, 31 };
 
// Convert array to a datatable
var tableNumbers = numbers.ToDataTable();
 
// For primitive types, access data using the 'value' key
foreach (DataRow row in tableNumbers.Rows) {
    Console.WriteLine($"{(Convert.IsDBNull(row["value"]) ? "[DBNull.Value]" : row["value"])}");
}
 
// 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.Extensions.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
			
C#
			
			// Convert To DataTable - List Of Strings

using Utils.Collections;

// Declare list of strings
var names = new List<string>() {
    "Kenneth",
    "Jennifer",
    "Lynn",
    "Sole",
    null
};

// Convert list to a datatable
var tableNames = names.ToDataTable();

// For primitive types, access data using the 'value' key
foreach (DataRow row in tableNames.Rows) {
    Console.WriteLine($"{(Convert.IsDBNull(row["value"]) ? "[DBNull.Value]" : row["value"])}");
}

// 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
28
29
				
						// Convert To DataTable - List Of Strings
 
using Utils.Collections;
 
// Declare list of strings
var names = new List<string>() {
    "Kenneth",
    "Jennifer",
    "Lynn",
    "Sole",
    null
};
 
// Convert list to a datatable
var tableNames = names.ToDataTable();
 
// For primitive types, access data using the 'value' key
foreach (DataRow row in tableNames.Rows) {
    Console.WriteLine($"{(Convert.IsDBNull(row["value"]) ? "[DBNull.Value]" : row["value"])}");
}
 
// expected output:
/*
    Kenneth
    Jennifer
    Lynn
    Sole
    [DBNull.Value]
*/

4. Convert To DataTable – List Of Objects

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

A class is used in this example, but a custom struct 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
			
C#
			
			// Convert To DataTable - List Of Objects

using Utils.Collections;

public class Part {
    public string PartName { get; set; }
    public int? PartId;
}

// Declare list of objects
var parts = new List<Part>() {
    new Part() {
        PartName = "crank arm",
        PartId = 1234
    },
    new Part() {
        PartName = "chain ring",
        PartId = 1334
    },
    new Part() {
        PartName = "regular seat",
        PartId = 1434
    },
    new Part() {
        PartName = "banana seat",
        PartId = 1444
    },
    new Part() {
        PartName = "cassette",
        PartId = 1534
    },
    new Part() {
        PartName = "shift lever",
        PartId = 1634
    },
    new Part() {
        PartName = null,
        PartId = null
    }
};

// Convert list to a datatable
var tableParts = parts.ToDataTable();

// For non primitive types, access data using the class property names
foreach (DataRow row in tableParts.Rows) { 
    Console.WriteLine($"{(Convert.IsDBNull(row["PartId"]) ? "[DBNull.Value]" : row["PartId"])} - {(Convert.IsDBNull(row["PartName"]) ? "[DBNull.Value]" : row["PartName"])}");
}  

// 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
57
58
59
				
						// Convert To DataTable - List Of Objects
 
using Utils.Collections;
 
public class Part {
    public string PartName { get; set; }
    public int? PartId;
}
 
// Declare list of objects
var parts = new List<Part>() {
    new Part() {
        PartName = "crank arm",
        PartId = 1234
    },
    new Part() {
        PartName = "chain ring",
        PartId = 1334
    },
    new Part() {
        PartName = "regular seat",
        PartId = 1434
    },
    new Part() {
        PartName = "banana seat",
        PartId = 1444
    },
    new Part() {
        PartName = "cassette",
        PartId = 1534
    },
    new Part() {
        PartName = "shift lever",
        PartId = 1634
    },
    new Part() {
        PartName = null,
        PartId = null
    }
};
 
// Convert list to a datatable
var tableParts = parts.ToDataTable();
 
// For non primitive types, access data using the class property names
foreach (DataRow row in tableParts.Rows) { 
    Console.WriteLine($"{(Convert.IsDBNull(row["PartId"]) ? "[DBNull.Value]" : row["PartId"])} - {(Convert.IsDBNull(row["PartName"]) ? "[DBNull.Value]" : row["PartName"])}");
}  
 
// 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.Extensions.ToList‘ to convert a datatable to a List(Of Integer?).

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





		
		
			5. Convert To List - Integer DataTable
			
C#
			
			// Convert To List - Integer DataTable

using Utils.Collections;

// Declare datatable of integers 
var tableNumbers = new DataTable();
tableNumbers.Columns.Add("Number", typeof(int));

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
var listNumbers = tableNumbers.ToList<int?>();

// Loop through the items in the returned list
foreach (var item in listNumbers) { 
    Console.WriteLine($"{(item == null ? "[null]" : item.ToString())}");
}

// expected output:
/*
    [null]
    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
38
39
				
						// Convert To List - Integer DataTable
 
using Utils.Collections;
 
// Declare datatable of integers 
var tableNumbers = new DataTable();
tableNumbers.Columns.Add("Number", typeof(int));
 
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
var listNumbers = tableNumbers.ToList<int?>();
 
// Loop through the items in the returned list
foreach (var item in listNumbers) { 
    Console.WriteLine($"{(item == null ? "[null]" : item.ToString())}");
}
 
// expected output:
/*
    [null]
    1987
    19
    22
    2009
    2019
    1991
    28
    31
*/

6. Convert To List – String DataTable

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

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





		
		
			6. Convert To List - String DataTable
			
C#
			
			// Convert To List - String DataTable

using Utils.Collections;

// Declare datatable of strings 
var tableNames = new DataTable();
tableNames.Columns.Add("Name", typeof(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
var listNames = tableNames.ToList<string>();

// Loop through the items in the returned list
foreach (var item in listNames) { 
    Console.WriteLine($"{(item == null ? "[null]" : item.ToString())}");
}

// expected output:
/*
    Kenneth
    Jennifer
    Lynn
    Sole
    [null]
*/
			
				
					
				
					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
				
						// Convert To List - String DataTable
 
using Utils.Collections;
 
// Declare datatable of strings 
var tableNames = new DataTable();
tableNames.Columns.Add("Name", typeof(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
var listNames = tableNames.ToList<string>();
 
// Loop through the items in the returned list
foreach (var item in listNames) { 
    Console.WriteLine($"{(item == null ? "[null]" : item.ToString())}");
}
 
// expected output:
/*
    Kenneth
    Jennifer
    Lynn
    Sole
    [null]
*/

7. Convert To List – Multi-Column DataTable

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

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

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





		
		
			7. Convert To List - Multi-Column DataTable
			
C#
			
			// Convert To List - Multi-Column DataTable

using Utils.Collections;

public class Part {
    public string PartName { get; set; }
    public int? PartId;
}

// Declare multi column datatable
var tableParts = new DataTable();
tableParts.Columns.Add("PartName", typeof(string));
tableParts.Columns.Add("PartId", typeof(int));

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

// Convert datatable to a list.
// When converting to an object, all matching columns are returned
var listParts = tableParts.ToList<Part>();

// Access properties like normal
foreach (var item in listParts) { 
    Console.WriteLine($"{(item.PartId == null ? "[null]" : item.PartId.ToString())} - {(item.PartName == null ? "[null]" : item.PartName.ToString())}");
}

// expected output:
/*
    1234 - crank arm
    1334 - chain ring
    1434 - regular seat
    1444 - banana seat
    1534 - cassette
    1634 - shift lever
    [null] - [null]
*/
			
				
					
				
					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
				
						// Convert To List - Multi-Column DataTable
 
using Utils.Collections;
 
public class Part {
    public string PartName { get; set; }
    public int? PartId;
}
 
// Declare multi column datatable
var tableParts = new DataTable();
tableParts.Columns.Add("PartName", typeof(string));
tableParts.Columns.Add("PartId", typeof(int));
 
tableParts.Rows.Add(new object[] {"crank arm", 1234});
tableParts.Rows.Add(new object[] {"chain ring", 1334});
tableParts.Rows.Add(new object[] {"regular seat", 1434});
tableParts.Rows.Add(new object[] {"banana seat", 1444});
tableParts.Rows.Add(new object[] {"cassette", 1534});
tableParts.Rows.Add(new object[] {"shift lever", 1634});
tableParts.Rows.Add(new object[] {System.DBNull.Value, System.DBNull.Value});
 
// Convert datatable to a list.
// When converting to an object, all matching columns are returned
var listParts = tableParts.ToList<Part>();
 
// Access properties like normal
foreach (var item in listParts) { 
    Console.WriteLine($"{(item.PartId == null ? "[null]" : item.PartId.ToString())} - {(item.PartName == null ? "[null]" : item.PartName.ToString())}");
}
 
// expected output:
/*
    1234 - crank arm
    1334 - chain ring
    1434 - regular seat
    1444 - banana seat
    1534 - cassette
    1634 - shift lever
    [null] - [null]
*/

8. Utils Namespace

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





		
		
			Utils Namespace
			
C#
			
			// ============================================================================
//    Author: Kenneth Perkins
//    Date:   May 9, 2021
//    Taken From: http://programmingnotes.org/
//    File:  Utils.cs
//    Description: Handles general utility functions
// ============================================================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

namespace Utils {
    namespace Collections {
        public static class Extensions {
            /// <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>
            public static List<T> ToList<T>(this DataTable source) {
                var type = typeof(T);
                var list = new List<T>();
                var isPrimitive = IsPrimitiveType(type);
                var members = new List<System.Reflection.MemberInfo>();

                if (!isPrimitive) {
                    members.AddRange(type.GetProperties());
                    members.AddRange(type.GetFields());
                }

                // Convert data rows to the object
                foreach (DataRow row in source.Rows) {
                    var obj = CreateObject<T>(isPrimitive, row, members);
                    list.Add(obj);
                }
                return list;
            }

            /// <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>
            public static DataTable ToDataTable<T>(this IEnumerable<T> source) {
                var type = typeof(T);
                var table = new DataTable(type.Name);
                var isPrimitive = IsPrimitiveType(type);
                var primitiveColumnName = "value";
                var members = new List<System.Reflection.MemberInfo>();

                if (!isPrimitive) {
                    var flags = System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public;
                    members.AddRange(type.GetProperties(flags));
                    members.AddRange(type.GetFields(flags));
                }

                // Build the header rows
                BuildColumns<T>(isPrimitive, primitiveColumnName, table, members);

                // Add the data rows
                foreach (var item in source) {
                    var row = CreateRow(isPrimitive, primitiveColumnName, item, table, members);
                    table.Rows.Add(row);
                }
                return table;
            }

            private static void BuildColumns<T>(bool isPrimitive, string primitiveColumnName, DataTable table, IEnumerable<System.Reflection.MemberInfo> members) {
                if (isPrimitive) {
                    AddColumn(table, primitiveColumnName, typeof(T));
                } else {
                    foreach (var member in members) {
                        AddColumn(table, member.Name, GetMemberDataType(member));
                    }
                }
            }

            private static void AddColumn(DataTable table, string columnName, System.Type type) {
                table.Columns.Add(columnName, IsNullable(type) ? System.Nullable.GetUnderlyingType(type) : type);
            }

            private static DataRow CreateRow<T>(bool isPrimitive, string primitiveColumnName, T obj, DataTable table, IEnumerable<System.Reflection.MemberInfo> members) {
                var row = table.NewRow();
                if (isPrimitive) {
                    SetRowValue(row, primitiveColumnName, obj);
                } else {
                    foreach (var member in members) {
                        var value = CanRead(member) ? GetMemberValue(obj, member) : null;
                        SetRowValue(row, member.Name, value);
                    }
                }
                return row;
            }

            private static void SetRowValue(DataRow row, string columnName, object value) {
                row[columnName] = (value == null ? System.DBNull.Value : value);
            }

            private static T CreateObject<T>(bool isPrimitive, DataRow row, IEnumerable<System.Reflection.MemberInfo> members) {
                T obj = default;
                if (isPrimitive) {
                    SetObjectValue(isPrimitive, ref obj, row[0]);
                } else {
                    obj = System.Activator.CreateInstance<T>();
                    foreach (DataColumn column in row.Table.Columns) {
                        var member = members.FirstOrDefault(x => x.Name.ToLower() == column.ColumnName.ToLower());
                        if (member == null || !CanWrite(member)) {
                            continue;
                        }
                        var value = row[column.ColumnName];
                        SetObjectValue(isPrimitive, ref obj, value, member);
                    }
                }
                return obj;
            }

            private static void SetObjectValue<T>(bool isPrimitive, ref T obj, object value, System.Reflection.MemberInfo member = null) {
                if (System.Convert.IsDBNull(value)) {
                    var type = isPrimitive ? typeof(T) : GetMemberDataType(member);
                    var nullValue = IsNullable(type) ? null : GetDefaultValue(type);
                    if (isPrimitive) {
                        obj = (T)nullValue;
                    } else {
                        SetObjectValue(ref obj, member, nullValue);
                    } 
                } else {
                    if (isPrimitive) {
                        obj = (T)value;
                    } else {
                        SetObjectValue(ref obj, member, value);
                    }  
                }
            }

            private static void SetObjectValue<T>(ref T obj, System.Reflection.MemberInfo member, object value) {
                // Boxing method used for modifying structures
                var boxed = obj.GetType().IsValueType ? (object)obj : obj;
                SetMemberValue(ref boxed, member, value);
                obj = (T)boxed;
            }

            private static void SetMemberValue<T>(ref T obj, System.Reflection.MemberInfo member, object value) {
                if (IsProperty(member)) {
                    var prop = (System.Reflection.PropertyInfo)member;
                    if (prop.SetMethod != null) {
                        prop.SetValue(obj, value);
                    } 
                } else if (IsField(member)) {
                    var field = (System.Reflection.FieldInfo)member;
                    field.SetValue(obj, value);
                }
            }

            private static object GetMemberValue(object obj, System.Reflection.MemberInfo member) {
                object result = null;
                if (IsProperty(member)) {
                    var prop = (System.Reflection.PropertyInfo)member;
                    result = prop.GetValue(obj, prop.GetIndexParameters().Count() == 1 ? new object[] { null } : null);
                } else if (IsField(member)) {
                    var field = (System.Reflection.FieldInfo)member;
                    result = field.GetValue(obj);
                }
                return result;
            }

            private static object GetDefaultValue(System.Type type) {
                return type.IsValueType ? System.Activator.CreateInstance(type) : null;
            }

            private static bool IsPrimitiveType(System.Type type) {
                return type.IsPrimitive
                   || type.IsEnum
                   || (type.IsValueType && !IsCustomStructure(type))
                   || IsType(type, typeof(string));
            }

            private static bool IsCustomStructure(System.Type type) {
                var result = type.IsValueType && !type.IsPrimitive
                   && (string.IsNullOrWhiteSpace(type.Namespace)
                       || !type.Namespace.StartsWith("System")
                       || !type.FullName.StartsWith("System."))
                   && !System.Reflection.Assembly.GetAssembly(type).Location.ToLower().Contains("microsoft");
                return result;
            }

            private static bool IsNullable(System.Type type) {
                return System.Nullable.GetUnderlyingType(type) != null;
            }

            private static System.Type GetMemberDataType(System.Reflection.MemberInfo member) {
                System.Type result = null;
                if (IsProperty(member)) {
                    result = ((System.Reflection.PropertyInfo)member).PropertyType;
                } else if (IsField(member)) {
                    result = ((System.Reflection.FieldInfo)member).FieldType;
                }
                return result;
            }

            private static bool CanWrite(System.Reflection.MemberInfo member) {
                return IsProperty(member) ? ((System.Reflection.PropertyInfo)member).CanWrite : IsField(member);
            }

            private static bool CanRead(System.Reflection.MemberInfo member) {
                return IsProperty(member) ? ((System.Reflection.PropertyInfo)member).CanRead : IsField(member);
            }

            private static bool IsProperty(System.Reflection.MemberInfo member) {
                return IsType(member.GetType(), typeof(System.Reflection.PropertyInfo));
            }

            private static bool IsField(System.Reflection.MemberInfo member) {
                return IsType(member.GetType(), typeof(System.Reflection.FieldInfo));
            }

            private static bool IsType(System.Type type, System.Type targetType) {
                return type.Equals(targetType) || type.IsSubclassOf(targetType);
            }
        }
    }
}// 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
				
						// ============================================================================
//    Author: Kenneth Perkins
//    Date:   May 9, 2021
//    Taken From: http://programmingnotes.org/
//    File:  Utils.cs
//    Description: Handles general utility functions
// ============================================================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
 
namespace Utils {
    namespace Collections {
        public static class Extensions {
            /// <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>
            public static List<T> ToList<T>(this DataTable source) {
                var type = typeof(T);
                var list = new List<T>();
                var isPrimitive = IsPrimitiveType(type);
                var members = new List<System.Reflection.MemberInfo>();
 
                if (!isPrimitive) {
                    members.AddRange(type.GetProperties());
                    members.AddRange(type.GetFields());
                }
 
                // Convert data rows to the object
                foreach (DataRow row in source.Rows) {
                    var obj = CreateObject<T>(isPrimitive, row, members);
                    list.Add(obj);
                }
                return list;
            }
 
            /// <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>
            public static DataTable ToDataTable<T>(this IEnumerable<T> source) {
                var type = typeof(T);
                var table = new DataTable(type.Name);
                var isPrimitive = IsPrimitiveType(type);
                var primitiveColumnName = "value";
                var members = new List<System.Reflection.MemberInfo>();
 
                if (!isPrimitive) {
                    var flags = System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public;
                    members.AddRange(type.GetProperties(flags));
                    members.AddRange(type.GetFields(flags));
                }
 
                // Build the header rows
                BuildColumns<T>(isPrimitive, primitiveColumnName, table, members);
 
                // Add the data rows
                foreach (var item in source) {
                    var row = CreateRow(isPrimitive, primitiveColumnName, item, table, members);
                    table.Rows.Add(row);
                }
                return table;
            }
 
            private static void BuildColumns<T>(bool isPrimitive, string primitiveColumnName, DataTable table, IEnumerable<System.Reflection.MemberInfo> members) {
                if (isPrimitive) {
                    AddColumn(table, primitiveColumnName, typeof(T));
                } else {
                    foreach (var member in members) {
                        AddColumn(table, member.Name, GetMemberDataType(member));
                    }
                }
            }
 
            private static void AddColumn(DataTable table, string columnName, System.Type type) {
                table.Columns.Add(columnName, IsNullable(type) ? System.Nullable.GetUnderlyingType(type) : type);
            }
 
            private static DataRow CreateRow<T>(bool isPrimitive, string primitiveColumnName, T obj, DataTable table, IEnumerable<System.Reflection.MemberInfo> members) {
                var row = table.NewRow();
                if (isPrimitive) {
                    SetRowValue(row, primitiveColumnName, obj);
                } else {
                    foreach (var member in members) {
                        var value = CanRead(member) ? GetMemberValue(obj, member) : null;
                        SetRowValue(row, member.Name, value);
                    }
                }
                return row;
            }
 
            private static void SetRowValue(DataRow row, string columnName, object value) {
                row[columnName] = (value == null ? System.DBNull.Value : value);
            }
 
            private static T CreateObject<T>(bool isPrimitive, DataRow row, IEnumerable<System.Reflection.MemberInfo> members) {
                T obj = default;
                if (isPrimitive) {
                    SetObjectValue(isPrimitive, ref obj, row[0]);
                } else {
                    obj = System.Activator.CreateInstance<T>();
                    foreach (DataColumn column in row.Table.Columns) {
                        var member = members.FirstOrDefault(x => x.Name.ToLower() == column.ColumnName.ToLower());
                        if (member == null || !CanWrite(member)) {
                            continue;
                        }
                        var value = row[column.ColumnName];
                        SetObjectValue(isPrimitive, ref obj, value, member);
                    }
                }
                return obj;
            }
 
            private static void SetObjectValue<T>(bool isPrimitive, ref T obj, object value, System.Reflection.MemberInfo member = null) {
                if (System.Convert.IsDBNull(value)) {
                    var type = isPrimitive ? typeof(T) : GetMemberDataType(member);
                    var nullValue = IsNullable(type) ? null : GetDefaultValue(type);
                    if (isPrimitive) {
                        obj = (T)nullValue;
                    } else {
                        SetObjectValue(ref obj, member, nullValue);
                    } 
                } else {
                    if (isPrimitive) {
                        obj = (T)value;
                    } else {
                        SetObjectValue(ref obj, member, value);
                    }  
                }
            }
 
            private static void SetObjectValue<T>(ref T obj, System.Reflection.MemberInfo member, object value) {
                // Boxing method used for modifying structures
                var boxed = obj.GetType().IsValueType ? (object)obj : obj;
                SetMemberValue(ref boxed, member, value);
                obj = (T)boxed;
            }
 
            private static void SetMemberValue<T>(ref T obj, System.Reflection.MemberInfo member, object value) {
                if (IsProperty(member)) {
                    var prop = (System.Reflection.PropertyInfo)member;
                    if (prop.SetMethod != null) {
                        prop.SetValue(obj, value);
                    } 
                } else if (IsField(member)) {
                    var field = (System.Reflection.FieldInfo)member;
                    field.SetValue(obj, value);
                }
            }
 
            private static object GetMemberValue(object obj, System.Reflection.MemberInfo member) {
                object result = null;
                if (IsProperty(member)) {
                    var prop = (System.Reflection.PropertyInfo)member;
                    result = prop.GetValue(obj, prop.GetIndexParameters().Count() == 1 ? new object[] { null } : null);
                } else if (IsField(member)) {
                    var field = (System.Reflection.FieldInfo)member;
                    result = field.GetValue(obj);
                }
                return result;
            }
 
            private static object GetDefaultValue(System.Type type) {
                return type.IsValueType ? System.Activator.CreateInstance(type) : null;
            }
 
            private static bool IsPrimitiveType(System.Type type) {
                return type.IsPrimitive
                   || type.IsEnum
                   || (type.IsValueType && !IsCustomStructure(type))
                   || IsType(type, typeof(string));
            }
 
            private static bool IsCustomStructure(System.Type type) {
                var result = type.IsValueType && !type.IsPrimitive
                   && (string.IsNullOrWhiteSpace(type.Namespace)
                       || !type.Namespace.StartsWith("System")
                       || !type.FullName.StartsWith("System."))
                   && !System.Reflection.Assembly.GetAssembly(type).Location.ToLower().Contains("microsoft");
                return result;
            }
 
            private static bool IsNullable(System.Type type) {
                return System.Nullable.GetUnderlyingType(type) != null;
            }
 
            private static System.Type GetMemberDataType(System.Reflection.MemberInfo member) {
                System.Type result = null;
                if (IsProperty(member)) {
                    result = ((System.Reflection.PropertyInfo)member).PropertyType;
                } else if (IsField(member)) {
                    result = ((System.Reflection.FieldInfo)member).FieldType;
                }
                return result;
            }
 
            private static bool CanWrite(System.Reflection.MemberInfo member) {
                return IsProperty(member) ? ((System.Reflection.PropertyInfo)member).CanWrite : IsField(member);
            }
 
            private static bool CanRead(System.Reflection.MemberInfo member) {
                return IsProperty(member) ? ((System.Reflection.PropertyInfo)member).CanRead : IsField(member);
            }
 
            private static bool IsProperty(System.Reflection.MemberInfo member) {
                return IsType(member.GetType(), typeof(System.Reflection.PropertyInfo));
            }
 
            private static bool IsField(System.Reflection.MemberInfo member) {
                return IsType(member.GetType(), typeof(System.Reflection.FieldInfo));
            }
 
            private static bool IsType(System.Type type, System.Type targetType) {
                return type.Equals(targetType) || type.IsSubclassOf(targetType);
            }
        }
    }
}// 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
			
C#
			
			// ============================================================================
//    Author: Kenneth Perkins
//    Date:   May 9, 2021
//    Taken From: http://programmingnotes.org/
//    File:  Program.cs
//    Description: The following demonstrates the use of the Utils Namespace
// ============================================================================
using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Data;
using Utils.Collections;

public class Program {

    public class Part {
        public string PartName { get; set; }
        public int? PartId;
    }
    
    static void Main(string[] args) {
        try {
            // Declare array of integers 
            var numbers = new int?[] { null, 1987, 19, 22, 2009, 2019, 1991, 28, 31 };

            // Convert array to a datatable
            var tableNumbers = numbers.ToDataTable();

            var numbers2 = tableNumbers.ToList<int>();

            // For primitive types, access data using the 'value' key
            foreach (DataRow row in tableNumbers.Rows) {
                Display($"{(Convert.IsDBNull(row["value"]) ? "[DBNull.Value]" : row["value"])}");
            }

            Display("");

            // Declare list of strings
            var names = new List<string>() {
                "Kenneth",
                "Jennifer",
                "Lynn",
                "Sole",
                null
            };

            // Convert list to a datatable
            var tableNames = names.ToDataTable();

            var names2 = tableNames.ToList<string>();

            // For primitive types, access data using the 'value' key
            foreach (DataRow row in tableNames.Rows) {
                Display($"{(Convert.IsDBNull(row["value"]) ? "[DBNull.Value]" : row["value"])}");
            }

            Display("");


            // Declare list of objects
            var parts = new List<Part>() {
                new Part() {
                    PartName = "crank arm",
                    PartId = 1234
                },
                new Part() {
                    PartName = "chain ring",
                    PartId = 1334
                },
                new Part() {
                    PartName = "regular seat",
                    PartId = 1434
                },
                new Part() {
                    PartName = "banana seat",
                    PartId = 1444
                },
                new Part() {
                    PartName = "cassette",
                    PartId = 1534
                },
                new Part() {
                    PartName = "shift lever",
                    PartId = 1634
                },
                new Part() {
                    PartName = null,
                    PartId = null
                }
            };

            // Convert list to a datatable
            var tableParts = parts.ToDataTable();

            // For non primitive types, access data using the class property names
            foreach (DataRow row in tableParts.Rows) { 
                Display($"{(Convert.IsDBNull(row["PartId"]) ? "[DBNull.Value]" : row["PartId"])} - {(Convert.IsDBNull(row["PartName"]) ? "[DBNull.Value]" : row["PartName"])}");
            }

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

            // ==========================


            // Declare datatable of integers 
            var dtblNumbers = new DataTable();
            dtblNumbers.Columns.Add("Number", typeof(int));

            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
            var listNumbers = dtblNumbers.ToList<int?>();

            // Loop through the items in the returned list
            foreach (var item in listNumbers) { 
                Display($"{(item == null ? "[null]" : item.ToString())}");
            }

            Display("");

            // Declare datatable of strings 
            var dtblNames = new DataTable();
            dtblNames.Columns.Add("Name", typeof(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
            var listNames = dtblNames.ToList<string>();

            // Loop through the items in the returned list
            foreach (var item in listNames) { 
                Display($"{(item == null ? "[null]" : item.ToString())}");
            }

            Display("");

            // Declare multi column datatable
            var dtblParts = new DataTable();
            dtblParts.Columns.Add("PartName", typeof(string));
            dtblParts.Columns.Add("PartId", typeof(int));

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

            // Convert datatable to a list.
            // When converting to an object, all matching columns are returned
            var listParts = dtblParts.ToList<Part>();

            // Access properties like normal
            foreach (var item in listParts) { 
                Display($"{(item.PartId == null ? "[null]" : item.PartId.ToString())} - {(item.PartName == null ? "[null]" : item.PartName.ToString())}");
            }
        } catch (Exception ex) {
            Display(ex.ToString());
        } finally {
            Console.ReadLine();
        }
    }

    static void Display(string message) {
        Console.WriteLine(message);
        Debug.Print(message);
    }
}// 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
				
						// ============================================================================
//    Author: Kenneth Perkins
//    Date:   May 9, 2021
//    Taken From: http://programmingnotes.org/
//    File:  Program.cs
//    Description: The following demonstrates the use of the Utils Namespace
// ============================================================================
using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Data;
using Utils.Collections;
 
public class Program {
 
    public class Part {
        public string PartName { get; set; }
        public int? PartId;
    }
    
    static void Main(string[] args) {
        try {
            // Declare array of integers 
            var numbers = new int?[] { null, 1987, 19, 22, 2009, 2019, 1991, 28, 31 };
 
            // Convert array to a datatable
            var tableNumbers = numbers.ToDataTable();
 
            var numbers2 = tableNumbers.ToList<int>();
 
            // For primitive types, access data using the 'value' key
            foreach (DataRow row in tableNumbers.Rows) {
                Display($"{(Convert.IsDBNull(row["value"]) ? "[DBNull.Value]" : row["value"])}");
            }
 
            Display("");
 
            // Declare list of strings
            var names = new List<string>() {
                "Kenneth",
                "Jennifer",
                "Lynn",
                "Sole",
                null
            };
 
            // Convert list to a datatable
            var tableNames = names.ToDataTable();
 
            var names2 = tableNames.ToList<string>();
 
            // For primitive types, access data using the 'value' key
            foreach (DataRow row in tableNames.Rows) {
                Display($"{(Convert.IsDBNull(row["value"]) ? "[DBNull.Value]" : row["value"])}");
            }
 
            Display("");
 
 
            // Declare list of objects
            var parts = new List<Part>() {
                new Part() {
                    PartName = "crank arm",
                    PartId = 1234
                },
                new Part() {
                    PartName = "chain ring",
                    PartId = 1334
                },
                new Part() {
                    PartName = "regular seat",
                    PartId = 1434
                },
                new Part() {
                    PartName = "banana seat",
                    PartId = 1444
                },
                new Part() {
                    PartName = "cassette",
                    PartId = 1534
                },
                new Part() {
                    PartName = "shift lever",
                    PartId = 1634
                },
                new Part() {
                    PartName = null,
                    PartId = null
                }
            };
 
            // Convert list to a datatable
            var tableParts = parts.ToDataTable();
 
            // For non primitive types, access data using the class property names
            foreach (DataRow row in tableParts.Rows) { 
                Display($"{(Convert.IsDBNull(row["PartId"]) ? "[DBNull.Value]" : row["PartId"])} - {(Convert.IsDBNull(row["PartName"]) ? "[DBNull.Value]" : row["PartName"])}");
            }
 
            Display("'==========================");
 
            // ==========================
 
 
            // Declare datatable of integers 
            var dtblNumbers = new DataTable();
            dtblNumbers.Columns.Add("Number", typeof(int));
 
            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
            var listNumbers = dtblNumbers.ToList<int?>();
 
            // Loop through the items in the returned list
            foreach (var item in listNumbers) { 
                Display($"{(item == null ? "[null]" : item.ToString())}");
            }
 
            Display("");
 
            // Declare datatable of strings 
            var dtblNames = new DataTable();
            dtblNames.Columns.Add("Name", typeof(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
            var listNames = dtblNames.ToList<string>();
 
            // Loop through the items in the returned list
            foreach (var item in listNames) { 
                Display($"{(item == null ? "[null]" : item.ToString())}");
            }
 
            Display("");
 
            // Declare multi column datatable
            var dtblParts = new DataTable();
            dtblParts.Columns.Add("PartName", typeof(string));
            dtblParts.Columns.Add("PartId", typeof(int));
 
            dtblParts.Rows.Add(new object[] {"crank arm", 1234});
            dtblParts.Rows.Add(new object[] {"chain ring", 1334});
            dtblParts.Rows.Add(new object[] {"regular seat", 1434});
            dtblParts.Rows.Add(new object[] {"banana seat", 1444});
            dtblParts.Rows.Add(new object[] {"cassette", 1534});
            dtblParts.Rows.Add(new object[] {"shift lever", 1634});
            dtblParts.Rows.Add(new object[] {System.DBNull.Value, System.DBNull.Value});
 
            // Convert datatable to a list.
            // When converting to an object, all matching columns are returned
            var listParts = dtblParts.ToList<Part>();
 
            // Access properties like normal
            foreach (var item in listParts) { 
                Display($"{(item.PartId == null ? "[null]" : item.PartId.ToString())} - {(item.PartName == null ? "[null]" : item.PartName.ToString())}");
            }
        } catch (Exception ex) {
            Display(ex.ToString());
        } finally {
            Console.ReadLine();
        }
    }
 
    static void Display(string message) {
        Console.WriteLine(message);
        Debug.Print(message);
    }
}// 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: C#, Console Tags: c-sharp, datatable, datatable to list, list, list to datatable

Leave a ReplyCancel reply

Search

Compile Your Code Now

Translate Page

Popular Pages

Recent

Archives

Categories