How to Create report by Scheduling with windows service using C#

Introduction

The purpose of this article is to illustrate how to create report by the scheduled time. Sometimes users need to generate report without GUI, but with scheduled time. We can easily do it with Windows Service, since it provides timer which we can set schedule time the same time it does not have a user interface.

Without reports any financial application should not become 100% completed system and desired system by the user. Because of end user always needs to view the data in simple format, say they don't read or view you primary key data, date column data and so on. Through this article, I will explain how to generate semi-automated report using scheduling service in .NET with crystal report.

Prerequisites

• Sql server 2005/2008 express
• Visual studio 2005/2008/2010
• Crystal Report X or later version

Implementation

Database Design

Before we start to implement scheduling service and create report, we have to setup the database to create report in later on this article demonstration.I have created a simple database using sql server 2005 and added a table with few columns. The table name is “BookInfo”.

Create Windows Service

Lets create a windows service project using visual studio 2008,in the project template collection select windows service called as ” ReportWithWidowsService”. You can download sample project at end of this article. 

Now we have to configure windows service in order to run in windows operating system. If you are aware of creating windows service in .NET, please read following,

1. http://www.codeproject.com/KB/system/WindowsService.aspx

Design Report

Let’s add new report into project, to this right click in project and select add item and then select Reporting in left hand side list items.then new wizard come up to further settings and more. 

Now you have to select the report wizard; it’s the Microsoft report control for .NET Framework, and name the report “BookInforReport”. Click the add button add to your project.

When clicked the add button shows a message window like the one below.  It’s just information on what you need to do, in order complete report. 

Then click next button to continue in the report creating process.Now we need to select which type of data source we need to set to the report. It’s given below:

Next, we will select the data source.  Because our goal is creating reports with automated windows service,so we need to select a simple data source process by object or service.  we have e to select the service or object in this case, this demostration i'm using Object data source then click next button to continue.

Now we’ve selected data source and we need to create a connection string to access the database.

In this window, if you don’t have the database connection already configured then you have to create a new connection with the “new connection” button. I have created already connection with database, then click next button to continue.

Then we need to select which table or view to use to build this report with our data source. 

Now you have to select a table to design report and given name to the dataset call as “RealworldDataSet”.Then click finish button to finish data source assign processing to report and start the next process (when you are click finish it closes and focuses on the second window which you have opened in the first step).

Now you ready to attach this data source to the report. 

Now you have to select which table is/are used to create the report.  You can add new data sources also by clicking “add data source” but here we don’t need add another data source because the one you have previously added is sufficient to process. Click next button to continue.

And this point you ignore two steps which are related to formatting reports.   After you pass the formatting and grouping windows then finally you should see the report as like following

Once everything is fine click finish button to add report file in your project. The final design view of the report as like below, 

The design process is completed. Now we have to write code to fetch data and set into report as data source. Before set the data source into report, you have to write code for setup the timer to schedule the reporting interval base.

Setup Timer

Add a timer object into the class and create timer object like below,

timerdataFetcher.Interval = int.Parse(ReportWithWindowsService.Properties.Settings.Default.TimeInterval);
timerdataFetcher.Enabled = true;
timerdataFetcher.Tick += new EventHandler(timerdataFetcher_Tick);
timerdataFetcher.Start();

Now timers ready with interval, every interval of the timer event, you have to connect to database and fetch records from table set into report. And also export report as PDF format to save on local file directory in order to send email with attachment to your client or users automatically.

C# Code

private void BuildReport()
        {
            LocalReport rpvbookInfor = new LocalReport();

            //declare connection string
            string connectionString = "Data Source=CONNECTIONSTRING";

            DataSet dsReport = new RealWorldDataSet();
            try
            {
                using (SqlConnection conReport = new SqlConnection(connectionString))
                {
                    if (conReport.State != ConnectionState.Open)
                    {
                        //open connection
                        conReport.Open();
                    }
                    using (SqlCommand cmdReport = new SqlCommand())
                    {
                        //prepare connection object to get the data through reader and populate into dataset
                        cmdReport.CommandType = CommandType.Text;
                        cmdReport.Connection = conReport;
                        cmdReport.CommandText = "Select * FROM BooksInfo";

                        using (SqlDataReader drReport = cmdReport.ExecuteReader())
                        {
                            dsReport.Tables[0].Load(drReport);
                        }
                    }
                }
                if (dsReport.Tables[0].Rows.Count == 0)
                {
                    return;
                }
                //provide local report information to viewer
                rpvbookInfor.ReportEmbeddedResource = "ReportWithWindowsService.BookInforReport.rdlc";

                //prepare report data source
                ReportDataSource bookreportrds = new ReportDataSource();
                bookreportrds.Name = " RealWorldDataSet_BooksInfo";
                bookreportrds.Value = dsReport.Tables[0];
                rpvbookInfor.DataSources.Add(bookreportrds);

                Warning[] warnings;
                string[] streamids;
                string mimeType;
                string encoding;
                string filenameExtension;

                // here we need to specify the report format and attributes.
                byte[] bytes = rpvbookInfor.Render(
                    "PDF", null, out mimeType, out encoding, out filenameExtension,
                    out streamids, out warnings);

                using (FileStream fs = new FileStream(@"D:\output.pdf", FileMode.Create))
                {
                    fs.Write(bytes, 0, bytes.Length);
                }

                //send newly create pdf file as email attachment
                //SendMail("you smtp server", "BookInfo", SettingsComplaint.Default.EmailTo, "Service Stoped!", "Contact IT support", "output.pdf");
            }
            catch (Exception ex)
            {
                string strInitialError = "Initial Error Message: " + ex.Message;

                string FinalErrorMessage = string.Empty;
                Exception innerError = ex.InnerException;
                while (!((innerError == null)))
                {
                    FinalErrorMessage += innerError.Message;
                    innerError = innerError.InnerException;
                }

                //write the error message to text file
                using (StreamWriter sw = new StreamWriter(@"c:\ServiceLog.txt"))
                {
                    sw.WriteLine(strInitialError);
                    sw.WriteLine("-------------------");
                    sw.WriteLine(FinalErrorMessage);
                }
            }

        }

In above code there are many key points to take note.

 1.    Create report object

LocalReport rpvbookInfor = new LocalReport();

2.    Then you have to connect to database and fetch data from Bookinfo table create a Data table.

using (SqlConnection conReport = new SqlConnection(connectionString))
                {
                    if (conReport.State != ConnectionState.Open)
                    {
                        //open connection
                        conReport.Open();
                    }
                    using (SqlCommand cmdReport = new SqlCommand())
                    {
                        //prepare connection object to get the data through reader and populate into dataset
                        cmdReport.CommandType = CommandType.Text;
                        cmdReport.Connection = conReport;
                        cmdReport.CommandText = "Select * FROM BooksInfo";

                        using (SqlDataReader drReport = cmdReport.ExecuteReader())
                        {
                            dsReport.Tables[0].Load(drReport);
                        }
                    }
                }

3.    Set designed Report file to Local report object

//provide local report information to viewer
rpvbookInfor.ReportEmbeddedResource = "ReportWithWindowsService.BookInforReport.rdlc";

4.    Set DataTable object to report object add as data source

ReportDataSource bookreportrds = new ReportDataSource();
bookreportrds.Name = " RealWorldDataSet_BooksInfo";
bookreportrds.Value = dsReport.Tables[0];
rpvbookInfor.DataSources.Add(bookreportrds);

 5.    Export report as PDF format in order to send as attachment with email

byte[] bytes = rpvbookInfor.Render(
                    "PDF", null, out mimeType, out encoding, out filenameExtension,
                    out streamids, out warnings);

using (FileStream fs = new FileStream(@"D:\output.pdf", FileMode.Create))
{
fs.Write(bytes, 0, bytes.Length);
}

Send email With Report Attachment

Next you have to write code for send email in .Net with attachment of generated report by service.

C# Code

public void SendReportByMail(string mailServerName,
            string mailFrom, string MailTo, string subject,
            string body, string fileName)
        {
            try
            {
                //MailMessage represents the e-mail being sent
                using (MailMessage message = new MailMessage(mailFrom,
                       MailTo, subject, body))
                {
                    message.IsBodyHtml = true;
                    message.Attachments.Add(new Attachment(fileName));
                    SmtpClient mailClient = new SmtpClient();
                    mailClient.Host = mailServerName;
                    mailClient.Port = 25;
                    mailClient.UseDefaultCredentials = true;
                    mailClient.DeliveryMethod = SmtpDeliveryMethod.PickupDirectoryFromIis;
                    //Send delivers the message to the mail server
                    mailClient.Send(message);
                }
            }
            catch (SmtpException ex)
            {
                throw new ApplicationException
                   ("Smtp error sending mail: " + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        
}

Now implement is finish, next you have to create setup windows to install and test service. I leave this with you guys. Further reading create setup for windows service,

1. http://www.codeproject.com/KB/system/WindowsService.aspx
2. http://arcanecode.wordpress.com/2007/05/23/windows-services-in-c-adding-the-installer-part-3/

Once you are setup done and run service, generated report will like followings,

Download Sample Project

Download source files -1809 kb

Conclusion

In this article I have explain step by steps how to create report using Microsoft report and then create windows schedule service to generate report automatically and export as PDF and send as email attachment.I hopes this is help.