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
Lets take two Tables for Header and Detail Data
Lets create the tables and populate some data
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
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.
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
4 comments:
Write CommentsGreat Article!
ReplyDeletevery nice help thanks
ReplyDeleteAwesomest awesome.... Loved this post
ReplyDeleteThanks a lot for your lovely comment.... :-)
Delete