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

.NET - Convert Generic Collection to DataTable

提问by Ronnie Overby

I am trying to convert a generic collection (List) to a DataTable. I found the following code to help me do this:

我正在尝试将通用集合（列表）转换为数据表。我找到了以下代码来帮助我做到这一点：

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

My problem is that when I change one of the properties of MySimpleClass to a nullable type, I get the following error:

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

DataSet does not support System.Nullable<>.

DataSet does not support System.Nullable<>.

How can I do this with Nullable properties/fields in my class?

如何使用班级中的 Nullable 属性/字段执行此操作？

采纳答案by Marc Gravell

Then presumably you'll need to lift them to the non-nullable form, using Nullable.GetUnderlyingType, and perhaps change a few nullvalues to DbNull.Value...

然后大概您需要将它们提升为不可为空的形式，使用Nullable.GetUnderlyingType，并且可能将一些null值更改为DbNull.Value...

Change the assignment to be:

将分配更改为：

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

and when adding the columns to be:

并在添加列时：

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

And it works. (??is the null-coalescing operator; it uses the first operand if it is non-null, else the second operand is evaluated and used)

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

回答by J. Steen

Well. Since DataSet does not support nullable types, you'd have to check if the property is a generic type, get the generic definition of that type and then get the argument (which is the actual type) using, perhaps, Nullable.GetUnderlyingType. If the value is null, just use DBNull.Valuein the DataSet.

好。由于 DataSet 不支持可为 null 的类型，因此您必须检查该属性是否为泛型类型，获取该类型的泛型定义，然后使用Nullable.GetUnderlyingType. 如果该值为空，则仅DBNull.Value在 DataSet 中使用。

回答by Anton Gogolev

If Nullable.GetUnderlyingType()given your prop.PropertyTypereturns a not-null value, use that as the type of a column. Otherwise, use prop.PropertyTypeitself.

如果Nullable.GetUnderlyingType()给定您prop.PropertyType返回的非空值，请将其用作列的类型。否则，使用prop.PropertyType自身。

回答by Halcyon

I know this question is old, but I had the same issue for an extension method I made. Using the response from Marc Gravell, I was able to modify my code. This extension method will handle lists of primitive types, strings, enumerations and objects with primitive properties.

我知道这个问题很老，但我对我制作的扩展方法有同样的问题。使用 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;
    }
}

回答by landers

Here's a version with some modifications to allow for nulls and '\0' characters without blowing up the DataTable.

这是一个经过一些修改的版本，允许使用空值和 '\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('##代码##')))
                    {
                        dr[pia[i].Name] = (object)DBNull.Value;
                    }
                    else
                    {
                        dr[pia[i].Name] = temp;
                    }
                }

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

    }
}