Click here to Skip to main content
15,887,477 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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

VB
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)



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,
SQL
Select Cutomers.firstname,Customers.lastname, order.orderid, order.orderdate from customers inner join order on customers.customerid = order.customerid 



Thanks,
Dino
Posted
Comments
Maciej Los 27-Nov-14 2:24am    
Does your text files are comma separated/delimited? If yes, you're doing it probably wrong. I'd suggest to read data from text file using ADO.NET.
Dino the Sink 27-Nov-14 10:09am    
I thought about ado.net but the text files are not delimited so I am parsing them using several different delimiters based on how a line begins and what keywords are in a line. Plus I don't have a single file per table. I have to process several files in this way to build my tables.
Tomas Takac 27-Nov-14 4:25am    
Is there any particular reason why you chose DataTable? Why not creating POCO classes and query them using linq?
Dino the Sink 27-Nov-14 10:09am    
I am used to sql based tables so that is why I chose a dataset with multiple tables. I have not used linq.
Tomas Takac 27-Nov-14 10:38am    
And would you be interested in such solution? Or do you prefer to use DataTables?

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

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:
VB
Dim queryResults = From o In orders Select o.Customer.FirstName, o.Customer.LastName, o.OrderID, o.OrderDate

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.

Edit: 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:
VB
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

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.
VB
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

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:
VB
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
 
Share this answer
 
v2
Comments
Maciej Los 27-Nov-14 15:00pm    
Very interesting example! +5!
Please, see my solution.
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.

VB
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


Above example merges two datatables into new one ;) So, if you want to display data in DataGridView, you bind it to the datatable created via extension method.

For further information, please see:
Queries in LINQ to DataSet[^]
LINQ in Visual Basic[^]
 
Share this answer
 
Comments
Tomas Takac 27-Nov-14 16:12pm    
+5 nice, this way the OP can use his current infrastructure.
Maciej Los 27-Nov-14 17:04pm    
Thank you, Tomas ;)
Dino the Sink 27-Nov-14 17:03pm    
This makes since thanks. I will try this. one question, I have option strict turned on so I have to type the variable qry, what is the type for that variable. do I need to add any references and import any classes to use this?
Maciej Los 27-Nov-14 17:11pm    
Place break point in this line and you'll see that it's IEnumerable(Of <anonymous type>).
Used references:
Imports System
Imports System.Core
Imports System.Data
Imports System.Data.DataSetExtensions
Imports System.Deployment
Imports System.Xml
Imports System.Xml.Linq
Imports System.Reflection
Imports System.Runtime.CompilerServices
Maciej Los 27-Nov-14 17:21pm    
By The Way: Please see the solution 1 by Tomas Takac. It's very good idea to use custom classes. Each of it could have custom parser to read text file and load data into it.
Finally, accept all valuable solutions (green button).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900