I am working on a "ToDataTable()" that I can use on IEnumerable. Lots of examples of that, such as here: Convert IEnumerable to DataTable. DataTables are desirable for me because I am coming from a Foxpro realm where we are used to cursors. So, I dig DataTables because they are relatively simple, easy to work with, and can house decent column meta-data. And I've already got a bunch of code that can display them nicely (with sort and filter grid headers), export to Excel, etc.
Someone made a really good comment on the thread I linked to above: "This doesn't work if the type is string as the properties are chars and length but the get value then tries to put the full string into the char column."
Problem is, certain things fit the extension method call (like a one-dimensional array of strings) but then throw errors when the conversion takes place. I wrote an extension method that adds IsValueType() to the mix, but the problem is that returns false for strings. Finally, I added two kludges: a parameter to force the item in the sequence to be treated as a value, and to treat the item in the sequence as a value if it is found to be of type "String".
Is there a better way of doing this, or are strings just an odd man out due to their unique breed of typing and reflection results? The following code works with every IEnumerable I can think of, and it works with one-dimensional arrays for all DataTable column types except for string (well, it works for strings with the hard-coded kludge, but would error out otherwise). It's not a big deal to hard-code the string scenario, I just wish there was a more elegant way to do this. Any thoughts?
public static DataTable ToDataTable<T>(this IEnumerable<T> items, string tableName = "", bool treatItemAsValue = false)
{
// We want a single extension method that can take in an enumerable sequence (such as a LINQ query)
// and return the result as a DataTable. We want this to be a one stop shop for converting
// various objects into DataTable format, as DataTables are a nice parallel to Foxpro cursors.
if (items == null) { return null; }
Type itemType = typeof(T);
bool typeIsNullable = itemType.IsGenericType && typeof(T).GetGenericTypeDefinition().Equals(typeof(Nullable<>));
string itemTypeName = "";
bool typeIsValue = false;
Type itemUnderlyingType = itemType;
if (typeIsNullable)
{
// Type of enumerable item is nullable, so we need to find its base type.
itemUnderlyingType = Nullable.GetUnderlyingType(itemType);
}
typeIsValue = itemUnderlyingType.IsValueType;
itemTypeName = itemUnderlyingType.Name;
DataTable dt = new DataTable();
DataColumn col = null;
if ((treatItemAsValue) || (itemTypeName == "String"))
{
// We have been asked to treat the item in the sequence as a value, of the items
// in the sequence are strings. Strings are NOT considered a value type in regards
// to IsValueType(), but when item values are assessed, it will be the value
// of the string that tries to pull in.
typeIsValue = true;
}
if (itemTypeName == "DataRow")
{
// Special case. If our enumerable type is DataRow, then we can utilize a more appropriate
// (built-in) extension method to convert enumerable DataRows to a DataTable.
dt = ((IEnumerable<DataRow>)items).CopyToDataTable();
}
else
{
// We must have an enumerable sequence/collection of some other type, possibly anonymous.
// Get properties of the enumerable to add as columns to the data table.
if (typeIsValue)
{
// Our enumerable items are of a value type (e.g. integers in a one-dimensional array).
col = dt.Columns.Add();
col.AllowDBNull = typeIsNullable;
col.ColumnName = itemTypeName;
col.DataType = itemUnderlyingType;
// Now walk through the enumeration and add rows to our data table (single values).
foreach (var item in items)
{
dt.Rows.Add(item);
}
}
else
{
// The type should be something we can walk through the properties of in order to
// generate properly named and typed columns of our DataTable.
PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in props)
{
Type propType = prop.PropertyType;
// Is it a nullable type? Get the underlying type.
if (propType.IsGenericType && propType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
propType = new NullableConverter(propType).UnderlyingType;
}
dt.Columns.Add(prop.Name, propType);
}
// Now walk through the enumeration and add rows to our data table.
foreach (var item in items)
{
var values = new object[props.Length];
for (int i = 0; i < props.Length; i++)
{
values[i] = props[i].GetValue(item, null);
}
dt.Rows.Add(values);
}
}
}
// Give the DataTable a reasonable name.
if (tableName.Length == 0)
{
if (typeof(T).IsAnonymous())
{
// Anonymous types have really goofy names, so there is no use using that as table name.
tableName = "Anonymous";
}
else
{
// This is NOT an anonymous type, so we can use the type name as table name.
tableName = typeof(T).Name;
}
}
return dt;
}
