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

That's how we do it where I work. In our case we get a DataReader and manually populate the fields of class instance. We also derive a new list type from List<MyClass> to go with it. Make sure you do proper checking for DBNull.Value and proper conversion and all that.

Answer 2

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.

Answer 3

It seems your design is attempting to separate responsibilities but stops half way. Returning something as nebulous as a DataTable to your presentation layer is implicitly coupling it to specific schema knowledge of the data source. Most likely, the DataTable contains information the presentation doesn't even need. Handling the specifics in your BLL and returning a strongly typed purposeful object to your PL is more consistent with the model it already seems you are attempting to design to.