如何将列表转换为数据集?

Given a list of objects, I am needing to transform it into a dataset where each item in the list is represented by a row and each property is a column in the row. This DataSet will then be passed to an Aspose.Cells function in order to create an Excel document as a report.

给定一个对象列表，我需要将其转换为一个数据集，其中列表中的每个项都由一行表示，并且每个属性都是行中的一个列。这个数据集将被传递给一个Aspose。单元格的功能是创建一个Excel文档作为报告。

Say I have the following:

说我有以下几点:

public class Record
{
   public int ID { get; set; }
   public bool Status { get; set; }
   public string Message { get; set; }
}

Given a List records, how do I transform it into a DataSet as follows:

给定一个列表记录，如何将其转换为数据集如下:

ID Status Message
1  true   "message" 
2  false  "message2" 
3  true   "message3" 
...

At the moment the only thing I can think of is as follows:

目前我唯一能想到的是:

DataSet ds = new DataSet
ds.Tables.Add();
ds.Tables[0].Add("ID", typeof(int));    
ds.Tables[0].Add("Status", typeof(bool));
ds.Tables[0].Add("Message", typeof(string));

foreach(Record record in records)
{
    ds.Tables[0].Rows.Add(record.ID, record.Status, record.Message);
}

But this way leaves me thinking there must be a better way since at the very least if new properties are added to Record then they won't show up in the DataSet...but at the same time it allows me to control the order each property is added to the row.

但这种方式让我觉得肯定有更好的方法，因为至少如果添加了新属性，它们就不会出现在数据集中……但是同时它允许我控制每个属性被添加到行中的顺序。

Does anyone know of a better way to do this?

有人知道更好的方法吗?

5 个解决方案

#1

You can do it through reflection and generics, inspecting the properties of the underlying type.

您可以通过反射和泛型来完成，检查底层类型的属性。

Consider this extension method that I use:

考虑一下我使用的扩展方法:

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

        //Inspect the properties and create the columns in the DataTable
        foreach (PropertyInfo pi in pia)
        {
            Type ColumnType = pi.PropertyType;
            if ((ColumnType.IsGenericType))
            {
                ColumnType = ColumnType.GetGenericArguments()[0];
            }
            dt.Columns.Add(pi.Name, ColumnType);
        }

        //Populate the data table
        foreach (T item in collection)
        {
            DataRow dr = dt.NewRow();
            dr.BeginEdit();
            foreach (PropertyInfo pi in pia)
            {
                if (pi.GetValue(item, null) != null)
                {
                    dr[pi.Name] = pi.GetValue(item, null);
                }
            }
            dr.EndEdit();
            dt.Rows.Add(dr);
        }
        return dt;
    }

#2

Apart from additionally using Reflection to determine the properties of class Record to take care of adding new properties, that's pretty much it.

除了额外使用反射来确定类记录的属性来处理添加新属性之外，这是非常重要的。

#3

I've written a small library myself to accomplish this task. It uses reflection only for the first time an object type is to be translated to a datatable. It emits a method that will do all the work translating an object type.

我自己写了一个小图书馆来完成这项任务。它仅在第一次将对象类型转换为datatable时使用反射。它发出一种方法，它将完成所有翻译对象类型的工作。

Its blazing fast. You can find it here: ModelShredder on GoogleCode

它的速度极快。你可以在这里找到它:GoogleCode上的ModelShredder。

#4

I made some changes to CMS' extension method to handle the case when the List contains primitive or String elements. In that case the resulting DataTable will only have one Column with a Row for each of the values in the list.

当列表包含原始元素或字符串元素时，我对CMS的扩展方法做了一些修改。在这种情况下，生成的DataTable只有一个列，其中每个值都在列表中。

At first I thought of including all value types (not only primitive types) but I didn't want Structures (which are value types) to be included.

起初，我想包括所有的值类型(不仅是基本类型)，而且我不想要包含结构(它们是值类型)。

