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

Answer 2

No, creating a list is not costly. Compared to creating the data table and fetching the data from the database, it's very cheap.

You can make it even cheaper by creating the list after populating the table, so that you can set the initial capacity to the number of rows that you will put in it:

IList<INote> notes = new List<INote>(table.Rows.Count);