Wednesday, 18 September 2013

Nested GridView With Expandable

Nested GridView with Expandable:

In this article I'm trying to explain how to work with nested GridView with expandable option. For example if we click on parent row information then only the child grid information to be displayed. This is very pretty to show the data.

Table Design:

   
        
     CREATE TABLE Student
     (
 Student_Id INT,
 Student_Name VARCHAR(250),
 Class_Id INT,
 Class_Name VARCHAR(100),
 Gain_Marks INT,
 Total_Marks INT,
 Telugu INT,
 HINDI INT,
 English INT,
 Maths INT,
 Science INT,
 Social INT,
 Active_Tag CHAR(1),
 Created_By INT,
 Created_On DATETIME,
 Modified_By INT,
 Modified_On DATETIME,
 PRIMARY KEY (Student_Id,Class_Id)
     )
   
   


Prepare Stored Procedrue:
   


/*
 EXEC Get_Student_Mark_Details @Trans_Type='student',@STUDENT_ID=1,@CLASS_ID=8
 EXEC Get_Student_Mark_Details @Trans_Type='class',@STUDENT_ID=1,@CLASS_ID=8
 EXEC Get_Student_Mark_Details @Trans_Type='marks',@STUDENT_ID=1,@CLASS_ID=8
*/

CREATE PROCEDURE Get_Student_Mark_Details
(
 @Trans_Type VARCHAR(100),
 @STUDENT_ID int = null,
 @CLASS_ID int =null
)
AS
BEGIN
 IF @Trans_Type='student'
 BEGIN
  SELECT DISTINCT STUDENT_ID,STUDENT_NAME 
  FROM STUDENT  
 END
 ELSE IF @Trans_Type='class'
 BEGIN
  SELECT STUDENT_ID,CLASS_ID,CLASS_NAME,GAIN_MARKS,TOTAL_MARKS, 
    CAST ((CAST( GAIN_MARKS as FLOAT)/ CAST( TOTAL_MARKS AS FLOAT) * 100) AS VARCHAR(MAX))+'%' as Percentage
  FROM STUDENT 
  WHERE STUDENT_ID=@STUDENT_ID
 END
 ELSE IF @Trans_Type='marks'
 BEGIN
  SELECT STUDENT_ID,CLASS_ID,CLASS_NAME,TELUGU,HINDI,English,Maths,Science,Social 
  FROM STUDENT 
  WHERE STUDENT_ID=@STUDENT_ID AND CLASS_ID=@CLASS_ID
 END
END

Source Code:

   
        
 &lt%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="CS" %&gt

&lt!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt
&lthtml xmlns="http://www.w3.org/1999/xhtml"&gt
&lthead runat="server"&gt
    &lttitle&gt&lt/title&gt
    &ltstyle type="text/css"&gt
        body
        {
            font-family: Arial;
            font-size: 10pt;
        }
        .Grid td
        {
            background-color: #A1DCF2;
            color: black;
            font-size: 10pt;
            line-height:200%
        }
        .Grid th
        {
            background-color: #3AC0F2;
            color: White;
            font-size: 10pt;
            line-height:200%
        }
        .ChildGrid td
        {
            background-color: #eee !important;
            color: black;
            font-size: 10pt;
            line-height:200%
        }
        .ChildGrid th
        {
            background-color: #6C6C6C !important;
            color: White;
            font-size: 10pt;
            line-height:200%
        }
    &lt/style&gt
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"&gt</script>
<script type="text/javascript"&gt
    $("[src*=plus]").live("click", function () {
        $(this).closest("tr").after("&lttr&gt&lttd&gt&lt/td&gt&lttd colspan = '999'&gt" + $(this).next().html() + "&lt/td&gt&lt/tr&gt")
        $(this).attr("src", "images/minus.png");
    });
    $("[src*=minus]").live("click", function () {
        $(this).attr("src", "images/plus.png");
        $(this).closest("tr").next().remove();
    });
