Convert IEnumerable <dynamic> to DataTable

Question

I am querying a database to retrieve data. It can have more than 1 line. I save them in IEnumerable.

Why the dynamics? because I could add a new column to the table, and I don't want to change my code in order to adapt it again.

Then I convert IEnumerable to datatable. I have a problem getting properties inside a dynamic object. Can anybody 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 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 API to list dynamically related members of a DynamicObject . You can only be attached to them on demand, by name. Your code, as written, will return only properties defined in the actual DynamicObject class, which does not define properties (hence, an empty array).

As an alternative to using reflection, you can use the DataRecordDynamicWrapper ICustomTypeDescriptor , which provides you with a way to display the properties in your data record ( full 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; } } } } 

Then you can change your EnumToDataTable() method to use the System.ComponenetModel API 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 approach to this approach is that EnumToDataTable() will return to the standard type descriptor for elements that do not implement ICustomTypeDescriptor (for example, for a simple old CLR object, it will behave similar to the source code).

Answer 2

The a_oProperties array a_oProperties empty because you do not have a public property declared in the DataRecordDynamicWrapper class. Indeed, according to the documentation , the GetProperties() method returns all public properties of the current type.

The only possible candidate for a property can be:

 public DataRecordDynamicWrapper(IDataRecord dataRecord) { _dataRecord = dataRecord; } 

but it is a method. In addition, the method / property name is missing.

The property in your case should be declared as follows:

 private IDataRecord _dataRecord; public IDataRecord DataRecord { set{ _dataRecord = value; } get{ return _dataRecord; } } 

Read more about properties here .