将IEnumerable<dynamic>转换为DataTable

Question

我查询数据库以获取数据。它可能有多行。我将它们保存到IEnumerable中。

为什么要使用dynamic？因为我可能会在表中添加新列，我不想再改动我的代码来适应这种变化。

然后，我将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;
        }
    }

- king jia

---

哪个问题？您的数组 a_oProperties 是空的吗？ - Alberto Solano

@AlbertoSolano 是的，它是空的... 但是l_oItems里面有一些东西。 - king jia

Answer 1

您无法使用反射API枚举动态绑定的DynamicObject成员。您只能按名称按需绑定它们。如写入的代码，仅返回实际DynamicObject类定义的属性，该类未定义任何属性（因此为空数组）。

作为使用反射的替代方案，您可以使您的DataRecordDynamicWrapper实现ICustomTypeDescriptor，这将为您提供一种公开数据记录属性的方法（完整示例在此处）。

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对象），回退到标准类型描述符的行为，它将类似于您的原始代码。

- Mike Strobel

---

哇，真的是一份非常好的解释和答案。你值得我给你一个赞 :) - king jia

Answer 2

数组a_oProperties为空，因为您在类DataRecordDynamicWrapper中没有声明任何公共属性。根据文档，GetProperties()方法返回当前类型的所有公共属性。

唯一可能的候选属性是：

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

但这是一种方法。此外，该方法/属性名称缺失。

在您的情况下，属性应该声明为：

private IDataRecord _dataRecord;

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

有关属性的更多信息在这里。

- Alberto Solano