How to Convert DataTable to Generic List in C# [duplicate]

Question

I need to convert C# DataTable to Generic Collection List

DataTable Columns Respectively

 1. EmpId  (this is Int DataType)
 2. EmpName   (this is varchar DataType)
 3. EmpAddress  (this is varchar DataType)
 4. EmpPhone  (this is varchar DataType)
 5. Status   (this is Boolean DataType)
 6. EmpRelationKey (this is int DataType)

so my DataTable Contains Values for above fields. here i need to assign this value into my list

My List Variables Respectively

class Employee
{
protected int EmpId  ;
protected string EmpName =String.Empty;
protected string EmpAddress  = String.Empty;
protected string EmpPhone  = String.Empty;
protected bool Status ;
protected int EmpRelationKey ;
}

Declaring List
List<Employee> Emp= new List<Employee>

so now i need to assign DataTable values to this List. the code should be very professional.

i have tried this method

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

foreach (DataRow row in dt.Rows)  
{  
   employees.Add(new Employee  
   {  
   EmpId  = Convert.ToInt32(row["EmpId"]), 
   EmpName = row["EmpName"].ToString() ,
   EmpAddress =   row["EmpName"].ToString(),
   Emphone =   row["EmpPhone"].ToString(),
   Status = Convert.toBoolean(row["Status"])
   });  
}   

but i don't want to mention column names , is there any other ways to assign values without mentioning each and every column name from DataTable

Answer 1

You can try something like this:

List<Employee> listObject = dTable.AsEnumerable()
                                  .Select(x => new Employee()
                                  {
                                    EmpId = x.Field<int>("EmpId"),
                                    EmpName = x.Field<string>("EmpName"),
                                    EmpAddress = x.Field<string>("EmpName"),
                                    EmpPhone = x.Field<string>("EmpPhone"),
                                    Status = x.Field<bool>("Status"),
                                    EmpRelationKey = x.Field<int>("EmpRelationKey")
                                  }).ToList();

Answer 2

using System.Reflection;

Then,

public static List<T> BindList<T>(DataTable dt)
{
    // Example 1:
    // Get private fields + non properties
    //var fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance);

    // Example 2: Your case
    // Get all public fields
    var fields = typeof(T).GetFields();

    List<T> lst = new List<T>();

    foreach (DataRow dr in dt.Rows)
    {
        // Create the object of T
        var ob = Activator.CreateInstance<T>();

        foreach (var fieldInfo in fields)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                // Matching the columns with fields
                if (fieldInfo.Name == dc.ColumnName)
                {
                    // Get the value from the datatable cell
                    object value = dr[dc.ColumnName];

                    // Set the value into the object
                    fieldInfo.SetValue(ob, value);
                    break;
                }
            }
        }

        lst.Add(ob);
    }

    return lst;
}

Example of usage:

DataTable dt1 = SqlHelper.GetTable("select * from employee;");
List<Employee> employees = BindList<Employee>(dt1);

DataTable dt2 = SqlHelper.GetTable("select * from membership;");
List<Membership> lstMembership = BindList<Membership>(dt2);

DataTable dt3 = SqlHelper.GetTable("select * from car order by name;");
List<Car> lstCar = BindList<Car>(dt3);

=====================

Extended Version

Above example assume that the data holds inside the DataTable has the same data type as your Class object's fields.

What if the data is not same as your class object's fields?

Such as null?

So, you might want to extend the method to take care just in case both data type are not the same.

public static List<T> BindList<T>(DataTable dt)
{
    // Example 1:
    // Get private fields + non properties
    //var fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance);

    // Example 2: Your case
    // Get all public fields
    var fields = typeof(T).GetFields();

    List<T> lst = new List<T>();

    foreach (DataRow dr in dt.Rows)
    {
        // Create the object of T
        var ob = Activator.CreateInstance<T>();

        foreach (var fieldInfo in fields)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                // Matching the columns with fields
                if (fieldInfo.Name == dc.ColumnName)
                {
                    Type type = fieldInfo.FieldType;

                    // Get the value from the datatable cell
                    object value = GetValue(dr[dc.ColumnName], type);

                    // Set the value into the object
                    fieldInfo.SetValue(ob, value);
                    break;
                }
            }
        }

        lst.Add(ob);
    }

    return lst;
}

static object GetValue(object ob, Type targetType)
{
    if (targetType == null)
    {
        return null;
    }
    else if (targetType == typeof(String))
    {
        return ob + "";
    }
    else if (targetType == typeof(int))
    {
        int i = 0;
        int.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(short))
    {
        short i = 0;
        short.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(long))
    {
        long i = 0;
        long.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(ushort))
    {
        ushort i = 0;
        ushort.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(uint))
    {
        uint i = 0;
        uint.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(ulong))
    {
        ulong i = 0;
        ulong.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(double))
    {
        double i = 0;
        double.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(DateTime))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(bool))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(decimal))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(float))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(byte))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(sbyte))
    {
        // do the parsing here...
    }
    else if........
    ..................

    return ob;
}