.net 将数据表转换为通用列表的最快方法

Fastest way to convert datatable to generic list

提问by Joel Coehoorn

I have a data tier select method that returns a datatable. It's called from a business tier method that should then return a strongly typed generic List.

我有一个返回数据表的数据层选择方法。它是从业务层方法调用的，然后应该返回一个强类型的泛型列表。

What I want to do is very similar (but not the same as) this question:
How do you convert a DataTable into a generic list?

我想要做的与这个问题非常相似（但不相同）：
How do you convert a DataTable into a generic list？

What's different is that I want the list to contain strongly-typed objects rather than datarows (also, I don't have linq avaiable here yet).

不同的是，我希望列表包含强类型对象而不是数据行（此外，我还没有 linq 可用）。

I'm concerned about performance. The business tier method will in turn be called from the presentation tier, and the results will be iterated for display to the user. It seems very wasteful to add an extra iteration at the business tier, only do it again right away for the presentation, so I want this to be as quick as possible.

我担心性能。业务层方法将依次从表示层调用，结果将被迭代以显示给用户。在业务层添加额外的迭代似乎非常浪费，只是为了演示而立即再次执行，所以我希望这尽可能快。

This is a common task, so I'm really looking for a good pattern that can be repeated over and over.

这是一项常见的任务，所以我真的在寻找一个可以反复重复的好模式。

回答by John

The problem with the sample above is that it is terribly slow. I have a DataTable with about 400 rows and this conversion takes a good 5 or 6 seconds!

上面示例的问题在于它非常慢。我有一个大约有 400 行的 DataTable，这个转换需要 5 到 6 秒！

It does seem like a pretty common task, so I'm surprised to not see someone here with a more performant solution.

这似乎是一项非常常见的任务，所以我很惊讶这里没有人提供更高性能的解决方案。

* Update!! *Just for kicks, I thought I would try converting using LINQ instead of iterating through the DataTable and adding to my List. The following is what I did:

* 更新！！*只是为了好玩，我想我会尝试使用 LINQ 进行转换，而不是遍历 DataTable 并添加到我的列表中。以下是我所做的：

   List<MyObject> returnList = new List<MyObject>();

   MyDataTable dtMyData = new MyTableAdapter().GetMyData();

   returnLists = (from l in dtMyData
                 select new MyObject
                 {
                    Active = l.IsActive,
                    Email = l.Email,
                    //...
                    //About 40 more properties
                    //...
                    ZipCode = l.Zipcode
                  }).ToList();

The first method (iterating through each row) took 5.3 seconds and the method using LINQ took 1.8 seconds!

第一种方法（遍历每一行）需要 5.3 秒，使用 LINQ 的方法需要 1.8 秒！

回答by Marc Gravell

Do you know the structure of the DataTableand the typed object ahead of time? You could use a delegate to do the mapping. If you don't (i.e. all you know is a Typeand properties) there are ways of accelerating dynamic member access (such as HyperDescriptor).

您是否DataTable提前知道和 类型对象的结构？您可以使用委托来进行映射。如果您不知道（即您只知道 aType和属性），则有多种方法可以加速动态成员访问（例如HyperDescriptor）。

Either way, consider an iterator block; that way you don't have to buffer the objects an entire second time; of course, if you are only dealing with smallish rowcounts this isn't an issue.

无论哪种方式，请考虑迭代器块；这样你就不必第二次缓冲对象；当然，如果您只处理较小的行数，这不是问题。

Can you clarify any of those points? I can add a lot more detail...

你能澄清其中的任何一点吗？我可以添加更多细节...

At the simplest, what is wrong with:

最简单的，有什么问题：

DataTable table = new DataTable {
    Columns = {
        {"Foo", typeof(int)},
        {"Bar", typeof(string)}
     }
};
for (int i = 0; i < 5000; i++) {
    table.Rows.Add(i, "Row " + i);
}

List<MyType> data = new List<MyType>(table.Rows.Count);
foreach (DataRow row in table.Rows) {
    data.Add(new MyType((int)row[0], (string)row[1]));
}

(the problems in the above might steer the right approach...)

（上面的问题可能会引导正确的方法......）

