Returning strongly typed stored procedure results

Question

Currently, if we want to get a list of records from the database, our DAL returns a DataTable to our business layer, which then returns the same DataTable to our calling interface (in this case an asp.vb page).

However I don't believe we should be returning a DataTable from the BLL, I always thought it would be better to return a strongly typed collection based on the fields in the stored procedure e.g.

public Class MyCustomType
    public customerId as int32
    public name as string
end Class

public function GetCustomers() as Generic.ICollection(Of MyCustomType)
    //call to DAL here
end function

Would the best way to achieve this be iterating over our DataTable, and for each DataRow, create a new MyCustomType object and add it to the collection, then return the collection?

Thanks.

Answer 1

Here's a link to a utility that can help map the DataTable to the MyCustomType object:

http://www.eggheadcafe.com/articles/20040221.asp

It's a bit old, but may be helpful. Perhaps there are some other tools out there as well such as AutoMapper.