This change arose from my need of converting a List(Of Long), or List<long>, into a DataTable to use it as a Table-Valued Parameter in a MS SQL 2008 Stored Procedure.

这种变化是由于我需要将一个列表(长)或List< Long >，转换成一个DataTable，在MS SQL 2008存储过程中使用它作为表值参数。

I'm sorry my code is in VB even though this question is tagged c#; my project is in VB (NOT my choice) and it shouldn't be hard to apply the changes in c#.

很抱歉，我的代码是在VB中，尽管这个问题被标记为c#;我的项目是在VB中(不是我的选择)，应用c#的更改并不难。

Imports System.Runtime.CompilerServices
Imports System.Reflection

Module Extensions

    <Extension()>
    Public Function ToDataTable(Of T)(ByVal collection As IEnumerable(Of T)) As DataTable
        Dim dt As DataTable = New DataTable("DataTable")
        Dim type As Type = GetType(T)
        Dim pia() As PropertyInfo = type.GetProperties()

        ' For a collection of primitive types create a 1 column DataTable
        If type.IsPrimitive OrElse type.Equals(GetType(String)) Then
            dt.Columns.Add("Column", type)
        Else
            ' Inspect the properties and create the column in the DataTable
            For Each pi As PropertyInfo In pia
                Dim ColumnType As Type = pi.PropertyType
                If ColumnType.IsGenericType Then
                    ColumnType = ColumnType.GetGenericArguments()(0)
                End If
                dt.Columns.Add(pi.Name, ColumnType)
            Next

        End If

        ' Populate the data table
        For Each item As T In collection
            Dim dr As DataRow = dt.NewRow()
            dr.BeginEdit()
            ' Set item as the value for the lone column on each row
            If type.IsPrimitive OrElse type.Equals(GetType(String)) Then
                dr("Column") = item
            Else
                For Each pi As PropertyInfo In pia
                    If pi.GetValue(item, Nothing) <> Nothing Then
                        dr(pi.Name) = pi.GetValue(item, Nothing)
                    End If
                Next
            End If
            dr.EndEdit()
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

End Module

#5

I found this code on Microsoft forum. This is so far one of easiest way, easy to understand and use. This has saved me hours. I have customized this as extension method without any change to actual implementaion. Below is the code. it doesn't require much explanation.

我在微软论坛上找到了这段代码。这是迄今为止最简单的方法之一，易于理解和使用。这节省了我几个小时。我已经将它定制为扩展方法，而不需要对实际的实现者进行任何更改。下面是代码。这并不需要太多的解释。

You can use two function signature with same implementation

您可以使用具有相同实现的两个函数签名。

1) public static DataSet ToDataSetFromObject(this object dsCollection)

1)公共静态数据集ToDataSetFromObject(对象dsCollection)

2) public static DataSet ToDataSetFromArrayOfObject( this object[] arrCollection). I'll be using this one in below example.

2)公共静态DataSet ToDataSetFromArrayOfObject(该对象[]arrCollection)。我将在下面的例子中使用这个。

// <summary>
// Serialize Object to XML and then read it into a DataSet:
// </summary>
// <param name="arrCollection">Array of object</param>
// <returns>dataset</returns>

public static DataSet ToDataSetFromArrayOfObject( this object[] arrCollection)
{
    DataSet ds = new DataSet();
    try {
        XmlSerializer serializer = new XmlSerializer(arrCollection.GetType);
        System.IO.StringWriter sw = new System.IO.StringWriter();
        serializer.Serialize(sw, dsCollection);
        System.IO.StringReader reader = new System.IO.StringReader(sw.ToString());
        ds.ReadXml(reader);
    } catch (Exception ex) {
        throw (new Exception("Error While Converting Array of Object to Dataset."));
    }
    return ds;
}

To use this extension in code

在代码中使用此扩展。

Country[] objArrayCountry = null;
objArrayCountry = ....;// populate your array
if ((objArrayCountry != null)) {
    dataset = objArrayCountry.ToDataSetFromArrayOfObject();
}