Click here to Skip to main content
Click here to Skip to main content

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

, 20 May 2003 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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.

<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 info 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.

<!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. Bare 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.

TreeNodeSrc="myTreeViewFile.xml"

License

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

Share

About the Author

Simon Segal
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

 
GeneralCopying a tree view into gmail compose box Pinmemberrockyurock27-Feb-10 8:20 
GeneralFind me here.... PinmemberSimon Segal20-Jun-08 22:40 
GeneralOpensource PHP DBTreeView PinmemberRodolphe Cardon20-Jul-07 11:08 
QuestionWhat about n-tier relationship PinmemberHirenjPatel12-Oct-06 1:05 
GeneralGot error message ! PinmemberKhinLLK15-Aug-06 22:49 
GeneralMicrosoft.Web.UI.WebControls.dll PinmemberAmir K15-May-06 13:27 
GeneralRe: Microsoft.Web.UI.WebControls.dll PinmemberSimon Segal5-May-06 13:52 
GeneralRe: Microsoft.Web.UI.WebControls.dll PinmemberAmir K15-May-06 21:18 
GeneralRe: Microsoft.Web.UI.WebControls.dll PinmemberSimon Segal6-May-06 2:47 
Questionhow to find offsetX and offsetX of treenode PinsussAnonymous12-Oct-05 7:26 
Hello,
 
I need to edit text in tree node like editlabel in window form. I try to use java script to show input text at the position of treenode. and when user input data, I will set the input string to treenode.text but, I can't find the X and Y position.
Can you help me.
 
Suthep.
 

QuestionUpdating the tree back into database PinmemberManjesh123422-Sep-05 19:20 
AnswerRe: Updating the tree back into database PinsussAnonymous23-Sep-05 2:32 
GeneralRe: Updating the tree back into database Pinmembermanjesh12323-Sep-05 15:09 
GeneralNew Download Location for Web Control Download PinmemberSimon Segal8-Jun-04 22:51 
GeneralIE TreeView Control Pinmemberchintan19791-Jun-04 19:00 
GeneralRe: IE TreeView Control PinmemberSimon Segal8-Jun-04 22:49 
QuestionHow can I get rid of the backgroud colour of the first node? Pinmemberpllms13-May-04 16:48 
QuestionCan I use TreeView Web Control in Dreamweaver? Pinmemberpllms13-May-04 0:09 
AnswerRe: Can I use TreeView Web Control in Dreamweaver? PinmemberSimon Segal13-May-04 0:17 
GeneralRe: Can I use TreeView Web Control in Dreamweaver? Pinmemberravijikijai1-Feb-06 4:16 
GeneralAdding data to treeview in Windows Forms Pinmembersasijrao14-Apr-04 5:12 
GeneralSetting a node's tag property PinmemberMSDevMan25-Mar-04 8:30 
GeneralRe: Setting a node's tag property PinmemberSimon Segal25-Mar-04 13:43 
GeneralRe: Setting a node's tag property PinmemberMSDevMan26-Mar-04 10:51 
GeneralRe: Setting a node's tag property PinmemberSimon Segal31-Mar-04 0:43 
QuestionHow to Dynamic create the query depend on tree tier? PinmemberRouBao14-Feb-04 18:32 
AnswerRe: How to Dynamic create the query depend on tree tier? PinmemberSimon Segal14-Feb-04 23:04 
Questionwithout the Web TreeView Control? PinmemberRouBao11-Feb-04 15:29 
GeneralNo DLL on MS Website PinmemberMikeRoad17-Aug-03 13:24 
GeneralRe: No DLL on MS Website PinmemberSimon Segal17-Aug-03 15:48 
GeneralRe: No DLL on MS Website PinmemberMikeRoad17-Aug-03 16:25 
GeneralRe: No DLL on MS Website Pinmemberchintan19791-Jun-04 22:54 
GeneralRe: No DLL on MS Website PinsussAnonymous2-Jun-04 1:04 
GeneralA Error Occur When Using Sql Server Query.. Pinsusskaya200319-Jul-03 4:46 
GeneralRe: A Error Occur When Using Sql Server Query.. PinmemberSimon Segal19-Jul-03 15:52 
GeneralRe: A Error Occur When Using Sql Server Query.. Pinmemberkaya2003@163.com20-Jul-03 19:03 
GeneralRe: A Error Occur When Using Sql Server Query.. PinmemberAbdul Salam Kunnummal29-Aug-03 5:12 
GeneralRe: A Error Occur When Using Sql Server Query.. PinmemberAbdul Salam Kunnummal29-Aug-03 5:35 
GeneralRe: A Error Occur When Using Sql Server Query.. Pinmemberfl00r5-Oct-03 5:38 
GeneralRe: A Error Occur When Using Sql Server Query.. PinsussDQ.Tang27-Jul-03 3:47 
GeneralRecursion PinsussAndrew Wall22-May-03 14:51 
GeneralRe: Recursion PineditorHeath Stewart9-Jul-03 11:34 
GeneralI Like It PinmemberRichard A. Johnn22-May-03 9:41 
GeneralRe: I Like It PinsussAnonymous28-Nov-03 5:37 
Generallink to the .NET web control doesnt work PinsussShielsy20-May-03 4:05 
GeneralRe: link to the .NET web control doesnt work PinsussAnonymous20-May-03 14:15 
GeneralRe: link to the .NET web control doesnt work PinmemberSimon Segal26-May-03 21:14 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 21 May 2003
Article Copyright 2003 by Simon Segal
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid