Tuesday 4 December 2012

Master Details Relationship in asp.net C# GridView DetailsView Dataset

GridView FormView master detail relationship

Step1) Add two datatables for master detail relationship
                            2 tables are
                 1.Employee Details
                 2.Sales Data for each employee.
 as shown below
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;
        Session["dataset"] = dataset;        }  
 Step2) Add GridView with the following fields
 <asp:GridView ID="GridView1"  runat="server" EmptyDataText="No data found" ViewStateMode="Enabled"
         AutoGenerateColumns="false" 
        >
        <Columns>
            <asp:BoundField HeaderText="FirstName" DataField="FirstName" ApplyFormatInEditMode="true" />
            <asp:BoundField HeaderText="LastName" DataField="LastName" />
            <asp:BoundField HeaderText="HireDate" DataField="HireDate"  DataFormatString="{0:D}"/>
            <asp:BoundField HeaderText="Department" DataField="Department" />
            <asp:BoundField HeaderText="Cost to Company" DataField="Cost to Company" DataFormatString="{0:C}" />
            <asp:TemplateField HeaderText="Emp ID">
                <ItemTemplate>
                    <asp:LinkButton  runat="server"   OnClick="Unnamed_Click1"   Text='<%# Eval("ID") %>'></asp:LinkButton>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
   </asp:GridView> 

Step 3) Add DetailsView to aspx page 
        <asp:DetailsView  ID="DetailsView1" DefaultMode="ReadOnly" EmptyDataText="No Details Available" 
            runat="server" Height="50px" Width="125px"></asp:DetailsView>
       set  default mode to ready only
Step 4) after building dataset,dataset stored in session object

in Page_Load  
protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                builddataset();
            else dataset = (DataSet)Session["dataset"];

            Bind();
        }

Step5) Bind method binds to GridView and FormView

 void Bind()
        {


            GridView1.DataSource = dataset.Tables[0];
            GridView1.DataBind();

 DetailsView1.DataSource = ((DataView)Session["childrows"]);
            DetailsView1.DataBind();

        } 

Step 6)   In GridView ItemTemplate Linkbutton ,linkbutton  event handler.  This is different from what you do in traditional way. i.e Iam not setting AutoSelectEnabled=true,  Iam dealing with ItemTemplate control.i.e LinkButton

  protected void Unnamed_Click1(object sender, EventArgs e)
        {
            try
            {
                LinkButton linkButton = (LinkButton)sender;
                ShowDetailsView(linkButton.Text);
                Bind();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

Step 7)  ShowDetailsView  method  filters datatable based on Employee ID you clicked.

void ShowDetailsView(String key)
        {
            System.Data.DataView parentRows = new DataView();
           DataView ChildRowsView = new DataView(dataset.Tables[1]);
            ChildRowsView.RowFilter = "[Emp ID]=" key;
            Session["childrows"] = ChildRowsView; //child/filtered rows stored in session object
        } 

Run the Sample

Here the Output 






Complete Source Code 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
namespace WebApplication2
{
    public partial class master_details : System.Web.UI.Page
    {
        DataSet dataset = new DataSet();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                loadandbind();
            else dataset = (DataSet)Session["dataset"];

            Bind();
        }
        void Bind()
        {


            GridView1.DataSource = dataset.Tables[0];
            GridView1.DataBind();

            DetailsView1.DataSource = ((DataView)Session["childrows"]);
            DetailsView1.DataBind();


        }
        void loadandbind()
        {
            builddataset();


        }
        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;
            Session["dataset"] = dataset;
        }
        void ShowDetailsView(String key)
        {
            System.Data.DataView parentRows = new DataView();
            DataRow row = dataset.Tables[0].Rows.Find(key);
            DataView ChildRowsView = new DataView(dataset.Tables[1]);
            ChildRowsView.RowFilter = "[Emp ID]=" key;
            Session["childrows"] = ChildRowsView;
            //DetailsView1.DataSource = ChildRowsView;
            //DetailsView1.DataBind();
        }
        public void Page_Error(object sender, EventArgs e)
        {
            Exception ex = Server.GetLastError();
            Response.Write(ex.Message);
        }

        protected void Unnamed_Click1(object sender, EventArgs e)
        {
            try
            {
                LinkButton linkButton = (LinkButton)sender;
                ShowDetailsView(linkButton.Text);
                Bind();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

    }
}

No comments:

Post a Comment