Easy way to fill a DataTable from a List?

Question

I am calling into a method that returns a List<MyViewModel> and want to fill a DataTable from that. I can not change the return type as this method is called in a few others places that need it in that list form. I know I could iterate though the list and fill the table in one row at a time but I was hoping there was more elegant solution like it's .Load function for iDataReader.

Just curious - why you want convert light and readable `List` to the complicated `DataTable` object? — Fabio, Feb 06 '17 at 16:22
A plugin I use to export information to Excel requires the incoming data to be in a DataTable. — Matthew Verstraete, Feb 06 '17 at 16:23
Any solution will loop collection - so I think classic loop with adding row for every item in the list will be simple enough approach. — Fabio, Feb 06 '17 at 16:25
Possible duplicate of [Convert generic List/Enumerable to DataTable?](http://stackoverflow.com/questions/564366/convert-generic-list-enumerable-to-datatable) — Fabio, Feb 06 '17 at 16:26
@Matthew are you absolutely certain? EPPlus can export both DataTable with `LoadFromDataTable` and strongly-typed collections with `LoadFromCollection`. If your library can't handle lists, switch to EPPlus — Panagiotis Kanavos, Feb 06 '17 at 16:26
@Fabio you are right this is a dup of that. In my searching I did not run across it. — Matthew Verstraete, Feb 06 '17 at 16:28
You really should avoid DataTable in favor of strongly typed models as much as you can. — mason, Feb 06 '17 at 16:28

Panagiotis Kanavos · Accepted Answer · 2017-02-06T16:32:11.970

3

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.

edited Feb 06 '17 at 16:32

answered Feb 06 '17 at 16:29

Panagiotis Kanavos

120,703
13
188
236

Thank you, you are right in my intent and I will look at EPPLus. I will also look into the link to examine it more just as a learning thing. – Matthew Verstraete Feb 06 '17 at 16:32
MoreLINQ is open source. You can find the source for ToDataTable [here](https://github.com/morelinq/MoreLINQ/blob/master/MoreLinq/ToDataTable.cs) – Panagiotis Kanavos Feb 06 '17 at 16:33

maraaaaaaaa · Answer 2 · 2017-02-06T16:32:33.100

public DataTable TableFromMyViewModel(params MyViewModel[] items)
{
    DataTable _result = new DataTable("MyViewModel");

    // Do this for each field
    _result.Columns.Add("Field1", typeof(String));
    _result.Columns.Add("Field2", typeof(int));
    _result.Columns.Add("Field3", typeof(String));

    foreach (MyViewModel _item in items)
    {
        DataRow _row = _result.NewRow();
        _row["Field1"] = _item.Field1;
        _row["Field2"] = _item.Field2;
        _row["Field3"] = _item.Field3;
        _result.Rows.Add(_row);
    }

    return _result;
}

Easy way to fill a DataTable from a List?

2 Answers2