Convert a DataTable to Generic List Collection

Problem Statement

Suppose we have a C# DataTable and we want to translate it into a List of custom data types. We can easily do this by looping through the rows of the data table and add an instance of that data type for each row. For example consider this DataTable:



And we have a custom data type “Employee” that is defined as:

  1. public class Employee  
  2. {  
  3.    public int EmployeeId{ getset; }  
  4.    public stringEmployeeName { getset;}  
  5. }  
To convert this DataTable to List<Employee> we can simply do like this:
  1. List<Employee>employees = new List<Employee>();  
  2. foreach (DataRow row in dt.Rows)  
  3. {  
  4.    employees.Add(new Employee  
  5.    {  
  6.       EmployeeId= Convert.ToInt32(row["EmployeeId"]), EmployeeName =row["EmployeeName"].ToString()  
  7.    });  
  8. }  
But what if we have different data in the DataTable (populated from a database, XML file and so on), we need to repeat this process again and again for the other data types. So if we create a generic function that accepts a DataTable and custom data type at run time then with just one method we can convert any DataTable to the type we want (given that the columns in the DataTable matches with that of the properties of the custom type).

For example, an Employee DataTable as said above can be converted to a List<Employee>.

A Customer DataTable can be converted into List<Customer> and so on.

Code Snippet


Here is the code snippet for converting the DataTable to a generic list. Here is the procedure involved.

Step 1: Since we need to create a List<T>, define an instance and in this list we need to add our custom type T.

Step 2: For each row we need to add the custom type T to our generic list so for each row of the DataTable I am doing that and for fetching the custom type, I used a separate function GetItem<T>.

Step 3: In the GetType<T> function I am using reflection to fetch the properties of the custom type passed and am comparing the same with column names present in the DataTable since these columns will act as properties of each type.
  1. private static List<T> ConvertDataTable<T>(DataTable dt)  
  2. {  
  3.    List<T> data = newList<T>();  
  4.    foreach (DataRowrow in dt.Rows)  
  5.    {  
  6.       Titem = GetItem<T>(row);  
  7.       data.Add(item);  
  8.    }  
  9.    return data;  
  10. }  
  11.   
  12. private static TGetItem<T>(DataRow dr)  
  13. {  
  14.    Type temp = typeof(T);  
  15.    T obj =Activator.CreateInstance<T>();  
  16.    foreach (DataColumncolumn in dr.Table.Columns)  
  17.    {  
  18.       foreach (PropertyInfopro in temp.GetProperties())  
  19.       {  
  20.          if (pro.Name == column.ColumnName)  
  21.          pro.SetValue(obj,dr[column.ColumnName], null);  
  22.          else  
  23.          continue;  
  24.       }  
  25.    }  
  26.    return obj;  
  27. }  
Please let me know if you have any doubts and please do suggest any improvements of the code if needed.

Happy Coding.