This article is about to convert, a List of an object to a DataTable or, a DataTable to an object List. This is going to be a generic solution using extension methods and generics.
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
:
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<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
>
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];
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>
List<Student> newStudents = studentTbl.ToList<Student>();
Solution And Projects
It is a Visual Studio 2010 solution and .NET Framework 3.5
Limitations
- It doesn’t work with, has relation instances
- Name and datatype of the DataTable column and Class property should be same
- 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
[AttributeUsage(AttributeTargets.Field)]
public class DataTableHelperAttribute : Attribute
{
protected DataTableHelperAttribute()
{
}
}
[AttributeUsage(AttributeTargets.Class)]
public class ToDataTableAttribute : DataTableHelperAttribute
{
public String DataTableName { get; set; }
public ToDataTableAttribute(string dataTableName) : this()
{
DataTableName = dataTableName;
}
private ToDataTableAttribute() : base()
{
}
}
[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()
{
}
}
[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
public static List<TSource> ToList<TSource>(this DataTable dataTable) where TSource : new()
{
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;
propertyInfos.SetValue(aTSource, value, null);
}
dataList.Add(aTSource);
}
return dataList;
}
Using The Extention Methods
the usages are going to be as it was.
List<Student> students = Data.GetStudents();
DataTable studentTbl = students.ToDataTable();
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
- It doesn’t map, has relation instances
- 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
- 10th Jun, 2020: Initial version, 1.0.0
- 2nd May, 2020: Second version, 2.0.0