Convert IEnumerable<dynamic> to DataTable

Question

I query to Database to get data. It might has more than 1 row. I save them into IEnumerable.

Why dynamic? because I might add new column in the table and I dont want to change my code to adapt it again.

Then, I convert the IEnumerable to datatable. I have a problem to get the properties inside the dynamic object. Anyone can help me?

Here is my code:

DataTable dt;
string query = "SELECT * FROM WORKSHOP WHERE WORKSHOPID = 1";

// Execute Query
var result = Execute(query);

// Convert IEnumerable<dynamic> to DataTable (I Have Problem Here)
dt = CsvConverter.EnumToDataTable(result);

// Convert DataTable To CSV
var csv = CsvConverter.DataTableToCsv(dt, ",", true);

// Save File
string fileName = Path.GetTempPath() + Guid.NewGuid().ToString() + ".csv";
File.AppendAllText(fileName, csv);

// Method to Execute Query
public IEnumerable<dynamic> Execute(string commandText)
{
   using (var result = databaseManager.ReadData(commandText, false))
      foreach (IDataRecord record in result)
      {
         yield return new DataRecordDynamicWrapper(record);
      }
}

// Wrapper of Dynamic Record
public class DataRecordDynamicWrapper : DynamicObject
{
    private IDataRecord _dataRecord;
    public DataRecordDynamicWrapper(IDataRecord dataRecord) { _dataRecord = dataRecord; }

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        result = _dataRecord[binder.Name];
        return result != null;
    }
}

// Method to Convert Enum to DT
public static DataTable EnumToDataTable<T>(IEnumerable<T> l_oItems)
    {
        DataTable oReturn = new DataTable(typeof (T).Name);
        object[] a_oValues;
        int i;

        //#### Collect the a_oProperties for the passed T
        PropertyInfo[] a_oProperties = typeof (T).GetType().GetProperties();


        //#### Traverse each oProperty, .Add'ing each .Name/.BaseType into our oReturn value
        //####     NOTE: The call to .BaseType is required as DataTables/DataSets do not support nullable types, so it's non-nullable counterpart Type is required in the .Column definition
        foreach (PropertyInfo oProperty in a_oProperties)
        {
            oReturn.Columns.Add(oProperty.Name, BaseType(oProperty.PropertyType));
        }

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

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

            //#### .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;
    }

    public static Type BaseType(Type oType)
    {
        //#### If the passed oType is valid, .IsValueType and is logicially nullable, .Get(its)UnderlyingType
        if (oType != null && oType.IsValueType &&
            oType.IsGenericType && oType.GetGenericTypeDefinition() == typeof (Nullable<>)
            )
        {
            return Nullable.GetUnderlyingType(oType);
        }
            //#### Else the passed oType was null or was not logicially nullable, so simply return the passed oType
        else
        {
            return oType;
        }
    }

Answer 1

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).