Nested Gridview in ASP.NET
Consider we have following tables in database with columns
and relationship between tables as shown below. We have made few dummy entries
in every table.
Using ASP.NET, our task is to show data as follows:
Design
<asp:GridView ID="gridData" runat="server" Width="100%" AutoGenerateColumns="false" OnRowDataBound="gridData_RowDataBound">
<Columns>
<asp:TemplateField ItemStyle-Width="15%">
<HeaderTemplate>
<asp:Label ID="lblID" runat="server" Text="ID"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataID" runat="server" Text='<%#Eval("ID") %>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width="15%">
<HeaderTemplate>
<asp:Label ID="lblCITY_NAME" runat="server" Text="CITY
NAME"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:LinkButton ID="lblDataCITY_NAME" runat="server"
Text='<%#Eval("CITY_NAME")
%>' NavigateUrl="#" >
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width="15%">
<HeaderTemplate>
<asp:Label ID="lblTOTAL_INCOME" runat="server" Text="TOTAL
INCOME"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataTOTAL_INCOME" runat="server" Text='<%#Eval("TOTAL_INCOME","{0:n2}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width="15%" >
<HeaderTemplate>
<asp:Label ID="lblAVG_INCOME" runat="server" Text="AVG
INCOME"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataAVG_INCOME" runat="server"
Text='<%#Eval("AVG_INCOME","{0:n2}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width="15%">
<HeaderTemplate>
<asp:Label ID="lblMAX_INCOME" runat="server" Text="MAX
INCOME"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataMAX_INCOME" runat="server"
Text='<%#Eval("MAX_INCOME","{0:n2}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width="20%">
<HeaderTemplate>
<asp:Label ID="lblMIN_INCOME" runat="server" Text="MIN
INCOME"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataMIN_INCOME" runat="server" Text='<%#Eval
("MIN_INCOME","{0:n2}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField >
<asp:TemplateField ItemStyle-Width="20%">
<HeaderTemplate>
<asp:Label ID="lblGENDER_RATIO" runat="server" Text="GENDER RATIO
(%)"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataGENDER_RATIO" runat="server"
Text='<%#Eval("GENDER_RATIO","{0:n2}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width="0%">
<ItemTemplate>
<tr>
<td colspan="8">
<asp:Panel ID="pnlGrid" runat="server">
<%--ID USERID
FNAME LNAME GENDER ORG ROLE SALARY--%>
<asp:GridView ID="gridCityDetails" runat="server"
AutoGenerateColumns="false" Width="100%">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblUSERID" runat="server" Text="USERID"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataUSERID" runat="server" Text='<%#Eval("USERID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblFNAME" runat="server" Text="FNAME"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataFNAME" runat="server"
Text='<%#Eval("FNAME") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblLNAME" runat="server"
Text="LNAME"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataLNAME" runat="server"
Text='<%#Eval("LNAME") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblGENDER" runat="server"
Text="GENDER"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataGENDER" runat="server"
Text='<%#Eval("GENDER") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblORG" runat="server"
Text="ORG"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataORG" runat="server"
Text='<%#Eval("ORG") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblROLE" runat="server"
Text="ROLE"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataROLE" runat="server"
Text='<%#Eval("ROLE") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="lblSALARY" runat="server"
Text="SALARY"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblDataSALARY" runat="server"
Text='<%#Eval("SALARY") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle BackColor="#3399ff" />
<AlternatingRowStyle BackColor="#c0c0c0" />
</asp:GridView>
<%--
</div>--%>
</asp:Panel>
<asp:CollapsiblePanelExtender ID="CollapsiblePanelExtender1" runat="server" CollapseControlID="lblDataCITY_NAME"
ExpandControlID="lblDataCITY_NAME" TargetControlID="pnlGrid" Collapsed="true" SuppressPostBack="true" >
</asp:CollapsiblePanelExtender>
</td>
</tr>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
DESIGN
Highlights
1.
LinkButton lblDataCITY_NAME : Field
used to expand / collapse inner gridview. Note NavigateUrl="#" and CollapsiblePanelExtender CollapseControlID and ExpandControlID
properties.
2.
Total
width percentage of gridview itemtemplate , excluding last
nested gridview template is made equal to 100% ItemStyle-Width
3.
Make a note of float data field formatting
Text='<%#Eval("TOTAL_INCOME","{0:n2}")
%>'
4.
Inner gridview template field item style
width ItemStyle-Width="0%"
5.
Inner gridview, <td colspan="8"> to make sure it takes complete page width.
CODE
1. Stored Procedure
CREATE PROCEDURE [dbo].[SP_City_Income]
-- Add the
parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON
added to prevent extra result sets from
-- interfering with
SELECT statements.
SET NOCOUNT ON;
-- Insert
statements for procedure here
-- Get Result in
following format
-- ID | City Name |
Total Income | Avg Income |Max Income | Min Income | Gender Ratio
--Step 1. Create
temp table with this format
CREATE TABLE #TEMP(ID INT IDENTITY PRIMARY KEY , CITY_NAME VARCHAR(200),TOTAL_INCOME FLOAT,AVG_INCOME FLOAT,
MAX_INCOME FLOAT,MIN_INCOME FLOAT,GENDER_RATIO FLOAT);
--STEP 2. GET
DISTINCT CITY NAME,TOTAL_INCOME,AVG_INCOME,MAX_INCOME,MIN_INCOME
INSERT INTO #TEMP(CITY_NAME,TOTAL_INCOME,AVG_INCOME,MAX_INCOME,MIN_INCOME)
seleCT u.City,SUM(UOR.Salary),AVG(UOR.Salary),MAX(UOR.Salary),MIN(UOR.Salary) FROM
[dbo].[User] U , User_In_Org_Role UOR
WHERE u.User_ID=UOR.User_ID
GROUP BY U.City
--STEP3. GET GENDER
RATIO AS PER CITY
--CREATE TEMP TABLE
FOR THIS OPERATION : #TEMPUSER
CREATE TABLE #TEMPUSER(ID INT IDENTITY PRIMARY KEY,CITY_NAME VARCHAR(200),
MCOUNT INT,FCOUNT INT,RATIO FLOAT)
INSERT INTO #TEMPUSER(CITY_NAME,MCOUNT,FCOUNT,RATIO)
SELECT DISTINCT City,0,0,0 FROM [dbo].[User]
UPDATE #TEMPUSER
SET MCOUNT = (SELECT COUNT(User_ID) FROM [dbo].[User] U
WHERE U.City=CITY_NAME AND U.Gender='M'),
FCOUNT= (SELECT COUNT(User_ID) FROM [dbo].[User] U
WHERE U.City=CITY_NAME AND U.Gender='F')
UPDATE #TEMPUSER
SET RATIO=(FCOUNT*100.0)/MCOUNT
UPDATE T
SET T.GENDER_RATIO=TU.RATIO
FROM #TEMP T,#TEMPUSER TU
WHERE T.CITY_NAME=TU.CITY_NAME
SELECT * FROM #TEMP ORDER BY CITY_NAME
--DROP TEMP TABLE
DROP TABLE #TEMP;
DROP TABLE #TEMPUSER
END
2. Class Code
public DataSet getCityData()
{
ds = new DataSet();
try
{
SqlConnection con=new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SP_City_Income", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch (Exception)
{
throw;
}
return ds;
3. To
get data in outer gridview on button click event :
clsConnect cc = new clsConnect();
protected void btnGetData_Click(object sender, EventArgs e)
{
//execute
SP to get data
DataSet ds= cc.getCityData();
gridData.DataSource = ds.Tables[0];
gridData.DataBind();
}
}
4. For Inner Gridview – Stored Procedure
CREATE PROCEDURE [dbo].[SP_City_User]
-- Add the
parameters for the stored procedure here
@City varchar(200)
AS
BEGIN
-- SET NOCOUNT ON
added to prevent extra result sets from
-- interfering with
SELECT statements.
SET NOCOUNT ON;
-- Insert
statements for procedure here
--CREATE TEMP TABLE
FOR GETTING USER DETAILS AS FOLLOWS
--SRNO | FIRST NAME
| LAST NAME | GENDER | ORG | ROLE | SALARY
CREATE TABLE #TEMP
(ID INT
IDENTITY PRIMARY
KEY,USERID INT ,FNAME VARCHAR(200),LNAME VARCHAR(200),GENDER VARCHAR(20),ORG VARCHAR(200),ROLE VARCHAR(200),SALARY FLOAT)
INSERT INTO #TEMP (USERID,FNAME,LNAME,GENDER,ORG,ROLE,SALARY)
(SELECT U.User_ID,U.First_Name,U.Last_Name,U.Gender,O.Org_Name,R.Role_Name,UOR.Salary
FROM [dbo].[User] U,[dbo].[Organization] O,[dbo].[Role] R,[dbo].[User_In_Org_Role] UOR
WHERE U.User_ID=UOR.User_ID AND R.Role_ID=UOR.Role_ID AND O.Org_ID=UOR.Org_ID AND U.City=@City)
SELECT * FROM #TEMP
ORDER BY GENDER;
DROP TABLE #TEMP
END
5. Inner Gridview Class code
public DataSet getCityDetails(string CityName)
{
ds = new DataSet();
try
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SP_City_User", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlParameter City = new SqlParameter();
City.ParameterName = "@City";
City.DbType = DbType.String;
City.Value = CityName;
cmd.Parameters.Add(City);
da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch (Exception)
{
throw;
}
return ds;
}
6. Data Binding code : Inner Gridview
protected void gridData_RowDataBound(object sender, GridViewRowEventArgs e)
{
//bind
inner gridview for individual city
//step 1
: get city name
try
{
if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != -1)
{
LinkButton lbl = (LinkButton)e.Row.Cells[1].Controls[1];
//step
2 : Use this city name as a parameter in SP for getting contents of inner grid
DataSet ds = cc.getCityDetails(lbl.Text.Trim());
//get
inner gridview control
try
{
GridView gridCityDetails = (GridView)e.Row.Cells[7].Controls[1].Controls[2];
gridCityDetails.DataSource
= ds.Tables[0];
gridCityDetails.DataBind();
}
catch (Exception)
{
throw;
}
}
catch (Exception)
{
throw;
}
}
