The next step is to create a Windows Service that will
continually run in the background and determine if a report is supposed to be
executed and emailed to the users.
1.
Launch Visual Studio 2008.
2.
Select FileàNewàProject from the menu.
3.
Click on C#àWindows from the
Project Types and then click on the Windows Service template.
4.
Change the name of the project to AutomatedReports. Set the location to
the folder where you want to save your project.
5.
Click the OK button.
6.
Visual Studio will create a new Windows Service Project with a file
called Service1.cs and it will be displayed in design mode. Before we get into
the code for the service lets first create the classes that map to the three
tables we created earlier and contain the logic to select and save the records.
7.
The connection string and other settings will be stored in an App.config
file so you'll first need to add a reference to the System.Configuration
namespace. Right click on the Reference node under the AutomatedReports
project in the Solution Explorer. You'll also need to add a reference to the
CrystalDecisions.Shared and CrystalDecisions.CrystalReports.Engine namespaces.
8.
Scroll down the .NET list and select System.Configuration and then click
the OK button.
9.
Now you can add the class files. Right click on the AutomatedReports
project in the Solution Explorer and select AddàNewàClass from the pop-up menu.
10. Change
the name of the class to Schedule and click the Add button. This class will
map to the Schedule table.
11. Add
the following using statements
using System.Data.SqlClient;
using System.Configuration;
12. Now
add the properties.
public int Id { get; set; }
public int ReportId { get; set; }
public DateTime NextRunDateTime { get; set; }
public bool IsReadyToRun
{
get
{
if (DateTime.Now > NextRunDateTime)
{
return true;
}
else
{
return false;
}
}
}
The first three properties map to the fields in the table.
The third property IsReadyToRun contains the logic to determine if the current
date is past the next run date.
13. Add
the method that will retrieve the record from the database and map fields to
the properties in the class.
internal void Load(int reportId)
{
//Open connection
using (SqlConnection cn = new
SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
{
cn.Open();
//get the schedule
SqlCommand cmd = new SqlCommand("SELECT Id, ReportId, NextRunDateTime FROM
Schedule WHERE ReportId = " + reportId, cn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Id = Convert.ToInt32(dr["Id"]);
ReportId = Convert.ToInt32(dr["ReportId"]);
NextRunDateTime = (DateTime)dr["NextRunDateTime"];
}
dr.Close();
cn.Close();
}
}
14. Add
a method that can update the NextRunDateTime field. This method will be called
once a report has been run. The NextRunDateTime field will be incremented by 1
week.
internal void SaveNextRunDate(int reportId)
{
//Add a week
NextRunDateTime = NextRunDateTime.AddDays(7);
//Save to the database
using (SqlConnection cn = new
SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
{
cn.Open();
//get the schedule
SqlCommand cmd = new SqlCommand("UPDATE Schedule SET NextRunDateTime = '"
+ NextRunDateTime + "' WHERE ReportId = " + reportId, cn);
cmd.ExecuteNonQuery();
}
}
15. Next
you'll add a class that maps to the Recipient table. Right click on the
AutomatedReports project and click AddàNewàClass from the pop-up menu.
16. Add
the following using statements
using System.Data.SqlClient;
using System.Configuration;
17. Add
the RecipientTypeEnum enumeration.
public enum RecipientTypeEnum
{
To,
CC,
BCC
}
18. Now
add the properties.
public int Id { get; set; }
public int ReportId { get; set; }
public string EmailAddress { get; set; }
public RecipientTypeEnum RecipientType { get; set; }
19. That's
all that is needed for this class. However, since a report can have multiple
recipients I'm going to create a List class that can hold multiple Recipient
objects for a specific report. Add another class called RecipientList.
20. Change
the class so it inherits from the generic List class and the generic class
should be the Recipient class.
class RecipientList : List<Recipient>
{
}
21. Add
the following using statements.
using System.Data.SqlClient;
using System.Configuration;
22. Now
add the method that will load this list with the recipients for a specific
report.
internal void Load(int reportId)
{
//Open connection
using (SqlConnection cn = new
SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
{
cn.Open();
//Get the Recipients for this report.
SqlCommand cmd = new SqlCommand("SELECT Id, ReportId, EmailAddress,
RecipientType FROM Recipient WHERE ReportId = " + reportId, cn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
this.Add(new Recipient
{
Id = Convert.ToInt32(dr["Id"]),
ReportId = Convert.ToInt32(dr["ReportId"]),
EmailAddress = dr["EmailAddress"].ToString(),
RecipientType = (Recipient.RecipientTypeEnum)((byte)dr["RecipientType"])
});
}
dr.Close();
cn.Close();
}
}
23. Now
you can add the class that maps to the Report table. Add a new class called
Report.
24. Add
the following properties to this class.
public int Id { get; set; }
public string ReportFile { get; set; }
public string ReportName { get; set; }
public string RecordSelectionFormula { get; set; }
public CrystalDecisions.Shared.ExportFormatType ExportFormat { get; set; }
public string Server { get; set; }
public string DBName { get; set; }
public string UserId { get; set; }
public string Password { get; set; }
public Schedule Schedule { get; set; }
public RecipientList Recipients { get; set; }
25. Now
add the following constructor. This will instantiate the Schedule and
Recipients objects that are properties of this class.
public Report()
{
Schedule = new Schedule();
Recipients = new RecipientList();
}
26. The
last class to add is the List of reports. Add a new class called ReportList.
27. Change
the class so it inherits from the generic List class and the generic class
should be the Report class.
class ReportList : List<Report>
{
}
28. Add
the following using statements.
using System.Data.SqlClient;
using System.Configuration;
29. This
class has one method that loads all the reports and the associated recipients
and schedule associated with the report.
public void Load()
{
//Open connection
using (SqlConnection cn = new
SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
{
cn.Open();
//get the schedule
SqlCommand cmd = new SqlCommand("SELECT Id, ReportFile, ReportName,
RecordSelectionFormula, ExportFormat, Server, DBName, UserId, Password "
+ "FROM Report ", cn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Report report = new Report
{
Id = Convert.ToInt32(dr["Id"]),
ReportFile = dr["ReportFile"].ToString(),
ReportName = dr["ReportName"].ToString(),
RecordSelectionFormula = (dr["RecordSelectionFormula"] ==
DBNull.Value ? "" : dr["RecordSelectionFormula"].ToString()),
ExportFormat =
(CrystalDecisions.Shared.ExportFormatType)((byte)dr["ExportFormat"]),
Server = dr["Server"].ToString(),
DBName = dr["DBName"].ToString(),
UserId = dr["UserId"].ToString(),
Password = dr["Password"].ToString()
};
report.Schedule.Load(report.Id);
report.Recipients.Load(report.Id);
this.Add(report);
}
dr.Close();
cn.Close();
}
}
30. Now
we can finally add the code to the Service1 class which will perform the work
of executing the report and emailing it to the recipients. Right click on the
Service1.cs file in the Solution Explorer and select View Code from the pop-up
menu.
31. Add
the following using statements.
using System.Configuration;
using System.Timers;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Net.Mail;
32. The
service will use the Timer object that is part of the .NET Framework to
periodically run the code that checks if a report is to be run. The Timer
object has an Elapsed event that fires during a specified interval. Declare
the Timer object as a member variable.
private Timer _emailTimer;
33. When
the service starts it will instanciate the Timer object and set it's interval
property. You'll also need to create two other member variables.
private bool _isProcessing;
private ReportList _reports;
34. The
_isProcessing variable will be used to prevent the code in the Elapsed event
from firing if the a report is still processing. The _reports object contains
the list of reports from the database. The application will loop through the
report list and check if any report is supposed to executed and emailed to a user.
35. Copy
the following code to the OnStart event.
_reports = new ReportList();
_reports.Load();
//Instantiate a timer.
_emailTimer = new Timer();
//Check if the timer interval has been set.
string timerInterval = ConfigurationManager.AppSettings["TimerInterval"];
if (timerInterval != "")
{
_emailTimer.Interval = Convert.ToDouble(timerInterval);
}
else
{
//Default to 60 seconds
_emailTimer.Interval = 60000;
}
//Hook the Elapsed event to the event handler
_emailTimer.Elapsed += new ElapsedEventHandler(_emailTimer_Elapsed);
//Start the timer.
_emailTimer.Enabled = true;
The
"TimerInterval" is a setting that will be added to the App.config
file. This will allow you to change how often the Elapsed event fires without
changing any code. Notice that the _emailTimer object is instantiated and the
ElapsedEventHandler is attached to the Elapsed event. You'll need to create
the _emailTimer_Elapsed method declaration. This code in this method will be
run when the Elapsed event fires.
36. Add
the following method.
void _emailTimer_Elapsed(object sender, ElapsedEventArgs e)
{
if (!_isProcessing)
{
_isProcessing = true;
foreach (Report report in _reports)
{
if (report.Schedule.IsReadyToRun)
{
RunReport(report);
report.Schedule.SaveNextRunDate(report.Id);
}
}
_isProcessing = false;
}
}
37. This
method loops through the _reports list and calls another method called
RunReport if the report should be executed and emailed. Add the following code
for the RunReport method.
private void RunReport(Report report)
{
ReportDocument reportDocument = new ReportDocument();
reportDocument.Load(ConfigurationManager.AppSettings["ReportPath"] +
report.ReportFile);
//Check if there is a selection formula
if (report.RecordSelectionFormula != "")
{
reportDocument.DataDefinition.RecordSelectionFormula = report.RecordSelectionFormula;
}
//Set the table location for the tables in the report
SetTableLocation(reportDocument.Database.Tables, report.Server, report.DBName,
report.UserId, report.Password);
//create the email message
MailMessage mm = new MailMessage();
//set the from address
mm.From = new MailAddress(ConfigurationManager.AppSettings["FromAddress"]);
//set the recipients
foreach (Recipient recipient in report.Recipients)
{
switch (recipient.RecipientType)
{
case Recipient.RecipientTypeEnum.To:
mm.To.Add(new MailAddress(recipient.EmailAddress));
break;
case Recipient.RecipientTypeEnum.BCC:
mm.Bcc.Add(new MailAddress(recipient.EmailAddress));
break;
case Recipient.RecipientTypeEnum.CC:
mm.CC.Add(new MailAddress(recipient.EmailAddress));
break;
}
}
//Set mail properties
mm.Subject = "Report";
mm.Body = " ";
mm.IsBodyHtml = true;
//attach the report to the email.
mm.Attachments.Add(new Attachment(reportDocument.ExportToStream(report.ExportFormat),
report.ReportName));
//create smtp object, this uses your email server.
SmtpClient smtp = new SmtpClient(ConfigurationManager.AppSettings["EmailServer"]);
//send the message
smtp.Send(mm);
reportDocument.Close();
}
38. There's
only one more method you need to add to this class called SetTableLocation.
This sets the location of all the tables defined in the Crystal Report to the
AdventureWorks database.
private void SetTableLocation(Tables tables, string serverName, string dbName,
string userId, string password)
{
ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.ServerName = serverName;
connectionInfo.DatabaseName = dbName;
connectionInfo.UserID = userId;
connectionInfo.Password = password;
foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
{
TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
tableLogOnInfo.ConnectionInfo = connectionInfo;
table.ApplyLogOnInfo(tableLogOnInfo);
}
}
You should be able to compile the application now. Right
click on the project in the Solution Explorer and click Build. If you
encounter any errors you should fix them before moving on.
39. Now
you need to add the App.config file that will contain the settings the
application is looking for. Right click on the project in the Solution
Explorer and select AddàNewàItem from the pop-up menu.
40. Select
Application Configuration File from the list of templates and click the Add
button.
41. The
app.config file should have been added to the project.
42. Add
the following elements in the configuration section.
<appSettings>
<add key="TimerInterval" value="60000"/>
<add key="EmailServer" value="YOURMAILSERVER"/>
<add key="ReportPath" value="C:\YOURREPORTPATH\"/>
<add key="FromAddress" value="YOUREMAILADDRESS"/>
<add key="ConnectionString"
value="Data Source=YOURSERVER;User
ID=aspalliance;Password=aspalliance;Initial Catalog=AutomatedReports;"/>
</appSettings>
43. You'll
have to set the EmailServer to your server, the report path to where the
Invoice.rpt file is located, the FromAddress to a valid from address, and the
Data Source to the database server where the AutomatedReports database is
located.