Convert Ienumerable to Datatable

Convert IEnumerable to DataTable

Look at this one: Convert List/IEnumerable to DataTable/DataView

In my code I changed it into a extension method:

public static DataTable ToDataTable<T>(this List<T> items)
{
    var tb = new DataTable(typeof(T).Name);

    PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

    foreach(var prop in props)
    {
        tb.Columns.Add(prop.Name, prop.PropertyType);
    }

     foreach (var item in items)
    {
       var values = new object[props.Length];
        for (var i=0; i<props.Length; i++)
        {
            values[i] = props[i].GetValue(item, null);
        }

        tb.Rows.Add(values);
    }

    return tb;
}

An effecient way to convert from IEnumerable T to DataTable

Instead of going through a DataTable, I would implement an IDataReader for your collection and feed that to SqlBulkCopy. If done correctly, and using a lazy IEnumerable, it would be much faster, and use much less memory than the datatable route. Mark Gravell has already written such a library for converting IEnumerables to an IDataReader, and I would recommend you check that out before rolling your own.

FastMember can be found on NuGet here: https://www.nuget.org/packages/FastMember/
with the original source found here: https://code.google.com/p/fast-member/ with an example in this thread here: SqlBulkCopy from a List<>

UPDATE:
You may also need to change your command timeout, and set the batch size on the sqlbulkcopy, like this:

using (SqlCommand cmd = new SqlCommand(modelManager.GetInsertSproc(), sqlconn) { 
  CommandType = CommandType.StoredProcedure, CommandTimeout=300 })

and

bcp.BatchSize = 100000;

Convert IEnumerable string array to datatable

since, in your linked example, the file has a header row.

const char Delimiter = '|';

var dt = new DataTable;
using (var m = File.ReadLines(filePath).GetEnumerator())
{
    m.MoveNext();
    foreach (var name in m.Current.Split(Delimiter))
    {
        dt.Columns.Add(name);
    }

    while (m.MoveNext())
    {
        dt.Rows.Add(m.Current.Split(Delimiter));
    }
}

This reads the file in one pass.

Convert generic List/Enumerable to DataTable?

Here's a nice 2013 update using FastMember from NuGet:

IEnumerable<SomeType> data = ...
DataTable table = new DataTable();
using(var reader = ObjectReader.Create(data)) {
    table.Load(reader);
}

This uses FastMember's meta-programming API for maximum performance. If you want to restrict it to particular members (or enforce the order), then you can do that too:

IEnumerable<SomeType> data = ...
DataTable table = new DataTable();
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description")) {
    table.Load(reader);
}

Editor's Dis/claimer: FastMember is a Marc Gravell project. It's gold and full-on flies!

---

Yes, this is pretty much the exact opposite of this one; reflection would suffice - or if you need quicker, HyperDescriptor in 2.0, or maybe Expression in 3.5. Actually, HyperDescriptor should be more than adequate.

For example:

// remove "this" if not on C# 3.0 / .NET 3.5
public static DataTable ToDataTable<T>(this IList<T> data)
{
    PropertyDescriptorCollection props =
        TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for(int i = 0 ; i < props.Count ; i++)
    {
        PropertyDescriptor prop = props[i];
        table.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = props[i].GetValue(item);
        }
        table.Rows.Add(values);
    }
    return table;        
}

Now with one line you can make this many many times faster than reflection (by enabling HyperDescriptor for the object-type T).

---

Edit re performance query; here's a test rig with results:

Vanilla 27179
Hyper   6997

I suspect that the bottleneck has shifted from member-access to DataTable performance... I doubt you'll improve much on that...

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
public class MyData
{
    public int A { get; set; }
    public string B { get; set; }
    public DateTime C { get; set; }
    public decimal D { get; set; }
    public string E { get; set; }
    public int F { get; set; }
}

static class Program
{
    static void RunTest(List<MyData> data, string caption)
    {
        GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced);
        GC.WaitForPendingFinalizers();
        GC.WaitForFullGCComplete();
        Stopwatch watch = Stopwatch.StartNew();
        for (int i = 0; i < 500; i++)
        {
            data.ToDataTable();
        }
        watch.Stop();
        Console.WriteLine(caption + "\t" + watch.ElapsedMilliseconds);
    }
    static void Main()
    {
        List<MyData> foos = new List<MyData>();
        for (int i = 0 ; i < 5000 ; i++ ){
            foos.Add(new MyData
            { // just gibberish...
                A = i,
                B = i.ToString(),
                C = DateTime.Now.AddSeconds(i),
                D = i,
                E = "hello",
                F = i * 2
            });
        }
        RunTest(foos, "Vanilla");
        Hyper.ComponentModel.HyperTypeDescriptionProvider.Add(
            typeof(MyData));
        RunTest(foos, "Hyper");
        Console.ReadLine(); // return to exit        
    }
}