回答by Thameem

I know this answer is very late but i spent an hour and above to prepare this code because i needed this first then i thought some one in search of a solution for this question may get use of this..

我知道这个答案很晚了，但我花了一个小时或更长时间来准备这段代码，因为我首先需要这个，然后我想有人在寻找这个问题的解决方案可能会用到这个..

Inorder for my answer to work correctly, you need to have same names for your list properties as in the DataBase table-columns(fields) or the DataTable column names.

为了使我的答案正确工作，您的列表属性需要与 DataBase 表列（字段）或 DataTable 列名称中的名称相同。

Solution:

解决方案：

public List<dynamic> GetListFromDT(Type className, DataTable dataTable)
        {
            List<dynamic> list = new List<dynamic>();
            foreach (DataRow row in dataTable.Rows)
            {
                object objClass = Activator.CreateInstance(className);
                Type type = objClass.GetType();
                foreach (DataColumn column in row.Table.Columns)
                {
                    PropertyInfo prop = type.GetProperty(column.ColumnName);
                    prop.SetValue(objClass, row[column.ColumnName], null);   
                }
                list.Add(objClass);
            }
            return list;
        }

How to use ?

如何使用 ？

Lets say you have a values populated datatable named dtPersons

假设您有一个名为 dtPersons 的值填充数据表

DataTable dtPersons; //populate this datatable

Then lets say you have a class with the following properties.

然后假设您有一个具有以下属性的类。

public class Persons{
    public string Name {get; set;}
    public int Age {get; set;}
}

Now pack and put the method in your model. call the method as below.

现在打包并将方法放入您的模型中。调用方法如下。

List<dynamic> dynamicListReturned = GetListFromDT(typeof(Persons), dataTable);
List<Persons> listPersons = dynamicListReturned.Cast<Persons>().ToList();

That's it now you got your list from the datatable in listPersons.

就是这样，现在您从listPersons 的数据表中获得了您的列表。

Remember: Both the names in the class properties and the DataTable/Database should be the same

记住：类属性中的名称和DataTable/Database 中的名称应该相同

回答by Rune Grimstad

You will need to iterate through the datarows and convert them into your objects at some time anyway, but you could write a custom collection with a constructor that takes a collection of datarows and that converts each item into your object type when requested instead of all at once.

您将需要遍历数据行并在某个时候将它们转换为您的对象，但您可以编写一个带有构造函数的自定义集合，该构造函数接受数据行集合，并在请求时将每个项目转换为您的对象类型，而不是全部一次。

Say you define your custom collection that implements IList<T>. It could then have two internal fields - a reference to the collection of datarows and a List<T>. The List<T>would be initialized to the length of the collection of datarows but with null values.

假设您定义了实现IList<T>. 然后它可以有两个内部字段 - 一个对数据行集合的引用和一个List<T>. 在List<T>将被初始化到数据行的集合的长度，但空值。

Now in the indexer you could check if the List<T>contains a value at that index and if not you could create the object using whatever means would be useful and store it there before returning it.

现在在索引器中，您可以检查该索引处是否List<T>包含值，如果没有，您可以使用任何有用的方法创建对象，并在返回之前将其存储在那里。

This would postpone the creation of your objects until they were requested and you would only create the objects that were requested.

这将推迟您的对象的创建，直到它们被请求，并且您将只创建被请求的对象。

Your objects would probably need a constructor taking a DataRow or you would need some sort of factory to create them, but that is another topic.

您的对象可能需要一个带有 DataRow 的构造函数，或者您需要某种工厂来创建它们，但这是另一个主题。

回答by Yordan Georgiev

Just to provide some more user - friendliness to Mark's answer in a simple console app :

只是为了在一个简单的控制台应用程序中为 Mark 的回答提供更多的用户友好性：

