As we all are developers we have various tasks to do in the company. In a similar way one of our clients had a requirement that he should get an email with sales orders every 2 hours. For completing his requirement I have created a Sql Procedure which gets records, and Using ClosedXML I created an excel file on the fly and then I have attached that file in the email to send to clients. For doing this process every two hours I am using Task Scheduler and Schedule task for sending mail every two hours.

image

Platform Used

I had developed this Application using Microsoft Visual Studio Ultimate 2012 with Microsoft SQL server 2008 and to create Excel I have Used Closed XML.

Database Part

Before starting to create Console Application first we are going to have a look at the Database part of it.

For showing this demo I am using database [AdventureWorks2008R2] which is free to download.

Download sample database used for demo. Below is a table of SalesOrderDetail which gets order details which we are going to send periodically to client.

Table SalesOrderDetail

SalesOrderDetail

And store Procedure which is used for getting records from this table .

Store Procedure GetSalesDetails

  1. Create Proc [dbo].[GetSalesDetails]
  2. as
  3. begin
  4. SELECT TOP 20 [SalesOrderID]
  5. ,[SalesOrderDetailID]
  6. ,[CarrierTrackingNumber]
  7. ,[OrderQty]
  8. ,[ProductID]
  9. ,[SpecialOfferID]
  10. ,[UnitPrice]
  11. ,[UnitPriceDiscount]
  12. ,[LineTotal]
  13. ,[ModifiedDate]
  14. FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  15. end
Finally we have completed with database part.

Let’s start with Creating Console Application.

Creating Console Application

From Start page select File - New - Project.

After that new dialogs pop up from that select Templates - Visual C# - Windows.

The select Template Console Application and Name your Project as “MailScheduler” and finally click on ok button.

ok

After doing this a complete empty Project template will be create with Program.cs file.

template

After creating Project the first thing we are going to do is create a connection with the database such that we can get data from database which we want to send for doing that lets add App.config.

Adding App.config

To add App.config just right click on “MailScheduler” project then select Add and inside that select new item after that a new dialog will pop up with name “Add New Item;” after that in Search Installed Template search App.config and select Application Configuration File and click on Add button.

Add

After adding App.config now we are going to add Connection string and app setting to this App.config.
  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <configuration>
  3. <connectionStrings>
  4. <add name="Local"
  5. connectionString="Data Source=Sai-PC;
  6. Initial Catalog=AdventureWorks2008R2;
  7. Persist Security Info=True;
  8. User ID=Sa;Password=########;
  9. Min Pool Size=0;
  10. Max Pool Size=10000;
  11. Pooling=true;"
  12. providerName="System.Data.SqlClient" />
  13. </connectionStrings>
  14. <appSettings>
  15. <add key="MailFrom" value="sai#######@gmail.com" />
  16. <add key="Host" value="smtp.gmail.com" />
  17. <add key="Port" value="587" />
  18. </appSettings>
  19. </configuration>
After completing adding connection string and app setting to this App.config now we are going to add a Class and method to access data from database using simple ADO.NET.

Adding Class [GetExcelFile] to project

For adding class just right click on project then select add inside that select New item and from template select class and then name class as “GetExcelFile.cs”.

class

Snapshot after adding class GetExcelFile.

GetExcelFile

After adding “GetExcelFile.cs” now let add a Method in this class for getting data from sales table.

Adding Method getData to GetExcelFile class.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.SqlClient;
  5. using System.Linq;
  6. using System.Text;
  7. using System.Threading.Tasks;
  8. using System.Configuration;
  9. namespace MailScheduler
  10. {
  11. public class GetExcelFile
  12. {
  13. public void getData()
  14. {
  15. DataSet ds = null;
  16. using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Local"].ConnectionString))
  17. {
  18. try
  19. {
  20. SqlCommand cmd = new SqlCommand("GetSalesDetails", con);
  21. cmd.CommandType = CommandType.StoredProcedure;
  22. SqlDataAdapter da = new SqlDataAdapter();
  23. da.SelectCommand = cmd;
  24. ds = new DataSet();
  25. da.Fill(ds);
  26. } catch (Exception)
  27. {
  28. throw;
  29. } finally
  30. {
  31. ds.Dispose();
  32. }
  33. }
  34. }
  35. }
  36. }
