Bind DataGridView with Multiple Tables Using Linq

Introduction:

This article demonstrates how it is possible to bind datagridview with Multiple DataTables.

This Database is used for demonstration that is designed in SQL. The below image shows the relationship and attributes.

Pictorial View:  

Startup view when tblCustomer is loaded in DataGridView:

Select type of ID from a red circle: 

Here all the Custom Columns are added:

Important:

Each table has its own DataAdapter to fill it and labeled as WareHouseDataSetTableAdapters and all tables lie under a DataSet labeled as WareHouseDataSet.

Description:

In Datagridview, we have only a facility to bind grid with one table i.e., no concept of multiple DataTables binding.

To, Overcome this issue here is a possible approach to bind datagridview with a DataTables having multiple tables’ attributes embedded in it.

The procedure adopted for the resolution of issue is creating a blank DataTables having newly generated columns in it (as much as required). Finally bind filled DataTables with Datagridview. Below is the code:

Code:

VB.Net:

        Dim tblOrder As New WareHouseDataSet.tblOrderDataTable

        Dim tblCust As New WareHouseDataSet.tblCustomerDataTable

        Dim tblProduct As New WareHouseDataSet.tblProductDataTable

        Dim tblCompany As New WareHouseDataSet.tblCompanyDataTable

        Dim adaptOrder As New WareHouseDataSetTableAdapters.tblOrderTableAdapter

        Dim adaptCust As New WareHouseDataSetTableAdapters.tblCustomerTableAdapter

        Dim adaptProd As New WareHouseDataSetTableAdapters.tblProductTableAdapter

        Dim adaptCompany As New WareHouseDataSetTableAdapters.tblCompanyTableAdapter

        adaptOrder.Fill(tblOrder)

        adaptCust.Fill(tblCust)

        adaptProd.Fill(tblProduct)

        adaptCompany.Fill(tblCompany)

        Dim query = (From o In tblOrder _

                Join c In tblCust _

                On o.CustID Equals c.CustID _

                Join p In tblProduct _

                On p.PID Equals o.PID _

                Join comp In tblCompany _

                On comp.CompID Equals o.CompID _

                Where o.OrderId = MatchString _

                Select c.CustID, c.CustomerName, c.CustomerCell, p.ProductName, p.ProductPrice, p.ComparePrice, p.Warranty, o.OrderStatus, comp.CompName, comp.CompanyPhone, comp.CompAddress)

        Dim dt As DataTable = New DataTable("CustomTable")

        'Generating Columns

        For i As Integer = 0 To AvailableColumns.Count - 1

            Dim c As New DataColumn With {.ColumnName = AvailableColumns(i).ToString}

            dt.Columns.Add(c)

        Next

        'Setting Values in Cells

        For Each item In query

            Dim list As New List(Of Object)

            list.Add(item.CustID) : list.Add(item.CustomerName) : list.Add(item.CustomerCell) : list.Add(item.ProductName) : list.Add(item.ProductPrice) : list.Add(item.ComparePrice) : list.Add(item.Warranty) : list.Add(item.OrderStatus) : list.Add(item.CompName) : list.Add(item.CompanyPhone) : list.Add(item.CompAddress)

            dt.Rows.Add(list.ToArray)

        Next

dgrid.DataSource = dt  ‘Bind with DataGridView

At this point you have a DataGridView which will show the result having tblOrder, tblCompany, tblCustomer and tblProducts attributes.

Regards,

Engr. Shahan Ayyub