Creation of XML for Microsoft Tree View Control from SQL Server 2000 Database

Introduction

Tree is used for hierarchical display of data, and permits you to create effective user interfaces while easing development and maintenance time. You can effectively add any widget to the Tree. The scrollbars appear automatically as needed.

Install Microsoft Web Control

For use with ASP.NET, Microsoft has a small set of tools called Internet Explorer Web Controls, which include the Multiage, Tab Strip, Toolbar, and Tree View controls.

First, you have to go here to get the "iewebcontrols.msi" file. When you install it (by double clicking the downloaded file) on your web server, you get a folder on your default web (webctrl_client) and a set of components (Microsoft.Web.UI.WebControls). One step remains for you to be in business, and that is to add a reference to this excellent suite of controls in your Visual Studio 7.0 project. Just go to Project --> Add Reference, and look for the item called Microsoft IE Web Controls under the .NET tab.

Deployment

First, we need to let our page know about our new control. Put the following lines of code right below the @Page directive of your .aspx page -

<%@ Import namespace="Microsoft.Web.UI.WebControls" %>
<%@ Register TagPrefix="iewc" Namespace="Microsoft.Web.UI.WebControls" 
                              Assembly ="Microsoft.Web.UI.WebControls"%>

To start describing below example – Viewer to run the SQL script in SQL analyzer

In Web.Config file, you have to define your own connection string –

<appSettings>
    <add key="strConn" 
      value="workstation id='demo';packet size=4096;
             integrated security=SSPI;data source='demo\demo';
             persist security info=True;initial catalog=demo">
    </add>
</appSettings>

