More Fun With XML and XSLT!

This was a fun one. A client wanted to receive an email whenever a new user account is created on their web site. Simple enough, but they wanted all of the customer information such as address, email, etc. Now it's not a lot of data, about 10 fields and I could have just concatenated it into a string and sent it as the HTML body. But that's no fun! Instead we will use XSLT with some custom code to format the phone numbers properly. And we will get the data directly from a dataset! And we will do it all in memory without writing any files!

First, a SQL stored procedure to retrieve the customer data:


CREATE PROC usp_user

@Users_ID int

AS

SELECT u.Users_ID, u.Customers_ID, u.FName+' '+u.LName As DisplayName,
u.FName, u.LName, u.Email, u.Active, u.Create_Date, c.Company, c.Address1,
c.Address2, c.City, c.State, c.Zip, c.Phone1, c.Phone2, c.Fax,
c.Create_Date, c.Active, r.Role, u.User_Phone1
FROM Users u
INNER JOIN Customers c ON c.Customers_ID=u.Customers_ID
WHERE u.Users_ID=@Users_ID


Then, a class to handle our data format functions:


Public Class xsldataformatter
Public Function FormatPhone(ByVal strOrigPhoneAs String) As String
If IsDBNull(strOrigPhone) Then
Return ""
Else
Dim strMatch, strNewPhone As String
Dim intDigits As Integer
strMatch = "[^0-9]"

' This regex strips out all non number characters
strNewPhone = System.Text.RegularExpressions.Regex.Replace(strOrigPhone, strMatch, "", System.Text.RegularExpressions.RegexOptions.Singleline)
intDigits = strNewPhone.Length
If intDigits > 10 Then
strNewPhone = System.Text.RegularExpressions.Regex.Replace(strNewPhone, "^(...)(...)(....)(.*)$", "($1) $2-$3 x$4")
Else
strNewPhone = System.Text.RegularExpressions.Regex.Replace(strNewPhone, "^(...)(...)(....)(.*)$", "($1) $2-$3")
End If
Return (strNewPhone)
End If
End Function
End Class


Next, our XSLT file, "newuser.xslt". Notice the line at the top, xmlns:formatdata="urn:xsldataformatter", that will be used later:


<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
xmlns:formatdata="urn:xsldataformatter"
>
<xsl:output method="xml" indent="yes"/>

<xsl:template match="/NewUser/User">
<style>
body,td {font-family: Myriad, Arial, Helvetica, sans-serif; font-size: 10pt;}
td,th {vertical-align: top; }
h1, .h1 {font-family: Avant Garde, Century Gothic, Arial, Helvetica, sans-serif; font-size: 16pt; font-weight:bold; color: black; text-shadow: 3px 3px 3px #bbbbbb;}
h2, .h2 {font-family: Avant Garde, Century Gothic, Arial, Helvetica, sans-serif; font-size: 14pt; font-weight:bold; color: black; text-shadow: 3px 3px 3px #bbbbbb;}
h3, .h3 {font-family: Avant Garde, Century Gothic, Arial, Helvetica, sans-serif; font-size: 12pt; font-weight:bold; color: black; text-shadow: 3px 3px 3px #bbbbbb;}
h4, .h4 {font-family: Avant Garde, Century Gothic, Arial, Helvetica, sans-serif; font-size: 11pt; font-weight:bold; color: black; text-shadow: 3px 3px 3px #bbbbbb;}
TH, .th {background-color: #505050; color: White; font-weight:bold; border: 1px solid black}
A:link {color: #333333;}
A:visited {color: #333333}
A:hover {color: Blue}
</style>
<h1>New User Created</h1>
<table align="center">
<tr>
<th align="right" nowrap="nowrap">Company:</th>
<td nowrap="nowrap">
<xsl:element name="a">
<xsl:attribute name="href">
https://www.mywebsite.com/admin/customers.aspx?c=<xsl:value-of select="Customers_ID"/>
</xsl:attribute>
<xsl:value-of select="Company"/>
</xsl:element>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Name:</th>
<td nowrap="nowrap">
<xsl:element name="a">
<xsl:attribute name="href">
https://www.mywebsite.com/admin/users.aspx?u=<xsl:value-of select="Users_ID"/>
</xsl:attribute>
<xsl:value-of select="DisplayName"/>
</xsl:element>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Email:</th>
<td nowrap="nowrap">
<xsl:element name="a">
<xsl:attribute name="href">
mailto:<xsl:value-of select="Email"/>
</xsl:attribute>
<xsl:value-of select="Email"/>
</xsl:element>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Address:</th>
<td nowrap="nowrap">
<xsl:value-of select="Address1"/>
<xsl:if test="Address2 != ''">, <xsl:value-of select="Address2"/></xsl:if>
<br />
<xsl:value-of select="City"/>, <xsl:value-of select="State"/> <xsl:value-of select="Zip"/>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Phone 1:</th>
<td nowrap="nowrap">
<xsl:value-of select="formatdata:FormatPhone(Phone1)"/>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Phone 2:</th>
<td nowrap="nowrap">
<xsl:value-of select="formatdata:FormatPhone(Phone2)"/>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">User Phone:</th>
<td nowrap="nowrap">
<xsl:value-of select="formatdata:FormatPhone(User_Phone1)"/>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Fax:</th>
<td nowrap="nowrap">
<xsl:value-of select="formatdata:FormatPhone(Fax)"/>
</td>
</tr>
</table>
</xsl:template>
</xsl:stylesheet>



And finally, the routine to put it all together:


Sub SendNewUserNotification(ByVal userid As Integer)
Dim dsU As New DataSet("NewUser")
Dim daU As New SqlDataAdapter("usp_user", oConn)
With daU.SelectCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Users_ID", SqlDbType.Int).Value = userid
End With
daU.Fill(dsU, "User")

Dim x As New XmlDataDocument(dsU)
Dim xt As New XslCompiledTransform
Dim ms As New MemoryStream
Dim sr As New StreamReader(ms)
Dim xw As New XmlTextWriter(ms, System.Text.Encoding.UTF8)
Dim xa As New XsltArgumentList
xa.AddExtensionObject("urn:xsldataformatter", New xsldataformatter)
xt.Load(Server.MapPath("/newuser.xslt"))
xw.Flush()
xt.Transform(x, xa, xw)
ms.Seek(0, SeekOrigin.Begin)
Dim htm As String = sr.ReadToEnd
xw.Close()
ms.Close()
sr.Close()

Dim oMsg As New Net.Mail.MailMessage
oMsg.From = New Net.Mail.MailAddress("support@mywebsite.com")
oMsg.To.Add(New Net.Mail.MailAddress("admin@mywebsite.com"))
oMsg.Subject = "New User Notification"
oMsg.IsBodyHtml = True
oMsg.Body = htm
Dim smtp As New System.Net.Mail.SmtpClient()
Try
smtp.Send(oMsg)
Catch ex As Exception
End Try
End Sub


That's it! Our custom function is loaded into the XslCompiledTransform using the AddExtensionObject method of XsltArgumentList. Also, make sure your SMTP server is setup in web.config in the configuration section:


<system.net>
<mailSettings>
<smtp from="support@mywebsite.com">
<network host="localhost"/>
</smtp>
</mailSettings>
</system.net>