将IEnumerable <dynamic>转换为DataTable

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

为什么动态? 因为我可能会在表中添加新列,我不想更改我的代码以适应它。

然后,我将IEnumerable转换为数据表。 我有一个问题来获取动态对象内的属性。 任何人都可以帮助我?

这是我的代码:

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 ,它使您可以公开数据记录中的属性(此处为完整示例):

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对象,它的行为与原始代码类似)。


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

作为财产的唯一可能的候选人可能是:

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

但它是一种方法。 此外,方法/属性名称丢失。

在你的情况下,属性应该像这样声明:

private IDataRecord _dataRecord;

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

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

链接地址: http://www.djcxy.com/p/18725.html

上一篇: Convert IEnumerable<dynamic> to DataTable

下一篇: How to calculate number of polygons that are formed by sequence of points in 2D?