Automate delivery of Crystal Reports With a Windows Service
 
Published: 16 Feb 2010
Abstract
In this article, Vince demonstrates the creation of a Windows Service to automatically run and send a Crystal Report as an email attachment. After a basic introduction, he examines the creation of the database and windows service with the help of relevant source code and explanations. Towards the end of the article, Vince discusses the steps to be followed in order to install the windows service.
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 40585/ 65

Introduction

Crystal Reports is a terrific tool that allows you to easily incorporate professional style reporting in your applications.  The Crystal Reports designer is easy to use and the Report Viewer Control makes it easy to display reports. It also allows users to navigate through the report, export it to a file, jump to specific groups, and even search the report.  But, for as long as I've been developing applications and especially reports, users always seem to want more.  A common request that I get is to run a report automatically and have it emailed on a scheduled basis.

This article will show you how to do just that.  I'll create a Windows Service using Visual Studio 2008 and C#.  I'll create a database to store which reports need to be sent, to who, and when.  For this sample I'm going to use the Invoice report that was created in "Building an Invoice Application with ASP.NET and Crystal Reports - Part 1".  It would be helpful for you to have read this article first but it is not necessary.  All the code for this article can be found here.

Before you begin, you will need to have installed Visual Studio 2008 with Crystal Reports for .NET.  The samples are written in Visual Studio 2008 but they will work with Visual Studio 2005.  You also need to download the AdventureWorks sample database from http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=34032 for SQL Server 2008.  Download and install the SQL2008.AdventureWorks_All_Databases.x86.msi file.  If you do not have SQL Server 2008 you can use SQL Server 2005 but you'll need to download the 2005 AdventureWorks samples.  You will use the AdventureWorks database to build this report.

The Invoice report must be downloaded for this sample to work.  This is the Crystal Report report file that was created in the previous article.  You can download it here.  Download the zip file and unzip the Invoice.rpt file.  Make sure to remember where you saved the Invoice.rpt file because you'll set this path in the app.config file later in this article.

Step 1: Create the Database

First we'll create a database to store the information about the reports and the report schedule. 

1.    Open SQL Server Management Studio and create a new database called AutomatedReports.

2.    Create a new SQL Login and grant them access to this database.  The easiest way to give them access is to put them in the db_owner role.  For this example I created a user called aspalliance and gave them a password of aspalliance.

There are three tables in this database. 

Table: Report

Field

DataType

Allow Nulls

Description

Id

Int

N

Identity, Primary Key

ReportFile

Varchar(50)

N

The Crystal Report File Name

ReportName

Varchar(50)

N

A friendly name for the report.  This will appear in the subject of the email.

RecordSelectionFormula

Varchar(max)

Y

Optional.  The selection formula for the report. 

ExportFormat

Tinyint

N

Maps to the CrystalDecisions.Shared.ExportFormatType enumeration

1=Crystal Report

2=RichText

3=WordForWindows

4=Excel

5=PortableDocFormat

6=HTML32

7=HTML40

8=ExcelRecord

Server

Varchar(50)

N

The database server that the report should connect to.

DBName

Varchar(50)

N

The database that the report should connect to.

UserId

Varchar(50)

N

The user id the report should connect the database as.

Password

Varchar(50)

N

The password for the user id.

 

Table: Schedule

Field

DataType

Allow Nulls

Description

Id

Int

N

Identity, Primary Key

ReportId

Int

N

Foreign Key to the report table

NextRunDateTime

DateTime

Y

The next date and time the report is supposed to run.

 

Table: Recipient

Field

DataType

Allow Nulls

Description

Id

Int

N

Identity, Primary Key

ReportId

Int

N

Foreign Key to the report table

EmailAddress

Varchar(50)

N

The email address to send the report

RecipientType

Tinyint

N

0=To

1=CC

2=BCC

 

3.    Create each table in the database.

This article will allow the user to automatically deliver an Invoice report in both PDF and Excel format.  The report will be sent as an attachment in an email.  You'll need to add two records to the Report table to accomplish this.

Report

File

Report

Name

RecordSelection

Formula

Export

Format

Invoice.rpt

Invoice for Abel, Catherine.pdf

{SalesOrderHeader.ContactID} = 2

5

Invoice.rpt

Invoice for Abel, Catherine.xls

{SalesOrderHeader.ContactID} = 2

8

Be sure to set the correct Server, DBName, UserId, and Password for the AdventureWorks database since the Invoice report is built using the AdventureWorks database.

Now add records to the Recipient table.  You simply need to add the Id of the Report record and a valid Email Address.  You should create at least one record for each report.

Next you should add a record in the Schedule table for each report.  All you need to do is add the Id of the Report and a DateTime in the NextRunDateTime field.  Make the NextRunDateTime less than the current date so the report will run the first time you run the service.

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.

Step 3: Install the Windows Service

The last step is to install the Windows Service and run it.  There are a few steps you need to follow in order to have the service install correctly.

1.    Right click on the Service1.cs file and select View Designer from the pop-up menu.

2.    Right click on the Designer and select Add Installer from the pop-up menu.  A new file will be added to the project called ProjectInstaller.cs.

3.    Click on the serviceProcessInstaller1 icon on the designer and view its properties.

4.    You can change the Account property to specify the account the Windows Service should run as when it is executed.  Change the Account to LocalService.

5.    Now click on the serviceInstaller1 icon on the designer and view its properties.

6.    This is where you can define the Description, Display Name, and StartType.  Set the Description to "This service emails reports."  Set the Display Name to "AutomatedReports".  Set the StartType to Automatic.  This will ensure the service start when the computer starts.

7.    Recompile the project again.

8.    Now you can install the service.  To do this you need to use the command line utility call InstallUtil.exe that comes with the Visual Studio.  To open the command prompt click on the Start Button and navigate to All ProgramsàMicrosoft Visual Studio 2008àVisual Studio ToolsàVisual Studio 2008 Command Prompt.

9.    Change the directory to the folder that contains the AutomatedReports.exe file.  This will be in the bin\Debug folder under your project.  If you compiled this for release then it will be under the bin\Release folder.

10. Enter InstallUtil.exe AutomatedReports.exe and press enter.  This will install the executable as a service and it should appear in the Services located in the Administration Tools in Windows.

Summary

This article showed you how to create a Windows Service using Visual Studio 2008 to automate the process of email reports to users.  This can be quite a handy feature for any application.  This is a simple example but you can take this and extend it to put in full featured scheduling and more options when running the report such as dynamic grouping or sorting.

Good luck on your project and happy coding.



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-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 10:56:45 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search