KPBDOTNET: Nested Gridview

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">

<asp:TemplateField ItemStyle-Width="15%">

<asp:Label ID="lblID" runat="server" Text="ID"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataID" runat="server" Text='<%#Eval("ID") %>' ></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField ItemStyle-Width="15%">

<asp:Label ID="lblCITY_NAME" runat="server" Text="CITY

NAME"></asp:Label>

</HeaderTemplate>

<asp:LinkButton ID="lblDataCITY_NAME" runat="server"

Text='<%#Eval("CITY_NAME") %>' NavigateUrl="#" >

</asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField ItemStyle-Width="15%">

<asp:Label ID="lblTOTAL_INCOME" runat="server" Text="TOTAL INCOME"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataTOTAL_INCOME" runat="server" Text='<%#Eval("TOTAL_INCOME","{0:n2}") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField ItemStyle-Width="15%" >

<asp:Label ID="lblAVG_INCOME" runat="server" Text="AVG

INCOME"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataAVG_INCOME" runat="server"

Text='<%#Eval("AVG_INCOME","{0:n2}") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField ItemStyle-Width="15%">

<asp:Label ID="lblMAX_INCOME" runat="server" Text="MAX

INCOME"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataMAX_INCOME" runat="server"

Text='<%#Eval("MAX_INCOME","{0:n2}") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField ItemStyle-Width="20%">

<asp:Label ID="lblMIN_INCOME" runat="server" Text="MIN

INCOME"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataMIN_INCOME" runat="server" Text='<%#Eval

("MIN_INCOME","{0:n2}") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField >

<asp:TemplateField ItemStyle-Width="20%">

<asp:Label ID="lblGENDER_RATIO" runat="server" Text="GENDER RATIO

(%)"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataGENDER_RATIO" runat="server"

Text='<%#Eval("GENDER_RATIO","{0:n2}") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField ItemStyle-Width="0%">

<tr>

<asp:Panel ID="pnlGrid" runat="server">

<%--ID USERID FNAME LNAME GENDER ORG ROLE SALARY--%>

<asp:GridView ID="gridCityDetails" runat="server"

AutoGenerateColumns="false" Width="100%">

<asp:TemplateField>

<asp:Label ID="lblUSERID" runat="server" Text="USERID"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataUSERID" runat="server" Text='<%#Eval("USERID") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Label ID="lblFNAME" runat="server" Text="FNAME"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataFNAME" runat="server"

Text='<%#Eval("FNAME") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Label ID="lblLNAME" runat="server"

Text="LNAME"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataLNAME" runat="server"

Text='<%#Eval("LNAME") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Label ID="lblGENDER" runat="server"

Text="GENDER"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataGENDER" runat="server"

Text='<%#Eval("GENDER") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Label ID="lblORG" runat="server"

Text="ORG"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataORG" runat="server"

Text='<%#Eval("ORG") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Label ID="lblROLE" runat="server"

Text="ROLE"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataROLE" runat="server"

Text='<%#Eval("ROLE") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Label ID="lblSALARY" runat="server"

Text="SALARY"></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataSALARY" runat="server"

Text='<%#Eval("SALARY") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</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

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

--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)

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

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;

}

KPBDOTNET

Pages

Tuesday, December 2, 2014

Nested Gridview

No comments:

Post a Comment

Blog Archive