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

Display GridView's selected Data in Dynamically generated controls

This article completely demonstrates the procedure for displaying the selected row of datagridview in a new form and allows edit and update. This will be done by generating textboxes and labels dynamically. Vb.Net versions for the sample code is shown.The DB is designed in SSMSE2005.

Database's attributes and relationship:

When row selected:

After edit Done button is pressed as a result a confirmation is appeared.

 

Description:

 

To get the selected row from datagridview we have several events to make use of but I selected two of them and by comparing the behavior (sequence of firing) of these events, one event was rejected. The events are:

•            datagridview_Selectionchanged
•            datagridview_CellClick

 

If datagridview_Selectionchanged event will be used to bind the source of datagridview and a piece of code to generate new form will be added, at a time of loading of form this event will fire ‘n’ times where n is the number of rows the datagridview source will contain. IT would be pathetic to a user to close the form ‘n’ times when not needed. So, this event will be rejected.

On the other hand, the datagridview_CellClick event will only fire when user will select a cell, at that time we will use datagridview.currentrow property.

Namespaces used:

VB.Net

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Linq

Declarations:

FrmMain:

VB.Net

    Dim constr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WareHouse.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    Dim da As SqlDataAdapter

    Dim ds As DataSet

    Dim cmd As SqlCommand

    Dim con As SqlConnection

FrmDetails:

VB.Net

    Dim Items As ArrayList

    Dim StartingPoint As Point = New Point(20, 40)

    Dim txtboxes As New List(Of CustomTextbox)

    Dim labels As New List(Of CustomLabel)

Here you will see two new custom datatypes CustomTextbox and CustomLabel, this is designed to create such a textbox and Label which best fits to our requirement. Here are the classes:

VB.Net:

Public Class CustomTextbox

    Inherits TextBox

    Dim txtboxWidth As Integer = 140

    Dim Newfont As New Font("Moire", 12, FontStyle.Regular, GraphicsUnit.Pixel, 1)

    Public Sub New()

        MyBase.Font = Newfont

        MyBase.Width = txtboxWidth

        MyBase.ForeColor = Color.Blue

        MyBase.Text = "Not Assigned Yet" //bydefault a value set

    End Sub

End Class

Public Class CustomLabel

    Inherits Label

    Dim Newfont As New Font("Moire", 12, FontStyle.Regular, GraphicsUnit.Pixel, 1)

    Public Sub New()

        MyBase.Font = Newfont

        MyBase.ForeColor = Color.Red

    End Sub

End Class

The most important part is this part, where in FrmMain, on DatagridView_CellClick the information is gathered and passed to FrmDetails, the code is as below:

VB.Net

            con = New SqlConnection(constr)

            con.Open()

            cmd = New SqlCommand("Select * from " & treeDataBase.SelectedNode.Tag, con)

            Dim size As Integer = cmd.ExecuteReader().FieldCount

            If Not IsDBNull(dgrid.CurrentRow.Cells(0).Value) Then ' cells(0) is primary key

               Dim contents As ArrayList = New ArrayList(dgrid.CurrentRow.Cells)

                Dim f As New FrmDetails

                f.DetectControlSetting(contents)

                f.ShowDialog()

                con.Close()

                ReleaseResources()

            End If

            con.Close()

Here DetectControlSetting is a static method on FrmDetail,
    
   Public Sub DetectControlSetting(ByVal contents As ArrayList)
        Items = New ArrayList
        Items.AddRange(contents.ToArray)
    End Sub

to provide data to this form. At this step the grid data saves into an ArrayList object in FrmDetail. Here is the code:

VB.Net

        Items = New ArrayList

        Items.AddRange(contents.ToArray)

On Form_Load event of FrmDetail we need to generate the CustomTextbox and CustomLabel for Display as mentioned above.

The code used is:

VB.Net

    Private Sub FrmDetails_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

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

            txtboxes.Add(New CustomTextbox)

            labels.Add(New CustomLabel())

            If Not IsDBNull(CType(Items(i), DataGridViewTextBoxCell).Value) Then

                labels.Item(i).Text = CType(Items(i), DataGridViewTextBoxCell).OwningColumn.HeaderText

                labels.Item(i).Left = StartingPoint.X : labels.Item(i).Top = StartingPoint.Y

                txtboxes.Item(i).Text = CType(Items(i), DataGridViewTextBoxCell).Value

                txtboxes.Item(i).Left = StartingPoint.X + 130 : txtboxes.Item(i).Top = StartingPoint.Y ' X + 130 is controls Gap

                StartingPoint = New Point(StartingPoint.X, StartingPoint.Y + 50)

            Else

                labels.Item(i).Text = CType(Items(i), DataGridViewTextBoxCell).OwningColumn.HeaderText

                txtboxes.Item(i).Left = StartingPoint.X + 130 : txtboxes.Item(i).Top = StartingPoint.Y ' X + 130 is distance between controls

                labels.Item(i).Left = StartingPoint.X : labels.Item(i).Top = StartingPoint.Y

            End If

        Next

        Me.Controls.AddRange(txtboxes.ToArray)

        Me.Controls.AddRange(labels.ToArray)

        Me.Height = txtboxes.Item(Items.Count - 1).Bottom + 100

        btnDone.Top = txtboxes.Item(Items.Count - 1).Bottom + 30

        btnDone.Left = (Me.Width / 2) - (btnDone.Width / 2)

        'IDs should be protected from editing

        For Each tb As CustomTextbox In txtboxes

            If IsNumeric(tb.Text) And Not tb.Text.Contains(".") Then

                tb.ReadOnly = True

            End If

        Next

    End Sub       

At FrmDetail, we have a button to update the data and close the form, let’s see how it is possible:(Select and switch is preferred instead of IF...Then...Else)

To update the data:

VB.Net

Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WareHouse.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

Dim cmd As New SqlCommand()

con.Open()

            Select Case tablename

                Case "tblProduct"

                    cmd.CommandText = "Update " & tablename & " SET ProductName ='" & txtboxes.Item(1).Text & "',ProductPrice=" & txtboxes.Item(2).Text & " where PID=" & txtboxes.Item(0).Text & ";"

                Case "tblCustomer"

                    cmd.CommandText = "Update " & tablename & " SET CustomerName ='" & txtboxes.Item(1).Text & "',CustomerCell='" & txtboxes.Item(2).Text & "' where CustID=" & txtboxes.Item(0).Text & ";"

                Case "tblOrder"

                    cmd.CommandText = "Update " & tablename & " SET OrderStatus ='" & txtboxes.Item(4).Text & "' where OrderID= " & txtboxes.Item(0).Text & " and CompID= " & txtboxes.Item(1).Text & " and PID= " & txtboxes.Item(2).Text & " and CustID= " & txtboxes.Item(3).Text & ";"

                Case "tblCompany"

                    cmd.CommandText = "Update " & tablename & " SET CompName ='" & txtboxes.Item(1).Text & "',CompAddress='" & txtboxes.Item(2).Text & "',CompanyPhone='" & txtboxes.Item(3).Text & "' where CompID=" & txtboxes.Item(0).Text & ";"

            End Select

            FrmMain.treeDataBase.SelectedNode = FrmMain.treeDataBase.Nodes(tablename)

            cmd.Connection = con

            cmd.ExecuteNonQuery()

            con.Close()

FrmMain.dgrid.Refresh()

        MessageBox.Show("Result Updated.", "Editing result", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Me.Close()

At this point the Datagrid will be populated with the updated result.

Cheers!!!!!

Regards,

Engr. Shahan Ayyub