How to Fill a Datatable with List<T>

How to fill a DataTable with a List(Of t) or convert a List(Of t) to a DataTable?

The code you linked assumes the members are declared as properties. You didn't declare properties. You can make it work with Reflection:

Imports System.Reflection
...

      Public Shared Function ConvertToDataTable(Of T)(ByVal list As IList(Of T)) As DataTable
        Dim table As New DataTable()
        Dim fields() As FieldInfo = GetType(T).GetFields()
        For Each field As FieldInfo In fields
          table.Columns.Add(field.Name, field.FieldType)
        Next
        For Each item As T In list
          Dim row As DataRow = table.NewRow()
          For Each field As FieldInfo In fields
            row(field.Name) = field.GetValue(item)
          Next
          table.Rows.Add(row)
        Next
        Return table
      End Function

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

Easy way to fill a DataTable from a ListModel?

From the comments it seems the real problem is that you want to export a strongly typed list to an Excel file but the library you use only accepts a DataTable.

I'd suggest you use the EPPlus library instead, which can load data both from a DataTable and collections, eg:

sheet.LoadFromDataTable(myTable);

or

sheet.LoadFromCollection(myList);

EPPlus is available as a NuGet package too.

In general, you can easily convert a collection to a DataTable with MoreLINQ's ToDataTable() extension, eg:

var myTable=myList.ToDataTable();

You can find the extension's code here. MoreLINQ is available as a NuGet package as well.

how to fill (write) datacolumn to list?

Ok.. so the below should do it.

public DataTable getfeechallan(Bal b)
    {
        SqlDataAdapter sdb = new SqlDataAdapter("Select count(*) From reserve", con);
        DataTable dt = new DataTable();
        List<string> dc = new List<string>();

        sdb.Fill(dt);

        foreach (DataRow dd in dt.Rows)
        {
           dc.Add(dd["ftid"].ToString());
        }

        Bal.abc = dc;

        return dt;
    }

One other observation. Unless you need the datatable returning, change the return type to "void". I presume all the data you need is now in the "Bal" object.

---

Related Topics