Automate delivery of Crystal Reports With a Windows Service
page 3 of 5
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 52198/ 125

Step 2: Create the Window Service

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.


View Entire Article

User Comments

Title: great article!   
Name: mira minkova
Date: 2013-01-07 6:09:56 AM
Comment:
very detailed and easy to follow! Thank you!
Title: get or set accessor expected?   
Name: Mike
Date: 2010-07-29 12:52:00 PM
Comment:
I'm trying to use this example to code a service and I'm receiving a "get or set accessor expected" exception in my RunReport method when I try to instantiate a ReportDocument object...

Any ideas on what the problem might be?

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-03-25 6:04:40 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search