Data Access Layer returns DataTable

Question

Please have a look at the following question, which i asked some time ago: Breaking BLL (Business Logic Layer) to BLL and DAL (Data Access Layer)

This approach (Data Transfer Object) seems to work well if I am returning one record from the data access layer i.e. getNameByID returns one record.

What happens if you have a Data Access Layer function called getName(), which returns many records e.g. thousands or millions to be processed in the Business Logic Layer? (it is a scheduled task). When this is required I am currently returning a DataTable (because data readers cannot outlive a connection in VB.NET 2008). However, this question and answer seems to negate this approach: Is returning DataTable or DataSet from DAL is wrong approach. Is this a poor approach?

I realise there are ORM tools like NHibernate, which I plan to use more for future projects. However, the data access code in my current project is already written by someone else but I want to refactor it as I go along.

Update Here is some code (as suggested by Stephen Doggart):

Imports Microsoft.VisualBasic

        Public Class PersonBLL
            Private Name As String
            Private Age As Integer

            Dim objPersonDAL As New PersonDAL
            Dim personList As List(Of Person)

            'Option 2
            Public Function getPersonByID() As List(Of Person)
                personList = objPersonDAL.getPersonByID()
                Return personList
            End Function



    Public Function ShowMessageBox(ByVal listPersonBLL As List(Of Person))
        For Each p As Person In listPersonBLL
            Me.Age = p.Age
            Me.Name = p.Name
            MsgBox(Me.Age)
            MsgBox(Me.Name)
        Next
    End Function

        End Class

        Public Class PersonDAL
            Private Name As String
            Private Age As Integer

            Public Function getPersonByID() As List(Of Person)
                'Connect to database and get Person.  Return a person object
                Dim personList As List(Of Person) = New List(Of Person)
                Dim p1 As New Person
                p1.Name = "Ian"
                p1.Age = 30
                personList.Add(p1)
                Dim p2 As New Person
                p2.Name = "Steven"
                p2.Age = 28
                personList.Add(p2)
                Dim p3 As New Person
                p3.Name = "Sharon"
                p3.Age = 29
                personList.Add(p3)
                Return (personList)
            End Function
        End Class

        Public Class Person
            Private _Name As String
            Private _Age As Integer

            Public Property Name() As String
                Get
                    Return _Name
                End Get
                Set(ByVal value As String)
                    _Name = value
                End Set
            End Property

            Public Property Age() As Integer
                Get
                    Return _Age
                End Get
                Set(ByVal value As Integer)
                    _Age = value
                End Set
            End Property
        End Class

    Public Class Form1

            Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
                'If Environment.GetCommandLineArgs(0) = "Test" Then
                'MsgBox("Test")
                'End If
                Dim p1 As PersonBLL = New PersonBLL
                Dim p2 As List(Of Person) = p1.getPersonByID()
                Dim p3 As PersonBLL = New PersonBLL
                p3.ShowMessageBox(p2)
            End Sub
        End Class

Answer 1

Returning a DataTable isn't completely terrible--there's certainly worse ways of doing it--it's only partly terrible. But, who wants to eat partly terrible food unless they have no other option?

So, unless there's some reason why you need to use a DataTable, I would recommend sticking with custom DTO classes and just have your DAL return a list of those objects. For instance:

Public Function GetNames() As List(Of NameDto)
    '...
End Function