The DataTable object is often used for data binding. The CopyToDataTable method takes the results of a query and copies the data into a DataTable, which can then be used for data binding. The CopyToDataTable methods, however, only operate on an IEnumerable<(Of <(T>)>) source where the generic parameter T is of type DataRow. Although this is useful, it does not allow tables to be created from a sequence of scalar types, from queries that project anonymous types, or from queries that perform table joins.
This topic describes how to implement two custom CopyToDataTable<T> extension methods that accept a generic parameter T of a type other than DataRow. The logic to create a DataTable from an IEnumerable<(Of <(T>)>) source is contained in the ObjectShredder<T> class, which is then wrapped in two overloaded CopyToDataTable<T> extension methods. The Shred method of the ObjectShredder<T> class returns the filled DataTable and accepts three input parameters: an IEnumerable<(Of <(T>)>) source, a DataTable, and a LoadOption enumeration. The initial schema of the returned DataTable is based on the schema of the type T. If an existing table is provided as input, the schema must be consistent with the schema of the type T. Each public property and field of the type T is converted to a DataColumn in the returned table. If the source sequence contains a type derived from T, the returned table schema is expanded for any additional public properties or fields.
For examples of using the custom CopyToDataTable<T> methods, see Creating a DataTable From a Query (LINQ to DataSet).
To implement the custom CopyToDataTable<T> methods in your application
Implement the ObjectShredder<T> class to create a DataTable from an IEnumerable<(Of <(T>)>) source:
Public Class ObjectShredder(Of T)
' Fields
Private _fi As FieldInfo()
Private _ordinalMap As Dictionary(Of String, Integer)
Private _pi As PropertyInfo()
Private _type As Type
' Constructor
Public Sub New()
Me._type = GetType(T)
Me._fi = Me._type.GetFields
Me._pi = Me._type.GetProperties
Me._ordinalMap = New Dictionary(Of String, Integer)
End Sub
Public Function ShredObject(ByVal table As DataTable, ByVal instance As T) As Object()
Dim fi As FieldInfo() = Me._fi
Dim pi As PropertyInfo() = Me._pi
If (Not instance.GetType Is GetType(T)) Then
' If the instance is derived from T, extend the table schema
' and get the properties and fields.
Me.ExtendTable(table, instance.GetType)
fi = instance.GetType.GetFields
pi = instance.GetType.GetProperties
End If
' Add the property and field values of the instance to an array.
Dim values As Object() = New Object(table.Columns.Count - 1) {}
Dim f As FieldInfo
For Each f In fi
values(Me._ordinalMap.Item(f.Name)) = f.GetValue(instance)
Next
Dim p As PropertyInfo
For Each p In pi
values(Me._ordinalMap.Item(p.Name)) = p.GetValue(instance, Nothing)
Next
' Return the property and field values of the instance.
Return values
End Function
' Summary: Loads a DataTable from a sequence of objects.
' source parameter: The sequence of objects to load into the DataTable.</param>
' table parameter: The input table. The schema of the table must match that
' the type T. If the table is null, a new table is created
' with a schema created from the public properties and fields
' of the type T.
' options parameter: Specifies how values from the source sequence will be applied to
' existing rows in the table.
' Returns: A DataTable created from the source sequence.
Public Function Shred(ByVal source As IEnumerable(Of T), ByVal table As DataTable, ByVal options As LoadOption?) As DataTable
' Load the table from the scalar sequence if T is a primitive type.
If GetType(T).IsPrimitive Then
Return Me.ShredPrimitive(source, table, options)
End If
' Create a new table if the input table is null.
If (table Is Nothing) Then
table = New DataTable(GetType(T).Name)
End If
' Initialize the ordinal map and extend the table schema based on type T.
table = Me.ExtendTable(table, GetType(T))
' Enumerate the source sequence and load the object values into rows.
table.BeginLoadData()
Using e As IEnumerator(Of T) = source.GetEnumerator
Do While e.MoveNext
If options.HasValue Then
table.LoadDataRow(Me.ShredObject(table, e.Current), options.Value)
Else
table.LoadDataRow(Me.ShredObject(table, e.Current), True)
End If
Loop
End Using
table.EndLoadData()
' Return the table.
Return table
End Function
Public Function ShredPrimitive(ByVal source As IEnumerable(Of T), ByVal table As DataTable, ByVal options As LoadOption?) As DataTable
' Create a new table if the input table is null.
If (table Is Nothing) Then
table = New DataTable(GetType(T).Name)
End If
If Not table.Columns.Contains("Value") Then
table.Columns.Add("Value", GetType(T))
End If
' Enumerate the source sequence and load the scalar values into rows.
table.BeginLoadData()
Using e As IEnumerator(Of T) = source.GetEnumerator
Dim values As Object() = New Object(table.Columns.Count - 1) {}
Do While e.MoveNext
values(table.Columns.Item("Value").Ordinal) = e.Current
If options.HasValue Then
table.LoadDataRow(values, options.Value)
Else
table.LoadDataRow(values, True)
End If
Loop
End Using
table.EndLoadData()
' Return the table.
Return table
End Function
Public Function ExtendTable(ByVal table As DataTable, ByVal type As Type) As DataTable
' Extend the table schema if the input table was null or if the value
' in the sequence is derived from type T.
Dim f As FieldInfo
Dim p As PropertyInfo
For Each f In type.GetFields
If Not Me._ordinalMap.ContainsKey(f.Name) Then
Dim dc As DataColumn
' Add the field as a column in the table if it doesn't exist
' already.
dc = IIf(table.Columns.Contains(f.Name), table.Columns.Item(f.Name), table.Columns.Add(f.Name, f.FieldType))
' Add the field to the ordinal map.
Me._ordinalMap.Add(f.Name, dc.Ordinal)
End If
Next
For Each p In type.GetProperties
If Not Me._ordinalMap.ContainsKey(p.Name) Then
' Add the property as a column in the table if it doesn't exist
' already.
Dim dc As DataColumn
dc = IIf(table.Columns.Contains(p.Name), table.Columns.Item(p.Name), table.Columns.Add(p.Name, p.PropertyType))
' Add the property to the ordinal map.
Me._ordinalMap.Add(p.Name, dc.Ordinal)
End If
Next
' Return the table.
Return table
End Function
End Class
public class ObjectShredder<T>
{
private System.Reflection.FieldInfo[] _fi;
private System.Reflection.PropertyInfo[] _pi;
private System.Collections.Generic.Dictionary<string, int> _ordinalMap;
private System.Type _type;
// ObjectShredder constructor.
public ObjectShredder()
{
_type = typeof(T);
_fi = _type.GetFields();
_pi = _type.GetProperties();
_ordinalMap = new Dictionary<string, int>();
}
/// <summary>
/// Loads a DataTable from a sequence of objects.
/// </summary>
/// <param name="source">The sequence of objects to load into the DataTable.</param>
/// <param name="table">The input table. The schema of the table must match that
/// the type T. If the table is null, a new table is created with a schema
/// created from the public properties and fields of the type T.</param>
/// <param name="options">Specifies how values from the source sequence will be applied to
/// existing rows in the table.</param>
/// <returns>A DataTable created from the source sequence.</returns>
public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options)
{
// Load the table from the scalar sequence if T is a primitive type.
if (typeof(T).IsPrimitive)
{
return ShredPrimitive(source, table, options);
}
// Create a new table if the input table is null.
if (table == null)
{
table = new DataTable(typeof(T).Name);
}
// Initialize the ordinal map and extend the table schema based on type T.
table = ExtendTable(table, typeof(T));
// Enumerate the source sequence and load the object values into rows.
table.BeginLoadData();
using (IEnumerator<T> e = source.GetEnumerator())
{
while (e.MoveNext())
{
if (options != null)
{
table.LoadDataRow(ShredObject(table, e.Current), (LoadOption)options);
}
else
{
table.LoadDataRow(ShredObject(table, e.Current), true);
}
}
}
table.EndLoadData();
// Return the table.
return table;
}
public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options)
{
// Create a new table if the input table is null.
if (table == null)
{
table = new DataTable(typeof(T).Name);
}
if (!table.Columns.Contains("Value"))
{
table.Columns.Add("Value", typeof(T));
}
// Enumerate the source sequence and load the scalar values into rows.
table.BeginLoadData();
using (IEnumerator<T> e = source.GetEnumerator())
{
Object[] values = new object[table.Columns.Count];
while (e.MoveNext())
{
values[table.Columns["Value"].Ordinal] = e.Current;
if (options != null)
{
table.LoadDataRow(values, (LoadOption)options);
}
else
{
table.LoadDataRow(values, true);
}
}
}
table.EndLoadData();
// Return the table.
return table;
}
public object[] ShredObject(DataTable table, T instance)
{
FieldInfo[] fi = _fi;
PropertyInfo[] pi = _pi;
if (instance.GetType() != typeof(T))
{
// If the instance is derived from T, extend the table schema
// and get the properties and fields.
ExtendTable(table, instance.GetType());
fi = instance.GetType().GetFields();
pi = instance.GetType().GetProperties();
}
// Add the property and field values of the instance to an array.
Object[] values = new object[table.Columns.Count];
foreach (FieldInfo f in fi)
{
values[_ordinalMap[f.Name]] = f.GetValue(instance);
}
foreach (PropertyInfo p in pi)
{
values[_ordinalMap[p.Name]] = p.GetValue(instance, null);
}
// Return the property and field values of the instance.
return values;
}
public DataTable ExtendTable(DataTable table, Type type)
{
// Extend the table schema if the input table was null or if the value
// in the sequence is derived from type T.
foreach (FieldInfo f in type.GetFields())
{
if (!_ordinalMap.ContainsKey(f.Name))
{
// Add the field as a column in the table if it doesn't exist
// already.
DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name]
: table.Columns.Add(f.Name, f.FieldType);
// Add the field to the ordinal map.
_ordinalMap.Add(f.Name, dc.Ordinal);
}
}
foreach (PropertyInfo p in type.GetProperties())
{
if (!_ordinalMap.ContainsKey(p.Name))
{
// Add the property as a column in the table if it doesn't exist
// already.
DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]
: table.Columns.Add(p.Name, p.PropertyType);
// Add the property to the ordinal map.
_ordinalMap.Add(p.Name, dc.Ordinal);
}
}
// Return the table.
return table;
}
}
Implement the custom CopyToDataTable<T> extension methods in a class:
Public Module CustomLINQtoDataSetMethods
<Extension()> _
Public Function CopyToDataTable(Of T)(ByVal source As IEnumerable(Of T)) As DataTable
Return New ObjectShredder(Of T)().Shred(source, Nothing, Nothing)
End Function
<Extension()> _
Public Function CopyToDataTable(Of T)(ByVal source As IEnumerable(Of T), ByVal table As DataTable, ByVal options As LoadOption?) As DataTable
Return New ObjectShredder(Of T)().Shred(source, table, options)
End Function
End Module
public static class CustomLINQtoDataSetMethods
{
public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
{
return new ObjectShredder<T>().Shred(source, null, null);
}
public static DataTable CopyToDataTable<T>(this IEnumerable<T> source,
DataTable table, LoadOption? options)
{
return new ObjectShredder<T>().Shred(source, table, options);
}
}
Add the ObjectShredder<T> class and CopyToDataTable<T> extension methods to your application.
Module Module1
Sub Main()
' Your application code using CopyToDataTable<T>.
End Sub
End Module
Public Module CustomLINQtoDataSetMethods
…
End Module
Public Class ObjectShredder(Of T)
…
End Class
class Program
{
static void Main(string[] args)
{
// Your application code using CopyToDataTable<T>.
}
}
public static class CustomLINQtoDataSetMethods
{
…
}
public class ObjectShredder<T>
{
…
}
Concepts
Other Resources