Convert DataTable to Generic List in C#

Question

Disclaimer: I know its asked at so many places at SO.
My query is a little different.

Coding Language: C# 3.5

I have a DataTable named cardsTable that pull data from DB and I have a class Cards which have only some properties(no constructor)

public class Cards
{
    public Int64 CardID { get; set; }
    public string CardName { get; set; }
    public Int64 ProjectID { get; set; }
    public Double CardWidth { get; set; }
    public Double CardHeight { get; set; }
    public string Orientation { get; set; }
    public string BackgroundImage { get; set; }
    public string Background { get; set; }
}

I want to insert the cardsTable data to an object of type List.
My data will be having null fields in it and so the method should not error when i convert the data. Is the below method the best way?

DataTable dt = GetDataFromDB();
List<Cards> target = dt.AsEnumerable().ToList().ConvertAll(x => new Cards { CardID = (Int64)x.ItemArray[0] });

Answer 1

You could actually shorten it down considerably. You can think of the Select() extension method as a type converter. The conversion could then be written as this:

List<Cards> target = dt.AsEnumerable()
    .Select(row => new Cards
    {
        // assuming column 0's type is Nullable<long>
        CardID = row.Field<long?>(0).GetValueOrDefault(),
        CardName = String.IsNullOrEmpty(row.Field<string>(1))
            ? "not found"
            : row.Field<string>(1),
    }).ToList();

Answer 2

I think all the solutions can be improved and make the method more general if you use some conventions and reflection. Let's say you name your columns in the datatable the same name as the properties in your object, then you could write something that look at all your properties of your object and then look up that column in the datatable to map the value.

I did the opposite, that is... from IList to datatable, and the code I wrote can be seen at: http://blog.tomasjansson.com/convert-datatable-to-generic-list-extension/

It shouldn't be that hard to go the other way, and it should be that hard to overload the functions so you can provide information of which properties you want to include or exclude.

EDIT: So the code to make it work is:

public static class DataTableExtensions
{
    private static Dictionary<Type,IList<PropertyInfo>> typeDictionary = new Dictionary<Type, IList<PropertyInfo>>();
    public static IList<PropertyInfo> GetPropertiesForType<T>()
    {
        var type = typeof(T);
        if(!typeDictionary.ContainsKey(typeof(T)))
        {
            typeDictionary.Add(type, type.GetProperties().ToList());
        }
        return typeDictionary[type];
    }

    public static IList<T> ToList<T>(this DataTable table) where T : new()
    {
        IList<PropertyInfo> properties = GetPropertiesForType<T>();
        IList<T> result = new List<T>();

        foreach (var row in table.Rows)
        {
            var item = CreateItemFromRow<T>((DataRow)row, properties);
            result.Add(item);
        }

        return result;
    }

    private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
    {
        T item = new T();
        foreach (var property in properties)
        {
            property.SetValue(item, row[property.Name], null);
        }
        return item;
    }

}

If you have a DataTable you can just write yourTable.ToList<YourType>() and it will create the list for you. If you have more complex type with nested objects you need to update the code. One suggestion is to just overload the ToList method to accept an params string[] excludeProperties which contains all your properties that shouldn't be mapped. Of course you can add some null checking in the foreach loop of the CreateItemForRow method.

UPDATE: Added static dictionary to store the result from the reflection operation to make it a little bit faster. I haven't compiled the code, but it should work :).

Answer 3

Just a little simplification. I don't use ItemArray:

List<Person> list = tbl.AsEnumerable().Select(x => new Person
                    {
                        Id = (Int32) (x["Id"]),
                        Name = (string) (x["Name"] ?? ""),
                        LastName = (string) (x["LastName"] ?? "")
                    }).ToList();

Answer 4

The .ToList() is in the wrong place, and if some fields can be null you'll have to deal with these as they wont convert to Int64 if they're null

DataTable dt = GetDataFromDB();
List<Cards> target = dt.AsEnumerable().Select(
  x => new Cards { CardID = (Int64)(x.ItemArray[0] ?? 0) }).ToList();