Introduction
You know how it is, sometimes you're having a very large generic list but what you need is a DataTable, because that old legacy application needs it, or you want to bulkcopy it to the database.
No problems you might think. There's an AsDataTable function in the Linq namespace.
Well yes there is, but it only works with Linq to Dataset.
So what other solutions are there besides hardcoding it every time you need it?
Well, you can use reflection, the problem is just that the performance sucks.
So, since I wasn't able to find anyone having created the functionality, I had to do it myself.
Using the code
There's only one public method, an Extension method called AsDataTable that takes an IEnumerable as a parameter and is simply used like MyGenericList.AsDataTable()
public static DataTable AsDataTable<TSource>(this IEnumerable<TSource> Collection)
{
DataTable dt = DataTableCreator<TSource>.GetDataTable();
Func<TSource, object[]> Map = DataRowMapperCache<TSource>.GetDataRowMapper(dt);
foreach (TSource item in Collection)
{
dt.Rows.Add(Map(item));
}
return dt;
}
<Extension> _
Public Function AsDataTable(Of TSource)(Collection As IEnumerable(Of TSource)) As DataTable
Dim dt As DataTable = DataTableCreator(Of TSource).GetDataTable
Dim Map As Func(Of TSource, Object()) = DataRowMapperCache(Of TSource).GetDataRowMapper(dt)
For Each item As TSource In Collection
dt.Rows.Add(Map(item))
Next
Return dt
End Function
This part is pretty simple, it fetches a new DataTable from a cache and fetches a Delegate that acts like a mapper between an Instance and an ObjectArray.
Then the delegate is used on every item in the collection to create an ObjectArray that's added to the DataTables RowCollection.
The reason for adding an Array instead of a DataRow is that the DataRow does not have a public constructor. It was simpler this way.
Creating the DataTable
The construction of the DataTable is done by reflection, since it is cached it's only done once per Item Class and will not affect performance more than once
static internal DataTable CreateDataTable<TSource>()
{
DataTable dt = new DataTable();
foreach (FieldInfo SourceMember in typeof(TSource).GetFields(BindingFlags.Instance | BindingFlags.Public))
{
dt.AddTableColumn(SourceMember, SourceMember.FieldType);
}
foreach (PropertyInfo SourceMember in typeof(TSource).GetProperties(BindingFlags.Instance | BindingFlags.Public))
{
if (SourceMember.CanRead)
{
dt.AddTableColumn(SourceMember, SourceMember.PropertyType);
}
}
return dt;
}
Friend Function CreateDataTable(Of TSource)() As DataTable
Dim dt As New DataTable()
For Each SourceMember As FieldInfo In GetType(TSource).GetFields(BindingFlags.Instance Or BindingFlags.[Public])
dt.AddTableColumn(SourceMember, SourceMember.FieldType)
Next
For Each SourceMember As PropertyInfo In GetType(TSource).GetProperties(BindingFlags.Instance Or BindingFlags.[Public])
If SourceMember.CanRead Then
dt.AddTableColumn(SourceMember, SourceMember.PropertyType)
End If
Next
Return dt
End Function
What this method does is to loop through all public instance members of the TSource class, and checks if they're fields or readable properties.
If the Member is of a type that is supported by a DataColumn it will be added using the Name, Type and whether it should allow DbNull or not.
public static void AddTableColumn(this DataTable dt, MemberInfo SourceMember, Type MemberType)
{
if (MemberType.IsAllowedType())
{
DataColumn dc;
string FieldName = GetFieldNameAttribute(SourceMember);
if (string.IsNullOrWhiteSpace(FieldName))
{
FieldName = SourceMember.Name;
}
if (Nullable.GetUnderlyingType(MemberType) == null)
{
dc = new DataColumn(FieldName, MemberType);
dc.AllowDBNull = !MemberType.IsValueType;
}
else
{
dc = new DataColumn(FieldName, Nullable.GetUnderlyingType(MemberType));
dc.AllowDBNull = true;
}
dt.Columns.Add(dc);
}
}
<Extension> _
Private Sub AddTableColumn(dt As DataTable, SourceMember As MemberInfo, MemberType As Type)
If MemberType.IsAllowedType Then
Dim dc As DataColumn
Dim FieldName As String = GetFieldNameAttribute(SourceMember)
If String.IsNullOrWhiteSpace(FieldName) Then
FieldName = SourceMember.Name
End If
If Nullable.GetUnderlyingType(MemberType) Is Nothing Then
dc = New DataColumn(FieldName, MemberType)
dc.AllowDBNull = Not MemberType.IsValueType
Else
dc = New DataColumn(FieldName, Nullable.GetUnderlyingType(MemberType))
dc.AllowDBNull = True
End If
dt.Columns.Add(dc)
End If
End Sub
If you would want the DataColumn to have a different name than the Member you can add a FieldNameAttribute to it
[FieldName("Some odd fieldname")]
public string Name { get; set; }
<FieldName("Some odd fieldname")>
Property Name As String
The FieldNameAttribute obviously takes precedence over TargetMembers name
Creating the DataRowMapper
The mapper is created using an Expression Tree and reflection.
It's done by looping through the DataColumns in the DataTable and matching them by name or FieldnameAttribute to the Instanceclass
static internal Func<TSource, object[]> CreateDataRowMapper<TSource>(DataTable dt)
{
Type SourceType = typeof(TSource);
ParameterExpression SourceInstanceExpression = Expression.Parameter(SourceType, "SourceInstance");
List<Expression> Values = new List<Expression>();
foreach (DataColumn col in dt.Columns)
{
foreach (FieldInfo SourceMember in SourceType.GetFields(BindingFlags.Instance | BindingFlags.Public))
{
if (MemberMatchesName(SourceMember, col.ColumnName))
{
Values.Add(GetSourceValueExpression(SourceInstanceExpression, SourceMember));
break;
}
}
foreach (PropertyInfo SourceMember in SourceType.GetProperties(BindingFlags.Instance | BindingFlags.Public))
{
if (SourceMember.CanRead && MemberMatchesName(SourceMember, col.ColumnName))
{
Values.Add(GetSourceValueExpression(SourceInstanceExpression, SourceMember));
break;
}
}
}
NewArrayExpression body = Expression.NewArrayInit(Type.GetType("System.Object"), Values);
return Expression.Lambda<Func<TSource, object[]>>(body, SourceInstanceExpression).Compile();
}
Friend Function CreateDataRowMapper(Of TSource)(dt As DataTable) As Func(Of TSource, Object())
Dim SourceType As Type = GetType(TSource)
Dim SourceInstanceExpression As ParameterExpression = Expression.Parameter(SourceType, "SourceInstance")
Dim Values As New List(Of Expression)
For Each col As DataColumn In dt.Columns
For Each SourceMember As FieldInfo In SourceType.GetFields(BindingFlags.Instance Or BindingFlags.[Public])
If MemberMatchesName(SourceMember, col.ColumnName) Then
Values.Add(GetSourceValueExpression(SourceInstanceExpression, SourceMember))
Exit For
End If
Next
For Each SourceMember As PropertyInfo In SourceType.GetProperties(BindingFlags.Instance Or BindingFlags.[Public])
If SourceMember.CanRead AndAlso MemberMatchesName(SourceMember, col.ColumnName) Then
Values.Add(GetSourceValueExpression(SourceInstanceExpression, SourceMember))
Exit For
End If
Next
Next
Dim body As NewArrayExpression = Expression.NewArrayInit(Type.[GetType]("System.Object"), Values)
Return Expression.Lambda(Of Func(Of TSource, Object()))(body, SourceInstanceExpression).Compile()
End Function
When we have a match we create a MemberExpression representing the value in the Field or Property that we add to an array that's used to create a NewArrayInitExpression.
private static Expression GetSourceValueExpression(ParameterExpression SourceInstanceExpression, MemberInfo SourceMember)
{
MemberExpression MemberExpression = Expression.PropertyOrField(SourceInstanceExpression, SourceMember.Name);
Expression SourceValueExpression;
if (Nullable.GetUnderlyingType(SourceMember.ReflectedType) == null)
{
SourceValueExpression = Expression.Convert(MemberExpression, typeof(object));
}
else
{
SourceValueExpression = Expression.Condition(
Expression.Property(Expression.Constant(SourceInstanceExpression), "HasValue"),
MemberExpression,
Expression.Constant(DBNull.Value),
typeof(object));
}
return SourceValueExpression;
}
Private Function GetSourceValueExpression(SourceInstanceExpression As ParameterExpression, SourceMember As MemberInfo) As Expression
Dim MemberExpression As MemberExpression = Expression.PropertyOrField(SourceInstanceExpression, SourceMember.Name)
Dim SourceValueExpression As Expression
If Nullable.GetUnderlyingType(SourceMember.ReflectedType) Is Nothing Then
SourceValueExpression = Expression.Convert(MemberExpression, GetType(Object))
Else
SourceValueExpression = Expression.Condition(
Expression.Property(Expression.Constant(SourceInstanceExpression), "HasValue"),
MemberExpression,
Expression.Constant(DBNull.Value),
GetType(Object)
)
End If
Return SourceValueExpression
End Function
This expression is then compiled into a delegate.
The DataTable and MapperDelegate is then cached to enhance performance
Points of Interest
The creation of the MemberExpression could be done at the same time as the creation of the DataTable, but I've decided against it, to make future enhancements easier
History
- 25th March, 2015: v1.0 First release
- 28th April, 2015: v1.1 Some refactoring and typechecking
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
