The goal of this article is to provide a simple, easy and interesting solution for displaying data with multi level master/detail relationship using nested Gridviews.
Consider a scenario where we need to display master/detail relationships for multi level hierarchal data. E.g., displaying Terms, definitions and their related articles in the form of a glossary, like the screen shot below:
This is a 2-level master/detail relationship. The first level shows Term, definition and its approval status (in Parent Gridview). The second level shows list of articles corresponding to that term (in a Child Gridview).
The Database Tables:
Create the following tables in SQL Server 2000/2005 and insert few records into them.
* Glossary_Term table (which contains all terms and their definitions):
|
CREATE TABLE [dbo].[Glossary_Term]
(
[Glossary_Term_Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Definition] [varchar](500) NOT NULL,
CONSTRAINT
[Glossary_Term_Glossary_Term_Id_PK] PRIMARY KEY
(
[Glossary_Term_Id] ASC
)
)
GO
|
- Article table (which contains all articles):
|
CREATE TABLE [dbo].[Article]
(
[Article_Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50)NOT NULL,
CONSTRAINT
[Article_Article_Id_PK] PRIMARY KEY
(
[Article_Id] ASC
)
)
GO
|
- Related Article table (which links terms and articles):
|
CREATE TABLE [dbo].[Related_Article]
(
[Related_Article_Id] [int] IDENTITY(1,1) NOT NULL,
[Glossary_Term_Id] [int] NOT NULL,
[Article_Id] [int] NOT NULL,
CONSTRAINT
[Related_Article_Related_Article_Id_PK] PRIMARY
KEY
(
[Related_Article_Id] ASC
)
)
GO
|
Using the Code:
|
<%--***** BEGIN - Code for Parent GridView *****--%>
<asp:GridView ID="gvTerm" runat="server" GridLines="None"
AutoGenerateColumns="False" ShowHeader="False">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblTerm"
runat="server"
Text='<%#
Eval("Name") %>'></asp:Label>
<asp:Label ID="lblDefinition"
runat="server"
Text='<%#
Eval("Definition") %>'></asp:Label>
<asp:Label ID="lblRelatedArticles"
runat="server"
Text="Related
Articles:">
</asp:Label>
<%--***** BEGIN - Code for Child GridView ******--%>
<asp:GridView ID="gvArticles" runat="server" GridLines="None"
AutoGenerateColumns="False" ShowHeader="False">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:HyperLink ID="hlArticleName"
Text='<%#
Eval("Name") %>'
NavigateUrl='<%#
Eval("URL") %>' Target="_blank" runat="server">
</asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<%--***** END - Code for Child GridView *****--%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
<asp:Label ID="lblNoRecords"
runat="server"
ForeColor="Red"
Text="No
Records Found"></asp:Label>
</EmptyDataTemplate>
</asp:GridView>
<%--***** END - Code for Parent GridView *****--%>
|
Parent Gridview (gvTerm):
The parent gridview shows all terms and their definitions fetched from glossary_term table. The select statement used in the stored procedure used for this would be like:
|
SELECT Glossary_Term_Id, [Name], Definition FROM Glossary_Term
|
In the code behind, the parent gridview is data bound by calling the below “ViewGlossaryTerms()” method in page load:
|
Public Sub ViewGlossaryTerms()
Dim
objBOGlossary As New
BOGlossary
Dim
dsGlossaryTerms As New
DataSet
Try
'Call
ViewGlossaryTerms method BOGlossary class.
objBOGlossary.ViewGlossaryTerms(dsGlossaryTerms)
'Set
the datasource of the gridview gvTerm to the dataset returned by the above method.
gvTerm.DataSource =
dsGlossaryTerms
'Databind
the gridview
gvTerm.DataBind()
Catch
objException As Exception
HandleException(objException,
GENERAL_CONST_EXCEPTION_UIPOLICY, DP)
DisplayError()
Finally
objBOGlossary = Nothing
End Try
End Sub
|
Note: The objBOGlossary.ViewGlossaryTerms(dsGlossaryTerms) method calls a DA method which executes a stored procedure that has the above mentioned SQL SELECT statement. (3-Tier architechture)
The selected columns are data bound to the controls using the keyword “Eval” in aspx as shown:
Child Gridview (gvArticles):
The child gridview (“gvArticles”) shows all articles fetched from article table for a particular term. The select statement used in the stored procedure used for this would be like:
SELECT
|
Article.Article_Id,
RA.Related_Article_Id,
[NAME],URL
FROM
dbo.Article as Article,
dbo.Related_Article
RA
WHERE
Glossary_Term_Id=
@pi_iGlossary_Term_Id
AND Article.Article_Id=RA.Article_Id
|
Notice that the above SELECT statement expects an input parameter “@pi_iGlossary_Term_Id”, which is essentially the Glossary_Term_Id of a term in parent Gridview. We can use the RowDataBound event of parent gridview to get the Glossary_Term_Id of the term that has been data bound.
The inline comments in the code below provide a detailed description of how this is handled.
|
Protected Sub
gvTerm_RowDataBound(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.GridViewRowEventArgs) Handles
gvTerm.RowDataBound
'Declaring
variables
Dim
iGlossaryTermId As Integer
Dim
drvSelectedRow As DataRowView
Dim
objBOGlossary As New
BOGlossary
Dim
dsGlossaryArticles As New DataSet
Try
'If the
rowtype of the particular row is datarow then,
If
(e.Row.RowType = DataControlRowType.DataRow) Then
Dim
gvArticles As New
GridView
Find
the control with the name "gvArticles" from the current row of
gvTerms.
'TypeCast
it to type GridView and store it in the new gridview 'gvArticles'.
gvArticles = CType(e.Row.FindControl(“gvArticles”),
GridView)
'Retrieve
the underlying data item. Here, the underlying data item is a DataRowView
object.
drvSelectedRow = CType(e.Row.DataItem, DataRowView)
'Retrieve
the "Glossary_Term_Id" value for the current row.
iGlossaryTermId = CType(drvSelectedRow(“Glossary_Term_Id”),
Integer)
'Call
the ViewGlossaryArticles method of BOGlossary class.
objBOGlossary.ViewGlossaryArticles(iGlossaryTermId,
dsGlossaryArticles)
'Set
the datasource of gvArticles to the dataset fetched by the above method.
gvArticles.DataSource =
dsGlossaryArticles
'Databind
the gridview
gvArticles.DataBind()
End If
Catch objException As Exception
HandleException(objException,
GENERAL_CONST_EXCEPTION_UIPOLICY, DP)
DisplayError()
Finally
objBOGlossary = Nothing
End Try
End Sub
|
The RowDataBound event handler fires as each DataRow is data bound. This event simulates the presence of some sort of loop. i.e., for each glossary term in the DataRow of parent gridview, the child gridview is found and data bound.
In this way, nesting of Gridviews can be done for any number of levels by finding the child control in RowDataBound event and assigning it the required primary key.
Is nesting Gridviews the only solution?
Definitely not. The scenario described in this article can be handled by generating HTML dynamically in code behind. This is done by looping through all the records of dataset, building a string builder having data bound asp controls and repeating this code in the loop. This is comparatively a tedious and complicated task. So nesting Gridviews is the optimized solution.
Tags: