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:
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.
Run the attached download SQLXML file to produce this table and fill it with data.
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�.
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">
<!---->
</script>
</HEAD>
<bodyMS_POSITIONING="GridLayout">
<formid="ReflexiveTreeNodes" method="post" runat="server">
-->
<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"