Now we have got data from database now we need to export this to excel file for doing that we are going to add a package from NuGet Package.

Add NuGet Package [ClosedXML]

What is ClosedXML?

ClosedXML makes it easier for developers to create Excel 2007/2010 files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB).

For adding package from NuGet just right click on solution then select Manage NuGet Package from list.

Description reference.

reference

After selecting a new dialog will pop up of Manage NuGet Packages from that select online Menu from right, Then in search online box just enter ClosedXML after that in package select first one ClosedXML and click on installed button.

ClosedXML

After successfully adding ClosedXML it should show a Right green mark.

ClosedXML

After completing adding ClosedXML now we are going to write a method for creating an Excel in specific folder.

Add Method to GetExcelFile Class for creating Excel using Dataset

In this method we are going to get data from dataset and using ClosedXML we are going to create excel file in specific folder in application for that I have created a folder in application with name “ExcelFiles”.

ExcelFiles
  1. private void ExportDataSetToExcel(DataSet ds)
  2. {
  3. string AppLocation = "";
  4. AppLocation = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
  5. AppLocation = AppLocation.Replace("file:\\", "");
  6. string file = AppLocation + "\\ExcelFiles\\DataFile.xlsx";
  7. using(XLWorkbook wb = new XLWorkbook())
  8. {
  9. wb.Worksheets.Add(ds.Tables[0]);
  10. wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
  11. wb.Style.Font.Bold = true;
  12. wb.SaveAs(file);
  13. }
  14. }
To write Excel to specific folder we must get path to that folder where we want to write this Excel file below is code snippet for doing this.
  1. string AppLocation = "";
  2. AppLocation = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
  3. AppLocation = AppLocation.Replace("file:\\", "");
  4. string file = AppLocation + "\\ExcelFiles\\DataFile.xlsx";
We are going to write this Excel file with name “DataFile.xlsx”.

GetExcel class code Snippet

Now we need to call ExportDataSetToExcel() Method in getData() Method.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.SqlClient;
  5. using System.Linq;
  6. using System.Text;
  7. using System.Threading.Tasks;
  8. using System.Configuration;
  9. using ClosedXML.Excel;
  10. namespace MailScheduler
  11. {
  12. public class GetExcelFile
  13. {
  14. public void getData()
  15. {
  16. DataSet ds = null;
  17. using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Local"].ConnectionString))
  18. {
  19. try
  20. {
  21. SqlCommand cmd = new SqlCommand("GetSalesDetails", con);
  22. cmd.CommandType = CommandType.StoredProcedure;
  23. SqlDataAdapter da = new SqlDataAdapter();
  24. da.SelectCommand = cmd;
  25. ds = new DataSet();
  26. da.Fill(ds);
  27. ExportDataSetToExcel(ds);
  28. } catch (Exception)
  29. {
  30. throw;
  31. } finally
  32. {
  33. ds.Dispose();
  34. }
  35. }
  36. }
  37. private void ExportDataSetToExcel(DataSet ds)
  38. {
  39. string AppLocation = "";
  40. AppLocation = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
  41. AppLocation = AppLocation.Replace("file:\\", "");
  42. string file = AppLocation + "\\ExcelFiles\\DataFile.xlsx";
  43. using(XLWorkbook wb = new XLWorkbook())
  44. {
  45. wb.Worksheets.Add(ds.Tables[0]);
  46. wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
  47. wb.Style.Font.Bold = true;
  48. wb.SaveAs(file);
  49. }
  50. }
  51. }
  52. }
After calling this method we have completed with creating Excel file on fly now we are going send this file with Mail as attachment.

