Tuesday, 4 December 2012

GridView DetailsView master detail relationship in asp.net vb.net

GridView DetailsView master detail relationship  in asp.net vb.net

Step1) Add two datatables for master detail relationship

                            2 tables are
                 1.Employee Details
                 2.Sales Data for each employee.
 as shown below

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

            ' }
            '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
            Session("dataset") = dataset
        End Sub
 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="DetailsView2" runat="server" DefaultMode="ReadOnly" EmptyDataText="No Details Available" Height="50px" Width="125px"> 
Step 4) 
after building dataset,dataset stored in session object

in Page_Load  
Dim dataset As DataSet =  New DataSet() 
        Protected  Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If Not IsPostBack Then
                loadandbind()
            Else If Session("dataset"),DataSet Then 
            End If

            Bind()
        End Sub

Step5) 
Bind method binds to GridView and FormView

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

            DetailsView1.DataSource = (CType(Session("childrows"), DataView))
            DetailsView1.DataBind()

        End Sub
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.

        Protected  Sub Unnamed_Click1(ByVal sender As Object, ByVal e As EventArgs)
            Try
                Dim linkButton As LinkButton = CType(sender, LinkButton)
                ShowDetailsView(linkButton.Text)
                Bind()
            Catch ex As Exception
                Response.Write(ex.Message)
            End Try
        End Sub


Run the Sample

Here the Output 





Complete Source Code 
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Namespace WebApplication2
    Public partial Class master_details
     Inherits System.Web.UI.Page
        Dim dataset As DataSet =  New DataSet() 
        Protected  Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If Not IsPostBack Then
                loadandbind()
            Else If Session("dataset"),DataSet Then 
            End If

            Bind()
        End Sub
        Private  Sub 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();

            DetailsView1.DataSource = (CType(Session("childrows"), DataView))
            DetailsView1.DataBind()

        End Sub
        Private  Sub loadandbind()
            builddataset()


        End Sub
        Private  Sub builddataset()
            ' dataset.Clear();
            ' if (dataset.Tables.Count > 0)
            ' {
            '     dataset.Tables[01].Constraints.Clear();
            '     dataset.Tables[0].Constraints.Clear();

            ' }
            '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
            Session("dataset") = dataset
        End Sub
        Private  Sub ShowDetailsView(ByVal key As String)
            Dim parentRows As System.Data.DataView =  New DataView() 
            Dim row As DataRow =  dataset.Tables(0).Rows.Find(key) 
            Dim ChildRowsView As DataView =  New DataView(dataset.Tables(1)) 
            ChildRowsView.RowFilter = "[Emp ID]=" key
            Session("childrows") = ChildRowsView
            'DetailsView1.DataSource = ChildRowsView;
            'DetailsView1.DataBind();
        End Sub
        Public  Sub Page_Error(ByVal sender As Object, ByVal e As EventArgs)
            Dim ex As Exception =  Server.GetLastError() 
            Response.Write(ex.Message)
        End Sub

        Protected  Sub Unnamed_Click1(ByVal sender As Object, ByVal e As EventArgs)
            Try
                Dim linkButton As LinkButton = CType(sender, LinkButton)
                ShowDetailsView(linkButton.Text)
                Bind()
            Catch ex As Exception
                Response.Write(ex.Message)
            End Try
        End Sub

    End Class
End Namespace