</script>
&lt/head&gt
&ltbody&gt
    &ltform id="form1" runat="server"&gt
    
    &ltasp:GridView ID="gvStudent" runat="server" AutoGenerateColumns="false" CssClass="Grid"
        DataKeyNames="Student_Id" OnRowDataBound="gvStudent_OnRowDataBound" Width="80%"&gt
        &ltColumns&gt
            &ltasp:TemplateField&gt
                &ltItemTemplate&gt
                    &ltimg alt = "" style="cursor: pointer" src="images/plus.png" /&gt
                    &ltasp:Panel ID="pnlclass" runat="server" Style="display: none"&gt
                    
                        &ltasp:GridView ID="gvClass" runat="server" AutoGenerateColumns="false" CssClass = "ChildGrid"
                         DataKeyNames="Class_Id" OnRowDataBound="gvClass_OnRowDataBound" Width="100%" &gt
                            &ltColumns&gt
                                &ltasp:BoundField ItemStyle-Width="150px" DataField="CLASS_ID" HeaderText="Class Id" HeaderStyle-Width="10%" /&gt
                                &ltasp:BoundField ItemStyle-Width="150px" DataField="CLASS_NAME" HeaderText="Class Name" HeaderStyle-Width="40%" /&gt
                                &ltasp:TemplateField HeaderText="Obtain Marks" HeaderStyle-Width="30%" ItemStyle-Width="30%"&gt
                                    &ltItemTemplate&gt
                                        &ltimg alt = "" style="cursor: pointer" src="images/plus.png" /&gt
                                        &ltasp:Panel ID="pnlmarks" runat="server" Style="display: none"&gt
                                            &ltasp:GridView ID="gvMarks" runat="server" AutoGenerateColumns="false" CssClass = "ChildGrid"&gt
                                                &ltColumns&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="Student_Id" HeaderText="Student Id" /&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="CLASS_ID" HeaderText="Class Id" /&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="class_name" HeaderText="Class Name" /&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="telugu" HeaderText="Telugu" /&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="hindi" HeaderText="Hindi" /&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="english" HeaderText="English" /&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="maths" HeaderText="Maths" /&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="science" HeaderText="Science" /&gt
                                                    &ltasp:BoundField ItemStyle-Width="150px" DataField="social" HeaderText="Social" /&gt
                                                &lt/Columns&gt
                                            &lt/asp:GridView&gt
                                        &lt/asp:Panel&gt
                                        &ltasp:Label ID="lblMarks" runat="server" Text='&lt%#DataBinder.Eval(Container.DataItem,"GAIN_MARKS") %&gt'&gt&lt/asp:Label&gt
                                    &lt/ItemTemplate&gt
                                &lt/asp:TemplateField&gt                                
                                &ltasp:BoundField ItemStyle-Width="150px" DataField="TOTAL_MARKS" HeaderText="Total Marks" HeaderStyle-Width="10%" /&gt
                                &ltasp:BoundField ItemStyle-Width="150px" DataField="Percentage" HeaderText="Percentage" HeaderStyle-Width="10%" /&gt
                                
                            &lt/Columns&gt
                        &lt/asp:GridView&gt
                    &lt/asp:Panel&gt
                &lt/ItemTemplate&gt
            &lt/asp:TemplateField&gt
            &ltasp:TemplateField HeaderText="Student ID"&gt
                &ltItemTemplate&gt
                    &ltasp:Label ID="lblStudentID" runat="server" Text='&lt%#DataBinder.Eval(Container.DataItem,"Student_Id") %&gt'&gt&lt/asp:Label&gt
                &lt/ItemTemplate&gt
            &lt/asp:TemplateField&gt
            &ltasp:BoundField ItemStyle-Width="150px" DataField="Student_Name" HeaderText="Student Name" /&gt                       
        &lt/Columns&gt
    &lt/asp:GridView&gt
    &lt/form&gt
&lt/body&gt
&lt/html&gt
   
   

Code Behind:

   
        
   using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

public partial class CS : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("DataBase=ENGSOFT;User id=sa;Password=P@ssword9");
    SqlDataAdapter da;
    DataSet ds;
    DataTable dt;
    SqlCommand cmd;
    int Student_Id = 1;
    int Class_Id = 8;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind_Student();
        }
    }
    
    protected void Bind_Student()
    {
        cmd = new SqlCommand("Get_Student_Mark_Details", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Trans_Type", "student");
        cmd.Parameters.AddWithValue("@STUDENT_ID", Student_Id);
        cmd.Parameters.AddWithValue("@CLASS_ID", Class_Id);
        dt = new DataTable();
        try
        {
            con.Open();
            da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                gvStudent.DataSource = dt;
                gvStudent.DataBind();
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }   
    protected void gvStudent_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string StuentId = gvStudent.DataKeys[e.Row.RowIndex].Value.ToString();
            GridView gvClass = e.Row.FindControl("gvClass") as GridView;
            cmd = new SqlCommand("Get_Student_Mark_Details", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Trans_Type", "class");
            cmd.Parameters.AddWithValue("@STUDENT_ID", StuentId);
            cmd.Parameters.AddWithValue("@CLASS_ID", Class_Id);
            dt = new DataTable();
            try
            {
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    gvClass.DataSource = dt;
                    gvClass.DataBind();
                }
            }
            catch (Exception ex)
            {
            }
            
        }
    }
    protected void gvClass_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {           
            GridView gvClass = sender as GridView;
            string StuentId = (e.Row.Parent.Parent.Parent.FindControl("lblStudentID") as Label).Text;
            string ClassId = gvClass.DataKeys[e.Row.RowIndex].Value.ToString();
            GridView gvMarks = e.Row.FindControl("gvMarks") as GridView;
            cmd = new SqlCommand("Get_Student_Mark_Details", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Trans_Type", "marks");
            cmd.Parameters.AddWithValue("@STUDENT_ID", StuentId);
            cmd.Parameters.AddWithValue("@CLASS_ID", ClassId);
            dt = new DataTable();
            try
            {
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    gvMarks.DataSource = dt;
                    gvMarks.DataBind();
                }
            }
            catch (Exception ex)
            {
            }
            
        }
    }
    
}
   
   

OutPut:
Refer Links:

http://www.aspsnippets.com/Articles/Nested-GridView-Example-in-ASPNet-using-C-and-VBNet.aspx

Conclusion:

Using this extendable nested GridView we can easily display the result in a single page itself even child grid contains more data also it's showing in a single page when they click parent grid record then only data to be visible to all.