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

• Download source files - 1.44 Kb

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

• MSDN website

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:

• Microsft Press Website and
• MSDN Website

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">
<!--
/* 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"