c# – 将IEnumerable转换为DataTable

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;
        }
    }

您不能使用反射API枚举DynamicObject的动态绑定成员.您只能按需按名称绑定到它们.您编写的代码将仅返回在实际DynamicObject类上定义的属性,该类不定义任何属性(因此为空数组).

作为使用反射的替代方法,您可以让DataRecordDynamicWrapper实现ICustomTypeDescriptor,这样您就可以在数据记录中公开属性(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; }
        }
    }
}

然后,您可以修改EnumToDataTable()方法以使用System.ComponenetModel API而不是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;
}

这种方法的好处是EnumToDataTable()将回退到没有实现ICustomTypeDescriptor的项的标准类型描述符(例如,对于普通的旧CLR对象,它将表现得与原始代码类似).