Tuesday, 4 December 2012

GridView FormView master detail relationship

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 Formview to aspx page 
 <asp:FormView ID="FormView1" runat="server" DefaultMode="ReadOnly" EmptyDataText="No Details Avaibale"
         EmptyDataRowStyle-BackColor="Yellow" EditRowStyle-ForeColor="WhiteSmoke"
        
        >
        <ItemTemplate>
        <table>
        <tr>
            <td> ID:
           <asp:Label  runat="server"  Text='<%# Eval("ID") %>'></asp:Label>
           </td>
           <td> Employee ID:
           <asp:Label  ID="TextBox1" runat="server"  Text='<%# Eval("Emp ID") %>'></asp:Label>
           </td>
           <td> Sales Man:
           <asp:Label  ID="TextBox2" runat="server"  Text='<%# Eval("Sales Name") %>'></asp:Label>
           </td>
           </tr>
           <tr><td>Year:2003<asp:Label  ID="TextBox3" runat="server"  Text='<%# Eval("2003") %>'></asp:Label>
           </td>
           </tr>
           <tr><td>Year:2004
           <asp:Label  ID="TextBox4" runat="server"  Text='<%# Eval("2004") %>'></asp:Label>
           </td></tr>
           <tr><td>Year:2005
           <asp:Label  ID="TextBox5" runat="server"  Text='<%# Eval("2005") %>'></asp:Label>
           </td>
           </tr>
           <tr><td>Year:2006
           <asp:Label  ID="TextBox6" runat="server"  Text='<%# Eval("2006") %>'></asp:Label>
           </td></tr>
           <tr><td>Year:2007
           <asp:Label  ID="TextBox7" runat="server"  Text='<%# Eval("2007") %>'></asp:Label>
           </td></tr>
           </table>
        </ItemTemplate>
        </asp:FormView>


Step 4) fter 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();

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

        } 


Step 6)   In GridView ItemTemplate Linkbutton ,it has 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"]);//dataset.Tables[0].DefaultView; ;//(DataView)Session["childrows"];
            //DetailsView1.DataBind();
            //DataList1.DataSource = ((DataView)Session["childrows"]);
            //DataList1.DataBind();

            FormView1.DataSource = ((DataView)Session["childrows"]);
            FormView1.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);
            }
        }

    }