Archive for August, 2006

I have been using the .net based Umbraco CMS for more then a year now and never have I had the feeling it limited me in anyway. But now I need a quick and dirty approach to connect to a database other then the Umbraco database.

Umbraco can grab XML feeds using the build in XSLT library extension, but I am just playing around at the moment and need direct access to manipulating query results.

So I created my own XSLT library. It has one 1 method at the moment: QueryDatabase(‘connection string’,'query’)

Just a note: it probably is not the greatest idea to use this in a production environment. What I plan to do afterwards, is to hard-code the query into another method not allowing any unknown request to the database.

But here is the code anyway (code pasted below), the dll and the VS 2003 project zip file.

If you want to use it without looking at the code just do the following actions:

1. Download the astuanax xslt libarary dll
2. Unzip the file and put it in the “/bin” folder of your Umbraco website
3. Edit the file “xsltExtensions.xml” in the folder “/config” and
4. Add the following line:
   <ext assembly=”/bin/astuanax” type=”astuanax.Library” alias=”astuanax” /><br />
5. Once you got this running, go to the XSLT editor in Umbraco and paste the following code into your xslt file:
   <textarea><br /> <xsl:copy-of select="astuanax:QueryDatabase(‘Server=127.0.0.1;Database=Northwind;User ID=****;Password=*****;Trusted_Connection=False’, ‘SELECT LastName FROM Employees’)"/><br /> </textarea><br />

   This will show you the XML code you can expect from this query.

Leave a comment if you like it. Or if you have a problem using it, let me know and I will try to fix it.

C# Code:

using System;<br /> using System.Xml;<br /> using System.Xml.Schema;<br /> using System.Xml.Xsl;<br /> using System.Xml.XPath;<br /> using System.Data;<br /> using System.Data.SqlClient;<br /> using System.IO;<br /> using System.Collections;<br /> using System.Globalization;<br /> using System.Net;<br /> using System.Text.RegularExpressions;</p> <p>namespace astuanax<br /> {<br /> /// <summary><br /> /// </summary><br /> public class Library<br /> {<br /> private static string _ErrorMessage = ""; </p> <p> /*<br /> * This method is enabling debug information to be passed along<br /> * doing them umbraco situps..<br /> */ </p> <p> public static string ErrorMessage()<br /> {<br /> return _ErrorMessage;<br /> }</p> <p> /// <summary><br /> /// Query a database other then the Umbraco database<br /> /// </summary><br /> /// <param name="connection string">server – database – username – password </param><br /> /// <param name="sql"> sql query</param><br /> /// <returns> XML dataset </returns></p> <p> public static XPathNodeIterator QueryDatabase(string conn, string query )<br /> {<br /> try<br /> {<br /> SqlConnection thisConnection = new SqlConnection(conn);<br /> // Server=localhost;Database=Northwind;User ID=****;Password=****;Trusted_Connection=False<br /> thisConnection.Open();<br /> SqlCommand thisCommand = thisConnection.CreateCommand();<br /> thisCommand.CommandText = query;<br /> SqlDataAdapter adapter = new SqlDataAdapter(thisCommand);</p> <p> DataSet ds = new DataSet();<br /> adapter.Fill(ds);<br /> thisConnection.Close();</p> <p> XmlDocument xdoc = new XmlDocument();<br /> xdoc.LoadXml(ds.GetXml());<br /> XPathNavigator xp = xdoc.CreateNavigator();<br /> return xp.Select(".");<br /> }<br /> catch (SqlException e)<br /> {<br /> _ErrorMessage = e.Message;<br /> return null;<br /> }<br /> }<br /> }<br /> }<br />