CREATE TABLE [XMLData] (
  [FuncId] [int] NOT NULL ,
  [FuncName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [FuncLId] [int] NULL ,
  [FuncLevName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [FuncLId1] [int] NULL ,
  [FuncLev1Name] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [FuncLId2] [int] NULL ,
  [FuncLev2Name] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [FuncLId3] [int] NULL ,
  [FuncLev3Name] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
 
INSERT INTO XMLData (FuncId, FuncName, FuncLId, FuncLevName, 
       FuncLId1, FuncLev1Name, FuncLId2, FuncLev2Name, 
       FuncLId3, FuncLev3Name) VALUES(1, 'Production', 1, 
       Solution Architect, 1, Project Manager, 1, 
       Project Leader, 1, Programmer)
INSERT INTO XMLData (FuncId, FuncName, FuncLId, 
       FuncLevName, FuncLId1, FuncLev1Name, FuncLId2, 
       FuncLev2Name, FuncLId3, FuncLev3Name) VALUES 
       (1, Production, 1, Solution Architect, 1, 
       Project Manager, 1, Project Leader, 2, Designer)
INSERT INTO XMLData (FuncId, FuncName, FuncLId, FuncLevName, 
       FuncLId1, FuncLev1Name, FuncLId2, FuncLev2Name, 
       FuncLId3, FuncLev3Name) VALUES (2, Marketing & Sales, 2, 
       Business Development Manager, 2, Area Manager, 2, 
       Area Sales Manager, 3, Sales Man)
INSERT INTO XMLData (FuncId, FuncName, FuncLId, FuncLevName, 
       FuncLId1, FuncLev1Name, FuncLId2, FuncLev2Name, 
       FuncLId3, FuncLev3Name) VALUES (2, Marketing & Sales, 2, 
       Business Development Manager, 2, Area Manager, 2, 
       Area Sales Manager, 4, Sales Women)

How to bind XML file with TreeView Control

<iewc:treeview id="tvwVenire" runat="server" 
                     AutoSelect="True" SelectExpands="True">
    <iewc:TreeNode TreeNodeSrc="xmlfile.xml" 
                          Text="DEMO TREE" Expanded="True"/>
</iewc:treeview>

How it works

Given below procedure picks the data on selected query from SQL Server and generates an XML file for Microsoft Tree Control.

XmlTextWriter promotes the namespace declaration to the root element to avoid having it duplicated on the two child elements.

Moreover, it ensures that the <?xml declaration comes first and that the <!DOCTYPE node comes before the root element. It ensures that value and format of the xml:space attribute is correct and makes sure that its value is acceptable according to the Extensible Markup Language (XML) 1.0 (Second Edition) recommendation. Finally, XmlTextWriter checks when a string is used as a parameter, for example, Null==String.Empty and String.Empty, and whether it follows the W3C rules.

There are three settings you can use to change the way your documents are indented. To start with, we'll use the XmlTextWriter class. This class contains a set of properties called Formatting and Indentation.

Use the Formatting property to set the indentation of the XML document or to determine whether it will be indented. The parser will automatically embed indents into your XML document to make it easier to view in text-oriented viewers.

Methods in the XmlWriter also come in pairs; the WriteStartDocument and WriteEndDocument, WriteStartElement and WriteEndElement, and the WriteStartAttribute and WriteEndAttribute method pairs. Using these methods you can create nested elements or attributes. It is with these method pairs that an XML document is built, and it allows the creation of complex elements or attributes without making errors.

Generate XML File Procedure (xmlfile.cs)

public class GenXML : System.Web.UI.Page
{
  private void btnSubmit_Click(object sender, System.EventArgs e)
  {
    string strConn = 
      System.Configuration.ConfigurationSettings.AppSettings ["strConn"];
    SqlConnection sqlconn = new SqlConnection(strConn);
    SqlCommand sqlcomm = new SqlCommand("SELECT * FROM XMLData",sqlconn);
    SqlDataReader dr;
    sqlconn.Open();

    //Read data into DataReader as dr
    dr=sqlcomm.ExecuteReader();
    string xmlDoc= Server.MapPath("..\\xmlfile.xml");
    XmlTextWriter writer = null;

    //Create instance of XmlTextWriter as a tool 
    //for writing our XML information
    writer = new XmlTextWriter(xmlDoc,Encoding.UTF8);

    // Indent the XML file to look nice
    writer.Formatting = Formatting.Indented;

    // Write the beginning header of our XML file along with a TREENODES tag
    writer.WriteStartDocument(true);
    writer.WriteStartElement("TREENODES");

    string lv,lv1,lv2,lv3,lv4;
    int flag1=0,flag2=0,flag3=0,flag4=0,flag5=0;
    dr.Read();
    lv=dr["FuncId"].ToString();
    lv1=dr["FuncLId"].ToString();
    lv2=dr["FuncLId1"].ToString();
    lv3=dr["FuncLId2"].ToString();
    lv4=dr["FuncLId3"].ToString();

    do
    {
      if (lv4!=dr["FuncLId3"].ToString() && lv4.Length!=0)
      {
        // write end TREE attributes
        writer.WriteEndElement();
      }
      if (lv3!=dr["FuncLId2"].ToString() && lv3.Length!=0)
      {
        writer.WriteEndElement();
      }
      if (lv2!=dr["FuncLId1"].ToString() && lv2.Length!=0)
      {
        writer.WriteEndElement();
      }
      if (lv1!=dr["FuncLId"].ToString() && lv1.Length!=0)
      {
        writer.WriteEndElement();
      }
      if (lv!=dr["FuncId"].ToString()  && lv.Length!=0)
      {
        writer.WriteEndElement();
      }
      for (int i=1; i<6; i++)
      {
        if (i==1 && lv1!=null)
        {
          if (lv!=dr["FuncId"].ToString())
          {
            lv=dr["FuncId"].ToString();
            flag1=0;
          }
          if(flag1==0 && lv==dr["FuncId"].ToString() && lv.Length!=0)
          {
            // write TREE attributes TAG names
            writer.WriteStartElement("TreeNode");
            writer.WriteAttributeString ("Text",dr["FuncName"].ToString());
            flag1=1;
          }
        }
        else
        {
          if(i==2 && lv1!=null)
          {
            if (lv1!=dr["FuncLId"].ToString())
            {
              lv1=dr["FuncLId"].ToString();
              flag2=0;
            }
            if(flag2==0 && lv1==dr["FuncLId"].ToString() && lv1.Length!=0)
            {
              writer.WriteStartElement("TreeNode");
              writer.WriteAttributeString ("Text", 
                     dr["FuncLevName"].ToString());
              flag2=1;
            }
          }
          else
          {
            if(i==3 && lv2!=null)
            {
              if (lv2!=dr["FuncLId1"].ToString())
              {
                lv2=dr["FuncLId1"].ToString();
                flag3=0;
              }
              if(flag3==0 && lv2==dr["FuncLId1"].ToString() &&  lv2.Length!=0)
              {
                writer.WriteStartElement("TreeNode");
                writer.WriteAttributeString ("Text", 
                      dr["FuncLev1Name"].ToString());
                flag3=1;
              }
            }
            else
            {
              if(i==4 && lv3!=null)
              {
                if (lv3!=dr["FuncLId2"].ToString())
                {
                  lv3=dr["FuncLId2"].ToString();
                  flag4=0;
                }
                if(flag4==0 && lv3==dr["FuncLId2"].ToString() && lv3.Length!=0)
                {
                  writer.WriteStartElement("TreeNode");
                  writer.WriteAttributeString ("Text", 
                        dr["FuncLev2Name"].ToString());
                  flag4=1;
                }
              }
              else
              {
                if(i==5 && lv4!=null)
                {
                  if (lv4!=dr["FuncLId3"].ToString())
                  {
                    lv4=dr["FuncLId3"].ToString();
                    flag5=0;
                  }
                  if(flag5==0 && 
                     lv4==dr["FuncLId3"].ToString() 
                     && lv4.Length!=0)
                  {
                    writer.WriteStartElement("TreeNode");
                    writer.WriteAttributeString ("Text", 
                          dr["FuncLev3Name"].ToString());
                    flag5=1;
                  }
                }
              }
            }
          }
        }
      }
    }while (dr.Read());
    writer.WriteEndDocument();
    writer.Flush();
    writer.Close();
    dr.Close();
  }
}

Format of XML File (xmlfile.xml)

<?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
<TREENODES>
  <TreeNode Text="Production">
     <TreeNode Text="Solution Architect">
        <TreeNode Text="Project Manager">
          <TreeNode Text="Project Leader">
             <TreeNode Text="Programmer"/> 
             <TreeNode Text="Designer"/> 
          </TreeNode>
        </TreeNode>
      </TreeNode>
  </TreeNode>
  <TreeNode Text="Marketing & Sales">
     <TreeNode Text="Business Development Manager">
       <TreeNode Text="Area Manager">
         <TreeNode Text="Area Sales Manager">
           <TreeNode Text="Sales Man"/>
           <TreeNode Text="Sales Women"/>
         </TreeNode>
       </TreeNode>
     </TreeNode>
  </TreeNode>
</TREENODES>

The Base Control

The control that will be used for this article –

<%@ import namespace="Microsoft.Web.UI.WebControls" %>
<%@ import Namespace="System.Web.UI.WebControls.WebControl" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.XML" %>
<%@ import Namespace="System.Text" %>