Generic List to DataTable

Question

Hi,

I have few methods that returns different Generic Lists.

Exists in .net any class static method or whatever to convert any list into a datatable? The only thing that i can imagine is use Reflection to do this.

IF i have this:

List<Whatever> whatever=new List<Whatever>();

(This next code doesnt work of course, but i would like to have the posibility of:

DataTable dt=(DataTable) whatever;

Thanks in advance. Kind Regards. Josema.

Answer 1

There is no generic in-built converter class in the .net framework base class library that can do this for you.

But here is a website with the code to do this using reflection.

And here is another

Answer 2

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        
    }
}

Answer 3

try this

 public static DataTable ListToDataTable<T>(IList<T> lst)
        {

             currentDT = CreateTable<T>();

            Type entType = typeof(T);

            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entType);
            foreach (T item in lst)
            {
                DataRow row = currentDT.NewRow();
                foreach (PropertyDescriptor prop in properties)
                {

                    if (prop.PropertyType == typeof(Nullable<decimal>) || prop.PropertyType == typeof(Nullable<int>) || prop.PropertyType == typeof(Nullable<Int64>))
                    {
                        if (prop.GetValue(item) == null)
                            row[prop.Name] = 0;
                        else
                            row[prop.Name] = prop.GetValue(item);
                    }
                    else
                        row[prop.Name] = prop.GetValue(item);



                }
                currentDT.Rows.Add(row);
            }

            return currentDT;

        }

        public static DataTable CreateTable<T>()
        {
            Type entType = typeof(T);
            DataTable tbl = new DataTable(DTName);
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entType);
            foreach (PropertyDescriptor prop in properties)
            {
                if (prop.PropertyType == typeof(Nullable<decimal>))
                     tbl.Columns.Add(prop.Name, typeof(decimal));
                else if (prop.PropertyType == typeof(Nullable<int>))
                    tbl.Columns.Add(prop.Name, typeof(int));
                else if (prop.PropertyType == typeof(Nullable<Int64>))
                    tbl.Columns.Add(prop.Name, typeof(Int64));
                else
                     tbl.Columns.Add(prop.Name, prop.PropertyType);
            }
            return tbl;
        }

Answer 4

I've written a small library myself to accomplish this task. It uses reflection only for the first time an object type is to be translated to a datatable. It emits a method that will do all the work translating an object type.

Its blazing fast. You can find it here: ModelShredder on GoogleCode