Saturday, 8 December 2012

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


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


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

        void builddataset()

        {


dataset.Clear();
if (dataset.Tables.Count > 0)
{
dataset.Tables[01].Constraints.Clear();
dataset.Tables[0].Constraints.Clear();


}
dataset.Tables.Clear();
DataTable table = new DataTable("SalesData");
DataColumn id = new DataColumn("ID", typeof(System.Int64));
id.AutoIncrement = true;
id.AutoIncrementSeed = 100;
DataColumn salesman = new DataColumn("Sales Name", typeof(System.String));
DataColumn SalesManID = new DataColumn("Emp ID", typeof(System.Int32));
DataColumn yr2003 = new DataColumn("2003", typeof(System.Int32));
DataColumn yr2004 = new DataColumn("2004", typeof(System.Int32));
DataColumn yr2005 = new DataColumn("2005", typeof(System.Int32));
DataColumn yr2006 = new DataColumn("2006", typeof(System.Int32));
DataColumn yr2007 = new DataColumn("2007", typeof(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);


DataRow row = 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);




DataTable personalDetailsTbl = new DataTable("EmpDetails");
DataColumn ID = new DataColumn("ID", typeof(System.Int32));
ID.AutoIncrement = true;
ID.AutoIncrementSeed = 11;
ID.ReadOnly = true;
DataColumn FirstName = new DataColumn("FirstName", typeof(System.String));
DataColumn LastName = new DataColumn("LastName", typeof(System.String));
DataColumn HireDate = new DataColumn("HireDate", typeof(System.DateTime));
DataColumn Dept = new DataColumn("Department", typeof(System.Int32));
DataColumn CTC = new DataColumn("Cost to Company", typeof(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);
DataRow PersonalRow = 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);


DataColumn parentCol = dataset.Tables["EmpDetails"].Columns["ID"];
DataColumn childCol = dataset.Tables["SalesData"].Columns["Emp ID"];
ForeignKeyConstraint fconstraint = 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);
fconstraint.UpdateRule = Rule.None;
fconstraint.DeleteRule = Rule.None;
fconstraint.AcceptRejectRule = AcceptRejectRule.None;
dataset.Tables[1].Constraints.Add(fconstraint);
dataset.EnforceConstraints = true;

        }

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 void button1_Click(object sender, EventArgs e)
        {
//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];
            
        }

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


private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
  //Filter rows based on Emp ID.
//
            DataGridViewCell cell =dataGridView1.CurrentCell;
            DataView ChildRows= new DataView(dataset.Tables[1], "[Emp ID]=" + cell.Value, "", DataViewRowState.CurrentRows);
            dataGridView2.DataSource = ChildRows;
        }


Step 5) Just Run the Program

Here is the OUTPUT