Click here to Skip to main content
Click here to Skip to main content

Tagged as

Linq To Dataset: Display the Contents of Several Tables in a Data Control

, 17 May 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
In several cases, we will be required to work with data in different datasets, or on the same dataset but at different DataTables
codeproject_screenshot.PNG

Introduction

In several cases, we will be required to work with data in different datasets, or on the same dataset but at different DataTables. In these cases, the power of Linq will help us solve the problem quickly and easily.

Using the Code

To do this, we will create a non-typed dataset with three tables: Order, Details, and Products, with the following fields:

Order Details Products
orderID orderID productID
orderDate productID productDesc
quantity price

To emulate the behavior of a method to read data from a database, I created a method called DevuelveDatos with the following code:

Public Function DevuelveDatos() As DataSet
'Create the new dataset with 3 tables.
Dim ds As New DataSet
ds.Tables.Add("Order")
ds.Tables.Add("Details")
ds.Tables.Add("Products")

'Create the tables columns:
'Order:
ds.Tables("Order").Columns.Add("orderID")
ds.Tables("Order").Columns.Add("orderDate")

'Details
ds.Tables("Details").Columns.Add("orderID")
ds.Tables("Details").Columns.Add("productID")
ds.Tables("Details").Columns.Add("quantity")

'Products
ds.Tables("Products").Columns.Add("productID")
ds.Tables("Products").Columns.Add("productDesc")
ds.Tables("Products").Columns.Add("price")

'Insert records on the tables
For i As Integer = 1 To 10
ds.Tables("Order").Rows.Add(New String() {i.ToString, Date.Now})
For j As Integer = 1 To 10
ds.Tables("Details").Rows.Add(New String() {i.ToString, j.ToString, i.ToString})
Next
ds.Tables("Products").Rows.Add(
New String() {i.ToString, "Producto " + i.ToString, New Random(25 * i).Next.ToString})
Next
ds.AcceptChanges()
Return ds
End Function

Well, in the form load event of our sample form data load. To show them in the datagrid after using Linq To Dataset:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) 
Handles MyBase.Load
Dim ds As New DataSet

'Get our data.
ds = DevuelveDatos()

'Asign the data to our Datagrid using Linq.
DataGridView1.DataSource = (
From roworden As DataRow In ds.Tables("Order") 
Join rowdetalles As DataRow In ds.Tables("Details") 
On roworden.Field(Of String)("orderID") Equals 
rowdetalles.Field(Of String)("orderID") 
Join rowproductos As DataRow In ds.Tables("Products") 
On rowdetalles.Field(Of String)("productID") 
Equals rowproductos.Field(Of String)("productID")
Select New With 
{ 
.CodOrden = roworden.Field(Of String)("orderID"), 
.FechaOrden = roworden.Field(Of String)("orderDate"), 
.Producto = rowproductos.Field(Of String)("productDesc"), 
.Cantidad = rowdetalles.Field(Of String)("quantity"), 
.PrzUnidad = rowproductos.Field(Of String)("price"), 
.Total = .Cantidad * .PrzUnidad 
} 
).ToList

End Sub

Well, here's the Linq To Dataset, a very simple language understandable to all who have ever used SQL, we will explain it by parts:

First create a DataRow object of our Order master table:

From roworden As DataRow In ds.Tables("Order")

Now, very similar to SQL, use the Join statement of Linq to join the detail table, we create a table DataRow for our details and indicate the fields of both rows (roworden and rowdetalles) used to join them:

Join rowdetalles As DataRow In ds.Tables("Details")
On roworden.Field(Of String)("orderID") Equals rowdetalles.Field(Of String)("orderID")

In the same way, we do this with the products table, creating a DataRow rowproductos:

Join rowproductos As DataRow In ds.Tables("Products")
On rowdetalles.Field(Of String)("productID") 
Equals rowproductos.Field(Of String)("productID")

Once we have indicated the joins of our tables, we only need to select the result of our LINQ to Dataset, this is done with the Select:

Select New With
{
.OrderCode= roworden.Field(Of String)("orderID"),
.OrderDate = roworden.Field(Of String)("orderDate"),
.Product = rowproductos.Field(Of String)("productDesc"),
.Quantity= rowdetalles.Field(Of String)("quantity"),
.UnitPrice= rowproductos.Field(Of String)("price"),
.Total = .Cantidad * .PrzUnidad
}

The first part (. FieldName) is the new field we want to create in the outcome, may be the same name as the source field or a different name and simply equate the row object field we want. If we were typed Dataset, instead of using Field (Of T) may use the name of our field directly. Finally, the total field is a calculated field from other fields above.

If you run the sample program, you will see that the result is the image that accompanies this article. Greetings to all and happy coding!

History

  • 15 May 2010 - First version

License

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

Share

About the Author

Josué Yeray Julián Ferreiro
Software Developer (Senior) Freelance Developer
Spain Spain
MVP Windows Platform Development 2014
MVP Windows Phone Development 2013
MVP Windows Phone Development 2012
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 17 May 2010
Article Copyright 2010 by Josué Yeray Julián Ferreiro
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid