Convert DataTable to List<T>

Question

Hello, I have a doubt, sometime I made this conversion from DataTable to List (C# code):

  List<EDog> lstDogs = (from drRow in dsDogs.Tables[0].AsEnumerable()
                                  select
                                      new EDog()
                                          {
                                              intIdDog = drRow.Field<int>("IdDog"),
                                              intIdOwner = drRow.Field<int?>("IdOwner"),
                                              intAge = drRow.Field<int>("Age"),
                                              strName = drRow.Field<string>("Name")
                                          }).ToList();

This worked fine, but now I'm thinking about doing it generic, so that any type of DataSet could be converted to a strongly typed list.

How could I make it generic? maybe a delegate surrounding this part and creating the object?

new EDog()
                                          {
                                              intIdDog = drRow.Field<int>("IdDog"),
                                              intIdOwner = drRow.Field<int?>("IdOwner"),
                                              intAge = drRow.Field<int>("Age"),
                                              strName = drRow.Field<string>("Name")
                                          }

I tried it but get an error:

select (lambda) expected....

Any suggestion?

The reason why I need this is because each DataRow of the result, needs to be converted to an Entity for better manipulation.

Thanks.

Answer 1

It won't lend itself to easily be converted, you can do it but it probably won't save much work.

Think about the intrinsic knowledge in your code sample: you know the type and name of each column in the DataTable and the type and name of the property it maps to in the output type. The alternative would be to know the type and index of each column (substituting name for index). In both cases you would need to define a mapping to contain that information.

The alternative would be to build a convention-based convertor--in other words your DataTable column names and their target properties would need to be named consistently and deviating from that convention would result in the conversion failing.

Answer 2

Is something like this what you are looking for?

public static List<T> ConvertDS<T>(DataSet ds, Converter<DataRow, T> converter)
{
    return
        (from row in ds.Tables[0].AsEnumerable()
         select converter(row)).ToList();
}

Answer 3

Have you considered a Linq projection?

Answer 4

Ok, let's have some fun:

public static class DataTableExtensions
{
    public static List<T> ToGenericList<T>(this DataTable datatable, Func<DataRow, T> converter)
    {
        return (from row in datatable.AsEnumerable()
                select converter(row)).ToList();
    }
}

class EDog
{
    private int intIdDog;
    private int intIdOwner;
    private int intAge;
    private string strName;

    ...

    public static EDog Converter(DataRow row)
    {
        return new EDog
                        {
                            intIdDog = (int)row["IdDog"],
                            intIdOwner = (int)row["IdOwner"],
                            intAge = (int)row["Age"],
                            strName = row["Name"] as string
                        };
    }
}

Usage:

List<EDog> dogs = dsDogs.Tables[0].ToGenericList<EDog>(EDog.Converter);

But there is not enough fun, right? What about this:

class DataRowKeyAttribute : Attribute
{
    private readonly string _Key;

    public string Key
    {
        get { return _Key; }
    }

    public DataRowKeyAttribute(string key)
    {
        _Key = key;
    }
}


static class DataTableExtensions
{
    public static List<T> ToGenericList<T>(this DataTable datatable) where T : new()
    {
        return (from row in datatable.AsEnumerable()
                select Convert<T>(row)).ToList();
    }

    private static T Convert<T>(DataRow row) where T : new()
    {
        var result = new T();

        var type = result.GetType();

        foreach (var fieldInfo in type.GetFields(BindingFlags.NonPublic | BindingFlags.Instance))
        {
            var dataRowKeyAttribute = fieldInfo.GetCustomAttributes(typeof (DataRowKeyAttribute), true).FirstOrDefault() as DataRowKeyAttribute;
            if (dataRowKeyAttribute != null)
            {
                fieldInfo.SetValue(result, row[dataRowKeyAttribute.Key]);
            }
        } 

        return result;
    }
}

class EDog
{
    [DataRowKey("IdDog")]
    private int intIdDog;
    [DataRowKey("IdOwner")]
    private int intIdOwner;
    [DataRowKey("Age")]
    private int intAge;
    [DataRowKey("Name")]
    private string strName;

    ... 
}

Usage:

List<EDog> dogs = dsDogs.Tables[0].ToGenericList<EDog>();

And if you want to have REAL fun, add error handling, consider caching reflection data to improve performance and changing fields to properties.