As you probably recall, ADO.Net uses a "disconnected" database model, meaning that it makes a temporary connection to a database to pull the data and all of the data is locally cached in RAM on the client PC. The DataSet object is, in many respects, a miniature in-memory relational database engine.
This might be a good time to review Using ADO.Net DataRelations
By using the built-in features of ADO.Net you can perform many database kinds of things like:
- Perform SQL-style queries
- Perform aggregations (like SUM, etc.) via expressions
- Perform an SQL-style database join
SQL Join syntax
Joining multiple Tables in a relational database (such as SQL Server) is done via an SQL statement such as:
SELECT Customers.CompanyName, Orders.OrderDate
ON Customers.CustomerID = Orders.CustomerID
In this example, the Customers table is joined to the Orders table based upon a relationship between the two tables where the CustomerID is the same.
There is no native ADO.Net support for parsing a complex SQL statement like the one above, but you can write a class module that will take two DataTables and join them together in much the same way as it's done via an SQL statement.
The trick is to leverage the ADO.Net feature called DataRelations. The DataSet object can hold a collection of relationships between DataTables. After you have created a relationship, you can then use this relationship to get data from a related table.
The Example Code
Let's look at a simple example of a LEFT join:
Dim dr_left, dr_right, dr_dest As DataRow Dim dt_left, dt_right As DataTable Dim dt As New DataTable ' create a relationship between the two tables ds.Relations.Add(New DataRelation("__RELATIONSHIP__", dc_right, dc_left, False)) For Each dr_left In dt_left.Rows dr_dest = dt.NewRow ' copy the data from the columns of the Left table For Each column In dt_left.Columns dr_dest(column.ColumnName) = dr_left(column.ColumnName) Next ' get the related rows via the Relationship dr_right = dr_left.GetParentRow("__RELATIONSHIP__") If Not IsNull(dr_right) then ' copy the data from the columns of the Right table For Each column In dt_right.Columns dr_dest(column.ColumnName) = dr_right(column.ColumnName) Next End If ' add the row to the ResultSet dt.Rows.Add(dr_dest) Next ' delete the temporary relationship ds.Relations.Remove("__RELATIONSHIP__")
Let's examine this example a little closer. The first thing we do is create a new DataRelation between the two tables. The last argument in the DataRelations constructor is set to False because we do not want to create a corresponding foreign key constraint to go along with our relationship.
' create a relationship between the two tables ds.Relations.Add(New DataRelation("__RELATIONSHIP__", dc_right, dc_left, False))
Note: While it's true that most SQL joins are performed on Primary Keys in one table against Foreign Keys on the other table, that is not a requirement. So, we can't assume that creating a foreign key constraint would be successful.
The next step is to copy all of the data from the columns in the left table. Since this example is a LEFT join, all rows from the left table should appear in the result set.
' copy the data from the columns of the left table For Each column In dt_left.Columns dr_dest(column.ColumnName) = dr_left(column.ColumnName) Next
This step is where the magic of Relations comes to play... we take a row from the left table and then get the related row from the right table. Then we copy the data from the right table's row into the result set just like we did with the left table.
' get the related rows via the Relationship dr_right = dr_left.GetParentRow("__RELATIONSHIP__") If Not IsNull(dr_right) then ' copy the data from the columns of the Right table For Each column In dt_right.Columns dr_dest(column.ColumnName) = dr_right(column.ColumnName) Next End If
OK, now that you've got the basics of doing Relationship joins, let's look at a real-world function that can join two tables using LEFT, RIGHT, INNER, and FULL join methods. In this demo function, we create unique column names for the Result Set, since column name collisions are otherwise possible.
' Join two tables together Private Function JoinTables(ByVal LeftTable As DataTable, ByVal RightTable As DataTable, _ ByVal LeftColumn As String, ByVal RightColumn As String, ByVal JoinType _ As String) As DataTable Dim dr_left, dr_right, dr_dest, dr_temp, drs() As DataRow Dim dt, left_dt, right_dt As DataTable Dim right_col, left_col, column As String Dim i As Integer Dim RightColumnList, LeftColumnList As ArrayList ' build the detached TempTable that will be returned dt = BuildTempTable() left_dt = LeftTable right_dt = RightTable left_col = LeftColumn right_col = RightColumn ' what kind of join is this? Select Case JoinType Case "INNER" Case "LEFT" Case "RIGHT" ' We don't really do right joins... we just flip the arguments and ' do a left join instead. left_dt = RightTable right_dt = LeftTable left_col = RightColumn right_col = LeftColumn JoinType = "LEFT" Case "FULL" ' A full join is kinda complicated... we do a right join, then a left, ' then combine the two together. The optimum execution plan for a full ' join should be a "merge" operation... but let's not make things any ' more complicated than they already are. Dim temp As DataTable temp = JoinTables(RightTable, LeftTable, RightColumn, LeftColumn, "LEFT") dt = JoinTables(LeftTable, RightTable, LeftColumn, RightColumn, "LEFT") drs = temp.Select(TabColName(LeftTable.TableName, LeftColumn) & " is null") For Each dr_right In drs dt.ImportRow(dr_right) Next Return dt Case Else Throw New ApplicationException("SQL syntax error: Unknown Join type '" & _ JoinType & "'") End Select ' create a relationship between the two tables ds.Relations.Add(New DataRelation("__RELATIONSHIP__", right_dt.Columns(right_col), _ left_dt.Columns(left_col), False)) ' let's go! dr_temp = dt.NewRow LeftColumnList = GetTableColumns(left_dt.TableName) RightColumnList = GetTableColumns(right_dt.TableName) For Each dr_left In left_dt.Rows ' Get the related rows from the "right" table drs = dr_left.GetParentRows("__RELATIONSHIP__") ' For inner joins, we don't record anything unless there is a matching row If UBound(drs) >= 0 Or JoinType <> "INNER" Then dr_dest = dt.NewRow ' Let's start by just copying the columns from the "left" table For Each column In LeftColumnList dr_dest(TabColName(left_dt.TableName, column)) = dr_left(column) Next ' There are three possibilities... there are no matching rows, there is ' only one related row, there are many related rows. Select Case UBound(drs) Case -1 ' Just record the row as it is now (with just the columns from ' the left table). dt.Rows.Add(dr_dest) Case 0 dr_right = drs(0) For Each column In RightColumnList dr_dest(TabColName(right_dt.TableName, column)) = dr_right(column) Next dt.Rows.Add(dr_dest) Case Else ' Make a copy of the prototype datarow that we already filled in ' above. It already has the column data from the left table. dr_temp.ItemArray = dr_dest.ItemArray For Each dr_right In drs dr_dest = dt.NewRow ' Copy prototype row (the left table's data) dr_dest.ItemArray = dr_temp.ItemArray ' Copy the columns from the related rows in the right table For Each column In RightColumnList dr_dest(TabColName(right_dt.TableName, column)) = dr_right(column) Next dt.Rows.Add(dr_dest) Next End Select End If Next ' delete the temporary relationship we created above ds.Relations.Remove("__RELATIONSHIP__") Return dt End Function ' Make a unique column name Private Function MakeColumnName(ByVal TableName As String, ByVal ColumnName As _ String) As String Return (TableName.Replace(" ", "_").Trim("".ToCharArray) & "_" & _ ColumnName.Replace(" ", "_").Trim("".ToCharArray)) End Function
Note: The example above does not show the GetTableColumns() function which included in the source code example.
Read a related article on Using ADO Expressions
Read a related article on Using ADO DataRelations
Read a related article on In-Memory SQL Engine
Download the complete VB.Net Source code example used in this article: Join3.zip