Conversion Between DataTable and List<TSource> in C#

• DataTableAndList_v2_VS2017.zip - 14.9 KB
• DataTableAndList_v1_VS2015.zip - 5 KB

Background

• I was working on an entity framework based project, iTextSharp was in use to make PDF reports, and it was expecting DataTable but the entity framework would give List<TSource>. So every time we had to do a manual conversion from a List<TSource> to a DataTable.
• And in another case, JavaScriptSerializer was in use to create json responses in web services. But JavaScriptSerializer class was unable to serialize a DataTable. Again we had to convert a DataTable to a List<TSource>.

The idea was to create a generic solution, creating two extension methods to manage the expected conversion process.

Model

We are going to use this Student class, where

• students:List<Student> will be converted to studentTbl:DataTable
• studentTbl:DataTable will be converted to newStudents:List<Student>

public class Student
{
    public long Id { get; set; }
    public string Name { get; set; }
    public short Age { get; set; }
    public DateTime DateOfCreation { get; set; }
    public bool? IsActive { get; set; }
}

Version 1.0.0

This was the initial solution.

List<TSource> to DataTable

Here is the extension method to convert a List<TSource> to a DataTable:

/*Converts List To DataTable*/
public static DataTable ToDataTable<TSource>(this IList<TSource> data)
{
    DataTable dataTable = new DataTable(typeof(TSource).Name);
    PropertyInfo[] props = typeof(TSource).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in props)
    {
        dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? 
            prop.PropertyType);
    }

    foreach (TSource item in data)
    {
        var values = new object[props.Length];
        for (int i = 0; i < props.Length; i++)
        {
            values[i] = props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    return dataTable;
}  

Using the extension method as bellow, where students:List<Student> is converted to studentTbl:DataTable

/*List to DataTable conversion*/
List<Student> students = Data.GetStudents();
DataTable studentTbl = students.ToDataTable();     

DataTable to List<TSource>

Here is the extension method to convert a DataTable to a List<TSource>

/*Converts DataTable To List*/
public static List<TSource> ToList<TSource>(this DataTable dataTable) where TSource : new()
{
    var dataList = new List<TSource>();

    const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic;
    var objFieldNames = (from PropertyInfo aProp in typeof(TSource).GetProperties(flags)
                            select new { Name = aProp.Name, 
                            Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? 
                    aProp.PropertyType }).ToList();
    var dataTblFieldNames = (from DataColumn aHeader in dataTable.Columns
                                select new { Name = aHeader.ColumnName, 
                Type = aHeader.DataType }).ToList();
    var commonFields = objFieldNames.Intersect(dataTblFieldNames).ToList();
    
    foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
    {
        var aTSource = new TSource();
        foreach (var aField in commonFields)
        {
            PropertyInfo propertyInfos = aTSource.GetType().GetProperty(aField.Name);
            var value = (dataRow[aField.Name] == DBNull.Value) ? 
            null : dataRow[aField.Name]; //if database field is nullable
            propertyInfos.SetValue(aTSource, value, null);
        }
        dataList.Add(aTSource);
    }
    return dataList;
} 

Using the extension method as bellow, where studentTbl:DataTable is converted to newStudents:List<Student>

/*DataTable to List conversion*/
List<Student> newStudents = studentTbl.ToList<Student>(); 

Solution And Projects

It is a Visual Studio 2010 solution and .NET Framework 3.5

Limitations

1. It doesn’t work with, has relation instances
2. Name and datatype of the DataTable column and Class property should be same
3. Works fine with a plain class like Student 

What's Next?

Use attributes

• To ignore mapping
• Map column/property names
• Use ordering

Version 2.0.0

In our existing solution,  going to add attribute support. Let's check the attributes and usages.

Attributes

The attributes are as listed below

/*Base datatable helper attribute*/
[AttributeUsage(AttributeTargets.Field)]
public class DataTableHelperAttribute : Attribute
{
    protected DataTableHelperAttribute()
    {
    }
}



/*DataTable name configuration attribute*/
[AttributeUsage(AttributeTargets.Class)]
public class ToDataTableAttribute : DataTableHelperAttribute
{
    public String DataTableName { get; set; }
    public ToDataTableAttribute(string dataTableName) : this()
    {
        DataTableName = dataTableName;
    }

    private ToDataTableAttribute() : base()
    {
    }
}



/*List to DataTable map configuration attribute*/
[AttributeUsage(AttributeTargets.Property)]
public class ToColumnAttribute : DataTableHelperAttribute
{
    public int? ColumnOrder { get; set; }
    public bool? IgnoreMapping { get; set; }
    public String ColumnName { get; set; }

    public ToColumnAttribute(int columnOrder) : this(true)
    {
        ColumnOrder = columnOrder;
    }

    public ToColumnAttribute(string columnName) : this(true)
    {
        ColumnName = columnName;
    }

    public ToColumnAttribute(string columnName, int columnOrder) : this(columnName)
    {
        ColumnOrder = columnOrder;
    }

    public ToColumnAttribute(bool shouldMapp) : this()
    {
        IgnoreMapping = !shouldMapp;
    }

    private ToColumnAttribute() : base()
    {
    }
}




/*DataTable to list map configuration attribute*/
[AttributeUsage(AttributeTargets.Property)]
public class FromColumnAttribute : DataTableHelperAttribute
{
    public bool? IgnoreMapping { get; set; }
    public string ColumnName { get; set; }

    public FromColumnAttribute(string columnName) : this(true)
    {
        ColumnName = columnName;
    }

    public FromColumnAttribute(bool shouldMapp) : this()
    {
        IgnoreMapping = !shouldMapp;
    }

    private FromColumnAttribute() : base()
    {
    }
}

DataTableHelperAttribute is the base attribute, Other attributes

• ToDataTableAttribute maps class to DataTable. Used during List<TSource> to DataTable conversion
  • Sets the DataTable name, default name as the class name
• ToColumnAttribute maps a class property to a DataTable column. Used during List<TSource> to DataTable conversion
  • Sets column name, default name as the property name
  • Sets column order,  default order as the property order
  • Ignores any property from being mapped to a column
• FromColumnAttribute maps a DataTable column to a class property. Used during DataTable to List<TSource> conversion
  • Sets column name, default name as the property name
  • Ignores any property from being mapped from a column

Mapping Configurations

This helper class reads attribute values and creates mapping configurations. These configurations will be later used by the extension methods.

public class DataTableAttributeHelper
{
    internal T First<T>(Type type) where T : DataTableHelperAttribute
    {
        T attribute = (T)type.GetCustomAttributes(typeof(T), false).FirstOrDefault();
        return attribute;
    }

    internal T First<T>(PropertyInfo type) where T : DataTableHelperAttribute
    {
        T attribute = (T)type.GetCustomAttributes(typeof(T), false).FirstOrDefault();
        return attribute;
    }

    internal string ToDataTableName(Type type)
    {
        var attribute = First<ToDataTableAttribute>(type);
        string name = attribute == null 
                        ? type.Name 
                        : String.IsNullOrEmpty(attribute.DataTableName)
                            ? type.Name
                            : attribute.DataTableName;
        return name;
    }

    internal string ToColumnName(PropertyInfo prop)
    {
        var attribute = First<ToColumnAttribute>(prop);
        string name = attribute == null 
                        ? prop.Name 
                        : String.IsNullOrEmpty(attribute.ColumnName)
                            ? prop.Name
                            : attribute.ColumnName;
        return name;
    }

    internal Type PropertyType(PropertyInfo prop)
    {
        var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
        return type;
    }

    internal int? ToColumnOrder(PropertyInfo prop)
    {
        var attribute = First<ToColumnAttribute>(prop);
        int? order = attribute == null
                        ? null
                        : attribute.ColumnOrder;
        return order;
    }

    internal bool IsToColumnIgnored(PropertyInfo prop)
    {
        var attribute = First<ToColumnAttribute>(prop);
        bool value = attribute == null 
                        ? false 
                        : attribute.IgnoreMapping == null 
                            ? false 
                            : (bool)attribute.IgnoreMapping;
        return value;
    }

    internal bool IsFromColumnIgnored(PropertyInfo prop)
    {
        var attribute = First<FromColumnAttribute>(prop);
        bool value = attribute == null
                        ? false
                        : attribute.IgnoreMapping == null
                            ? false
                            : (bool)attribute.IgnoreMapping;
        return value;
    }

    internal string FromColumnName(PropertyInfo prop)
    {
        var attribute = First<FromColumnAttribute>(prop);
        string name = attribute == null
                        ? prop.Name
                        : String.IsNullOrEmpty(attribute.ColumnName)
                            ? prop.Name
                            : attribute.ColumnName;
        return name;
    }
}

Mapping configuration classes

public interface IMappToDataTable
{
    string FromProperty { get; set; }
    string ToColumn { get; set; }
    bool IgnoreMapp { get; set; }
    Type ColumnType { get; set; }
    int? ColumOrder { get; set; }
    int PropertyPosition { get; set; }
}

public class MappToDataTable : IMappToDataTable
{
    public string FromProperty { get; set; }
    public string ToColumn { get; set; }
    public bool IgnoreMapp { get; set; }
    public Type ColumnType { get; set; }
    public int? ColumOrder { get; set; }
    public int PropertyPosition { get; set; }
}

public interface IMappFromDataTable
{
    string FromColumn { get; set; }
    string ToProperty { get; set; }
    Type PropertyType { get; set; }
    bool IgnoreMapp { get; set; }
}

public class MappFromDataTable : IMappFromDataTable
{
    public string FromColumn { get; set; }
    public string ToProperty { get; set; }
    public Type PropertyType { get; set; }
    public bool IgnoreMapp { get; set; }
}

• MappToDataTable: Class property to DataTable column map configuration
• MappFromDataTable: DataTable column to class property map configuration

List<TSource> to DataTable

Using attributes to a class and to its properties

[ToDataTable("STUDENT_TABLE")]
public class Student
{
    public long Id { get; set; }
    public string Name { get; set; }
    [ToColumn("Remarks")]
    public string Description { get; set; }
    [ToColumn(5)]
    public bool? IsActive { get; set; }
    [ToColumn("CreateDateTime", 4)]
    public DateTime? DateOfCreation { get; set; }
    [ToColumn(false)]
    public Department Department { get; set; }
}   

• [ToDataTable("STUDENT_TABLE")] sets DataTable name
• [ToColumn("Remarks")] sets DataTable column name
• [ToColumn(5)] sets DataTable column order
• [ToColumn("CreateDateTime", 4)] sets DataTable column name and order
• [ToColumn(false)] ignores property to be mapped to a DataTable column

Modifying the existing extension method to incorporate attribute mapping

public static DataTable ToDataTable<TSource>(this IList<TSource> data)
{
    List<MappToDataTable> mapps = new List<MappToDataTable>();
    DataTableAttributeHelper helper = new DataTableAttributeHelper();
    PropertyInfo[] props = typeof(TSource).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    int propertyPosition = 0;
    foreach (PropertyInfo prop in props)
    {
        bool ignore = helper.IsToColumnIgnored(prop);
        if (ignore)
        {
            continue;
        }
        var mapp = new MappToDataTable
        {
            FromProperty = prop.Name,
            ToColumn = helper.ToColumnName(prop),
            ColumOrder = helper.ToColumnOrder(prop),
            PropertyPosition = ++propertyPosition,
            IgnoreMapp = ignore,
            ColumnType = helper.PropertyType(prop),
        };
        mapps.Add(mapp);
    }

    mapps = mapps.OrderBy(x => x.ColumOrder ?? int.MaxValue).ThenBy(x => x.PropertyPosition).ToList();
    DataTable dataTable = new DataTable(helper.ToDataTableName(typeof(TSource)));
    foreach (var mapp in mapps)
    {
        dataTable.Columns.Add(mapp.ToColumn, mapp.ColumnType);
    }

    foreach (TSource item in data)
    {
        var values = new object[mapps.Count];
        for (int i = 0; i < mapps.Count; i++)
        {
            values[i] = props.First(x => x.Name.Equals(mapps[i].FromProperty)).GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    return dataTable;
}   

DataTable to List<TSource>

Using attributes to a class and to its properties

public class Teacher
{
    public long Id { get; set; }
    public string Name { get; set; }
    [FromColumn("Remarks")]
    public string Description { get; set; }
    [FromColumn("CreateDateTime")]
    public DateTime? DateOfCreation { get; set; }
    [FromColumn(false)]
    public Department Department { get; set; }
}   

• [FromColumn("Remarks")] maps DataTable column "Remarks" to the class property 
• [FromColumn(false)] ignores property to be mapped from a DataTable column

Modifying the existing extension method to incorporate attribute mapping

/*Converts DataTable To List*/
public static List<TSource> ToList<TSource>(this DataTable dataTable) where TSource : new()
{
    /*check, one column mapped to only one prop*/
    List<MappFromDataTable> mapps = new List<MappFromDataTable>();
    DataTableAttributeHelper helper = new DataTableAttributeHelper();           
    PropertyInfo[] props = typeof(TSource).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in props)
    {
        bool ignore = helper.IsFromColumnIgnored(prop);
        if (ignore)
        {
            continue;
        }
        var mapp = new MappFromDataTable
        {
            ToProperty = prop.Name,
            FromColumn = helper.FromColumnName(prop),
            IgnoreMapp = ignore,
            PropertyType = helper.PropertyType(prop),
        };
        mapps.Add(mapp);
    }

    var objFieldNames = (from x in mapps select new { ColumnName = x.FromColumn, Type = x.PropertyType }).ToList();
    var dataTblFieldNames = (from DataColumn x in dataTable.Columns select new { ColumnName = x.ColumnName, Type = x.DataType }).ToList();
    var commonFields = objFieldNames.Intersect(dataTblFieldNames).ToList();

    var dataList = new List<TSource>();
    foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
    {
        var aTSource = new TSource();
        foreach (var aField in commonFields)
        {
            string propName = mapps.First(x => x.FromColumn.Equals(aField.ColumnName)).ToProperty;
            PropertyInfo propertyInfos = aTSource.GetType().GetProperty(propName);
            var columnValue = dataRow[aField.ColumnName];
            var value = (columnValue == DBNull.Value) ? null : columnValue; //if database field is nullable
            propertyInfos.SetValue(aTSource, value, null);
        }
        dataList.Add(aTSource);
    }
    return dataList;
}

Using The Extention Methods

the usages are going to be as it was.

/*List to DataTable conversion*/
List<Student> students = Data.GetStudents();
DataTable studentTbl = students.ToDataTable(); 


/*DataTable to List conversion*/
List<Student> newStudents = studentTbl.ToList<Student>();

Solution And Projects

It is a Visual Studio 2017 solution and .NET Framework 4

• DataTableHelper: Helper project, contains extension methods, attributes, and other classes
• Test.Unit: Unit test project
• DataTableAndList: A console project to test extension methods

To restore NuGet packages use the command

Update-Package -Reinstall

Limitations

1. It doesn’t map, has relation instances
2. The datatype of the DataTable column and Class property should be the same.

What's Next?

• Fix performance issues
• Use attributes to
  • Mapp a column to multiple properties
  • Mapp a property to multiple columns
  • Mapp complex has relation instances objects

The code may throw unexpected errors for untested inputs. If any, just let me know.

History

• 10^th Jun, 2020: Initial version, 1.0.0
• 2^nd May, 2020: Second version, 2.0.0