Convert generic List/Enumerable to DataTable?
Here's a nice 2013 update using FastMember from NuGet:
IEnumerable<SomeType> data = ...
DataTable table = new DataTable();
using(var reader = ObjectReader.Create(data)) {
table.Load(reader);
}
This uses FastMember's meta-programming API for maximum performance. If you want to restrict it to particular members (or enforce the order), then you can do that too:
IEnumerable<SomeType> data = ...
DataTable table = new DataTable();
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description")) {
table.Load(reader);
}
Editor's Dis/claimer: FastMember is a Marc Gravell project. It's gold and full-on flies!
Yes, this is pretty much the exact opposite of this one; reflection would suffice - or if you need quicker, HyperDescriptor
in 2.0, or maybe Expression
in 3.5. Actually, HyperDescriptor
should be more than adequate.
For example:
// remove "this" if not on C# 3.0 / .NET 3.5
public static DataTable ToDataTable<T>(this IList<T> data)
{
PropertyDescriptorCollection props =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for(int i = 0 ; i < props.Count ; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
Now with one line you can make this many many times faster than reflection (by enabling HyperDescriptor
for the object-type T
).
Edit re performance query; here's a test rig with results:
Vanilla 27179
Hyper 6997
I suspect that the bottleneck has shifted from member-access to DataTable
performance... I doubt you'll improve much on that...
Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
public class MyData
{
public int A { get; set; }
public string B { get; set; }
public DateTime C { get; set; }
public decimal D { get; set; }
public string E { get; set; }
public int F { get; set; }
}
static class Program
{
static void RunTest(List<MyData> data, string caption)
{
GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced);
GC.WaitForPendingFinalizers();
GC.WaitForFullGCComplete();
Stopwatch watch = Stopwatch.StartNew();
for (int i = 0; i < 500; i++)
{
data.ToDataTable();
}
watch.Stop();
Console.WriteLine(caption + "\t" + watch.ElapsedMilliseconds);
}
static void Main()
{
List<MyData> foos = new List<MyData>();
for (int i = 0 ; i < 5000 ; i++ ){
foos.Add(new MyData
{ // just gibberish...
A = i,
B = i.ToString(),
C = DateTime.Now.AddSeconds(i),
D = i,
E = "hello",
F = i * 2
});
}
RunTest(foos, "Vanilla");
Hyper.ComponentModel.HyperTypeDescriptionProvider.Add(
typeof(MyData));
RunTest(foos, "Hyper");
Console.ReadLine(); // return to exit
}
}
How to convert a list into data table
Add this function and call it, it will convert List to DataTable.
public static DataTable ToDataTable<T>(List<T> items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
//Get all the properties
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
//Defining type of data column gives proper data table
var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
//Setting column names as Property names
dataTable.Columns.Add(prop.Name, type);
}
foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
//inserting property values to datatable rows
values[i] = Props[i].GetValue(item, null);
}
dataTable.Rows.Add(values);
}
//put a breakpoint here and check datatable
return dataTable;
}
Add a Generic List/Enumerable DataRow to DataTable?
You need to move the "_dataGridTable.Rows.Add(newRow);" line outside of the inner foreach loop:
foreach (T item in rowData)
{
foreach (PropertyDescriptor prop in properties)
{
DataRow newRow = _dataGridTable.NewRow();
foreach (DataColumn column in _dataGridTable.Columns)
{
if (columnsHashSet.Contains(prop.Name))
{
newRow[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
break;
}
}
}
_dataGridTable.Rows.Add(newRow); // _dataGridTable is my existing DataTable
}
Convert IEnumerable to DataTable
Look at this one: Convert List/IEnumerable to DataTable/DataView
In my code I changed it into a extension method:
public static DataTable ToDataTable<T>(this List<T> items)
{
var tb = new DataTable(typeof(T).Name);
PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach(var prop in props)
{
tb.Columns.Add(prop.Name, prop.PropertyType);
}
foreach (var item in items)
{
var values = new object[props.Length];
for (var i=0; i<props.Length; i++)
{
values[i] = props[i].GetValue(item, null);
}
tb.Rows.Add(values);
}
return tb;
}
How to convert a list to datatable
i only want a Datatable which contains only the rows which are in
List<Datum>
Since you already have the method it's simple:
DataTable tblDatum = ToDataTable(myobj.data)
Convert Generic Nested List to Datatable
Just a quick solution:
public DataTable CreateNestedDataTable<TOuter, TInner>(IEnumerable<TOuter> list, string innerListPropertyName)
{
PropertyInfo[] outerProperties = typeof(TOuter).GetProperties().Where(pi => pi.Name != innerListPropertyName).ToArray();
PropertyInfo[] innerProperties = typeof(TInner).GetProperties();
MethodInfo innerListGetter = typeof(TOuter).GetProperty(innerListPropertyName).GetMethod;
// set up columns
DataTable table = new DataTable();
foreach (PropertyInfo pi in outerProperties)
table.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);
foreach (PropertyInfo pi in innerProperties)
table.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);
// iterate through outer items
foreach (TOuter outerItem in list)
{
var innerList = innerListGetter.Invoke(outerItem, null) as IEnumerable<TInner>;
if (innerList == null || innerList.Count() == 0)
{
// outer item has no inner items
DataRow row = table.NewRow();
foreach (PropertyInfo pi in outerProperties)
row[pi.Name] = pi.GetValue(outerItem) ?? DBNull.Value;
table.Rows.Add(row);
}
else
{
// iterate through inner items
foreach (object innerItem in innerList)
{
DataRow row = table.NewRow();
foreach (PropertyInfo pi in outerProperties)
row[pi.Name] = pi.GetValue(outerItem) ?? DBNull.Value;
foreach (PropertyInfo pi in innerProperties)
row[pi.Name] = pi.GetValue(innerItem) ?? DBNull.Value;
table.Rows.Add(row);
}
}
}
return table;
}
One could probably expand this even further, so it could work with multiple nested lists, or automatically recognize the properties that are nested lists. But I kept it simple here.
It’s used like this:
var table = CreateNestedDataTable<foo, bar>(GenericList, "GenericNestedList");
Tested with your examples, it produces the desired results:
Above code uses PropertyInfo.GetMethod
and PropertyInfo.GetValue
which were introduced with .NET 4.5. For 4.0, make the following replacements:
// before
typeof(TOuter).GetProperty(innerListPropertyName).GetMethod;
// after
typeof(TOuter).GetProperty(innerListPropertyName).GetGetMethod(true);
// for each row assignment
// before
row[pi.Name] = pi.GetValue(item) ?? DBNull.Value;
// after
row[pi.Name] = pi.GetValue(item, null) ?? DBNull.Value;
How can i Convert Generic List into dataTable?
haven't tried it outside of just making sure it builds, runs, and seems to populate the datatable fine.
var dataTable = new DataTable();
dataTable.Columns.Add("Col1", list1.GetType().GetGenericArguments().First());
dataTable.Columns.Add("Col2", list2.GetType().GetGenericArguments().First());
dataTable.Columns.Add("Col3", list3.GetType().GetGenericArguments().First());
dataTable.Columns.Add("Col4", list4.GetType().GetGenericArguments().First());
// assumes they all match on count
for (int i = 0; i < list1.Count; i++)
{
dataTable.Rows.Add(list1[i],
list2[i],
list3[i],
list4[i]);
}
Related Topics
Reach Control from Another Page. Asp.Net
Why Are Mutable Structs "Evil"
How to Calculate Someone'S Age Based on a Datetime Type Birthday
What's Wrong With Using Thread.Abort()
How to Get All Child Controls of a Windows Forms Form of a Specific Type (Button/Textbox)
.Net String.Format() to Add Commas in Thousands Place For a Number
Public Fields Versus Automatic Properties
In C#, Should I Use String.Empty or String.Empty or "" to Intitialize a String
Entity Framework Code First - Two Foreign Keys from Same Table
Deserialize Json into C# Dynamic Object
What Is the Correct Way to Create a Single-Instance Wpf Application
How to Do Impersonation in .Net
Pass List of Checkboxes into View and Pull Out Ienumerable
How Would You Count Occurrences of a String (Actually a Char) Within a String