65.9K
CodeProject is changing. Read more.
Home

Bind/Map DataTable to Object's Fields and Properties

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.94/5 (14 votes)

Aug 27, 2014

CPOL
viewsIcon

50439

Convert DataTable to Class Object

Lets take this DataTable as example:

public DataTable GetTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Username");
    dt.Columns.Add("Level", typeof(int));

    dt.Rows.Add("John", 1);
    dt.Rows.Add("Cait", 2);

    return dt;
}

We want to bind/map/convert the DataTable into the following class:

class Foo
{
    // Fields
    public string Username = "";

    // Properties
    public int Level { get; set; }
}

Bind DataTable to Object Class:

DataTable dt = GetTable();
Foo foo = BindData<Foo>(dt);

Code behind:

public T BindData<T>(DataTable dt)
{
    DataRow dr = dt.Rows[0];

    // Get all columns' name
    List<string> columns = new List<string>();
    foreach (DataColumn dc in dt.Columns)
    {
        columns.Add(dc.ColumnName);
    }

    // Create object
    var ob = Activator.CreateInstance<T>();

    // Get all fields
    var fields = typeof(T).GetFields();
    foreach (var fieldInfo in fields)
    {
        if (columns.Contains(fieldInfo.Name))
        {
            // Fill the data into the field
            fieldInfo.SetValue(ob, dr[fieldInfo.Name]);
        }
    }

    // Get all properties
    var properties = typeof(T).GetProperties();
    foreach (var propertyInfo in properties)
    {
        if (columns.Contains(propertyInfo.Name))
        {
            // Fill the data into the property
            propertyInfo.SetValue(ob, dr[propertyInfo.Name]);
        }
    }

    return ob;
}

Get a list of Objects from rows in a DataTable:

List<Foo> lst = BindDataList<Foo>(dt);

Code behind:

public List<T> BindDataList<T>(DataTable dt)
{
    List<string> columns = new List<string>();
    foreach (DataColumn dc in dt.Columns)
    {
        columns.Add(dc.ColumnName);
    }

    var fields = typeof(T).GetFields();
    var properties = typeof(T).GetProperties();

    List<T> lst = new List<T>();

    foreach (DataRow dr in dt.Rows)
    {
        var ob = Activator.CreateInstance<T>();

        foreach (var fieldInfo in fields)
        {
            if (columns.Contains(fieldInfo.Name))
            {
                fieldInfo.SetValue(ob, dr[fieldInfo.Name]);
            }
        }

        foreach (var propertyInfo in properties)
        {
            if (columns.Contains(propertyInfo.Name))
            {
                propertyInfo.SetValue(ob, dr[propertyInfo.Name]);
            }
        }

        lst.Add(ob);
    }

    return lst;
}

Above code is assuming that the DataType of DataColumn is equal to DataType of Fields and Properties. If they are not, like this:

DataTable dt = new DataTable();
dt.Columns.Add("Level", typeof(string));

class Foo
{
    public int Level = 0;
}

The DataType of "Level" in the DataColumn is String, but it is Int in Class of Foo. Therefore, we need to take extra steps to verify and convert the data.

Below is one of the possible way to verify and convert the data:

In above example, we'll change this line:

fieldInfo.SetValue(ob, dr[fieldInfo.Name]);

to this:

if (fieldInfo.FieldType == typeof(int))
{
    int i = ExtractInt(dr[fieldInfo.Name]);
    fieldInfo.SetValue(ob, i);
}
else
{
    fieldInfo.SetValue(ob, dr[fieldInfo.Name]);
}

Method of ExtractInt()

public int ExtractInt(object data)
{
    if (data.GetType() == typeof(int))
    {
        return (int)data;
    }
    else
    {
        int i = 0;
        int.TryParse(data + "", out i);
        return i;
    }
}

Happy coding