Relationships in Data Access Layer

[Nitesh]

Hi Guys,

I have 3 tables. One is called Sport(SportID, Description,Active)
sample data:

1 Soccer 1
2 Cricket 1

The second table is called Teams(TeamID, Description, Active)
sample data:
1 Under 14A 1
2 First XI 1

Since soccer and cricket can have an Under 14A team I have a table called Sport_Teams(ID, SportID(FK), TeamID(FK)) that allows me to have the many to many relationship.
sample data:
1 1(Soccer) 1(Under 14A)
2 1(Soccer) 2(Fisrt XI)
3 2(Cricket) 1(Under 14A)

My DataAccessLayer returns datatables 90% of the time.

#Region "Sports"
        Public Function GetSports() As DataTable

            Using con As New MySqlConnection(strCon)

                Dim cmd As MySqlCommand = New MySqlCommand("SELECT * FROM tblSport WHERE (iactive=1) ORDER BY descr;", con)

                con.Open()

                Dim reader As MySqlDataReader = cmd.ExecuteReader

                _data.Load(reader)

                con.Close()
                reader.Close()
                cmd.Dispose()

                Return _data
            End Using
        End Function

Similarly I have a function to return a datatable of teams.

Then I have a class called Sports that gets the data from the DAL.

Public Class Sports
        Inherits Generic
#Region "Constructors"
        Public Sub New()
            MyBase.New()
        End Sub
        Public Sub New(ByVal SportID As Long, ByVal Description As String, ByVal Active As Boolean?)
            MyBase.New(SportID, Description, Active)
        End Sub
#End Region
#Region "Methods"
        Public Shared Function GetSports() As DataTable
            Dim DAL As New MySchoolDataAccessLayer
            Dim dt As DataTable = DAL.GetSports()

            Return dt
        End Function

I have a smilar class to retrieve teams information

Finally my BLL looks like this. It interacts with the above class

Public Property AllSports() As DataTable
            Get
                Return m_SportTable
            End Get
            Set(ByVal value As DataTable)
                m_SportTable = value
            End Set
        End Property

        Public Function GetSports() As DataTable

            Me.AllSports = Sports.GetSports

            Return Me.AllSports
        End Function

Now I need to write a class Called Sport_Teams. I've gotten this far

Public Class Sport_Teams
        Private m_ID As Long
        Private m_Sport As Sports
        Private m_Teams As Teams
        Private m_Active As Boolean?

        Public Sub New()
            m_ID = 0
            m_Sport = Nothing
            m_Teams = Nothing
            m_Active = False
        End Sub
    End Class

I want to return Sport and Teams as types of Sport and Team. What is the best way to do this. I always get confused with relationships in my classes. Sonce sportid and teamid are foregn keys in Sport_Teams I don't know how to return their respective types in my sports_team class

[wild_bill]

Wouldn't it be easier to use strongly typed data tables, or another ORM that creates the relationships for you?