Automated E-mail Reports: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Automated E-mail Reports

Published: 31 Oct 2007

Abstract
In this article Andrew examines how to create a simple HTML report from a SQL Server database and automatically e-mail it.

by Andrew Mooney
Feedback

Average Rating:

Views (Total / Last 10 Days): 26939/ 48

Article Contents:

Introduction
Setting up the Application
How to Use the Application
Code Listings
Conclusion

Introduction

[ Back To Top ]

This application will create simple HTML reports from a SQL Server database and automatically email them. It is a console application that takes a single XML file as an argument. This XML file contains all the settings for each report. Windows Task Scheduler is used start the application at the desired times.

Setting up the Application

[ Back To Top ]

Requirements:

1. Microsoft .NET Framework 2.0

2. Microsoft SQL Server 2000 or later

3. A text editor such as Notepad

4. The sample report uses the pubs database, which can be downloaded here.

Create the following files from the code listings at the end of this article:

1. Listing 1 - EmailReports.cs. This file contains the code for the main application.

2. Listing 2 - Make.bat. This file is used to compile the code into a console application (.exe).

3. Listing 3 - Sample.xml. This file is a sample report file.

4. Listing 4 - WeeklyReports.bat. This file is used to run the sample report. You only need the first line of this listing. The second line is for running multiple reports.

Place all of these files in the same folder. Then run the Make.bat file to create the console application.

How to Use the Application

[ Back To Top ]

For each report that you want emailed, you will need to create an XML (see Listing 3) file with the following report attributes set:

1. server - The name or IP address of the SQL Server

2. database - The name of the database

3. from - Email address used to send from

4. to - A comma separated list of the email addresses you want the report sent to.

5. mailserver - The name of the email server

6. title - This is the title of the report and the subject of the email.

7. select - The SQL select statement used for the report

8. sum - The SQL select statement used as a summary for the report

9. file - The name of the HTML file that is created and then emailed

To run the report, create a batch file (see Listing 4) that passes the report file name to the application. Then use Windows Task Scheduler to run this batch file at the frequency you desire. If you want to run multiple reports at the same frequency, for example, once a week on Monday at 9:00 AM, this can be done from one batch file.

If an error occurs, the application creates a file named EmailReports.log in the same directory and records the error. Any future errors will be added to this file.

Code Listings

[ Back To Top ]

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

Conclusion

[ Back To Top ]

This sample console application demonstrates the power of the .NET Framework by using a small amount of code to provide a complete solution for emailing reports from a SQL Server database. Hopefully, you will find it useful just as it is or maybe even add features to it that will meet your specific needs.

User Comments

No comments posted yet.