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.Ad(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