Listing 1 - EmailReports.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Net;
using System.Net.Mail;
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("Email Reports")]
[assembly: AssemblyDescription("Emails HTML reports from a SQL Server database.")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Andrew Mooney")]
[assembly: AssemblyProduct("")]
[assembly: AssemblyCopyright("(c) Andrew Mooney. All rights reserved.")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("1.0.0.0")]
public class EmailReports
{
private DataTable dt;
public static void Main(string[] args)
{
DataSet ds = new DataSet();
try
{
EmailReports EmailReports1 = new EmailReports();
ds.ReadXml(Directory.GetCurrentDirectory() + "\\" + args[0]);
EmailReports1.dt = ds.Tables["report"];
EmailReports1.CreateReport();
EmailReports1.EmailReport();
}
catch (Exception ex)
{
TextWriter tw = new StreamWriter(Directory.GetCurrentDirectory() +
"\\EmailReports.log", true);
tw.WriteLine(System.DateTime.Now + ": " + ex.Message);
tw.Close();
}
}
private void CreateReport()
{
TextWriter tw = new StreamWriter(Directory.GetCurrentDirectory() + "\\" +
dt.Rows[0]["file"].ToString());
SqlConnection cn = new SqlConnection("Server=" +
dt.Rows[0]["server"].ToString() + ";Database=" +
dt.Rows[0]["database"].ToString() +
";Integrated Security=True;");
SqlDataAdapter da1 = new SqlDataAdapter(dt.Rows[0]["select"].ToString(),cn);
SqlDataAdapter da2 = new SqlDataAdapter(dt.Rows[0]["sum"].ToString(),cn);
DataSet ds = new DataSet();
da1.Fill(ds,"Table0");
da2.Fill(ds,"Table1");
tw.WriteLine("<html>");
tw.WriteLine("<head>");
tw.WriteLine("<style>");
tw.WriteLine("body{font-family:arial,helvetica;}");
tw.WriteLine("caption{font-style:bold;font-size:150%;}");
tw.WriteLine("table{border:1px solid black;}");
tw.WriteLine("th{border:1px solid black;background-color:#aaf}");
tw.WriteLine("td{border:1px solid black;}");
tw.WriteLine("</style>");
tw.WriteLine("<title>" + dt.Rows[0]["title"].ToString() + "</title>");
tw.WriteLine("</head>");
tw.WriteLine("<body>");
tw.WriteLine("<table cellpadding=\"3\" cellspacing=\"0\">");
tw.WriteLine("<caption>" + dt.Rows[0]["title"].ToString() + "</caption>");
tw.WriteLine("<tr>");
foreach(DataColumn c in ds.Tables[0].Columns)
{
tw.WriteLine("<th>" + c.ToString() + "</th>");
}
tw.WriteLine("</tr>");
foreach(DataRow r in ds.Tables[0].Rows)
{
tw.WriteLine("<tr>");
foreach(DataColumn c in ds.Tables[0].Columns)
{
tw.WriteLine("<td>" + r[c.ToString()] + "</td>");
}
tw.WriteLine("</tr>");
}
tw.WriteLine("</table>");
tw.WriteLine("<br/>");
tw.WriteLine("<table cellpadding=\"3\" cellspacing=\"0\">");
tw.WriteLine("<tr>");
foreach(DataColumn c in ds.Tables[1].Columns)
{
tw.WriteLine("<th>" + c.ToString() + "</th>");
}
tw.WriteLine("</tr>");
foreach(DataRow r in ds.Tables[1].Rows)
{
tw.WriteLine("<tr>");
foreach(DataColumn c in ds.Tables[1].Columns)
{
tw.WriteLine("<td><b>" + r[c.ToString()] + "</b></td>");
}
tw.WriteLine("</tr>");
}
tw.WriteLine("</table>");
tw.WriteLine("</body>");
tw.WriteLine("</html>");
tw.Close();
}
private void EmailReport()
{
string s = String.Empty;
using (StreamReader sr = new StreamReader(Directory.GetCurrentDirectory() +
"\\" + dt.Rows[0]["file"].ToString()))
{
s += sr.ReadToEnd();
}
MailMessage mm = new MailMessage(dt.Rows[0]["from"].ToString(),
dt.Rows[0]["to"].ToString(), dt.Rows[0]["title"].ToString(), s);
mm.IsBodyHtml = true;
System.Net.Mail.SmtpClient SmtpClient1 = new
System.Net.Mail.SmtpClient(dt.Rows[0]["mailserver"].ToString());
SmtpClient1.Send(mm);
}
}Listing 2 - Make.bat
csc /target:exe /out:EmailReports.exe EmailReports.cs
pause
Listing 3 - Sample.xml
<data>
<report server="(local)"
database="pubs"
from="yourname@company.com"
to="yourname@company.com,yourmanager@company.com"
mailserver="youremailserver"
title="Author List"
select="SELECT * FROM authors "
sum="SELECT COUNT(au_id) AS [TOTAL COUNT] FROM authors"
file="AuthorList.htm" />
</data>
Listing 4 - WeeklyReports.bat
EmailReports.exe sample.xml
EmailReports.exe report2.xml
