How to Convert DataTable to Generic List in C#

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

can you please help me out to solve this and post that code for reference — kselva, Aug 10 '16 at 06:14

mjb · Accepted Answer · 2016-08-11T00:16:46.583

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

DataTable dt1 = SqlHelper.GetTable("select * from employee;"); List employees = BindList(dt); — kselva, Aug 10 '16 at 09:05
@kselva Months ago, I have asked the same question. Many folks have shared their knowledge to me. And now, it is my turn to pass on the answers to someone else :) — mjb, Aug 19 '16 at 00:16
I tried the extended instance, and it didn't return fields when using `var fields = typeof(T).GetFields();`. As such, `var fields = typeof(T).GetFields();` may not work for custom classes. After some digging, I discovered to use `var fields = typeof(T).GetRuntimeFields();` _My solution doesn't fit in one comment, so the next comment is the solution._ — MacSpudster, Oct 27 '18 at 22:36
With `typeof(T).GetRuntimeFields()` you will get field names like: **k__BackingField**, of which does not match up with `fieldInfo.Name == dc.ColumnName` where dc.ColumnName is "EmpName". Instead, you'll need to convert each dc.ColumnName to match the fieldInfo.Name. I use a string template of `string fnTemplate = "<{0}>k__BackingField";` then use `tFN = string.Format(fnTemplate, dc.ColumnName).ToLower();` and then compare as `if (fieldInfo.Name.ToLower() == tFN)`. — MacSpudster, Oct 27 '18 at 22:37
what is dont know the binding type `T` can we get a generic list without specifying type? — Transformer, Dec 14 '18 at 01:52
@transformer If you don't bind it with a type 'T', then how does the system know what Fields of Properties that the type 'T' has? — mjb, Dec 15 '18 at 01:02
thats the challenge i am facing, since the incoming files are from different sources and schema is not defined — Transformer, Dec 15 '18 at 01:54
@transformer you can, by passing an object of your CLASS. Then you can get the type dynamically using myClassObject.GetType(). — mjb, Dec 20 '18 at 02:26

Manish Goswami · Answer 2 · 2016-08-10T06:32:06.387

3

using LinQ you can do ,

  List<DataRow> list = Datatablle.AsEnumerable().ToList();

Updated answer .Create Helper class to Convert Datatable to List

public static class Helper
{

    public static List<T> ConvertDataTableToList<T>(this DataTable table) where T : class, new()
    {
        try
        {
            List<T> list = new List<T>();

            foreach (var row in table.AsEnumerable())
            {
                T obj = new T();

                foreach (var prop in obj.GetType().GetProperties())
                {
                    try
                    {
                        PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                        propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                    }
                    catch
                    {
                        continue;
                    }
                }

                list.Add(obj);
            }

            return list;
        }
        catch
        {
            return null;
        }
    }
}

Then,

DataTable dtDatatable = GetEmployee();
List<Employee> employeeList = dtDatatable.ConvertDataTableToList<Employee>();

edited Aug 10 '16 at 06:32

answered Aug 10 '16 at 06:17

Manish Goswami

863
10
27

i need to assign DataTable Values Directly to this List – kselva Aug 10 '16 at 06:23
List employees = new List(); – kselva Aug 10 '16 at 06:23
Kindly check updated answer. – Manish Goswami Aug 10 '16 at 06:32
yes yes i will update the answer , just now i am applying into my code – kselva Aug 10 '16 at 06:53
i got error in this line dtDatatable.ConvertDataTableToList(); – kselva Aug 10 '16 at 07:30

score 3 · Answer 3 · answered Aug 10 '16 at 06:24

3

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

answered Aug 10 '16 at 06:24

sujith karivelil

28,671
6
55
88

_"but i don't want to mention column names"_ – CodeCaster Aug 10 '16 at 06:28
Use its column index instead – sujith karivelil Aug 10 '16 at 06:30
Yeah, @sujithkarivelil, using column index is also not dynamic, which is what kselva is seeking... – MacSpudster Oct 27 '18 at 22:39

Imad · Answer 4 · 2016-08-10T06:38:17.503

0

As mentioned in comment, whenever you make any changes in DataTable structure then those changes must be done in Class also and if your changing Class then sync those changes with DataTable structure.

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

foreach (DataRow row in dt.Rows)  
{  
    Employee emp= new Employee();

    PropertyInfo[] properties = typeof(Employee).GetProperties();
    for (int i = 0; i < properties.Length; i++)
    {
        property.SetValue(emp, Convert.ChangeType(row[i], property.GetType()));
    }
}

don't forget using System.Reflection;

edited Aug 10 '16 at 06:38

answered Aug 10 '16 at 06:25

Imad

7,126
12
55
112

1

keep in mind that if you change column name or property of your class - this code will produce incorrect result – Mikhail Tulubaev Aug 10 '16 at 06:34
what is stands for that property.setValue , error occur in that line – kselva Aug 10 '16 at 07:29
's' is capital not small. And what is the error – Imad Aug 10 '16 at 08:56

How to Convert DataTable to Generic List in C#

4 Answers4

Extended Version

Linked