ASP.NET 4 TUTORIALS: GridView DataList master detail relationship asp.net C#

GridView DataList master detail relationship asp.net C#

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"

<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">

<asp:LinkButton runat="server" OnClick="Unnamed_Click1" Text='<%# Eval("ID") %>'></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

Step 3) Add DataList to aspx page

<asp:DataList ID="DataList1" runat="server" ViewStateMode="Enabled">
            <ItemTemplate>
            <table>
        <tr>
            <td> ID:
           <asp:Label ID="Label1" 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:DataList>

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();

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

DataList1.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));