Automated E-mail Reports
page 4 of 5
by Andrew Mooney
Feedback
Average Rating: 
Views (Total / Last 10 Days): 27660/ 114

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

View Entire Article

User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-05-26 5:18:55 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search