Coverting DataTable to List

Question

Is there a way to do the conversion from DataTable to List<Task> without specifying the individual fields? More concise? Properties inferred?

public class Task
{
    public int Id { get; set; }
    public string Name { get; set; }
}


public class TasksDAL
{

    public static List<Task> GetTasks()
    {
        DataTable dt = GetTasksDT();
        List<Task> tasks = new List<Task>();
        tasks = (from DataRow row in dt.Rows
                 select new Task
                 {
                     Id = (int)row["Id"],
                     Name = row["Name"].ToString()
                 }).ToList();

        return tasks;
    }

}

I think [AutoMapper](https://github.com/AutoMapper/AutoMapper) shall help. I believe you should create map IDataReader, List. This [Q/A](https://stackoverflow.com/questions/35414228/using-automapper-to-map-a-datatable-to-an-object-dto) may help you — Harutyun Imirzyan, Feb 19 '18 at 19:59
You could create a constructor for `Task` that takes a `DataRow` as its parameter. — Chris, Feb 19 '18 at 20:00
Your code is somewhat redundant as-is - no reason to create a `new List` and then throw it away immediately, just use `var tasks = ` and get rid of the previous line. But you will need reflection for a generic solution with more fields. — NetMage, Feb 19 '18 at 21:24
Are you sure that you need a `DataTable` in the first place? Why not change your data layer so that it returns a `List`? — mason, Feb 19 '18 at 21:49
I am returning a List. Are you saying I don't need to use ADO.NET to retrieve the data from database? — Rod, Feb 20 '18 at 00:26

JM123 · Answer 1 · 2018-02-19T20:29:06.320

I have this old part of code that uses reflexion to populate a List of obj from a datatable.

The name of the column needs to be the same of the name of the property.

var result = dt.ToList<Task>()


  public static List<T> ToList<T>(this DataTable dt) where T : new()
        {
        var returnList = new List<T>();

        var properties = new PropertyInfo[dt.Columns.Count];

        for (int i = 0; i < properties.Length; i++)
        {
            properties[i] = typeof(T).GetProperty(dt.Columns[i].Caption, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
        }

        PropertyInfo property;

        foreach (DataRow dr in dt.Rows)
        {
            T resultItem = new T();

            for (int i = 0; i < properties.Length; i++)
            {
                if ((property = properties[i]) == null)
                {
                    continue;
                }

                if (!DBNull.Value.Equals((object)dr[i] ?? (object)DBNull.Value))
                {
                    property.SetValue(resultItem, dr[i], null);
                }
            }

            returnList.Add(resultItem);
        }

        return returnList;
    }

Doubt this works with var - unless you specify the type directly. — user6144226, Feb 19 '18 at 20:21
@user6144226 thank since it was not in code the task type was removed. — JM123, Feb 19 '18 at 20:29

score 0 · Answer 2 · answered Feb 19 '18 at 21:39

I prefer to return IEnumerable to List and reflect in the opposite direction, assigning all properties and fields with matching column names:

Using some extensions on MemberInfo to handle properties or fields:

public static object GetValue(this MemberInfo member, object srcObject) {
    if (member.MemberType == MemberTypes.Property)
        return ((PropertyInfo)member).GetValue(srcObject, null);
    else if (member.MemberType == MemberTypes.Field)
        return ((FieldInfo)member).GetValue(srcObject);
    else
        throw new Exception("Property must be of type FieldInfo or PropertyInfo");
}

public static void SetValue<T>(this MemberInfo member, object destObject, T value) {
    if (member.MemberType == MemberTypes.Property)
        ((PropertyInfo)member).SetValue(destObject, value);
    else if (member.MemberType == MemberTypes.Field)
        ((FieldInfo)member).SetValue(destObject, value);
    else
        throw new Exception("Property must be of type FieldInfo or PropertyInfo");
}

public static Type GetMemberType(this MemberInfo member) {
    switch (member.MemberType) {
        case MemberTypes.Field:
            return ((FieldInfo)member).FieldType;
        case MemberTypes.Property:
            return ((PropertyInfo)member).PropertyType;
        case MemberTypes.Event:
            return ((EventInfo)member).EventHandlerType;
        default:
            throw new ArgumentException("MemberInfo must be if type FieldInfo, PropertyInfo or EventInfo", "member");
    }
}

You can get the list of members with columns and assign them:

public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : new() {
    var props = typeof(T).GetMembers(BindingFlags.Public|BindingFlags.Instance)
                         .Where(p => dt.Columns.Contains(p.Name) && (p.MemberType == MemberTypes.Field || p.MemberType == MemberTypes.Property)).ToList();
    foreach (var row in dt.AsEnumerable()) {
        var aT = new T();
        foreach (var p in props)
            p.SetValue(aT, row[p.Name]);
        yield return aT;
    }
}

Coverting DataTable to List

2 Answers2