Convert IEnumerable dynamic to DataTable

You cannot use the reflection APIs enumerate the dynamically bound members of a DynamicObject. You can only bind to them on demand, by name. Your code, as written, will only return the properties defined on the actual DynamicObject class, which defines no properties (hence the empty array).

As an alternative to using reflection, you could have your DataRecordDynamicWrapper implement ICustomTypeDescriptor, which gives you a way to expose the properties on your data record (complete example here):

public class DataRecordDynamicWrapper : DynamicObject, ICustomTypeDescriptor
{
    private IDataRecord _dataRecord;
    private PropertyDescriptorCollection _properties;

    //
    // (existing members)
    //

    PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties()
    {
        if (_properties == null)
            _properties = GenerateProperties();
        return _properties;
    }

    private PropertyDescriptorCollection GenerateProperties()
    {
        var count = _dataRecord.FieldCount;
        var properties = new PropertyDescriptor[count];

        for (var i = 0; i < count; i++)
        {
            properties[i] = new DataRecordProperty(
                i,
                _dataRecord.GetName(i),
                _dataRecord.GetFieldType(i));
        }

        return new PropertyDescriptorCollection(properties);
    }

    //
    // (implement other ICustomTypeDescriptor members...)
    //

    private sealed class DataRecordProperty : PropertyDescriptor
    {
        private static readonly Attribute[] NoAttributes = new Attribute[0];

        private readonly int _ordinal;
        private readonly Type _type;

        public DataRecordProperty(int ordinal, string name, Type type)
            : base(name, NoAttributes)
        {
            _ordinal = ordinal;
            _type = type;
        }

        public override bool CanResetValue(object component)
        {
            return false;
        }

        public override object GetValue(object component)
        {
            var wrapper = ((DataRecordDynamicWrapper)component);
            return wrapper._dataRecord.GetValue(_ordinal);
        }

        public override void ResetValue(object component)
        {
            throw new NotSupportedException();
        }

        public override void SetValue(object component, object value)
        {
            throw new NotSupportedException();
        }

        public override bool ShouldSerializeValue(object component)
        {
            return true;
        }

        public override Type ComponentType
        {
            get { return typeof(IDataRecord); }
        }

        public override bool IsReadOnly
        {
            get { return true; }
        }

        public override Type PropertyType
        {
            get { return _type; }
        }
    }
}

You could then modify your EnumToDataTable() method to use the System.ComponenetModel APIs instead of System.Reflection:

public static DataTable EnumToDataTable<T>(IEnumerable<T> l_oItems)
{
    var firstItem = l_oItems.FirstOrDefault();
    if (firstItem == null)
        return new DataTable();

    DataTable oReturn = new DataTable(TypeDescriptor.GetClassName(firstItem));
    object[] a_oValues;
    int i;

    var properties = TypeDescriptor.GetProperties(firstItem);

    foreach (PropertyDescriptor property in properties)
    {
        oReturn.Columns.Add(property.Name, BaseType(property.PropertyType));
    }

    //#### Traverse the l_oItems
    foreach (T oItem in l_oItems)
    {
        //#### Collect the a_oValues for this loop
        a_oValues = new object[properties.Count];

        //#### Traverse the a_oProperties, populating each a_oValues as we go
        for (i = 0; i < properties.Count; i++)
            a_oValues[i] = properties[i].GetValue(oItem);

        //#### .Add the .Row that represents the current a_oValues into our oReturn value
        oReturn.Rows.Add(a_oValues);
    }

    //#### Return the above determined oReturn value to the caller
    return oReturn;
}

The upside to this approach is that EnumToDataTable() will fall back to the standard type descriptor for items which do not implement ICustomTypeDescriptor (e.g., for a plain old CLR object, it will behave similarly to your original code).

Revisiting IEnumerable to DataTable extension method -- Issue with strings

Well, no responses except a comment that pretty much missed the point, so I'll just post what I ended up with. This final version also accounts for items with no properties (such as "Object" itself), and does some better handing of null items/values:

    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;
        PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        if ((treatItemAsValue) || (itemTypeName == "String") || (props.Length == 0))
        {
            // We have been asked to treat the item in the sequence as a value, or the items
            // in the sequence is a string which cannot be "flattened" properly by analyzing properties.
            // OR, the type has no properties to put on display, so we should just use the item directly.
            // (like the base "Object" type).
            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();
                // Whether or not the type is nullable, the value might be null (e.g. for type "Object").
                col.AllowDBNull = true;
                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.
                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)
                {
                    if (item != null)
                    {
                        // Can only add an item as a row if it is not null.
                        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;
    }

Hope someone finds this useful...

---

Related Topics