Nov 24, 2010

GridView inside GridView in ASP.Net

In this post, we will see how to use gridview inside gridview and where it is required?

Common scenario to used GridView inside GridView is to display Header and Detail Relationship Data in asp.net web page where
  • Outer GridView will display Header Data 
  • Inner GridView will display detail data.
Now lets take a example to see how it works

Lets take two Tables for Header and Detail Data
  • Header Table stores header information of the sale like Invoice Date, Customer Name & Total Amount
  • Detail Table stores details information of the sale like Item, Qty, Price, Amount where Amount is a computed column (Qty*Price)

Lets create the tables and populate some data
CREATE TABLE dbo.tblSalesH(
     InvoiceNo        VARCHAR(10)
     , InvoiceDate    DATETIME
     , CustomerName   VARCHAR(50)
     , TotalAmount    NUMERIC(10,2)     
)     

CREATE TABLE dbo.tblSalesD(
     InvoiceNo        VARCHAR(10)
     , Item           VARCHAR(10)
     , Qty            INT
     , Price          NUMERIC(10,2)
     , Amount AS (Qty*Price)
)     

INSERT INTO dbo.tblSalesH  (InvoiceNo, InvoiceDate, CustomerName, TotalAmount)
SELECT 'INV0000001', '11/01/2010', 'Ajay Sharma', 1000 UNION ALL
SELECT 'INV0000002', '11/02/2010', 'Sandeep Mittal', 800 UNION ALL
SELECT 'INV0000003', '11/03/2010', 'Abhay Kumar', 650

INSERT INTO dbo.tblSalesD (InvoiceNo, Item, Qty, Price)
SELECT 'INV0000001', 'Item - 1', 10, 50 UNION ALL
SELECT 'INV0000001', 'Item - 2', 20, 25 UNION ALL
SELECT 'INV0000002', 'Item - 1', 10, 40 UNION ALL
SELECT 'INV0000002', 'Item - 2', 20, 20 UNION ALL
SELECT 'INV0000003', 'Item - 1', 7, 50 UNION ALL
SELECT 'INV0000003', 'Item - 2', 10, 30
Below is the code to display the relationship of Header and Detail data information in gridview. 

Additionally, you need to create an image folder in your website and place two gif files plus.gif and minus.gif that can be saved by clicking on the links

Relation.aspx
@ Page Language="C#" AutoEventWireup="true" CodeFile="Relation.aspx.cs" Inherits="Relation" %>
<head runat="server">
    <title>Sales Report</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="gvParent" runat="server" AutoGenerateColumns="False" CellPadding="4" Width="100%" OnRowDataBound="gvParent_RowDataBound" OnRowCommand="gvParent_RowCommand" ForeColor="Black" BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellSpacing="2">
            <FooterStyle BackColor="#CCCCCC" />
            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:ImageButton ID="btnImage" runat="server" ImageUrl="~/Images/plus.gif" CommandName="expand" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="InvoiceNo" HeaderText="Invoice No."></asp:BoundField>
                <asp:BoundField DataField="InvoiceDate" HeaderText="Invoice Date"></asp:BoundField>
                <asp:BoundField DataField="CustomerName" HeaderText="Customer Name"></asp:BoundField>
                <asp:BoundField DataField="TotalAmount" HeaderText="Total Amount"></asp:BoundField>
                <asp:TemplateField>
                    <ItemTemplate>
                        </td></tr>
                        <tr>
                            <td>
                            </td>
                            <td colspan="4">
                                <asp:GridView ID="gvChild" runat="server" Width="100%" Visible="False" AutoGenerateColumns="False" DataSource='<%# GetChildRelation(Container.DataItem,"Relation") %>' BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" ForeColor="Black" GridLines="Vertical">
                                    <AlternatingRowStyle BackColor="#CCCCCC" />
                                    <Columns>
                                        <asp:BoundField DataField="Item" HeaderText="Item"></asp:BoundField>
                                        <asp:BoundField DataField="Qty" HeaderText="Quantity"></asp:BoundField>
                                        <asp:BoundField DataField="Price" HeaderText="Price"></asp:BoundField>
                                        <asp:BoundField DataField="Amount" HeaderText="Total"></asp:BoundField>
                                    </Columns>
                                    <FooterStyle BackColor="#CCCCCC" />
                                    <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                                    <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                                    <SortedAscendingHeaderStyle BackColor="#808080" />
                                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                                    <SortedDescendingHeaderStyle BackColor="#383838" />
                                </asp:GridView>
                            </td>
                        </tr>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
            <RowStyle BackColor="White" />
            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#808080" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#383838" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>
Relation.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

public partial class Relation : System.Web.UI.Page
{        
    private string ConnectionString 
    { 
        get {return "Data Source=SERVERNAME;Initial Catalog=DBNAME;User Id=UserName;Password=Pwd;";}        
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataSet ds = new DataSet();            
            ds = GetData();
            ds.Tables[0].TableName = "Parent";
            ds.Tables[1].TableName = "Child";
            DataRelation dr = new DataRelation("Relation", ds.Tables["Parent"].Columns["InvoiceNo"], ds.Tables["Child"].Columns["InvoiceNo"], false);
            dr.Nested = true;
            ds.Relations.Add(dr);
            gvParent.DataSource = ds;
            gvParent.DataMember = "Parent";
            gvParent.DataBind();
        }
    }

    protected DataView GetChildRelation(object pDataItem, string pRelation)
    {
        DataRowView pvoDataRowView;
        pvoDataRowView = (DataRowView)pDataItem;
        if (pvoDataRowView != null)
            return pvoDataRowView.CreateChildView(pRelation);
        else
            return null;
    }

    protected void gvParent_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "expand")
        {
            int pviIndex;
            pviIndex = Convert.ToInt16(e.CommandArgument.ToString());
            ImageButton btn = new ImageButton();
            btn = (ImageButton)gvParent.Rows[pviIndex].FindControl("btnImage");
            if (btn.ImageUrl.ToLower().Contains("plus.gif"))
            {
                btn.ImageUrl = "images/minus.gif";
                ((GridView)gvParent.Rows[pviIndex].FindControl("gvChild")).Visible = true;
            }
            else
            {
                btn.ImageUrl = "images/plus.gif";
                ((GridView)gvParent.Rows[pviIndex].FindControl("gvChild")).Visible = false;
            }
        }
    }

    protected void gvParent_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            ImageButton btn = new ImageButton();
            btn = (ImageButton)e.Row.Cells[0].FindControl("btnImage");
            btn.CommandArgument = e.Row.RowIndex.ToString();
        }
    }

    private DataSet GetData()
    {
        DataSet ds = new DataSet();
        SqlConnection con = new SqlConnection(ConnectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = @"BEGIN SELECT InvoiceNo, CONVERT(VARCHAR, InvoiceDate, 106) AS InvoiceDate, CustomerName, TotalAmount from tblSalesH; SELECT InvoiceNo, Item, Qty, Price, Amount FROM tblSalesD END";
        cmd.CommandType = CommandType.Text;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        con.Close();
        return ds;
    }    
}
OUTPUT
Output

    Choose :
  • OR
  • To comment
4 comments:
Write comments