Tag Archives: list
C# || How To Convert An Array/List/IEnumerable To A DataTable & Convert A DataTable To A List Using C#
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.
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.
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.
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.
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.
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.
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!
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!
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.
VB.NET || How To Convert An Array/List/IEnumerable To A DataTable & Convert A DataTable To A List Using VB.NET
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.
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.
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.
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.
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.
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.
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!
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!
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.
Python || Snippet – How To Input Numbers Into A List & Display Its Contents Back To User
This snippet demonstrates how to place numbers into a list. It also shows how to display the contents of the list back to the user via stdout.
REQUIRED KNOWLEDGE FOR THIS SNIPPET
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 |
def main(): # declare variables arry = [] # initialize the list numElems = 0 # ask user how many items they want to place in list numElems = int(input("How many items do you want to place into the list?: ")) # print a newline print("") # user enters data into list using a for loop # you can also use a while loop, but for loops are more common # when dealing with lists for x in range(0, numElems): arry.append(int(input("Enter item #%d: " % (x+1)))) # display data print("nThe current items inside the list are: ") for x in range(0, numElems): print("Item #%d: %d" % ((x+1),arry[x])) if __name__ == "__main__": main() # 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.
Once compiled, you should get this as your output
How many items do you want to place into the list?: 3
Enter item #1: 6
Enter item #2: 4
Enter item #3: 2008The current items inside the list are:
Item #1: 6
Item #2: 4
Item #3: 2008