class Program
{
    static void Main(string[] args)
    {
        //define a DataTable obj
        DataTable table = new DataTable
        {
            Columns = {
            {"Foo", typeof(int)},
            {"Bar", typeof(string)}
         }
        };
        //populate it the DataTable 
        for (int i = 0; i < 3; i++)
        {
            table.Rows.Add(i, "Row " + i);
        }

        List<MyType> listWithTypedObjects= new List<MyType>(table.Rows.Count);
        foreach (DataRow row in table.Rows)
        {
            listWithTypedObjects.Add(new MyType((int)row[0], (string)row[1]));
        }

        Console.WriteLine(" PRINTING THE POPULATED LIST ");
        foreach (MyType objMyType in listWithTypedObjects)
        {
            Console.Write(" I have object of the type " + objMyType.ToString() + " => " );
            Console.Write(" with Prop1OfTypeInt " + objMyType.Prop1OfTypeInt.ToString() + " , ");
            Console.WriteLine(" with Prop1OfTypeInt " + objMyType.Prop2OfTypeString.ToString() + "  "); 
        }

        Console.WriteLine(" \n \n \n HIT A KEY TO EXIT THE PROGRAM ");
        Console.ReadKey();
    }
}

class MyType {

    public int Prop1OfTypeInt { get; set; }
    public string Prop2OfTypeString { get; set; } 

    /// <summary>
    /// Note the order of the passed parameters is important !!!
    /// </summary>
    public MyType( int prop1OfTypeInt , string prop2OfTypeString)
    {
        this.Prop1OfTypeInt = prop1OfTypeInt;
        this.Prop2OfTypeString = prop2OfTypeString; 

    }
}

回答by Ajay Baldha

public partial class issuereceive_manageroffice_bal
{
    public int issue_id{get;set;}
    public string process{get;set;}
    public DateTime issue_date{get;set;}
    public TimeSpan issue_time{get;set;}
    public string eg_no{get;set;}
    public string lotno{get;set;}
    public string clarity{get;set;}
    public string sieves{get;set;}
    public string shape{get;set;}
    public double issue_carat{get;set;}
    public int issue_pieces{get;set;}
    public int receive_pieces{get;set;}
    public double receive_carat{get;set;}
    public int kp_pieces{get;set;}
    public decimal kp_carat{get;set;}
    public double loss{get;set;}
    public string issue_manager{get;set;}
    public string issue_by{get;set;}
    public string receive_by{get;set;}
    public int status{get;set;}
    public DateTime receive_date{get;set;}
    public string receive_time{get;set;}
    public int factory_id{get;set;}

}


List<issuereceive_manageroffice_bal> issue_receive_list = new List<issuereceive_manageroffice_bal>();
issue_receive_list =
      (from DataRow dr in DataTable.Rows
      select new issuereceive_manageroffice_bal()
           {
               issue_id = 0,
               issue_time = TimeSpan.Parse("0"),
               receive_time = null,
               shape = null,
               process = dr["process"].ToString(),
               issue_date = Convert.ToDateTime(dr["issue_date"]),
               eg_no = dr["eg_no"].ToString(),
               lotno = dr["lotno"].ToString(),
               clarity = dr["clarity"].ToString(),
               sieves = dr["sieves"].ToString(),
               issue_carat = dr["issue_carat"].ToString() != "" ? double.Parse(dr["issue_carat"].ToString()) : 0,
               issue_pieces = dr["issue_pieces"].ToString() != "" ? int.Parse(dr["issue_pieces"].ToString()) : 0,
               receive_carat = dr["receive_carat"].ToString() != "" ? double.Parse(dr["receive_carat"].ToString()) : 0,
               kp_pieces = dr["kp_pieces"].ToString() != "" ? int.Parse(dr["kp_pieces"].ToString()) : 0,
               kp_carat = dr["kp_carat"].ToString() != "" ? decimal.Parse(dr["kp_carat"].ToString()) : 0,
               loss = dr["loss"].ToString() != "" ? double.Parse(dr["loss"].ToString()) : 0,
               issue_manager = dr["lotno"].ToString(),
               issue_by = dr["issue_by"].ToString(),
               receive_by = dr["receive_by"].ToString(),
               status = dr["status"].ToString() != "" ? int.Parse(dr["status"].ToString()) : 0,
               receive_date = Convert.ToDateTime(dr["receive_date"]),
               factory_id = dr["factory_id"].ToString() != "" ? int.Parse(dr["factory_id"].ToString()) : 0,

           }).ToList();