ADO: Using JOIN in SQL

In order to maintain proper relationships between data rows in ADO Recordset objects, the JOIN SQL statement is used to retrieve the data. A typical relationship scenario would be a query that retrieves the name and address details of all customers that ordered products during the first quarter of 1997. In ADO, this operation is handled using the following code:

strQuery = "SELECT DISTINCT Customers.CompanyName,
            Customers.City, Customers.Country "
strQuery = strQuery & "FROM Customers RIGHT JOIN Orders
            ON Customers.CustomerID = Orders.CustomerID"
strQuery = strQuery & "WHERE Orders.OrderDate BETWEEN
           '19970101' And '19970331'"

objRS.Open(strQuery, objConn)    'assuming objConn is a
                                 'valid connection object

The Recordset would return the following set of rows:

ADO .NET: The DataRelation Object

One of the core design goals of ADO .NET was to provide data access in a mobile, web-based, disconnected, and server-isolated application architecture. Implementing JOIN statements like in ADO would mean that the server needs to get involved in every data manipulation scenario performed by the client. This is nowhere near server isolation.

In ADO .NET, relationships between associated rows of different DataTables are maintained by objects called DataRelations. Refer to Chapter 3 for more information on the DataRelation object. In the following piece of code, we create a DataRelation object for two DataTable objects inside a DataSet:

Private Sub Create_Cust_Order_Relation()

    Dim custCol As DataColumn
    Dim orderCol As DataColumn

    custCol = objDataSet.Tables("Customers")
                        .Columns("CustID")
    orderCol = objDataSet.Tables("Orders")
                        .Columns("CustID")
  
    ' Create DataRelation.
    Dim relCustOrder As DataRelation
    relCustOrder = New DataRelation("CustomersOrders",
                   custCol, orderCol)

    ' Add the relation to the DataSet.
    objDataSet.Relations.Add(relCustOrder)
 End Sub

As illustrated in the code above, a DataRelation object is created along two DataColumn objects. The DataColumn objects must be of the same data type. Once a DataRelation is created, it is possible to show the data in several dimensions using DataViews.

You will learn more about the DataColumn and DataView objects in Chapter 3.