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

• Download source code & example - 33.39 KB

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:

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