C# 如何将 List<T> 转换为 DataSet？

How do I transform a List<T> into a DataSet?

提问by mezoid

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.Cellsfunction in order to create an Excel document as a report.

给定一个对象列表，我需要将其转换为一个数据集，其中列表中的每个项目由一行表示，每个属性是行中的一列。然后将此数据集传递给Aspose.Cells函数以创建 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:

给定一个 List 记录，如何将其转换为 DataSet 如下：

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.

但是这种方式让我认为必须有更好的方法，因为至少如果将新属性添加到 Record 那么它们将不会出现在 DataSet 中......但同时它允许我控制每个的顺序属性被添加到行中。

Does anyone know of a better way to do this?

有谁知道更好的方法来做到这一点？

采纳答案by CMS

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

回答by Mitch Wheat

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

除了另外使用Reflection来确定类的属性Record来处理添加新属性之外，仅此而已。

回答by Johannes Rudolph

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.

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

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

它的速度非常快。您可以在这里找到它：GoogleCode 上的 ModelShredder

回答by Tlatoani

I made some changes to CMS' extension method to handle the case when the Listcontains primitive or Stringelements. In that case the resulting DataTablewill only have one Columnwith a Rowfor each of the values in the list.

我对 CMS 的扩展方法进行了一些更改，以处理List包含原语或String元素的情况。在这种情况下，结果DataTable将只有一个Column，Row列表中的每个值都带有 a 。

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 DataTableto use it as a Table-Valued Parameter in a MS SQL 2008 Stored Procedure.

这种变化源于我需要将 a List(Of Long), or List<long>, 转换为 aDataTable以将其用作 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

回答by kumar chandraketu

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 objectdsCollection)

1) public static DataSet ToDataSetFromObject(这个对象dsCollection)

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

2) public static DataSet ToDataSetFromArrayOfObject(this object[]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();
}