Convert DataTable to Generic List in C#

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

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();

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 :).

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();

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();

well its the one line solution

it depends on whether or not you know the data in the database is all valid and will not contain anything that will break the above

eg a nullable field whenre you dont expect it - maybe due to a left join int eh sql that genertates the data.

So if you have validated the data before then yeah - I was goign to suggest some linq - but you got tht down.

If you need some validation however you should probably just loop through the datarows, generate your object as above and add it to the collection ... this will also allow you to handle errors in one row and still process the rest.

Thats the way i see it anyway

(damn i came on to downvote something so my rep was 1024)

You can map Data Table to model class using a Generic class like below.

Generic class

 public static class DataTableMappingtoModel
    {
        /// <summary>
        /// Maps Data Table values to coresponded model propertise
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> MappingToEntity<T>(this DataTable dt) 
        {
            try
            {
                var lst = new List<T>();
                var tClass = typeof (T);
                PropertyInfo[] proInModel = tClass.GetProperties();
                List<DataColumn> proInDataColumns = dt.Columns.Cast<DataColumn>().ToList();
                T cn;
                foreach (DataRow item in dt.Rows)
                {
                    cn = (T) Activator.CreateInstance(tClass);
                    foreach (var pc in proInModel)
                    {


                            var d = proInDataColumns.Find(c => string.Equals(c.ColumnName.ToLower().Trim(), pc.Name.ToLower().Trim(), StringComparison.CurrentCultureIgnoreCase));
                            if (d != null)
                                pc.SetValue(cn, item[pc.Name], null);


                    }
                    lst.Add(cn);
                }
                return lst;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }

Model class

public class Item
{
    public string ItemCode { get; set; }
    public string Cost { get; set; }
    public override string ToString()
    {
        return "ItemCode : " + ItemCode + ", Cost : " + Cost;
    }
}

Create DataTable

public DataTable getTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("ItemCode", typeof(string)));
    dt.Columns.Add(new DataColumn("Cost", typeof(string)));
    DataRow dr;
    for (int i = 0; i < 10; i++)
    {
        dr = dt.NewRow();
        dr[0] = "ItemCode" + (i + 1);
        dr[1] = "Cost" + (i + 1);
        dt.Rows.Add(dr);
    }
    return dt;
}

Now we can convert this DataTable to List like below:

DataTable dt = getTable();
List<Item> lst = dt.ToCollection<Item>();
foreach (Item cn in lst)
{
    Response.Write(cn.ToString() + "<BR/>");
}

Hope will help you

Here is a simple way to convert to generic list in c# with Where condition

List<Filter> filter = ds.Tables[0].AsEnumerable()
                        .Where(x => x.Field<int>("FilterID") == 5)
                        .Select(row => new Filter
                        {
                            FilterID = row.Field<int>("FilterID"),
                            FilterName = row.Field<string>("FilterName")
                        }).ToList();

First Define properties and use as per

public class Filter
{
    public int FilterID { get; set; }
    public string FilterName { get; set; }
}

Put Package:

using System.Linq;
using System.Collections.Generic;

Here is the fastest loop free solution to convert DataTable to a generic type list.

public static List<T> ConvertDataTable<T>(DataTable SourceData, Func<DataRow, T> RowConverter)
{
    List<T> list = new List<T>();
    if (SourceData == null || SourceData.Rows.Count < 1)
        return list;
    IEnumerable<T> enumerable = SourceData.AsEnumerable().Select(RowConverter);
    if (enumerable == null)
        return list;
    return new List<T>(enumerable);
}

And this is the implementation of this function.

public static List<T> ExecuteListOfObject<T>(DataTable SourceData)
{
    return ConvertDataTable(SourceData, ConvertRecord<T>);
}
public static T ConvertRecord<T>(DataRow drData)
{
    if (drData == null || drData[0] == DBNull.Value)
        return default(T);
    return (T)drData[0];
}

I built on top of Tomas Jansson's logic to include an "Ignore" attribute. This allows me to add other attribute's to the class being loaded without breaking the DataTable-To-Class loading itself.

Alternatively I also considered adding a separate parameter that holds the actual column name to be read from in the DataTable. In that case instead of using "row[property.Name]" then you'd use row[attribute.Name]" or something like that for that particular property.

public static class DataTableExtensions
{
    [AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]
    public sealed class IgnoreAttribute : Attribute { public IgnoreAttribute() { } }

    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)
            result.Add(CreateItemFromRow<T>((DataRow)row, properties));

        return result;
    }

    private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
    {
        T item = new T();

        foreach (var property in properties)
        {
            // Only load those attributes NOT tagged with the Ignore Attribute
            var atr = property.GetCustomAttribute(typeof(IgnoreAttribute));
            if (atr == null)
                property.SetValue(item, row[property.Name], null);
        }

        return item;
    }
}

Coming late but this can be useful. Can be called using:

table.Map(); or call with a Func to filter the values.

You can even change the mapping name between the type property and DataColumn header by setting the attributes on the property.

[AttributeUsage(AttributeTargets.Property)]
    public class SimppleMapperAttribute: Attribute
    {
        public string HeaderName { get; set; }
    }


     public static class SimpleMapper
{
    #region properties
    public static bool UseDeferredExecution { get; set; } = true;
    #endregion

#region public_interface  


    public static IEnumerable<T> MapWhere<T>(this DataTable table, Func<T, bool> sortExpression) where T:new()
    {
        var result = table.Select().Select(row => ConvertRow<T>(row, table.Columns, typeof(T).GetProperties())).Where((t)=>sortExpression(t));
        return UseDeferredExecution ? result : result.ToArray();
    }
    public static IEnumerable<T> Map<T>(this DataTable table) where T : new()
    {
        var result = table.Select().Select(row => ConvertRow<T>(row, table.Columns, typeof(T).GetProperties()));
        return UseDeferredExecution ? result : result.ToArray();
    }
    #endregion

#region implementation_details
    private static T ConvertRow<T>(DataRow row, DataColumnCollection columns, System.Reflection.PropertyInfo[] p_info) where T : new()
    {
        var instance = new T();
        foreach (var info in p_info)
        {
            if (columns.Contains(GetMappingName(info))) SetProperty(row, instance, info);             
        }
        return instance;
    }

    private static void SetProperty<T>(DataRow row, T instance, System.Reflection.PropertyInfo info) where T : new()
    {
        string mp_name = GetMappingName(info);
        object value = row[mp_name];
        info.SetValue(instance, value);
    }

    private static string GetMappingName(System.Reflection.PropertyInfo info)
    {
        SimppleMapperAttribute attribute = info.GetCustomAttributes(typeof(SimppleMapperAttribute),true).Select((o) => o as SimppleMapperAttribute).FirstOrDefault();
        return attribute == null ? info.Name : attribute.HeaderName;
    }
#endregion
}