Запрос:
select top 20 type_desc, name,
schema_name(schema_id) [schema],
convert(varchar, create_date, 104) created, is_ms_shipped
from sys.objects order by newid() for xml raw, root, type
Получаемый XML:
<root>
<row type_desc="PRIMARY_KEY_CONSTRAINT" name="PK_UnitMeasure_UnitMeasureCode" schema="Production" created="29.03.2012" is_ms_shipped="0" />
<row type_desc="INTERNAL_TABLE" name="xml_index_nodes_1509580416_256001" schema="sys" created="29.03.2012" is_ms_shipped="1" />
<row type_desc="CHECK_CONSTRAINT" name="CK_SalesOrderHeader_ShipDate" schema="Sales" created="29.03.2012" is_ms_shipped="0" />
<row type_desc="USER_TABLE" name="Employee" schema="HumanResources" created="29.03.2012" is_ms_shipped="0" />
....
| + XSLT |
<?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="/">
<html>
<head>
<style type="text/css">
p {
font-family: Verdana;
font-size: 10pt;
margin: 3px;
}
td {
font-family: Verdana;
font-size: 10pt;
margin: 3px;
padding: 2px 10px;
}
td.bool {
text-align: center;
font-family: Arial;
font-size: 14px;
font-weight: bold;
}
h3 {
font-family: Verdana;
margin: 3px;
}
th {
font-family: Verdana;
font-size: 10pt;
padding: 3px 10px;
text-align: left;
background-color: #E0E0E0;
border-top: 2px solid #A0A0A0;
border-bottom: 2px solid #A0A0A0;
}
table {
border-collapse: collapse;
border-bottom: 2px solid #A0A0A0;
}
</style>
</head>
<body>
<h3>
<xsl:text>Отчет о работе какого-то там сервиса</xsl:text>
</h3>
<p style="margin: 5px">
<xsl:text>Время загрузки данных: </xsl:text>
<xsl:value-of select="root/@from_date" />
</p>
<table>
<tr>
<th>Тип</th>
<th>Имя</th>
<th>Схема</th>
<th>Создан</th>
<th>Сист.</th>
</tr>
<xsl:for-each select="root/row">
<tr>
<td>
<xsl:value-of select="@type_desc" />
</td>
<td>
<xsl:value-of select="@name" />
</td>
<td>
<xsl:value-of select="@schema" />
</td>
<td>
<xsl:value-of select="@created" />
</td>
<td class="bool">
<xsl:choose>
<xsl:when test="@is_ms_shipped=1">
<xsl:value-of select="'√'" />
</xsl:when>
</xsl:choose>
</td>
</tr>
</xsl:for-each>
</table>
<p style="margin-top: 5px">Письмо автоматически сгенерировано каким-то сервисом. Отвечать на это письмо не нужно.</p>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
|
Код:
var xd = new XmlDocument();
using (var cnn = new SqlConnection("Data Source=....;Initial Catalog=AdventureWorks2008R2;Integrated Security=True"))
using(var cmd = new SqlCommand(Properties.Resources.ReportQuery, cnn))
{
cnn.Open();
using(var rd = cmd.ExecuteReader())
if (rd.Read() && !rd.IsDBNull(0))
using(var xr = rd.GetSqlXml(0).CreateReader())
xd.Load(xr);
}
xd.DocumentElement.SetAttribute("from_date", DateTime.Now.ToString("dd.MM.yyyy"));
var xslt = new XslCompiledTransform();
using(var sr = new StringReader(Properties.Resources.ReportTransform))
using(var xr = XmlReader.Create(sr))
xslt.Load(xr);
var report = new StringBuilder();
using(var sw = new StringWriter(report))
using(var xw = XmlWriter.Create(sw))
xslt.Transform(xd, null, xw);
using (
var msg = new MailMessage
{
From = new MailAddress("SomeService.SE@mycompany.gov"),
Subject = "Оповещение сервиса",
IsBodyHtml = true,
Body = report.ToString(),
})
{
msg.To.Add(new MailAddress("VasyaPupkin@mycompany.gov"));
using (var mailer = new SmtpClient(
Properties.Settings.Default.MailServer,
Properties.Settings.Default.MailPort)
{
UseDefaultCredentials = true
})
mailer.Send(msg);
}
Результат: