Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET

Dynamically Populating a Microsoft Internet Explorer TreeView Web Control from a Database using SQL Server 2000 & XML Explicit

Rate me:
Please Sign up or sign in to vote.
4.68/5 (32 votes)
20 May 2003CPOL5 min read 263.1K   2K   85   47
How to build an Internet Exporer TreeView Web Controls node list dynamically using SQLXML and the Explicit mode

Introduction

Microsoft has made available the Internet Explorer Web controls which are not included in the framework by default. The assembly includes several controls one of which is the TreeView control which is discussed in this article. I will not be discussing how to use the control other than dynamically loading it with nodes using SQL Server's “Explicit mode”, and having each of the nodes' NavigateUrl attribute set to call a JavaScript function. I shall also not be going into any depth of discussion on SQLXML, as it is way beyond the scope of the article. I do however provide links to some further information on the topic. I shall however discuss SQLXML in a bit more depth in a future article “DataBinding with SQLXML”. See the Microsoft documentation for further programming of the TreeView control.

Where to Find the IE Web Controls Download

Whilst it can be very handy to bind an XML document to the TreeView control, I often face the requirement to do this on the fly, to accommodate dynamic changes to the TreeView's underlying data structure as required by the most current business model. One way to achieve this dynamic output is to use SQL Server's built in XML support in combination with the TreeNodeSrc attribute of the TreeView Control. For example, recently I needed to provide a known and commonly used hierarchical diagnosis path for help desk operators to select whilst servicing a customer. The output of this looked something like this:

TreeView

Writing the Query to Return XML

SQL Server 2000 provides excellent XML support, providing XML output from SQL Queries using ‘Modes’ such as explicit, Auto and Raw. Our example requires the use of the explicit mode to produce a format which MS calls the universal table, which will give us the output we require. For more on returning XML from TSQL queries see: SQL Server Books on Line index, keyword XML, EXPLICIT, or:

I chose to represent the data recursively in the database using a single reflexive table to store the hierarchies. Each row fldDiagnosisParentId references another row fldDiagnosisId except for those rows where fldTierNumber is equal to a value of 1. Run the attached download SQLXML file to produce this table and fill it with data.

Sample screenshot

Run the attached download SQLXML file to produce this table and fill it with data.

Sample screenshot

Next, we need the query to produce the output desired.

SQL
create procedure spReflexiveNodesText
                as
select
                1 as Tag,
                0 as Parent,
                null as [TREENODES!1!text],
                null as [treenode!2!text],
                null as [treenode!2!NavigateUrl],
                null as [treenode!3!text],
                null as [treenode!3!NavigateUrl],
                null as [treenode!4!text],
                null as [treenode!4!NavigateUrl]
union 
                all
select
                2 as Tag,
                1 as Parent,
                null,
                fldDiagnosisLabel as [treenode!2!text],
                'Javascript:FillBox("' + 
                     cast(fldDiagnosisId as varchar(10)) + '")'
                     as [treenode!2!NavigateUrl],
                null as [treenode!3!text],
                null as [treenode!3!NavigateUrl],
                null as [treenode!4!text],
                null as [treenode!4!NavigateUrl]
from
                tblDiagnosis
where
                fldTierNumber = 1
union
                all
select
                3 as Tag,
                2 as Parent,
                null,
                t1.fldDiagnosisLabel,
'Javascript:FillBox("' + cast(t1.fldDiagnosisId as varchar(10)) + '")',
                t2.fldDiagnosisLabel as [treenode!3!text],
                'Javascript:FillBox("' + 
                cast(t2.fldDiagnosisId as varchar(10)) + '")'
                as [treenode!3!NavigateUrl],
                null,
                null
from
                tblDiagnosis as t1
inner join
                tblDiagnosis as t2
on
                t1.fldDiagnosisId = t2.fldDiagnosisParentId
where
                t1.fldTierNumber = 1
and
                t2.fldTierNumber = 2
union
                all
select
                4 as Tag,
                3 as Parent,
                null,
                t1.fldDiagnosisLabel,
'Javascript:FillBox("' + cast(t1.fldDiagnosisId as varchar(10)) + '")',
                t2.fldDiagnosisLabel,
'Javascript:FillBox("' + cast(t2.fldDiagnosisId as varchar(10)) + '")',
                t3.fldDiagnosisLabel as [treenode!4!text],
                'Javascript:FillBox("' + 
                         cast(t3.fldDiagnosisId as varchar(10)) + '")'
                         as [treenode!4!NavigateUrl]
from
                tblDiagnosis as t1
inner join
                tblDiagnosis as t2
on
                t1.fldDiagnosisId = t2.fldDiagnosisParentId
inner join
                tblDiagnosis as t3
on
                t2.fldDiagnosisId = t3.fldDiagnosisParentId
where
                t1.fldTierNumber = 1
and
                t2.fldTierNumber = 2
and
                t3.fldTierNumber = 3
order by
                [TREENODES!1!text],
                [treenode!2!text],
                [treenode!3!text],
                [treenode!4!text]
for 
                xml explicit

If you run this query in query analyzer and remove the for xml explicit directive, you will get what MS calls a universal table output (which can be handy for debugging). If you run the query with the for xml explicit directive, the output will be a long XML string like that listed below. The output listed highlights the fact that our query in this instance is producing the required XML structure required by the TreeView control and has taken advantage of the treenode elements NavigateUrl attribute which will call a JavaScript function in the page containing the TreeView.

XML
<TREENODES>
<treenode text="Modem" NavigateUrl="Javascript:FillBox("3")">
<treenode text="Cable" NavigateUrl="Javascript:FillBox("7")">
<treenode text="Broken" NavigateUrl="Javascript:FillBox("11")"/>
</treenode>
</treenode>
<treenode text="Monitor" NavigateUrl="Javascript:FillBox("4")">
<treenode text="Fuzzy" NavigateUrl="Javascript:FillBox("8")">
<treenode text="Driver" NavigateUrl="Javascript:FillBox("12")"/>
</treenode>
</treenode>
<treenode text="PC" NavigateUrl="Javascript:FillBox("1")">
<treenode text="Ram" NavigateUrl="Javascript:FillBox("5")">
<treenode text="Faulty" NavigateUrl="Javascript:FillBox("9")"/>
</treenode>
</treenode>
<treenode text="Printer" NavigateUrl="Javascript:FillBox("2")">
<treenode text="Cartridges" NavigateUrl="Javascript:FillBox("6")">
<treenode text="Empty" NavigateUrl="Javascript:FillBox("10")"/>
</treenode></treenode>
</TREENODES>

Note: There are as many self joins required as there are tier levels, which is not exactly dynamic, meaning that if you add a tier to the data in the table, then you need to change the query. This can be overcome by first discovering the max() tier value found in the table and building a SQL statement up in a string and then executing the statement contained therein using the sp_executesql system stored procedure. The first select statement in the Union query is required to give us the root node “TREENODES” which is required by the TreeView Control. The text attributes of each treenode element is what provides each of the TreeView node's individual display labels and the NaviateUrl is commonly used to navigate to another HTTP location or in this instance passing control to a JavaScript function to produce some further processing.

For more information on XML modes available in SQL Server 2000, check out this link. This link provides a sample chapter of the book “Programming Microsoft® SQL Server™ 2000 with XML” by Graeme Malcolm and published by the MS press.

Setting Up SQL Server for Access through a URL

The next step in making the query work for us is setting up a SQL Server 2000 virtual directory that will allow us to call the stored procedure using a URL string. When SQL Server is setup, it should configure a menu item in its start menu folder labeled “Configure SQL XML support in IIS”.

IIS Virtual Directory

Once this is complete, you will be able to access the stored procedure from Internet Explorer using the URL: http://localhost/TreeSqlXml?sql=execute+spReflexiveNodesText.

For more information on how to set up the virtual directory, see: SQL Server Books or try MSDN website.

For more on how to set up SQL Server for querying a database with a URL string see: Books on Line and use index keyword ‘XML, executing stored procedures at URL’ or try MSDN website.

The ASPX page's HTML code.

HTML
<!DOCTYPEHTMLPUBLIC"-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>ReflexiveTreeNodes</title>
<metaname="GENERATOR"Content="Microsoft Visual Studio 7.0">
<metaname="CODE_LANGUAGE"Content="C#">
<metaname="vs_defaultClientScript" content="JavaScript">
<metaname="vs_targetSchema" 
    content="http://schemas.microsoft.com/intellisense/ie5">
<scriptlanguage="javascript">
<!--
/* The Function specified in the output
   of the stored procedure 
   SEE the NavigateUrl attribute
   Of each treenode element  */
function FillBox(val)
       {
              alert(val);
       }
//-->
</script>
</HEAD>
<bodyMS_POSITIONING="GridLayout">
       <formid="ReflexiveTreeNodes" method="post" runat="server">
       <!--Note the URL string used as the value of the TreeNodeSrc -->
       <iewc:TreeViewid="TreeView1"
       style="Z-INDEX: 101; LEFT: 6px; POSITION: absolute; TOP: 8px"
runat="server" 
TreeNodeSrc="http://localhost/TreeSqlXml?sql=execute+spReflexiveNodesText">
</iewc:TreeView>
       </form>
</body>
</HTML>

NOTE: Remember to reference the Microsoft.Web.UI.WebControls.dll in your project and include the URL to the stored procedure as the value for your TreeView tag's TreeNodeSrc attribute.

Caching

Choose the most appropriate caching policy for your page based on the expected frequency of change to underlying database table and whether or not any parameters are passed from the application to the stored procedure. Bear in mind that the stored procedure will fetch data from the database or cache, dependant on whether the request for data is already cached.

Finally

It is also worth mentioning that the TreeView control can also be populated by passing the fully qualified path of an XML file to the TreeNodeSrc attribute.

HTML
TreeNodeSrc="myTreeViewFile.xml"

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Chief Technology Officer
Australia Australia
Simon Segal resides in Melbourne Australia, is a certified MCAD, MCSD, MCDBA, MCSE, MCST BizTalk Specialist and has been working in the Software Development industry for some 10 years now. His key area of interest are distributed systems / SOA built with Microsoft technologies.

Comments and Discussions

 
GeneralRe: A Error Occur When Using Sql Server Query.. Pin
Simon Segal19-Jul-03 14:52
Simon Segal19-Jul-03 14:52 
GeneralRe: A Error Occur When Using Sql Server Query.. Pin
kaya2003@163.com20-Jul-03 18:03
kaya2003@163.com20-Jul-03 18:03 
GeneralRe: A Error Occur When Using Sql Server Query.. Pin
Abdul Salam Kunnummal29-Aug-03 4:12
Abdul Salam Kunnummal29-Aug-03 4:12 
GeneralRe: A Error Occur When Using Sql Server Query.. Pin
Abdul Salam Kunnummal29-Aug-03 4:35
Abdul Salam Kunnummal29-Aug-03 4:35 
GeneralRe: A Error Occur When Using Sql Server Query.. Pin
fl00r5-Oct-03 4:38
fl00r5-Oct-03 4:38 
GeneralRe: A Error Occur When Using Sql Server Query.. Pin
DQ.Tang27-Jul-03 2:47
DQ.Tang27-Jul-03 2:47 
GeneralRecursion Pin
Member 76865722-May-03 13:51
Member 76865722-May-03 13:51 
GeneralRe: Recursion Pin
Heath Stewart9-Jul-03 10:34
protectorHeath Stewart9-Jul-03 10:34 
Using recursive stored procs is also possible (just another alternative).

 

Reminiscent of my younger years...
10 LOAD "SCISSORS"
20 RUN

GeneralI Like It Pin
Richard Johnn22-May-03 8:41
Richard Johnn22-May-03 8:41 
GeneralRe: I Like It Pin
Anonymous28-Nov-03 4:37
Anonymous28-Nov-03 4:37 
Generallink to the .NET web control doesnt work Pin
Member 1248511320-May-03 3:05
Member 1248511320-May-03 3:05 
GeneralRe: link to the .NET web control doesnt work Pin
Anonymous20-May-03 13:15
Anonymous20-May-03 13:15 
GeneralRe: link to the .NET web control doesnt work Pin
Simon Segal26-May-03 20:14
Simon Segal26-May-03 20:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.