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>
