从2个相关的VB.net表中选择数据 [英] select data from 2 related VB.net tables
问题描述
我有一个应用程序，它读入文本文件，解析它并根据内容创建5个vb.net数据表，插入行，并将它们添加到数据集中。 4个表基于primary \foreign密钥原则彼此之间存在关系，但我没有将其编码到表中。表格如下
I have an application that reads in text files, parses it and creates 5 vb.net datatables based on the content, inserts the rows, and adds them to a dataset. the 4 tables have relationships with each other based on primary\foreign key principles, but I have not coded that into the tables. the tables are as follows
Dim dt As DataTable
Dim ds As New DataSet
dt = New DataTable("Costomers")
dt.Columns.Add("CustomerID")
dt.Columns.Add("FirstName")
dt.Columns.Add("LastName")
dt.Columns.Add("Email")
dt.Columns.Add("Phone-Number")
ds.Tables.Add(dt)
dt = New DataTable("InventoryCategories")
dt.Columns.Add("InvtCatID")
dt.Columns.Add("CategoryName")
dt.Columns.Add("CategoryDesc")
ds.Tables.Add(dt)
dt = New DataTable("InventoryItems")
dt.Columns.Add("InvtItemsID")
dt.Columns.Add("InvtCatID")
dt.Columns.Add("Price")
dt.Columns.Add("InventoryDesc")
ds.Tables.Add(dt)
dt = New DataTable("Order")
dt.Columns.Add("OrderID")
dt.Columns.Add("CustomerID")
dt.Columns.Add("OrderDate")
ds.Tables.Add(dt)
dt = New DataTable("OrderItems")
dt.Columns.Add("OrderItemsID")
dt.Columns.Add("OrderID")
dt.Columns.Add("InvtItemsID")
dt.Columns.Add("Quantity")
ds.Tables.Add(dt)
获取数据子集我已经找到了如何使用datatable.select（）方法，但我怎么能像对待SQL Querey一样对待它并使用连接构建一个SQL Like语句以从一个表中获取数据，并从另一个表IE中获取相关数据，
to get subsets of data I have figured out how to use the datatable.select() methyod, but how can I treat this like a SQL Querey and built a SQL Like statement with a join to get data from one table and the related data from the other table IE,
Select Cutomers.firstname,Customers.lastname, order.orderid, order.orderdate from customers inner join order on customers.customerid = order.customerid
谢谢，
Dino
Thanks,
Dino
推荐答案
正如评论中所提到的，我建议你离开DataTable并使用POCO类来存储你的数据和使用LINQ查询那些。对于您的示例查询，您必须声明这些类：
As mentioned in the comments I would recommend you to move away from DataTable and use POCO classes to store your data and query those using LINQ. For your sample query you would have to declare these classes:
Public Class Customer
Public Property CustomerID As Integer
Public Property FirstName As String
Public Property LastName As String
Public Property Email As String
Public Property PhoneNumber As String
End Class
Public Class [Order]
Public Property OrderID As Integer
Public Property OrderDate As Date
Public Property Customer As Customer
End Class
我将跳过解析文件并创建类的部分。我会假设你会将它们保存在列表中。那么LINQ查询将非常简单：
I will skip the part where you parse the file and create the classes. I will assume you will keep them in a list. Then the LINQ query will be rather simple:
Dim queryResults = From o In orders Select o.Customer.FirstName, o.Customer.LastName, o.OrderID, o.OrderDate
这里有两点需要注意。 First Order包含对Customer的直接引用。加载实体时需要解决这个问题。其次，linq查询生成一个匿名类型的IEnumerable。例如，您可以将其提供给您的UI，但不能从包含方法返回它。
我很欣赏这与您习惯的不同。如果您需要有关如何加载实体或如何处理queryResults的进一步帮助，我很乐意更新答案。
编辑：如何存储实体
模型中的每个实体都有一个id。在这个例子中我假设它是一个整数，但它可以是任何东西 - id并不重要。为了识别id，我们引入一个接口并用以下内容标记每个实体：
Two things to note here. First Order contains a direct reference to Customer. You would need to resolve that when you load your entities. Second, the linq query produces a IEnumerable of an anonymous type. You can feed that to your UI for example, but you cannot return it from the containing method.
I appreciate this is different than you are used to. If you need further help on how to load the entities or how to process the queryResults I'm happy to update the answer.
How to store entities
Each entity in your model has an id. In this sample I assume it is an integer, but it could be anything - id doesn't really matter. To identify the id we introduce an interface and mark each entity with that:
Public Interface IEntityWithId
Property ID As Integer
End Interface
Public Class Customer
Implements IEntityWithId
Public Property CustomerID As Integer Implements IEntityWithId.ID
Public Property FirstName As String
Public Property LastName As String
Public Property Email As String
Public Property PhoneNumber As String
End Class
Public Class [Order]
Implements IEntityWithId
Public Property OrderID As Integer Implements IEntityWithId.ID
Public Property OrderDate As Date
Public Property Customer As Customer
End Class
然后我们需要一个实体商店，一个存储库。这将在内部将实体存储在字典中，以便id可以轻松访问实体。我们将为一个类中的所有entite分组存储库 - 数据上下文。此体系结构借鉴了实体框架。
Then we need a store for the entities, a repository. This will store the entities in a dictionary internally so the entities are easily accessible by id. We will group repositories for all entites in one class - a data context. This architecture is borrowed from Entity Framework.
Public Class Repository(Of TEntity As IEntityWithId)
Private entities As New Dictionary(Of Integer, TEntity)
Public Sub Add(entity As TEntity)
entities.Add(entity.ID, entity) ' add [id, entity] pair
End Sub
Public Function GetById(id As Integer) As TEntity
Return entities(id)
End Function
Public ReadOnly Property All As IEnumerable(Of TEntity)
Get
Return entities.Values
End Get
End Property
End Class
Public Class DataContext
Public Property Customers As Repository(Of Customer)
Public Property Orders As Repository(Of [Order])
Public Sub New()
Customers = New Repository(Of Customer)
Orders = New Repository(Of [Order])
End Sub
End Class
解析输入时，创建一个新的实体并将其添加到数据上下文中。首先你需要阅读客户，只有订单？还记得Order类中的Customer引用吗？加载订单时，您需要已经加载客户，以便您可以按ID解析对象。上面的代码将使它变得更容易：
When you parse the input, you create a new entity and add it to the data context. First you need to read customers, only then orders? Remember the Customer reference in Order class? When loading orders you need to have customers already loaded so you can resolve the objects by id. The above code will make it a lot easier:
Dim ctx As New DataContext
ctx.Customers.Add(New Customer With { .CustomerID = 1, .FirstName = "Paul", .LastName = "Newman" })
ctx.Orders.Add(New [Order] With { .OrderID = 1000, .OrderDate= #11/21/2014#, .Customer = ctx.Customers.GetById(1) })
ctx.Orders.Add(New [Order] With { .OrderID = 2000, .OrderDate= #11/24/2014#, .Customer = ctx.Customers.GetById(1) })
Dim queryResults = From o In ctx.Orders.All Select o.Customer.FirstName, o.Customer.LastName, o.OrderID, o.OrderDate
感谢您的解释。
好的，如果你不能使用ADO .NET，你可以使用Linq查询连接数据。
注意： 能够要使用以下示例，请点击此链接：如何：实现CopyToDataTable< t> ;通用类型T不是DataRow的地方 [ ^ ]创建扩展方法。
Thank you for explanation.
OK, if you can not use ADO.NET, you can join data using Linq query.
Note: to be able to use below example, follow this link: How to: Implement CopyToDataTable<t> Where the Generic Type T Is Not a DataRow[^] to create extension method.
Dim dt As DataTable = Nothing
Dim dr As DataRow = Nothing
Dim ds As DataSet = New DataSet
dt = New DataTable("Customers")
dt.Columns.Add("CustomerID", Type.GetType("System.Int32"))
dt.Columns.Add("FirstName", Type.GetType("System.String"))
dt.Columns.Add("LastName", Type.GetType("System.String"))
dt.Columns.Add("Email", Type.GetType("System.String"))
dt.Columns.Add("Phone-Number", Type.GetType("System.String"))
dr = dt.NewRow()
dr("CustomerID") = 1
dr("FirstName") = "Maciej"
dr("LastName") = "Los"
dr("Email") = "los@los.com.pl"
dr("Phone-Number") = "NA"
dt.Rows.Add(dr)
dr = dt.NewRow()
dr("CustomerID") = 22
dr("FirstName") = "John"
dr("LastName") = "Doe"
dr("Email") = "doe@doe.com.zl"
dr("Phone-Number") = "NA"
dt.Rows.Add(dr)
ds.Tables.Add(dt)
dt = New DataTable("Orders")
dt.Columns.Add("OrderID", Type.GetType("System.Int32"))
dt.Columns.Add("CustomerID", Type.GetType("System.Int32"))
dt.Columns.Add("OrderDate", Type.GetType("System.DateTime"))
dr = dt.NewRow()
dr("OrderID") = 1
dr("CustomerID") = 1
dr("OrderDate") = Date.Now
dt.Rows.Add(dr)
dr = dt.NewRow()
dr("OrderID") = 2
dr("CustomerID") = 20
dr("OrderDate") = Date.Now
dt.Rows.Add(dr)
ds.Tables.Add(dt)
Dim qry = From c In ds.Tables("Customers").AsEnumerable() _
Join o In ds.Tables("Orders").AsEnumerable On c.Item("CustomerID") Equals o.Item("CustomerID") _
Select New With { _
.CustomerID = c.Item("CustomerID"), _
.FirstName = c.Item("FirstName"), _
.LastName = c.Item("LastName"), _
.OrderID = o.Item("OrderID"), _
.OrderDate = o.Item("OrderDate") _
}
'CopyToDataTable is Linq extension method
dt = qry.CopyToDataTable()
For Each dr In dt.Rows
Console.WriteLine("{0} | {1} | {2} | {3} | {4}", dr("CustomerId"), dr("FirstName"), dr("LastName"), dr("OrderID"), dr("OrderDate"))
Next
Console.ReadKey()
ds = Nothing
dt = Nothing
dr = Nothing
以上示例将两个数据表合并为一个;）因此，如果要在DataGridView中显示数据，请将其绑定到通过扩展方法创建的数据表。
如需了解更多信息，请参阅：
LINQ to DataSet中的查询 [ ^ ]
Visual Basic中的LINQ [ ^ ]
这篇关于从2个相关的VB.net表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
