c# - DataTable to List<object>

Question

How do I take a DataTable and convert it to a List?

I've included some code below in both C# and VB.NET, the issue with both of these is that we create a new object to return the data, which is very costly. I need to return a reference to the object.

The DataSetNoteProcsTableAdapters.up_GetNoteRow object does implement the INote interface.

I am using ADO.NET, along with .NET 3.5

c# code

public static IList<INote> GetNotes() 
{ 
    DataSetNoteProcsTableAdapters.up_GetNoteTableAdapter adapter =
        new DataSetNoteProcsTableAdapters.up_GetNoteTableAdapter(); 
    DataSetNoteProcs.up_GetNoteDataTable table =
        new DataSetNoteProcs.up_GetNoteDataTable(); 

    IList<INote> notes = new List<INote>(); 

    adapter.Connection = DataAccess.ConnectionSettings.Connection; 
    adapter.Fill(table); 

    foreach (DataSetNoteProcs.up_GetNoteRow t in table) { 
        notes.Add((INote)t); 
    } 

    return notes;
} 

VB.NET Code

Public Shared Function GetNotes() As IList(Of INote)
    Dim adapter As New DataSetNoteProcsTableAdapters.up_GetNoteTableAdapter
    Dim table As New DataSetNoteProcs.up_GetNoteDataTable

    Dim notes As IList(Of INote) = New List(Of INote)

    adapter.Connection = DataAccess.ConnectionSettings.Connection
    adapter.Fill(table)

    For Each t As DataSetNoteProcs.up_GetNoteRow In table
        notes.Add(CType(t, INote))
    Next

    Return notes
End Function

Answer 1

I have another approach that might be worth taking a look at. It's a helper method. Create a custom class file named CollectionHelper:

    public static IList<T> ConvertTo<T>(DataTable table)
    {
        if (table == null)
            return null;

        List<DataRow> rows = new List<DataRow>();

        foreach (DataRow row in table.Rows)
            rows.Add(row);

        return ConvertTo<T>(rows);
    }

Imagine you want to get a list of customers. Now you'll have the following caller:

List<Customer> myList = (List<Customer>)CollectionHelper.ConvertTo<Customer>(table);

The attributes you have in your DataTable must match your Customer class (fields like Name, Address, Telephone).

I hope it helps!

For who are willing to know why to use lists instead of DataTables: link text

The full sample:

http://lozanotek.com/blog/archive/2007/05/09/Converting_Custom_Collections_To_and_From_DataTable.aspx