Udemy

How to Convert DataTable to List in C#

Monday, September 14, 2015 0 Comments A+ a-

When writing Data Access Layer, when we get data from the database using ADO.net, most of the times we use DataTable or DataSet to hold the data in memory, then we have to map the DataTable  to List of Model type.


One way is to iterate on the rows of DataTable and create objects of Model and add to List one. Let me show you with example.


I have table in database which has information about Doctors, my table name is Doctor which contains different columns which are for capturing information of Doctor.


I have following Model class on front end which represents Doctor :

public class Doctor
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string ImagePath { get; set; }
    public string Type { get; set; }
}



When we will get Doctors from database in datatable we can create a list of Doctors with following approach in our Data Access:

List<doctor> doctors = new List<doctor>();
for (int i = 0; i < dtDoctors.Rows.Count; i++)
{

    Doctor objDoctor = new Doctor();

    objDoctor.ID = dtDoctors.Rows[i]["DoctorID"].ToString();
    objDoctor.Name = dtDoctors.Rows[i]["Name"].ToString();
    objDoctor.ImagePath = dtDoctors.Rows[i]["Image"].ToString();
    objDoctor.Type = dtDoctors.Rows[i]["Type"].ToString();

    doctorList.Add(objDoctor);
}

It will surely do the job for you, but think that you have many tables in your database and whenever you will fetch data you will have to do a loop the way i showed above and will have to map properties from datatable, which does not look cool as we are rewriting same thing again and again which is against DRY principle.


We can achieve what we have did above using generics,relection and extension methods feature provided by .Net Framework.We can add the following  extension method in our project and just call it to convert DataTable to List of Type which we want to.

Following is the extension method for it:

public static class ExtensionMethods
{
    public static List<T> ToList<T>(this DataTable dt)
    {
        List<T> data = new List<T>();
        foreach (DataRow row in dt.Rows)
        {
            T item = GetItem<T>(row);
            data.Add(item);
        }
        return data;
    }

    private static T GetItem<T>(DataRow dr)
    {
        Type temp = typeof(T);
        T obj = Activator.CreateInstance<T>();

        foreach (DataColumn column in dr.Table.Columns)
        {
            foreach (PropertyInfo pro in temp.GetProperties())
            {
                if (pro.Name == column.ColumnName && dr[column.ColumnName] != DBNull.Value)
                    pro.SetValue(obj, dr[column.ColumnName], null);
                else
                    continue;
            }
        }
        return obj;
    }
}

Now we can call it on instance of DataTable and can specify that convert it to which type of List.

For Example:

List<Doctor> doctors = dtDoctors.ToList<Doctor>();


Now that looks cool, we have just written two extension method for DataTable and now we can reuse it anywhere which enables us to reduce code redundancy.


One thing to consider is that the column names you are returning from your sql query or Stored Procedure should be same as of the properties in the Model class and the datatype should also be matched so that i can be converted.
Coursera - Hundreds of Specializations and courses in business, computer science, data science, and more