In this section I’m going to provide the technical details
needed to build a simple infrastructure for retrieving dynamic content from a
SQL Server database and displaying it on a web page using XSLT.
As discussed above, the process of displaying dynamic content
includes the following steps:
- Extracting database content as XML data.
- Retrieving XML from the appropriate
stored procedure.
- Transforming XML into HTML using XSLT.
- Displaying dynamic content on the web page.
Step 1 - Extracting Database Content
The most flexible and powerful way to extract database
content is simply to create stored procedures that output XML. This way all the
data needed to for the interface can be easily extracted with one database
call. A future change to the content only requires slight adjustments of the
appropriate stored procedure without any changes to the calling code.
Listing 1 - builds an XML document with a user’s
first and last name and a list of tasks this user needs to accomplish
CREATE procedure [dbo].[Get_User_Profile_XML]
@User_ID varchar(10)
AS
SET NOCOUNT ON;
SELECT Users.[User_ID] AS [@id],
Users.First_Name AS [@first],
Users.Last_Name AS [@last],
--all user tasks
(
SELECT Tasks_ID AS [@id],
Task_Description AS [@description]
FROM dbo.Users_Tasks
WHERE Users_Tasks.[User_ID] = Users.[User_ID]
FOR XML PATH('task'),TYPE, ROOT('tasks')
)
FROM dbo.Users
WHERE Users.[User_ID] = @User_ID
FOR XML PATH('user'), TYPE;Note: This was written against SQL Server
2005 and can be easily adjusted to use FOR XML EXPLICIT for use with SQL Server
2000.
Listing 2 - the output XML of the above stored
procedure
<user id="U12" first="John" last="Smith">
<tasks>
<task id="T34" description="Annual review"/>
<task id="T56" description="File my taxes"/>
</tasks>
</user>
Step 2 – Retrieving the XML Content from the Database
After building the appropriate stored procedure, we need to
develop the code that can call this procedure on runtime and extract the
appropriate data for the current user.
As increasing flexibility and lowering long-term maintenance
costs are our primary goals, it is often best to keep the code needed to
retrieve that database information almost entirely indifferent of the content
it gets back. Extracting XML directly from the stored procedure gives us just
that. We can keep changing the XML structure and content without requiring any
changes to the calling code.
Listing 3 - Extract XML data from stored procedure
and send XML (as a string) to the client
private string GetUserProfileXML(string userId)
{
const string MY_CONNECTION_STRING = "YOUR CONNECTION STRING";
const string PROCEDURE = "dbo.Get_User_Profile_XML";
using (SqlConnection connection = new SqlConnection(MY_CONNECTION_STRING))
using (SqlCommand command = new SqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = PROCEDURE;
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@User_ID", SqlDbType.VarChar, 10);
param.Value = new SqlString(userId);
command.Parameters.Add(param);
using (XmlReader reader = command.ExecuteXmlReader())
{
reader.MoveToContent();
string resultXml = reader.ReadOuterXml();
return resultXml;
}
}
}Note: This opens a connection to SQL
server, configures the command object and its @ID parameter, and reads the XML
as a string from the XmlReader.
Step 3 –Transforming XML into HTML using XSLT
Our next task is to transform the XML into HTML that can be displayed
to the current user.
XSLT can be quite handy and an ideal solution when it comes
to transforming XML into HTML. XSLT offers enormous flexibility, as it can be
quickly adjusted to handle any changes to the XML content or changes to new
requirements.
XSLT files can be easily included as part of any web
application, and although this is beyond the scope of this article, development
teams can choose to slightly adjust this approach and globalize their web
applications by developing different XSLT files per language supported.
Listing 4 - XSLT that might be used to process the
XML data above
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="user">
<div class="userInfo">
<xsl:value-of
select="concat('Tasks for ', @first,' ',@last,':')"/>
</div>
<div class="userTasks">
<xsl:for-each select="tasks/task">
<div class="userTask">
<a>
<xsl:attribute name="href">
<xsl:value-of select="concat('taskinfo.aspx?id=',@id)"/>
</xsl:attribute>
<xsl:value-of select="@description"/>
</a>
</div>
</xsl:for-each>
</div>
</xsl:template>
</xsl:stylesheet>Note: This outputs the heading for the
page (first and last name) and then outputs every associated user task.
Listing 5 - the HTML result (without any applied
CSS) might look like the following
Tasks for John Smith:
Annual review
File my taxes
Step 4 – Displaying Dynamic Content on the Web Page
The final step is to create the web page needed to process
and display the appropriate user content. As described at the beginning of the
article, we need to retrieve the XML from the database by calling the
GetUserProfileXML function we developed earlier, transform the XML into HTML
using the XSLT file above, and then finally to display the HTML to the user.
We first create an ASPX page with a server side DIV to
ultimately contain the HTML. The page might look like the one on listing 6.
Listing 6
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="userprofile.aspx.cs" Inherits="UserProfile" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>My User Profile Page</title>
</head>
<body>
<form id="form1" runat="server">
<div id="_divData" runat="server">
</div>
</form>
</body>
</html>
Note: The DIV is set to run on the server
so the HTML can be assigned on the server.
Next, we need to develop the function that takes XML and
transforms it into HTML using an XSLT file.
A function that accepts an XSLT file name and the XML data,
and performs the transformation might look like the one on listing 7.
Listing 7
private string GetPageHTML(string xsltFileName,string xmlData)
{
string fullXsltFilePath = Server.MapPath("~/" + xsltFileName);
using (XmlReader dataReader = LoadXMLToReader(xmlData))
{
XslCompiledTransform xslTrans = new XslCompiledTransform();
xslTrans.Load(fullXsltFilePath);
using (MemoryStream outputStream = new MemoryStream())
{
xslTrans.Transform(dataReader, null, outputStream);
outputStream.Position = 0;
using (StreamReader sr = new StreamReader(
outputStream, Encoding.UTF8))
{
string resultHtml = sr.ReadToEnd();
return resultHtml;
}
}
}
}Note: This function gets the full path to
the XSLT file, loads the XML data into an XmlReader, and then uses
XslCompiledTransform object to transform the XML into HTML and return the HTML
as string to the calling code. Also, XslCompiledTransform is thread-safe
object, so I would highly recommend caching it to further increase website
performance.
Listing 8 - use the following helper utility to
load an XML string into an XmlReader object
private XmlReader LoadXMLToReader(string inputXML)
{
byte[] xmlData = Encoding.UTF8.GetBytes(inputXML);
MemoryStream xmlStream = new MemoryStream(xmlData);
xmlStream.Position = 0;
XmlReader reader = XmlReader.Create(xmlStream);
reader.Read();
return reader;
}Finally, to wrap everything up, here is what the Page_Load
function on the web page might look like:
Listing 9
protected void Page_Load(object sender, EventArgs e)
{
const string XSLT_FILE_NAME = "UserProfile.xslt";
//gets the current user id
string userId = "U12";
//loads the xml data from the database
string xmlData = GetUserProfileXML(userId);
//transform the XML into HTML
string html = GetPageHTML(XSLT_FILE_NAME, xmlData);
//shows the html to the user
_divData.InnerHtml = html;
}Note: The User ID is hard-coded but it
should be retrieved dynamically based on the current logged-in user.
