A generic Collection to DataTable Mapper

Jörgen Andersson

4.91/5 (24 votes)

27 Apr 2015CPOL2 min read

36.3K

1.7K

A high performance extension for creating a DataTable from a Generic collection.

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()

Shrink ▲

/// <summary>
/// Creates a DataTable from an IEnumerable
/// </summary>
/// <typeparam name="TSource">The Generic type of the Collection</typeparam>
/// <param name="Collection"></param>
/// <returns>DataTable</returns>
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;
}

Shrink ▲

''' <summary>
''' Creates a DataTable from an IEnumerable
''' </summary>
''' <typeparam name="TSource">The Generic type of the Collection</typeparam>
''' <param name="Collection"></param>
''' <returns>DataTable</returns>
<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

/// <summary>
/// Creates a DataTable from an IEnumerable
/// </summary>
/// <typeparam name="TSource">The Generic type of the Collection</typeparam>
/// <param name="Collection"></param>
/// <returns>DataTable</returns>
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;
}

Shrink ▲

''' <summary>
''' Creates a DataTable from an IEnumerable
''' </summary>
''' <typeparam name="TSource">The Generic type of the Collection</typeparam>
''' <param name="Collection"></param>
''' <returns>DataTable</returns>
<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

Shrink ▲

/// <summary>
/// Creates a DataTable with the same fields as the Generic Type argument
/// </summary>
/// <typeparam name="TSource">The Generic type</typeparam>
/// <returns>DataTable</returns>
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;
}

Shrink ▲

''' <summary>
''' Creates a DataTable with the same fields as the Generic Type argument
''' </summary>
''' <typeparam name="TSource">The Generic type</typeparam>
''' <returns>DataTable</returns>
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

/// <summary>
/// Creates a DataTable with the same fields as the Generic Type argument
/// </summary>
/// <typeparam name="TSource">The Generic type</typeparam>
/// <returns>DataTable</returns>
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;
}

Shrink ▲

''' <summary>
''' Creates a DataTable with the same fields as the Generic Type argument
''' </summary>
''' <typeparam name="TSource">The Generic type</typeparam>
''' <returns>DataTable</returns>
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.

Shrink ▲

/// <summary>
/// Adds a Column to a DataTable
/// </summary>
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);
    }
}

Shrink ▲

''' <summary>
''' Adds a Column to a DataTable
''' </summary>
<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

/// <summary>
/// Adds a Column to a DataTable
/// </summary>
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);
    }
}

Shrink ▲

''' <summary>
''' Adds a Column to a DataTable
''' </summary>
<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

Shrink ▲

[FieldName("Some odd fieldname")]
public string Name { get; set; }

Shrink ▲

<FieldName("Some odd fieldname")>
Property Name As String

[FieldName("Some odd fieldname")]
public string Name { get; set; }

Shrink ▲

<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

Shrink ▲

/// <summary>
/// Creates a delegate that maps an instance of TSource to an ItemArray of the supplied DataTable
/// </summary>
/// <typeparam name="TSource">The Generic Type to map from</typeparam>
/// <param name="dt">The DataTable to map to</param>
/// <returns>Func(Of TSource, Object())</returns>
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();
}

Shrink ▲

''' <summary>
''' Creates a delegate that maps an instance of TSource to an ItemArray of the supplied DataTable
''' </summary>
''' <typeparam name="TSource">The Generic Type to map from</typeparam>
''' <param name="dt">The DataTable to map to</param>
''' <returns>Func(Of TSource, Object())</returns>
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

Shrink ▲

/// <summary>
/// Creates a delegate that maps an instance of TSource to an ItemArray of the supplied DataTable
/// </summary>
/// <typeparam name="TSource">The Generic Type to map from</typeparam>
/// <param name="dt">The DataTable to map to</param>
/// <returns>Func(Of TSource, Object())</returns>
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();
}

Shrink ▲

''' <summary>
''' Creates a delegate that maps an instance of TSource to an ItemArray of the supplied DataTable
''' </summary>
''' <typeparam name="TSource">The Generic Type to map from</typeparam>
''' <param name="dt">The DataTable to map to</param>
''' <returns>Func(Of TSource, Object())</returns>
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.

Shrink ▲

/// <summary>
/// Creates an Expression representing the value of the SourceMember
/// </summary>
/// <param name="SourceInstanceExpression"></param>
/// <param name="SourceMember"></param>
/// <returns></returns>
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;
}

Shrink ▲

''' <summary>
''' Creates an Expression representing the value of the SourceMember
''' </summary>
''' <param name="SourceInstanceExpression"></param>
''' <param name="SourceMember"></param>
''' <returns></returns>
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

/// <summary>
/// Creates an Expression representing the value of the SourceMember
/// </summary>
/// <param name="SourceInstanceExpression"></param>
/// <param name="SourceMember"></param>
/// <returns></returns>
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;
}

Shrink ▲

''' <summary>
''' Creates an Expression representing the value of the SourceMember
''' </summary>
''' <param name="SourceInstanceExpression"></param>
''' <param name="SourceMember"></param>
''' <returns></returns>
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

25^th March, 2015: v1.0 First release
28^th April, 2015: v1.1 Some refactoring and typechecking

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)