如何在 VB.NET 中内部连接两个已经填充的数据表

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

提问by Keith

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

我将 Der Golem 的答案标记为正确，因为它是我所说的特定问题的正确答案。

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.

不幸的是，我未能检测到 datatable2 中的记录不是唯一的这一事实，这显然与在两个表上执行“内部连接”以获得我想要的结果不符。

Setting a DataRelation would work if it was.

如果可以，设置 DataRelation 会起作用。

---

---

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

我还是 VB.NET 的新手，所以请耐心等待。

• 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").

• 我有两个数据表，每个数据表都来自不同的数据库服务器。
• 它们都有三列（为了便于测试，最终程序将有 50+）。
• 他们都有一个公共数据列，我想通过（“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

这里有一个问题，但“答案”对我不起作用，它下面的 LINQ 选项也不起作用： 在 vb.net 中合并 2 个数据表

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.

我注意到其他地方的人建议使用 Linq。即使我不熟悉它，我也尽了最大努力并不断失败。

Table A (dtA):

表 A (dtA)：

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

Table B (dtB):

表 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):

决赛桌我想将两者结合起来（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:

我之前尝试添加 DataRelation 并不断收到错误消息，但我没有正确设置某些内容。既然我解决了这个问题，我又遇到了另一个错误：

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

“System.ArgumentException：无法启用此约束，因为并非所有值都具有相应的父值。”

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?

这个错误似乎是有道理的，因为 DataTable1 总共有 1950 行，而 DataTable2 有超过 4000 行，但这不是 DataRelation 的重点吗？它有效地内部连接了两个表，所以最终结果应该是 1950 行？

回答by Phant?maxx

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 = 25000is not partof the INNER JOIN

请注意，该记录Order = 25000是不属于内的JOIN

[EDIT]

[编辑]

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

根据你的评论，我看到你缺乏一些知识......

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

所以 - 假设您已经准备好数据库连接（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)

我假设有一个 OleDb 连接 - 但 SQL 连接实际上是一样的（用 Sql 替换 OleDb）

[EDIT 2]You decided to make me sweat!

[编辑 2]你决定让我出汗！

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

最后，有一个针对您非常具体的问题的解决方案：

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

如图所示：http: //msdn.microsoft.com/en-us/library/cc188919.aspx

The DataRelation object is what you need.

DataRelation 对象正是您所需要的。

Creating DataRelation Objects

创建 DataRelation 对象

' 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:

通过创建 DataRelation 对象，然后将它们添加到 DataSet 的 Relations 集合，三个 DataTable 对象的行集通过定义的字段相互关联。与大多数 ADO.NET 对象一样，DataRelation 对象有几个不同的构造函数。我使用了接受关系名称、父表列和子表列的构造函数。如果有多个列定义关系，我可以传入父表列的数组和子表列的数组。另一种选择是使用我在图 3 中使用的前三个参数，然后传入第四个参数来表示是否应自动创建约束（传入布尔值）。但稍后会详细介绍约束。

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.

DataGrid 足够聪明，可以确定需要显示多个 DataTable 对象，并且它应该允许按 DataRelation 对象规定的顺序导航行集。