SQL table join to VB.net class object

Question

I'm a web programmer transitioning to vb.net programming and I am in need of proper guidance. What I am trying to do is perform an SQL table join as shown in the reference here. It's my understanding that it's a good practice to for OOP to create a Class Object for each "type" of table in SQL.

For example, In the link in Reference I would have the following classes in .net.

Public Class Orders
    Private _OrderID As GUID
    Private _CustomerID As GUID
    Private _OrderDate As Date

    Public Property OrderID As Guid
        Get
            Return _OrderID
        End Get
        Set(ByVal value As Guid)
            _OrderID = value
        End Set
    End Property

    Public Property CustomerID As Guid
        Get
            Return _CustomerID
        End Get
        Set(ByVal value As Guid)
            _CustomerID = value
        End Set
    End Property

    Public Property OrderDate As Date
        Get
            Return _OrderDate
        End Get
        Set(ByVal value As Date)
            _OrderDate = value
        End Set
    End Property

    Public Sub New(ByVal obj As Object)
    End Sub

End Class

and

Public Class Customers
    Private _CustomerID As GUID
    Private _CustomerName As String
    Private _ContactName As String
    Private _Country As String

    Public Property CustomerID As Guid
        Get
            Return _CustomerID
        End Get
        Set(ByVal value As Guid)
            _CustomerID = value
        End Set
    End Property

    Public Property CustomerName As String
        Get
            Return _CustomerName
        End Get
        Set(ByVal value As String)
            _CustomerName = value
        End Set
    End Property

    Public Property ContactName As String
        Get
            Return _ContactName
        End Get
        Set(ByVal value As String)
            _ContactName = value
        End Set
    End Property

    Public Property Country As String
        Get
            Return _Country
        End Get
        Set(ByVal value As String)
            _Country = value
        End Set
    End Property

    Public Sub New(ByVal obj As Object)
    End Sub

End Class

So given the two classes in .NET, how can I query my SQL with a table join and still maintain a proper OOP approach? Should I create a 3rd class which combines both tables?

Like This...

Public Class CustomerOrdersCombined
    Private _OrderID As GUID
    Private _CustomerID As GUID
    Private _OrderDate As Date
    Private _CustomerName As String
    Private _ContactName As String
    Private _Country As String

    Public Property OrderID As Guid
        Get
            Return _OrderID
        End Get
        Set(ByVal value As Guid)
            _OrderID = value
        End Set
    End Property

    Public Property CustomerID As Guid
        Get
            Return _CustomerID
        End Get
        Set(ByVal value As Guid)
            _CustomerID = value
        End Set
    End Property

    Public Property OrderDate As Date
        Get
            Return _OrderDate
        End Get
        Set(ByVal value As Date)
            _OrderDate = value
        End Set
    End Property

    Public Property CustomerName As String
        Get
            Return _CustomerName
        End Get
        Set(ByVal value As String)
            _CustomerName = value
        End Set
    End Property

    Public Property ContactName As String
        Get
            Return _ContactName
        End Get
        Set(ByVal value As String)
            _ContactName = value
        End Set
    End Property

    Public Property Country As String
        Get
            Return _Country
        End Get
        Set(ByVal value As String)
            _Country = value
        End Set
    End Property

    Public Sub New(ByVal obj As Object)
    End Sub

End Class

What is the best way to do this as an object? Let me know if this post is kind of confusing. I'm not sure if I made my question very clear. Thanks in advance.

Answer 1

First of all, yes this question is a bit confusing, mainly because it seems you're showing objects and asking about an INNER JOIN SQL statement into an object.

That being said I thing it seems that you have 2 tables in your Database Customers and Orders. I am assuming that a customer can have many orders, but an order can have only one customer making Orders and Customers have a 1-N relationship (one-to-many).

Now that I think I understand your question I will go into a bit of explaining why you shouldn't have a third object and the proper way to solve the problem. The main reason for normalizing your database and having 2 separate tables is that you don't want your Orders table also containing all the information for your customers. Now I'm assuming that the SQL query for your INNER JOIN would look something like this

SELECT 
    * 
FROM 
    Customers INNER JOIN Orders 
        ON Customers.CustomerID = Orders.CustomerID 
WHERE 
    Customers.CustomerID = @CustomerID

This would mean your are pulling all the data back for the same customer for each order. This means that if a customer has 100 orders you would in essence be creating 100 instances of your CustomerOrdersCombined Class and this would be in addition too your instance of customers class and 100 instances of your Orders Class. Hopefully you can see why this is a bad idea.

Now for how I would solve this problem.

Way one Make a method:
One way to solve the issue would be to create a method in your Customers class that would return all the Orders that are related to the customer

Public Function GetCustomerOrders() As List(Of Orders)

  Return (New Orders).GetOrdersByCustomerID(Me.CustomerID)

End Function

The function "GetOrdersByCustomerID" would be a constructor for the Orders Class that returns a list of Orders by a CustomerID The SQL for that would be something like

SELECT * FROM Orders WHERE CustomerID = @CustomerID

Way 2 of solving your problem: Orders is a property of your Customer Class, this may sound confusing but think of this assume that before your the instance of Customer class dies you want to look at the customers orders several times. In the above solution every time you want to look a the orders you have to hit the data base, this is in efficient and can be handled better. By making the customers orders a property of the Customer class, that way lives in memory until the customer object dies so you only have to hit the database once. Now you might be thinking what if I want to get information about a customer and not their 100 orders, why should I look that too. Well you shouldn't! There is a method called "Lazy Loading" This mean that you only load the order if/when they're requested. So This is how I would design the Customer Class

    Public Class Customers
    Private m_CustomerID As GUID
    Private m_CustomerName As String
    Private m_ContactName As String
    Private m_Country As String
    Private m_Orders As List(Of Orders)

    Public Property CustomerID As Guid
        Get
            Return _CustomerID
        End Get
        Set(ByVal value As Guid)
            _CustomerID = value
        End Set
    End Property

    Public Property CustomerName As String
        Get
            Return _CustomerName
        End Get
        Set(ByVal value As String)
            _CustomerName = value
        End Set
    End Property

    Public Property ContactName As String
        Get
            Return _ContactName
        End Get
        Set(ByVal value As String)
            _ContactName = value
        End Set
    End Property

    Public Property Country As String
        Get
            Return _Country
        End Get
        Set(ByVal value As String)
            _Country = value
        End Set
    End Property

    Public Property Orders AS List(Of Orders)
        Get 
            If m_Orders Is Nothing Then
                'Only get the orders if requested'
                m_Orders = (New Orders).GetOrdersByCustomerID(Me.CustomerID)
            End If
            Return m_Orders
        End Get
        Set(ByVal value As List(Of Orders))
            m_Orders = value
        End Set
    End Property
    Public Sub New(ByVal obj As Object)
    End Sub

End Class

Notice in the getter is where the value is set, so otherwise orders with be a blank value. I hope this long answer is helpful to you.

Answer 2

IMO you should not "always" think that everything you do, you must create a new object especially in this kind of situation regarding tables in .NET. I suggest learning ADO.NET first and you will see there DataTables class that you can use. Also, please study LINQ as you will be captivated by this when working with sql queries.

Answer 3

You're falling into the OO purity trap.

Don't go for that trap where you query for a collection, then iterate over the collection to do the JOIN. That's the n+1 latency trap. You'll die a slow, non-performance death doing things like that. Objects aren't buying you anything.

Databases have been optimized to do JOINs very well; let them. Do the JOIN in SQL and sort out the mapping into objects that way.

An Order object might have a collection of LineItem objects underneath. Bring them all back in one query and map them once you've got the data.