[聚合问答] How do you convert a DataTable into a generic list?

c#,generics,datatable 2017-11-30 9 阅读

Currently, I'm using:

DataTable dt = CreateDataTableInSomeWay();

List<DataRow> list = new List<DataRow>(); 
foreach (DataRow dr in dt.Rows)
{
    list.Add(dr);
}

Is there a better/magic way?

14个回答

217

If you're using .NET 3.5, you can use DataTableExtensions.AsEnumerable (an extension method) and then if you really need a List<DataRow> instead of just IEnumerable<DataRow> you can call Enumerable.ToList:

IEnumerable<DataRow> sequence = dt.AsEnumerable();

or

using System.Linq;
...
List<DataRow> list = dt.AsEnumerable().ToList();

2017-11-30
52

List<Employee> emp = new List<Employee>();

//Maintaining DataTable on ViewState
//For Demo only

DataTable dt = ViewState["CurrentEmp"] as DataTable;

//read data from DataTable 
//using lamdaexpression


emp = (from DataRow row in dt.Rows

   select new Employee
   {
       _FirstName = row["FirstName"].ToString(),
       _LastName = row["Last_Name"].ToString()

   }).ToList();

2017-11-30
32

With C# 3.0 and System.Data.DataSetExtensions.dll,

List<DataRow> rows = table.Rows.Cast<DataRow>().ToList();

2017-11-30
28

You could use

List<DataRow> list = new List<DataRow>(dt.Select());

dt.Select() will return all rows in your table, as an array of datarows, and the List constructor accepts that array of objects as an argument to initially fill your list with.

2017-11-30
10

If you just want a list of values from the "ID" int field returned, you could use...

List<int> ids = (from row in dt.AsEnumerable() select Convert.ToInt32(row["ID"])).ToList();

2017-11-30
9

using System.Data;


var myEnumerable = myDataTable.AsEnumerable();

List<MyClass> myClassList =
    (from item in myEnumerable
     select new MyClass{
         MyClassProperty1 = item.Field<string>("DataTableColumnName1"),
         MyClassProperty2 = item.Field<string>("DataTableColumnName2")
    }).ToList();

2017-11-30
8

You can create a extension function as :

public static List<T> ToListof<T>(this DataTable dt)
{
    const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance;
    var columnNames = dt.Columns.Cast<DataColumn>()
        .Select(c => c.ColumnName)
        .ToList();
    var objectProperties = typeof(T).GetProperties(flags);
    var targetList = dt.AsEnumerable().Select(dataRow =>
    {
        var instanceOfT = Activator.CreateInstance<T>();

        foreach (var properties in objectProperties.Where(properties => columnNames.Contains(properties.Name) && dataRow[properties.Name] != DBNull.Value))
        {
            properties.SetValue(instanceOfT, dataRow[properties.Name], null);
        }
        return instanceOfT;
    }).ToList();

    return targetList;
}


var output = yourDataInstance.ToListof<targetModelType>();

2017-11-30
6

Again, using 3.5 you may do it like:

dt.Select().ToList()

BRGDS

2017-11-30
4

A more 'magic' way, and doesn't need .NET 3.5.

If, for example, DBDatatable was returning a single column of Guids (uniqueidentifier in SQL) then you could use:

Dim gList As New List(Of Guid)
gList.AddRange(DirectCast(DBDataTable.Select(), IEnumerable(Of Guid)))

2017-11-30
3

DataTable.Select() doesnt give the Rows in the order they were present in the datatable.

If order is important I feel iterating over the datarow collection and forming a List is the right way to go or you could also use overload of DataTable.Select(string filterexpression, string sort).

But this overload may not handle all the ordering (like order by case ...) that SQL provides.

2017-11-30
3

// this is better suited for expensive object creation/initialization
IEnumerable<Employee> ParseEmployeeTable(DataTable dtEmployees)
{
    var employees = new ConcurrentBag<Employee>();

    Parallel.ForEach(dtEmployees.AsEnumerable(), (dr) =>
    {
        employees.Add(new Employee() 
        {
            _FirstName = dr["FirstName"].ToString(),
            _LastName = dr["Last_Name"].ToString()
        });
    });

    return employees;
}

2017-11-30
3

I some modified code from this answer (https://stackoverflow.com/a/24588210/4489664) because when I used nullable DateTime in my class it returned error

public static List<T> DataTableToList<T>(this DataTable table) where T : class, new()
{
    try
    {
        T tempT = new T();
        var tType = tempT.GetType();
        List<T> list = new List<T>();
        foreach (var row in table.Rows.Cast<DataRow>())
        {
            T obj = new T();
            foreach (var prop in obj.GetType().GetProperties())
            {
                var propertyInfo = tType.GetProperty(prop.Name);
                var rowValue = row[prop.Name];
                var t = Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType;

                try
                {
                    object safeValue = (rowValue == null || DBNull.Value.Equals(rowValue)) ? null : Convert.ChangeType(rowValue, t);
                    propertyInfo.SetValue(obj, safeValue, null);

                }
                catch (Exception ex)
                {//this write exception to my logger
                    _logger.Error(ex.Message);
                }
            }
            list.Add(obj);
        }
        return list;
    }
    catch
    {
        return null;
    }
}

2017-11-30
2

DataTable dt;   // datatable should contains datacolumns with Id,Name

List<Employee> employeeList=new List<Employee>();  // Employee should contain  EmployeeId, EmployeeName as properties

foreach (DataRow dr in dt.Rows)
{
    employeeList.Add(new Employee{EmployeeId=dr.Id,EmplooyeeName=dr.Name});
}

2017-11-30
1

Here's a DataTable extension method that converts a DataTable to a generic list.

https://gist.github.com/gaui/a0a615029f1327296cf8

Usage:

List<Employee> emp = dtTable.DataTableToList<Employee>();

2017-11-30

注:本文内容来自互联网,旨在为开发者提供分享、交流的平台。如有涉及文章版本等事宜,请你联系站长进行处理。