.NET - 将通用集合转换为数据表

Question

我想将一个通用集合（List）转换为DataTable。我找到了以下代码来帮助我完成：

// Sorry about indentation
public class CollectionHelper
{
private CollectionHelper()
{
}

// this is the method I have been using
public static DataTable ConvertTo<T>(IList<T> list)
{
    DataTable table = CreateTable<T>();
    Type entityType = typeof(T);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (T item in list)
    {
        DataRow row = table.NewRow();

        foreach (PropertyDescriptor prop in properties)
        {
            row[prop.Name] = prop.GetValue(item);
        }

        table.Rows.Add(row);
    }

    return table;
}    

public static DataTable CreateTable<T>()
{
    Type entityType = typeof(T);
    DataTable table = new DataTable(entityType.Name);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (PropertyDescriptor prop in properties)
    {
        // HERE IS WHERE THE ERROR IS THROWN FOR NULLABLE TYPES
        table.Columns.Add(prop.Name, prop.PropertyType);
    }

    return table;
}
}

我的问题是，当我将MySimpleClass的属性之一更改为可空类型时，我会收到以下错误：

DataSet不支持System.Nullable<>。

如何在我的类中使用可空属性/字段？

- Ronnie Overby

Answer 1

那么你可能需要使用 Nullable.GetUnderlyingType 将它们提升为非可空形式，然后将一些 null 值更改为 DbNull.Value...

将赋值语句更改为：

row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

并且当添加这些列时：

table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(
            prop.PropertyType) ?? prop.PropertyType);

它有效。(??是空合并运算符; 如果第一个操作数非空，则使用第一个操作数，否则计算并使用第二个操作数)

- Marc Gravell

---

当我长大后，我想成为Marc Gravell。 - Ronnie Overby

嗯。空值合并运算符。好漂亮。 - J. Steen

在VB中，使用IF(Nullable.GetUnderlyingType(prop.PropertyType), prop.PropertyType)。该代码行的意思不太清楚，需要更多上下文才能进行准确的翻译。 - GilShalit

Answer 2

好的。由于DataSet不支持可空类型，您需要检查属性是否为通用类型，获取该类型的通用定义，然后使用Nullable.GetUnderlyingType获取参数（即实际类型）。如果值为null，在DataSet中只需使用DBNull.Value。

- metaphor

Answer 3

如果 Nullable.GetUnderlyingType() 返回一个非空值，就使用它作为列的类型，该方法的参数是你的 prop.PropertyType。否则，就使用 prop.PropertyType 本身作为列的类型。

- Anton Gogolev

Answer 4

我知道这个问题很老了，但我也遇到了一个自己写的扩展方法的相同问题。使用 Marc Gravell 的响应，我能够修改我的代码。这个扩展方法可以处理一些基本类型、字符串、枚举和具有基本属性的对象列表。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;

