Saturday, 8 December 2012

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