Creating Master Details using DataGirdView in Winforms/VB.NET

How to Create Master Details using DataGirdView in Winforms/Visual C#

Creating Master  Details using DataGirdView in Winforms/VB.NET

Step1)  Add 2 datagridviews to same page

Step 2) Custom dataSet/Pragmatically Add DataSet

• @ tables  1 for Employee Details
• Second table has sales details for each Employee

Private  Sub builddataset()

 

dataset.Clear() 

If dataset.Tables.Count > 0 Then

dataset.Tables(01).Constraints.Clear() 

dataset.Tables(0).Constraints.Clear() 

 

 

End If

dataset.Tables.Clear() 

Dim table As DataTable =  New DataTable("SalesData") 

Dim id As DataColumn =  New DataColumn("ID",Type.GetType(System.Int64)) 

id.AutoIncrement = True 

id.AutoIncrementSeed = 100 

Dim salesman As DataColumn =  New DataColumn("Sales Name",Type.GetType(System.String)) 

Dim SalesManID As DataColumn =  New DataColumn("Emp ID",Type.GetType(System.Int32)) 

Dim yr2003 As DataColumn =  New DataColumn("2003",Type.GetType(System.Int32)) 

Dim yr2004 As DataColumn =  New DataColumn("2004",Type.GetType(System.Int32)) 

Dim yr2005 As DataColumn =  New DataColumn("2005",Type.GetType(System.Int32)) 

Dim yr2006 As DataColumn =  New DataColumn("2006",Type.GetType(System.Int32)) 

Dim yr2007 As DataColumn =  New DataColumn("2007",Type.GetType(System.Int32)) 

 

 

table.Columns.Add(id) 

table.Columns.Add(salesman) 

table.Columns.Add(yr2003) table.Columns.Add(yr2004) table.Columns.Add(yr2005) 

table.Columns.Add(yr2006) table.Columns.Add(yr2007) table.Columns.Add(SalesManID) 

 

 

Dim row As DataRow =  table.NewRow() 

row(1) = "Shayam" row(2) = 5000 row(3) = 6000 row(4) = 6000 row(5) = 8000 row(6) = 10000 row(7) = 11 

table.Rows.Add(row) 

row = table.NewRow() 

row(1) = "Benegal" row(2) = 8000 row(3) = 9000 row(4) = 10000 row(5) = 12000 row(6) = 14000 row(7) = 12 

table.Rows.Add(row) 

row = table.NewRow() 

row(1) = "Rowdy rathore" row(2) = 8000 row(3) = 9000 row(4) = 10000 row(5) = 12000 row(6) = 14000 row(7) = 13 

table.Rows.Add(row) 

 

 

row = table.NewRow() 

row(1) = "talaash" row(2) = 8000 row(3) = 9000 row(4) = 10000 row(5) = 12000 row(6) = 14000 row(7) = 14 

table.Rows.Add(row) 

 

 

 

 

Dim personalDetailsTbl As DataTable =  New DataTable("EmpDetails") 

Dim ID As DataColumn =  New DataColumn("ID",Type.GetType(System.Int32)) 

ID.AutoIncrement = True 

ID.AutoIncrementSeed = 11 

ID.ReadOnly = True 

Dim FirstName As DataColumn =  New DataColumn("FirstName",Type.GetType(System.String)) 

Dim LastName As DataColumn =  New DataColumn("LastName",Type.GetType(System.String)) 

Dim HireDate As DataColumn =  New DataColumn("HireDate",Type.GetType(System.DateTime)) 

Dim Dept As DataColumn =  New DataColumn("Department",Type.GetType(System.Int32)) 

Dim CTC As DataColumn =  New DataColumn("Cost to Company",Type.GetType(System.Single)) 

personalDetailsTbl.Columns.Add(ID)

personalDetailsTbl.Columns.Add(FirstName)

personalDetailsTbl.Columns.Add(LastName) 

personalDetailsTbl.Columns.Add(HireDate)

personalDetailsTbl.Columns.Add(Dept)

personalDetailsTbl.Columns.Add(CTC) 

personalDetailsTbl.Constraints.Add("PrimaryKey_EmpID", ID, True) 

Dim PersonalRow As DataRow =  personalDetailsTbl.NewRow() 

PersonalRow(1) = "Shyam" PersonalRow(2) = "Prasad"

PersonalRow(3) = New System.DateTime(2000, 10, 12) 

PersonalRow(4) = 10 PersonalRow(5) = 10000.33f 

personalDetailsTbl.Rows.Add(PersonalRow) 

PersonalRow = personalDetailsTbl.NewRow() 

PersonalRow(1) = "Benegal" PersonalRow(2) = "Prasad"

PersonalRow(3) = New System.DateTime(2012, 10, 12) 

PersonalRow(4) = 10 PersonalRow(5) = 11100.33f 

personalDetailsTbl.Rows.Add(PersonalRow) 

PersonalRow = personalDetailsTbl.NewRow() 

PersonalRow(1) = "Rowdy Rathore" PersonalRow(2) = "Kirshna"

PersonalRow(3) = New System.DateTime(1990, 10, 12) 

PersonalRow(4) = 10 PersonalRow(5) = 5555.55f 

personalDetailsTbl.Rows.Add(PersonalRow) 

PersonalRow = personalDetailsTbl.NewRow() 

PersonalRow(1) = "talaash" PersonalRow(2) = "Prasad"

PersonalRow(3) = New System.DateTime(1983, 10, 12) 

PersonalRow(4) = 10 PersonalRow(5) = 77677.77f 

personalDetailsTbl.Rows.Add(PersonalRow) 

 

 

dataset.Tables.Add(personalDetailsTbl) 

dataset.Tables.Add(table) 

 

 

Dim parentCol As DataColumn =  dataset.Tables("EmpDetails").Columns("ID") 

Dim childCol As DataColumn =  dataset.Tables("SalesData").Columns("Emp ID") 

Dim fconstraint As ForeignKeyConstraint =  New ForeignKeyConstraint(parentCol,childCol) 

'DataRelation relation = new DataRelation("foreign_key_id", parentCol, childCol); 

'relation.ParentTable.TableName = "EmpDetails"; 

'relation.ChildTable.TableName = "SalesData"; 

'dataset.Relations.Add(relation); 

fconstraInteger.UpdateRule = Rule.None 

fconstraInteger.DeleteRule = Rule.None 

fconstraInteger.AcceptRejectRule = AcceptRejectRule.None 

dataset.Tables(1).Constraints.Add(fconstraint) 

dataset.EnforceConstraints = True

End Sub

Step 3)  Mapping table columns and setting DataFieldValue

  as shown below

Step 4)  Add Button Control to Load dataSet and DataGridView1

  In Button Event Handler add following code

 Private  Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)

'Programatically building the dataset with 2 tables employee +sales data

            builddataset()

 

'This is second approach to add datafield mapping or programatically mapping columns with DataProperty name ,defined in DataSet Table.

            dataGridView2.Columns(0).DataPropertyName = "Emp ID"

            dataGridView2.Columns(01).DataPropertyName = "Sales Name"

            dataGridView2.Columns(02).DataPropertyName = "2003"

            dataGridView2.Columns(03).DataPropertyName = "2004"

            dataGridView2.Columns(04).DataPropertyName = "2005"

            dataGridView2.Columns(05).DataPropertyName = "2006"

            dataGridView2.Columns(06).DataPropertyName = "2007"

            dataGridView1.DataSource = dataset.Tables(0)

 

 End Sub

Step 4)  In DataGridView add Event handler for "CellContentClick" 

Private  Sub dataGridView1_CellContentClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)

  'Filter rows based on Emp ID.

'

            Dim cell As DataGridViewCell = dataGridView1.CurrentCell 

            Dim ChildRows As DataView =  New DataView(dataset.Tables(1),"[Emp ID]=" + cell.Value,"",DataViewRowState.CurrentRows) 

            dataGridView2.DataSource = ChildRows

End Sub

Step 5) Just Run the Program

Here is the OUTPUT