AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1468&pId=-1
Automated E-mail Reports
page
by Andrew Mooney
Feedback
Average Rating: 
Views (Total / Last 10 Days): 27662/ 117

Introduction

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

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

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

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

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.



©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-05-27 1:43:29 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search