Using ADO.NET, XML, & XSL to Automate Website Maintenance

To automate updates to my website, I am storing content in a database. Using ADO.NET the table from the database is converted to XML. An XSL style sheet is used to transform the XML into HTML.

Note Jan 28, 2007: I no longer do my website this way.  When I wrote this article my service provider did not have ASP.NET, so I was trying to find a way to automate the generation of web pages before uploading to the server.  After switching to a service provider with ASP.NET capability I have been trying various ways for content creation.  I am writting this note now, since I am going through all my pages as I convert to using Master Pages.  This takes me back to entering the content directly in the page.  The difference is that the side bars and header reside in a master page so only need done once.  A change made to the master page, changes every page that uses that master.  End of Note.

As a test case, I have written some code to generate the menu which will be displayed on every page. The following is a description of this test code.

Here is what the database table for the side-bar menu looks like.

Here is the code to open a connection to the database and read the MenuItem table into a data set.

string source = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";"
    + @"User ID=Admin;Data Source=C:\bill\web\access\web.mdb";
   string select = "SELECT * FROM MenuItem";
   OleDbConnection conn = new OleDbConnection( source );
   conn.Open();
   OleDbDataAdapter adapter = new OleDbDataAdapter( select, conn );
   DataSet ds = new DataSet();
   adapter.Fill( ds );
   conn.Close();

Give the table and dataset names to obtain meaningful tags in the XML.

ds.Tables[0].TableName = "MenuItem"; ds.DataSetName = "Menu";

Write the XML to a file in case we want to look at it.

ds.WriteXml( "MenuText.xml" );

Generate an XML document from the dataset.

XmlDataDocument doc = new XmlDataDocument( ds );

Create an XSL Transform and read the XSL file into it.

XslTransform trans = new XslTransform();
trans.Load( @"C:\bill\web\Menu\Menu.xsl" );

Create a StreamWriter to be used by the XSL Transform to output the HTML to a file.

StreamWriter sw = new StreamWriter( "Menu.html" );

Transform the XML into HTML.

trans.Transform( doc, null, sw );

Close the StreamWriter.

sw.Close(); 

View the XML generated from the dataset .

View the XSL transform . Note: I have changed the file extension from xsl to xml so that it will display in IE.

View resulting HTML

The generated HTML creates a menu similar to the one on the side of this page.

Here is equivalent VB.NET code.

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Xml
Imports System.Xml.Xsl

Module Module1
   Sub Main()
      Dim source As String
      source = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";"_
         + "User ID=Admin;Data Source=C:\bill\web\access\web.mdb"
      Dim selectStr As String
      selectStr = "SELECT * FROM MenuItem"
      Dim conn As OleDbConnection
      conn = New OleDbConnection(source)
      conn.Open()
      Dim adapter As OleDbDataAdapter
      adapter = New OleDbDataAdapter(selectStr, conn)
      Dim ds As DataSet
      ds = New DataSet()
      adapter.Fill(ds)
      conn.Close()
      ds.Tables(0).TableName = "MenuItem"
      ds.DataSetName = "Menu"
      ds.WriteXml("MenuText.xml")
      Dim doc As XmlDataDocument
      doc = New XmlDataDocument(ds)
      Dim trans As XslTransform
      trans = New XslTransform()
      trans.Load("C:\bill\web\Menu\Menu.xsl")
      Dim sw As StreamWriter
      sw = New StreamWriter("Menu.html")
      trans.Transform(doc, Nothing, sw)
      sw.Close()
   End Sub
End Module