Adding New Class and Method to SendMail

For adding class just right click on project then select add inside that select New item and from template select class and then name class as “SendMail.cs”,

SendMail

Inside that Class we are going to add Method with name SendMail.

Below is code Snippet of SendMail Class.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Configuration;
  4. using System.Linq;
  5. using System.Net;
  6. using System.Net.Mail;
  7. using System.Text;
  8. using System.Threading.Tasks;
  9. namespace MailScheduler
  10. {
  11. public class SendMail
  12. {
  13. public void SendEmail(string MailTo, string MailSubject)
  14. {
  15. try
  16. {
  17. string AppLocation = "";
  18. AppLocation = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
  19. AppLocation = AppLocation.Replace("file:\\", "");
  20. string file = AppLocation + "\\ExcelFiles\\DataFile.xlsx";
  21. MailMessage mail = new MailMessage();
  22. SmtpClient SmtpServer = new SmtpClient(ConfigurationManager.AppSettings["Host"].ToString());
  23. mail.From = new MailAddress(ConfigurationManager.AppSettings["MailFrom"].ToString());
  24. mail.To.Add(MailTo); // Sending MailTo
  25. List < string > li = new List < string > ();
  26. li.Add("[email protected]");
  27. //li.Add("[email protected]");
  28. //li.Add("[email protected]");
  29. //li.Add("[email protected]");
  30. //li.Add("[email protected]");
  31. mail.CC.Add(string.Join < string > (",", li)); // Sending CC
  32. mail.Bcc.Add(string.Join < string > (",", li)); // Sending Bcc
  33. mail.Subject = MailSubject; // Mail Subject
  34. mail.Body = "Sales Report *This is an automatically generated email, please do not reply*";
  35. System.Net.Mail.Attachment attachment;
  36. attachment = new System.Net.Mail.Attachment(file); //Attaching File to Mail
  37. mail.Attachments.Add(attachment);
  38. SmtpServer.Port = Convert.ToInt32(ConfigurationManager.AppSettings["Port"]); //PORT
  39. SmtpServer.EnableSsl = true;
  40. SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network;
  41. SmtpServer.UseDefaultCredentials = false;
  42. SmtpServer.Credentials = new NetworkCredential("Email id of Gmail", "Password of Gmail");
  43. SmtpServer.Send(mail);
  44. } catch (Exception ex)
  45. {
  46. throw ex;
  47. }
  48. }
  49. }
  50. }
Let’s understand code snippet of SendMail

Part 1

In this code snippet we are getting Excel file path and then attach this file to Mail.
  1. string AppLocation = "";
  2. AppLocation =
  3. System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
  4. AppLocation = AppLocation.Replace("file:\\", "");
  5. string file = AppLocation + "\\ExcelFiles\\DataFile.xlsx";
Part 2

In this code snippet we are sending Mail.

For sending mail we require Host name.
  1. <appSettings>
  2. <add key="Host" value="smtp.gmail.com" />
  3. </appSettings>
  5. MailMessage mail = new MailMessage();
  6. SmtpClient SmtpServer = new SmtpClient(ConfigurationManager.AppSettings["Host"].ToString());
Part 3

For setting mail from and Mail to,
  1. mail.From = new MailAddress(ConfigurationManager.AppSettings["MailFrom"].ToString());
  2. mail.To.Add(MailTo); // Sending MailTo
Part 4

For setting CC and BCC (required mail in comma separated format),
  1. List<string> li = new List<string>();
  2. li.Add("[email protected]");
  3. li.Add("[email protected]");
  4. li.Add("[email protected]");
  6. mail.CC.Add(string.Join<string>(",", li)); // Sending CC
  8. mail.Bcc.Add(string.Join<string>(",", li)); // Sending Bcc
Part 5

For setting Mail Subject and Mail Body,
  1. mail.Subject = MailSubject; // Mail Subject
  3. mail.Body = "Sales Report *This is an automatically generated email, please do not reply*";
