.NET - convert a shared collection to a DataTable

Question

I am trying to convert a general collection (List) to a DataTable. I found the following code to help me do this:

// Sorry about indentation public class CollectionHelper { private CollectionHelper() { } // this is the method I have been using public static DataTable ConvertTo<T>(IList<T> list) { DataTable table = CreateTable<T>(); Type entityType = typeof(T); PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType); foreach (T item in list) { DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) { row[prop.Name] = prop.GetValue(item); } table.Rows.Add(row); } return table; } public static DataTable CreateTable<T>() { Type entityType = typeof(T); DataTable table = new DataTable(entityType.Name); PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType); foreach (PropertyDescriptor prop in properties) { // HERE IS WHERE THE ERROR IS THROWN FOR NULLABLE TYPES table.Columns.Add(prop.Name, prop.PropertyType); } return table; } } 

My problem is that when I change one of the properties of MySimpleClass to a type with a null value, I get the following error:

DataSet does not support System.Nullable<>.

How can I do this using Nullable properties / fields in my class?

Answer 1

Then, probably, you will need to raise them to a non- null form using Nullable.GetUnderlyingType and possibly change some null values ​​to DbNull.Value ...

Change destination:

 row[prop.Name] = prop.GetValue(item) ?? DBNull.Value; 

and when adding columns:

 table.Columns.Add(prop.Name, Nullable.GetUnderlyingType( prop.PropertyType) ?? prop.PropertyType); 

And it works. ( ?? is an operator with zero coalescence, it uses the first operand if it is not equal to zero, otherwise the second operand is evaluated and used)

Answer 2

Well. Since the DataSet does not support nullable types, you will need to check if the property is a generic type, get a generic definition of that type, and then get the argument (which is the actual type) using possibly Nullable.GetUnderlyingType . If the value is null, just use DBNull.Value in the DataSet.

Answer 3

If Nullable.GetUnderlyingType() , if your prop.PropertyType returns non-null, use it as a column type. Otherwise use prop.PropertyType .

Answer 4

Here is the version with some changes, allowing to allow zeros and characters '\ 0', without exploding DataTable.

 using System; using System.Collections; using System.Collections.Generic; using System.Reflection; using System.Data; namespace SomeNamespace { public static class Extenders { public static DataTable ToDataTable<T>(this IEnumerable<T> collection, string tableName) { DataTable tbl = ToDataTable(collection); tbl.TableName = tableName; return tbl; } public static DataTable ToDataTable<T>(this IEnumerable<T> collection) { DataTable dt = new DataTable(); Type t = typeof(T); PropertyInfo[] pia = t.GetProperties(); object temp; DataRow dr; for (int i = 0; i < pia.Length; i++ ) { dt.Columns.Add(pia[i].Name, Nullable.GetUnderlyingType(pia[i].PropertyType) ?? pia[i].PropertyType); dt.Columns[i].AllowDBNull = true; } //Populate the table foreach (T item in collection) { dr = dt.NewRow(); dr.BeginEdit(); for (int i = 0; i < pia.Length; i++) { temp = pia[i].GetValue(item, null); if (temp == null || (temp.GetType().Name == "Char" && ((char)temp).Equals('\0'))) { dr[pia[i].Name] = (object)DBNull.Value; } else { dr[pia[i].Name] = temp; } } dr.EndEdit(); dt.Rows.Add(dr); } return dt; } } } 

Answer 5

I know this question is old, but I had the same problem for the extension method that I did. Using an answer from Mark Gravell, I was able to change my code. This extension method will process lists of primitive types, strings, enumerations, and objects with primitive properties.

 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Linq; using System.Text; /// <summary> /// Converts a List&lt;T&gt; to a DataTable. /// </summary> /// <typeparam name="T">The type of the list collection.</typeparam> /// <param name="list">List instance reference.</param> /// <returns>A DataTable of the converted list collection.</returns> public static DataTable ToDataTable<T>(this List<T> list) { var entityType = typeof (T); // Lists of type System.String and System.Enum (which includes enumerations and structs) must be handled differently // than primitives and custom objects (eg an object that is not type System.Object). if (entityType == typeof (String)) { var dataTable = new DataTable(entityType.Name); dataTable.Columns.Add(entityType.Name); // Iterate through each item in the list. There is only one cell, so use index 0 to set the value. foreach (T item in list) { var row = dataTable.NewRow(); row[0] = item; dataTable.Rows.Add(row); } return dataTable; } else if (entityType.BaseType == typeof (Enum)) { var dataTable = new DataTable(entityType.Name); dataTable.Columns.Add(entityType.Name); // Iterate through each item in the list. There is only one cell, so use index 0 to set the value. foreach (string namedConstant in Enum.GetNames(entityType)) { var row = dataTable.NewRow(); row[0] = namedConstant; dataTable.Rows.Add(row); } return dataTable; } // Check if the type of the list is a primitive type or not. Note that if the type of the list is a custom // object (eg an object that is not type System.Object), the underlying type will be null. var underlyingType = Nullable.GetUnderlyingType(entityType); var primitiveTypes = new List<Type> { typeof (Byte), typeof (Char), typeof (Decimal), typeof (Double), typeof (Int16), typeof (Int32), typeof (Int64), typeof (SByte), typeof (Single), typeof (UInt16), typeof (UInt32), typeof (UInt64), }; var typeIsPrimitive = primitiveTypes.Contains(underlyingType); // If the type of the list is a primitive, perform a simple conversion. // Otherwise, map the object properties to columns and fill the cells with the properties' values. if (typeIsPrimitive) { var dataTable = new DataTable(underlyingType.Name); dataTable.Columns.Add(underlyingType.Name); // Iterate through each item in the list. There is only one cell, so use index 0 to set the value. foreach (T item in list) { var row = dataTable.NewRow(); row[0] = item; dataTable.Rows.Add(row); } return dataTable; } else { // TODO: // 1. Convert lists of type System.Object to a data table. // 2. Handle objects with nested objects (make the column name the name of the object and print "system.object" as the value). var dataTable = new DataTable(entityType.Name); var propertyDescriptorCollection = TypeDescriptor.GetProperties(entityType); // Iterate through each property in the object and add that property name as a new column in the data table. foreach (PropertyDescriptor propertyDescriptor in propertyDescriptorCollection) { // Data tables cannot have nullable columns. The cells can have null values, but the actual columns themselves cannot be nullable. // Therefore, if the current property type is nullable, use the underlying type (eg if the type is a nullable int, use int). var propertyType = Nullable.GetUnderlyingType(propertyDescriptor.PropertyType) ?? propertyDescriptor.PropertyType; dataTable.Columns.Add(propertyDescriptor.Name, propertyType); } // Iterate through each object in the list adn add a new row in the data table. // Then iterate through each property in the object and add the property value to the current cell. // Once all properties in the current object have been used, add the row to the data table. foreach (T item in list) { var row = dataTable.NewRow(); foreach (PropertyDescriptor propertyDescriptor in propertyDescriptorCollection) { var value = propertyDescriptor.GetValue(item); row[propertyDescriptor.Name] = value ?? DBNull.Value; } dataTable.Rows.Add(row); } return dataTable; } }