Converting DataTable To Model List

This blog refers to the generic solution for converting any datatable to Model List in C#.

In this blog, we will discuss the simple common solution to convert an object of type DataTable into a list of Models in C#. I have tried my best to explain the use of the common generic method to convert any datable to Model List. I hope this would be helpful in the situations where we need to set the value of each property of a model with the column values of Datatable using a "For" loop.

Requirement

There might be a situation where you have a model named "Student" and you want to show the list of students on your page. You have the result from SQL in Datatable named "ResultDT". Similarly, you may have a number of other models and DataTables.

If your Student Model is like below, then your data table should also have the same column name with same datatypes. That means your result in "ResultDT" should have at least these 3 columns - StudentId, RoleNumber, Name.

public class Student
{
   public int StudentId { get; set; }
   public int RoleNumber { get; set; }
   public string Name { get; set; }
}

Need of Generic method to convert datatable to model list

Generally, we have to set the property of model one by one within for each or we have to use some tools or plugins for mapping with the properties. So this generic method is needed because:

• To avoid writing lot of code to set properties of the model
• A generic method would be re-usable for all Models and data tables
• It's clean and less code would be required.

Generic Method

Here is the generic method that you can add to any of your common files from where you can call this whenever required to convert any data table to the model list. Suppose, the name of the class is CommonMethod in which this method exists.

public static class CommonMethod {
    public static List < T > ConvertToList < T > (DataTable dt) {
        var columnNames = dt.Columns.Cast < DataColumn > ().Select(c => c.ColumnName.ToLower()).ToList();
        var properties = typeof(T).GetProperties();
        return dt.AsEnumerable().Select(row => {
            var objT = Activator.CreateInstance < T > ();
            foreach(var pro in properties) {
                if (columnNames.Contains(pro.Name.ToLower())) {
                    try {
                        pro.SetValue(objT, row[pro.Name]);
                    } catch (Exception ex) {}
                }
            }
            return objT;
        }).ToList();
    }
}

Example

Below is the function to get the list of students in which we are using the generic method to convert our data table result to List of Model "Student".

public List < Student > GetStudentList() {
    Datatable ResultDT = new DataTable();
    ResultDT = _DataBAL.GetData(); // Call BusinessLogic to fill DataTable, Here your ResultDT will get the result in which you will be having single or multiple rows with columns "StudentId,RoleNumber and Name"
    List < Student > Studentlist = new List < Student > ();
    Studentlist = CommonMethod.ConvertToList < Student > (ResultDT);
    return Studentlist;
}