如何在VB.NET中内部连接两个已经填充的DataTable [英] How to inner join two already filled DataTables in VB.NET

查看：222
本文介绍了如何在VB.NET中内部连接两个已经填充的DataTable的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述



不幸的是，我没有发现这个事实， datatable2中的记录不是唯一的，这显然不能在两个表中执行我想要的结果的内部联接。



设置如果是，DataRelation将会起作用。





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




  • 我有两个数据表，分别由不同的数据库服务器填充。

  • 列（为了方便测试，最终程序将有50 +）。

  • 他们都有一个我想要加入的公共数据列（OrderNum）。



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



这是我的示例代码：

  DB1 = New DatabaseConnectionSQL1 
 DB1.OpenConn（）
 DB2 =新建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）
 
'这个doe没有工作，因为它只是附加表
'dtA.Merge（dtB，True）
'我尝试创建一个数据集并设置一个关系，但一直失败
'我有在这里尝试了至少10种不同的东西。我在我的机智的尽头。 
 
 dgvDataGrid.DataSource = dtCombined 
 dgvDataGrid.Refresh（）
 
 DB1.CloseConn（）
 DB2.CloseConn（）

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



表A（dtA）：

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

表B（dtB）：

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

决赛桌我想要组合两个（dtCombined）：

 订单| Account_Number | Account_Name | Line_Number |型号
 10000 | 10000000000001 | BlahA | 00000000034 | MD35Z 
 20000 | 10000000000002 | BlahB | 00000027735 | A511L 
 30000 | 10000000000003 | BlahC | 00000000910 | M232C

任何帮助将不胜感激。



< hr>

我以前添加了DataRelation，并且不断收到错误，但是我没有正确设置东西。现在我修复了这个问题，我收到另一个错误：



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

  dt1 =新的DataTable（DataTable1）
 dt1.Columns.Add（ ，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 Data Relation = New DataRelation（Combined，_ 
 ds.Tables（DataTable1）。列（OrderNo），_ 
 ds.Tables（DataTable2）。列（OrderNo ）
 ds.Relations.Add（dr）
 
 dgvDataGrid.DataSource = ds 
 dgvDataGrid.Refresh（）
 
 Conn1.Close（）
 Conn2.Close（）





有意义，因为DataTable1有1950个总行，而DataTable2有4000多个，但不是DataRelation的要点吗？它有效地内连接两个表，所以最终结果应该是1950行？

解决方案

您要执行的查询如下所示一个：

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

请注意， Order = 25000 是不是部分 INNER JOIN





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



所以 - ASSUMING你已经有你的数据库连接准备（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（）



<我>假设一个OleDb连接 - 但是一个SQL连接是一样的（替换OleDb与Sql）



你决定我的汗水！



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



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



DataRelation对象是您需要的。



创建DataRelation对象 

 '创建DataRelation和
'将客户与订单相关联
 DataRelation oDr_Customer2Order = new DataRelation Customer2Order，
 oDs.Tables [Customer]。列[CustomerID]，
 oDs.Tables [Order]。列[CustomerID]）; 
 oDs.Relations.Add（oDr_Customer2Order）;

通过创建DataRelation对象，然后将它们添加到DataSet的关系集合中，这三个DataTable对象的行集通过定义的字段彼此相关。像大多数ADO.NET对象一样，DataRelation对象有几个不同的构造函数。我使用接受关系的名称的构造函数，父表的列和子表的列。如果有多个列定义了关系，我可以传递一个父表的列数组和一个子表的列数组。另一个选择是使用与图3中使用的相同的前三个参数，然后传入第四个参数来表示是否自动创建约束（传递一个布尔值）。但更多的是在一段时间的制约。
一旦DataSet被填充了三个行集，并建立了链接DataTable对象的关系，DataSet就可以很容易地通过设置DataSource属性来显示在Web窗体的DataGrid中：

  dataGrid1.DataSource = oDs;

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


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?

解决方案

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.

这篇关于如何在VB.NET中内部连接两个已经填充的DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