DataTable to List<> using Generics

Introduction

There may be a situation when you need to convert a DataTable into List<> in C#. We will see how we can do this using Generics.

Background

Somedays ago, I had to work on a project, basically I had to add some features on that. The legacy code fetches data from database as DataTable and then converts it to a specific List<> if needed. I had to add more tables in datbase and classes to the C# code. When I had to convert all these DataTable to List<>, I got pissed off. Then tried to build something more general which will do these stuffs easily.

Convert DataTable to List<>

At first we'll take a Class Tuple to map the columns of the Object.

The Class Property Name must be same as the Column Name of the DataTable. The mapping is directly upon "Class Property Name" and "Column Name of the DataTable".

sealed class Tuple<T1, T2>
{
    public Tuple() { }
    public Tuple(T1 value1, T2 value2) { Value1 = value1; Value2 = value2; }
    public T1 Value1 { get; set; }
    public T2 Value2 { get; set; }
}

Now we'll pass the DataTable toDataTableToList class. At first we'll get the columns mapping from the Object that passed here. And then we can find the data from DataTable by matching the mapping with that.

public class DataTableToList
    {
        public static List<T> Convert<T>(DataTable table)
            where T : class, new()
        {
            List<Tuple<DataColumn, PropertyInfo>> map =
                new List<Tuple<DataColumn, PropertyInfo>>();

            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                if (table.Columns.Contains(pi.Name))
                {
                    map.Add(new Tuple<DataColumn, PropertyInfo>(
                        table.Columns[pi.Name], pi));
                }
            }

            List<T> list = new List<T>(table.Rows.Count);
            foreach (DataRow row in table.Rows)
            {
                if (row == null)
                {
                    list.Add(null);
                    continue;
                }
                T item = new T();
                foreach (Tuple<DataColumn, PropertyInfo> pair in map)
                {
                    object value = row[pair.Value1];
                    if (value is DBNull) value = null;
                    pair.Value2.SetValue(item, value, null);
                }
                list.Add(item);
            }
            return list;
        }
    }

How to use!

I've made a library for that. You an use that.

Suppose, we have a DataTable containing Students data.

DataTable dtStudent = GetDataTable();

.......

static DataTable GetDataTable()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("Roll", typeof(string));

    // Here we add five DataRows.
    table.Rows.Add("David", "a-100");
    table.Rows.Add("Sam", "a-101");
    table.Rows.Add("Christoff", "a-102");
    table.Rows.Add("Janet", "a-103");
    table.Rows.Add("Melanie", "a-104");

    return table;
}

Now, I'm not comfortable with this DataTable :) So, lets convert this dtStudent to List<Student>.

Simply call this -

List<Student> listStudents = DataTableToListLib.DataTableToList.Convert<Student>(dtStudent);

//Student class
public class Student
{
    public string Name { get; set; }
    public string Roll { get; set; }
}