How to inner join two already filled DataTables in VB.NET

Question

I marked Der Golem's answer as correct as it was the right answer for the specific problem I said I was having.

Unfortunately, I failed to detect the fact that the records in datatable2 aren't unique, which obviously doesn't jive well with performing an "inner join" on the two tables for the result I was wanting.

Setting a DataRelation would work if it was.

---

I'm still new to VB.NET, so please bear with me.

• I have two data tables, each filled from different database servers.
• They both have three columns (for ease of testing, final program will have 50+).
• They both have a common data column that I want to inner join by ("OrderNum").

There's a question here, but the "answer" doesn't work for me, nor the LINQ option below it: Merging 2 data tables in vb.net

This is my example code:

DB1 = New DatabaseConnectionSQL1
DB1.OpenConn()
DB2 = New DB_DatabaseConnectionSQL2
DB2.OpenConn()

Dim dtA As DataTable = New DataTable("DataTable1")
Dim dtB As DataTable = New DataTable("DataTable2")
Dim dtCombined As DataTable = New DataTable("CombinedDataTable")

dtA.Columns.Add("Order", System.Type.GetType("System.String"))
dtA.Columns.Add("Account_Number", System.Type.GetType("System.String"))
dtA.Columns.Add("Account_Name", System.Type.GetType("System.String"))

'"Order" = "Head_Order_Number"

dtB.Columns.Add("Head_Order_Number", System.Type.GetType("System.String"))
dtB.Columns.Add("Line_Number", System.Type.GetType("System.Int32"))
dtB.Columns.Add("Model", System.Type.GetType("System.String"))

dtA = DB1.GetDataTable(sQuery1)
dtB = DB2.GetDataTable(sQuery2)

'This doesn't work as it just appends the table
'dtA.Merge(dtB, True)
'I tried creating a DataSet and setting a Relation, but that kept failing
'I've tried at least 10 different things here. I'm at my wit's end.

dgvDataGrid.DataSource = dtCombined 
dgvDataGrid.Refresh()

DB1.CloseConn()
DB2.CloseConn()

I noticed people in other places are suggesting using Linq. Even though I'm not familiar with it, I tried my best and kept failing.

Table A (dtA):

Order | Account_Number | Account_Name
10000 | 10000000000001 | BlahA
20000 | 10000000000002 | BlahB
30000 | 10000000000003 | BlahC

Table B (dtB):

Head_Order_Number| Line_Number | Model
10000            | 00000000034 | MD35Z
15000            | 00000000530 | MX25A
25000            | 00000024535 | P231Y
20000            | 00000027735 | A511L
30000            | 00000000910 | M232C

Final table I want combining the two (dtCombined):

Order | Account_Number | Account_Name | Line_Number | Model
10000 | 10000000000001 | BlahA        | 00000000034 | MD35Z
20000 | 10000000000002 | BlahB        | 00000027735 | A511L
30000 | 10000000000003 | BlahC        | 00000000910 | M232C

Any help would be greatly appreciated.

---

I tried adding a DataRelation before and kept getting an error, but I wasn't setting something up properly. Now that I fixed that problem, I'm getting another error:

"System.ArgumentException: This constraint cannot be enabled as not all values have corresponding parent values."

dt1 = New DataTable("DataTable1")
dt1.Columns.Add("order_number", System.Type.GetType("System.String"))
dt1.Columns.Add("account_name", System.Type.GetType("System.String"))

dt2 = New DataTable("DataTable2")
dt2.Columns.Add("head_order_number", System.Type.GetType("System.String"))
dt2.Columns.Add("model", System.Type.GetType("System.String"))

Conn1.ConnectionString = sConnString1
Dim da1 As SqlDataAdapter = New SqlDataAdapter(sQuery1, Conn1)
Conn1.Open()

Conn2.ConnectionString = sConnString2
Dim da2 As SqlDataAdapter = New SqlDataAdapter(sQuery2, Conn2)
Conn2.Open()

ds = New DataSet
da1.Fill(ds, "DataTable1")
da2.Fill(ds, "DataTable2")

Dim dr As DataRelation = New DataRelation("Combined", _
    ds.Tables("DataTable1").Columns("OrderNo"), _
    ds.Tables("DataTable2").Columns("OrderNo"))
ds.Relations.Add(dr)

dgvDataGrid.DataSource = ds
dgvDataGrid.Refresh()

Conn1.Close()
Conn2.Close()

---

That error seems to make sense, as DataTable1 has 1950 total rows, while DataTable2 has over 4000, but isn't that the point of the DataRelation? It effectively inner joins the two tables so the end result should be 1950 rows?

Answer 1

The query you want to execute looks like this one:

Dim sql As String = "SELECT dta.*, dtB.* FROM dtA INNER JOIN dtB ON dtA.Order = dtB.Order"

Please note that the record with Order = 25000 is not part of the INNER JOIN

[EDIT]

As per your comment, I see you lack some knowldge...

So - ASSUMING you already have your db connection prepared (conn):

Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter
da.SelectCommand = cmd
conn.Open()
Dim ds As DataSet = New DataSet
da.Fill(ds, "Result")
conn.Close()

dgvDataGrid.datasource = ds
ds.DataBind()

I'm assuming an OleDb Connection - But a SQL connection is really the same (replace OleDb with Sql)

[EDIT 2] You decided to make me sweat!

Finally, there's a solution for your very specific problem:

As shown here: http://msdn.microsoft.com/en-us/library/cc188919.aspx

The DataRelation object is what you need.

Creating DataRelation Objects

' Create the DataRelation and
' relate the customers to their orders
DataRelation oDr_Customer2Order = new DataRelation("Customer2Order",
    oDs.Tables["Customer"].Columns["CustomerID"],
    oDs.Tables["Order"].Columns["CustomerID"]);
oDs.Relations.Add(oDr_Customer2Order);

By creating the DataRelation objects and then adding them to the DataSet's Relations collection, the three DataTable objects' rowsets are related to one another through the defined fields. Like most of the ADO.NET objects, the DataRelation object has several different constructors. I used the constructor that accepts the name of the relation, the parent table's column, and the child table's column. If there were multiple columns that define the relationship, I could have passed in an array of the parent table's columns and an array of the child table's columns. Another option is to use the same first three parameters that I used in Figure 3 and then pass in a fourth parameter to represent whether the constraints should be created automatically (pass in a Boolean value). But more on constraints in a moment. Once the DataSet is filled with the three rowsets and the relations are established linking the DataTable objects, the DataSet could easily be displayed in a DataGrid on a Web Form by setting the DataSource property like this:

dataGrid1.DataSource = oDs;

The DataGrid is clever enough to figure out that there are multiple DataTable objects that need to be displayed and that it should allow the rowsets to be navigated in the order that's prescribed by the DataRelation objects.