/// <summary>
/// Converts a List&lt;T&gt; to a DataTable.
/// </summary>
/// <typeparam name="T">The type of the list collection.</typeparam>
/// <param name="list">List instance reference.</param>
/// <returns>A DataTable of the converted list collection.</returns>
public static DataTable ToDataTable<T>(this List<T> list)
{
    var entityType = typeof (T);

    // Lists of type System.String and System.Enum (which includes enumerations and structs) must be handled differently 
    // than primitives and custom objects (e.g. an object that is not type System.Object).
    if (entityType == typeof (String))
    {
        var dataTable = new DataTable(entityType.Name);
        dataTable.Columns.Add(entityType.Name);

        // Iterate through each item in the list. There is only one cell, so use index 0 to set the value.
        foreach (T item in list)
        {
            var row = dataTable.NewRow();
            row[0] = item;
            dataTable.Rows.Add(row);
        }

        return dataTable;
    }
    else if (entityType.BaseType == typeof (Enum))
    {
        var dataTable = new DataTable(entityType.Name);
        dataTable.Columns.Add(entityType.Name);

        // Iterate through each item in the list. There is only one cell, so use index 0 to set the value.
        foreach (string namedConstant in Enum.GetNames(entityType))
        {
            var row = dataTable.NewRow();
            row[0] = namedConstant;
            dataTable.Rows.Add(row);
        }

        return dataTable;
    }

    // Check if the type of the list is a primitive type or not. Note that if the type of the list is a custom 
    // object (e.g. an object that is not type System.Object), the underlying type will be null.
    var underlyingType = Nullable.GetUnderlyingType(entityType);
    var primitiveTypes = new List<Type>
    {
        typeof (Byte),
        typeof (Char),
        typeof (Decimal),
        typeof (Double),
        typeof (Int16),
        typeof (Int32),
        typeof (Int64),
        typeof (SByte),
        typeof (Single),
        typeof (UInt16),
        typeof (UInt32),
        typeof (UInt64),
    };

    var typeIsPrimitive = primitiveTypes.Contains(underlyingType);

    // If the type of the list is a primitive, perform a simple conversion.
    // Otherwise, map the object's properties to columns and fill the cells with the properties' values.
    if (typeIsPrimitive)
    {
        var dataTable = new DataTable(underlyingType.Name);
        dataTable.Columns.Add(underlyingType.Name);

        // Iterate through each item in the list. There is only one cell, so use index 0 to set the value.
        foreach (T item in list)
        {
            var row = dataTable.NewRow();
            row[0] = item;
            dataTable.Rows.Add(row);
        }

        return dataTable;
    }
    else
    {
        // TODO:
        // 1. Convert lists of type System.Object to a data table.
        // 2. Handle objects with nested objects (make the column name the name of the object and print "system.object" as the value).

        var dataTable = new DataTable(entityType.Name);
        var propertyDescriptorCollection = TypeDescriptor.GetProperties(entityType);

        // Iterate through each property in the object and add that property name as a new column in the data table.
        foreach (PropertyDescriptor propertyDescriptor in propertyDescriptorCollection)
        {
            // Data tables cannot have nullable columns. The cells can have null values, but the actual columns themselves cannot be nullable.
            // Therefore, if the current property type is nullable, use the underlying type (e.g. if the type is a nullable int, use int).
            var propertyType = Nullable.GetUnderlyingType(propertyDescriptor.PropertyType) ?? propertyDescriptor.PropertyType;
            dataTable.Columns.Add(propertyDescriptor.Name, propertyType);
        }

        // Iterate through each object in the list adn add a new row in the data table.
        // Then iterate through each property in the object and add the property's value to the current cell.
        // Once all properties in the current object have been used, add the row to the data table.
        foreach (T item in list)
        {
            var row = dataTable.NewRow();

            foreach (PropertyDescriptor propertyDescriptor in propertyDescriptorCollection)
            {
                var value = propertyDescriptor.GetValue(item);
                row[propertyDescriptor.Name] = value ?? DBNull.Value;
            }

            dataTable.Rows.Add(row);
        }

        return dataTable;
    }
}

- Halcyon

---

这个可以工作！但只适用于可空类型。在“bool typeIsPrimitive = primitiveTypes.Contains(underlyingType);”之前添加语句“if (underlyingType == null) underlyingType = entityType;”，以便它也适用于List<int>。感谢您的分享！ - Krisztián Balla

代码运行得很好，但是为了避免与"CS1106扩展方法必须定义在非泛型静态类中"错误冲突，我不得不从ToDataTable<T>(this List<T> list)中删除了'this'。 - Fandango68

Answer 5

这是一个经过修改的版本，允许空值和'\0'字符而不会破坏DataTable。

using System;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Data;

namespace SomeNamespace
{
    public static class Extenders
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> collection, string tableName)
        {
            DataTable tbl = ToDataTable(collection);
            tbl.TableName = tableName;
            return tbl;
        }

        public static DataTable ToDataTable<T>(this IEnumerable<T> collection)
        {
            DataTable dt = new DataTable();
            Type t = typeof(T);
            PropertyInfo[] pia = t.GetProperties();
            object temp;
            DataRow dr;

            for (int i = 0; i < pia.Length; i++ )
            {
                dt.Columns.Add(pia[i].Name, Nullable.GetUnderlyingType(pia[i].PropertyType) ?? pia[i].PropertyType);
                dt.Columns[i].AllowDBNull = true;
            }

            //Populate the table
            foreach (T item in collection)
            {
                dr = dt.NewRow();
                dr.BeginEdit();

                for (int i = 0; i < pia.Length; i++)
                {
                    temp = pia[i].GetValue(item, null);
                    if (temp == null || (temp.GetType().Name == "Char" && ((char)temp).Equals('\0')))
                    {
                        dr[pia[i].Name] = (object)DBNull.Value;
                    }
                    else
                    {
                        dr[pia[i].Name] = temp;
                    }
                }

                dr.EndEdit();
                dt.Rows.Add(dr);
            }
            return dt;
        }

    }
}

- landers