Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
 
Add your own
alternative version
Go to top

Tree utilities in SQL Server 2000 and 2005, and OLAP implementations

, 19 Jul 2006
This article describes how to efficiently store and access tree structures in a SQL Server database, and how to use them in OLAP implementations.
refreshsalescube.zip
RefreshSalesCube
App.ico
bin
Release
Interop.DSO.dll
Interop.MSOLAPADMINLib2.dll
Interop.POMInterfaces.dll
Interop.VBA.dll
RefreshSalesCube.exe
RefreshSalesCube.csproj.user
sales.zip
sqlscripts.zip
tree_util.zip
tree_util
bin
tree_util.dll
Global.asax
tree.xsl
tree_util.csproj.webinfo
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.IO;
using System.Net;
using System.Data.SqlClient;

namespace tree_util
{
	/// <summary>
	/// Summary description for index.
	/// </summary>
	public class index : System.Web.Services.WebService
	{
		public index()
		{
			//CODEGEN: This call is required by the ASP.NET Web Services Designer
			InitializeComponent();

		}

		#region Component Designer generated code
		
		//Required by the Web Services Designer 
		private IContainer components = null;
				
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if(disposing && components != null)
			{
				components.Dispose();
			}
			base.Dispose(disposing);		
		}
		
		#endregion


		[WebMethod]
		public XmlDocument GetDirectTree(int RootID)
		{
			XmlDocument xmldoc = new XmlDocument();
			string strXML;
			try
			{
				SqlConnection cnn = new SqlConnection(Config.ConnectionString);
				cnn.Open();
				SqlCommand cmd = new SqlCommand("TREE_GET_XML", cnn);
				cmd.CommandType = CommandType.StoredProcedure;
				SqlParameter param = new SqlParameter("@ROOT_ID", typeof(int));
				param.Value = RootID;
				cmd.Parameters.Add(param);
				XmlReader rdr = cmd.ExecuteXmlReader();
				rdr.Read();
				strXML = rdr.ReadOuterXml();
				if(strXML != string.Empty)
					xmldoc.LoadXml(strXML);
				else
					xmldoc.LoadXml("<root></root>");
			}
			catch(Exception ex)
			{
				strXML = "<errors>";
				while(ex != null)
				{
					strXML += "<error>";
					strXML += ex.Message;
					strXML += "</error>";
					ex = ex.InnerException;
				}
				strXML += "</errors>";
				xmldoc.LoadXml(strXML);
			}
			return xmldoc;
		}

		[WebMethod]
		public XmlDocument GetStructuredTree(int RootID)
		{
			XmlDocument xmldoc = new XmlDocument();
			string strXML;
			try
			{
				xmldoc = GetDirectTree(RootID);
				strXML = Transform(xmldoc);
				xmldoc.LoadXml(strXML);
			}
			catch(Exception ex)
			{
				strXML = "<errors>";
				while(ex != null)
				{
					strXML += "<error>";
					strXML += ex.Message;
					strXML += "</error>";
					ex = ex.InnerException;
				}
				strXML += "</errors>";
				xmldoc.LoadXml(strXML);
			}
			return xmldoc;
		}

		private string Transform(XmlDocument xmldoc)
		{
			string strXML = string.Empty;
			try
			{
				string xslTemplatePath = Server.MapPath("") + "\\tree.xsl";
				XPathNavigator nav = xmldoc.DocumentElement.CreateNavigator();

				XmlDocument xsldoc = new XmlDocument();
				xsldoc.Load(xslTemplatePath);
				XmlNamespaceManager nsmgr = new XmlNamespaceManager(xsldoc.NameTable);
				nsmgr.AddNamespace("xsl", "http://www.w3.org/1999/XSL/Transform");

				XmlUrlResolver resolver = new XmlUrlResolver();
				XslTransform trans = new XslTransform();
				trans.Load(xsldoc, resolver, this.GetType().Assembly.Evidence);

				TextWriter writer = new StringWriter();

				XmlTextWriter xmlWriter = new XmlTextWriter(writer);
				xmlWriter.Formatting = Formatting.Indented;

				trans.Transform(nav, null, xmlWriter, null);

				strXML = writer.ToString();
			}
			catch(Exception ex)
			{
				strXML = "<errors>";
				while(ex != null)
				{
					strXML += "<error>";
					strXML += ex.Message;
					strXML += "</error>";
					ex = ex.InnerException;
				}
				strXML += "</errors>";
			}
			return strXML;
		}

	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

Share

About the Author

Dan Radu
Web Developer
Romania Romania
I live and work in Bucharest, Romania. I am programmer since 1998, when I have developed a "good taste" application for a catering company. Now I develop .NET applications (windows and ASP.NET) for large SQL Server database systems, with tens of millions of records.
I like to develop also in other languages like Object Pascal (Delphi), PHP, C++, VB, scripting. I enjoy the XML power, both on client side and server side.

| Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 19 Jul 2006
Article Copyright 2006 by Dan Radu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid