Databases - ASP.NET Version

In this version, the resources listed under the Tools tab of http://www.thexmltoolkit.org are stored in a mySQL database, rather than in an XML file. The ASP.NET script (C#) establishes the connection with the database, checks the connection, performs the query, and returns the results to be combined with a static XML file, transformed by the XSL, and formatted with CSS to display the Web page.

The resulting Web page looks exactly like the Tools page on this Web site; the only difference being that the names, descriptions, and URLs of the listed resources come from a database.


<%--
*******************************************************************************************
* COPYRIGHT NOTICE:
* Copyright 2006 Center for Technology in Government.
* The Center grants permission to use and modify this software code free of charge provided  
* this copyright notice is included. By using this code you agree to indemnify the Center from 
* any liability that might arise from its use. Selling the code is expressly forbidden.
* ******************************************************************************************
--%>

<%-- Using MSDN libraries --%>
<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Xml.Xsl" %>
<%@ Import Namespace="System.Xml.XPath" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Odbc" %>

<script language="C#" runat="server">

 /* This function is called every time when aspx page load */
 public void Page_Load(Object sender, EventArgs E) 
 { 

  /*******************************************************************************
   Initial information for database connection and xml translating
  *******************************************************************************/
  String StrConnect = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;DATABASE=databasename; 
   UID=username; PASSWORD=password; OPTION=3";
  String strSQL = "SELECT * FROM tools;"; 

  /****************************************************************************
   Open XML file, only has title infor, no  data
   Get data from database 
   Convert database data to XML format, 
   Append new data to XML file, processed only in memory 
   Generated xml data format must conform to existing XSL sheet
   We can use existing XSL file, nothing needs to be changed
   New xml data only exist in memory 
  ******************************************************************************/ 
  XmlNode sect1,para,ulink,value,title;
  XmlDocument xmlDoc = new XmlDocument();
  xmlDoc.Load(Server.MapPath( "tools/tools_title.xml"));

  // Get the insert pointer for new data
  XmlNodeList elemList = xmlDoc.GetElementsByTagName("chapter");

  XmlNode refNode = elemList.Item(0);
  // Connect to database 
  using (OdbcConnection db_connect = new OdbcConnection(StrConnect))
  {
    OdbcCommand command = new OdbcCommand(strSQL, db_connect);
    try
        { db_connect.Open(); }
    catch (Exception ex)
        { Response.Write(ex.Message);}

    // Execute the DataReader and access the data.
    OdbcDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
     //  Create the new element
     sect1 = xmlDoc.CreateElement("sect1");
     //  Insert the new element
     sect1 = refNode.AppendChild(sect1);    
     title = xmlDoc.CreateElement("title");
     title = sect1.AppendChild(title);
        ulink = xmlDoc.CreateElement("ulink");
        ulink = title.AppendChild(ulink);
        ((XmlElement)ulink).SetAttribute( "url", reader.GetString(3) );
             value = xmlDoc.CreateTextNode( reader.GetString(1) );
             value = ulink.AppendChild(value);
     para = xmlDoc.CreateElement("para");
     para = sect1.AppendChild(para);
         value = xmlDoc.CreateTextNode( reader.GetString(2) + "(" );
         value = para.AppendChild(value);
         ulink = xmlDoc.CreateElement("ulink");
         ulink = para.AppendChild(ulink);
         ((XmlElement)ulink).SetAttribute( "url", reader.GetString(3) ) ;
             value = xmlDoc.CreateTextNode( reader.GetString(3) );
             value = ulink.AppendChild(value);
         value = xmlDoc.CreateTextNode(")");
         value = para.AppendChild(value);      
    }  // end while
    // Call Close when done reading.
    reader.Close();
  } //end using 

  /******************************************************************
   Translate xml data into html 
   Send back to clent browser
   Use existed XSL file 
   Processed in Memory 
  *******************************************************************/
  XsltArgumentList args = new XsltArgumentList();
  args.AddParam("name","","tools");
  args.AddParam("extension","",".aspx");
  String xslPath = Server.MapPath("level1.xsl"); 
  XPathNavigator doc = xmlDoc.CreateNavigator();
  //Instantiate the XslTransform Class
  XslTransform xslDoc = new XslTransform();
  xslDoc.Load(xslPath);
  xslDoc.Transform(doc,args,Response.Output);
 }

</script>

Valid values would need to be inserted for your database connection. Otherwise, this script works much like the scripts in the Environment section of this Web site.

The XML Toolkit

ASP.NET Version