Part 6,

For setting Attachment.
  1. System.Net.Mail.Attachment attachment;
  2. attachment = new System.Net.Mail.Attachment(file); //Attaching File to Mail
  3. mail.Attachments.Add(attachment);
Part 7

For setting port number and Gmail ID Credentials for sending mail.
  1. <appSettings>
  2. <add key="Port" value="587" />
  3. </appSettings>
  5. SmtpServer.Port = Convert.ToInt32(ConfigurationManager.AppSettings["Port"]); //PORT
  6. SmtpServer.EnableSsl = true;
  7. SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network;
  8. SmtpServer.UseDefaultCredentials = false;
  9. SmtpServer.Credentials = new NetworkCredential("Email id of Gmail", "Password of Gmail");
  10. SmtpServer.Send(mail);
After completing send mail now let’s call this method in main.

Main method Code snippet

Here we have called both class methods.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  6. namespace MailScheduler
  7. {
  8. class Program
  9. {
  10. static void Main(string[] args)
  11. {
  12. GetExcelFile GF = new GetExcelFile();
  13. GF.getData();
  14. SendMail Sm = new SendMail();
  15. Sm.SendEmail("[email protected]", "Auto Mail");
  16. }
  17. }
  18. }
Now we have completed code part let’s checking this method in debug view.

Debug View

Below snapshot of Dataset while getting data,

Dataset

Below snapshot of Creating Excel file in specific folder on fly,

folder

Below snapshot of Main Method while debugging,

Method

Below snapshot of Sending Mail Method

I have pinned some variable while debugging to showing real time values.

values

Mail received to my Gmail.

Mail

Mail

Mail

Now we have Sent Mail now let’s schedule this to Task Scheduler to send this to client in every 2 hour.

Scheduling Task

Just in windows Run type task then select Task Scheduler.

Scheduler

After selecting Task Scheduler a new dialog will pop up with name Task Scheduler.

Task Scheduler view
Scheduler

From Task Scheduler dialog there is Actions panel inside that select Create Task.

After selecting it will open new dialog inside that it will ask for task Name I have named as “MailTask” then need to enter Description “Mail Scheduler for Every 2 hours” this will help you to remember for what purpose you have create this Scheduler.

Then in Security option we are going to choose “Run whether user is logged on or not” below we are also going to check checkbox.

Finally we want this scheduler to run on high privileges for that we are going to check checkbox.

After doing all setting click on OK button.

You setting you can see below snapshot.

Create Task view
view

After clicking on OK button it will show a message to add a Task click on Ok button.

Task

Create Action view Now after clicking it will take you to Action tab.

Create

In New action tab just select new button [New] a new dialog will pop up as shown in below snapshot.

action

After in Action we are going to select “Start a Program”.

Then click on browse option for selecting program which should run on scheduling we are going to select .exe of our project which should run after every hour.

The .exe [MailScheduler.exe] is located in your bin folder of project and its name is same name as project name. In below snapshot I am displaying how to select .exe [MailScheduler.exe] file.

Selecting program view (selecting MailScheduler.exe),

program

add

After selecting program just click on ok button a similar dialog must appear.

View after (selecting MailScheduler.exe)
actions

If you want to edit or delete this action you can select button below,

Let’s now we are moving forward to set trigger.

Setting Trigger

For that select Trigger tab as shown below.

Trigger

In New Trigger tab just select new button [New] a new dialog will pop up as shown in below snapshot.

Trigger

Settings

Inside setting we are going to select Daily option then it will be a Recur every 1 days.

Advanced settings

Select checkbox “Repeat task every” from dropdown select 2 hours if it is not there then you can enter it.

Then to set “for a duration of “from dropdown select 1 day.

Finally click on ok button you have successfully configured scheduler.

After scheduling it should display in Active Task Panel.

Panel

Final mail after scheduling.
Final mail
Finally we have mate Client Requirement.