Midnight Cubicle

Few days back,in my free time I created a Windows Service for sending birthday wishes to all my office colleagues through email. In this windows service data is fetched from the database table and emails are sent to all the persons whose birthday falls on the particular day.

The service start whenever the system starts automatically whenever system starts and send email and then stop automatically after some time interval.

Database table:

Following is the database table used for storing records.

Step for creating service:

Step 1: Open Visual studio and create a new Windows Service project. Name it whatever you want

Step 2:  By default there will be a file called Service1.cs, u can also rename it according to the requirement. ( I renamed it Birthday Wisher).

Step 3: Right click on this file and select View Code. It will open the code behind file of the service.

Note: It you are new to windows services, I will suggest you should go through this post about the basics of Windows Services.

Step 4: Add the following namespaces in the BirthdayWisher.cs

</pre>
using System.Text;
 using System.Data.SqlClient;
 using System.Net.Mail;
 using System.IO;
 using System.Configuration;

Note: After adding these namespaces in the header. Please check the References folder of the project. If it contains reference to “System.configuration” them its fine otherwise add the reference.

Step 5: Now right click on the Project Folder -> Add ->New Item and add a new Application Configuration File and name it as “App.config” then add this set of code in the config file:

<configuration>
<connectionStrings>
<add name="ConnectionString" connectionString="server=servername;uid=username;pwd=password;database=databasename;"/>
</connectionStrings>
<appSettings>
<add key="MailFromAddress" value="abc@abc.com"/>
<add key="MailServer" value="IP address of your mail server"/>
<add key="DebugMode" value="true" />
<add key="ServiceRunInterval" value="10000"/>
<add key="LogFilePath" value="Path for storing the Log file"></add>
</appSettings>
</configuration>

Step 6: This will be the default code in the service code behind file. Now create a new instance of Timer as shown in the code.

public partial class BirthdayWisher : ServiceBase
{
System.Timers.Timer createOrderTimer;                //Create a new instance of Timer.

public BirthdayWisher()
{
InitializeComponent();
}

protected override void OnStart(string[] args)
{
}

protected override void OnStop()
{
}

}

Step 7: Add the following code to the OnStart event.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
string selectCmd = "select id,name,email,dob,isMailSent from RSJ_bdrecord";
SqlDataAdapter ad = new SqlDataAdapter(selectCmd,con);
DataTable dt = new DataTable();

try
{
ad.Fill(dt);                //Fill the datatable with the rows
}

catch(Exception ex)
{
WriteToLogFile(ex.Message);               //In case of any exception it call function WriteToLogFile for logging the exception. We will see this function shortly.
}

foreach (DataRow dr in dt.Rows)
{
if (Convert.ToInt32(dr["isMailSent"]) == 0)         //Checks whether mail is already sent or not.
{
DateTime dtDOB = (DateTime)dr["dob"];
DateTime today = DateTime.Now;
string dow = today.DayOfWeek.ToString().ToLower();
if (dow == "monday")                                                   //If today is monday then check for previous saturday and sunday also.
{
DateTime lastSunday = today.AddDays(-1);
DateTime lastSaturday = today.AddDays(-2);
if ((dtDOB.Day == today.Day || dtDOB.Day == lastSaturday.Day || dtDOB.Day == lastSunday.Day) && dtDOB.Month ==today.Month)
{
sendmail(dr);                                               //Call the function sendmail for sending mail.
}
}
else
{
if (dtDOB.Day == today.Day && dtDOB.Month == today.Month)
{
sendmail(dr);
}
}

updateRecord(dr);                                   //After sending mail it call update function which updates the isMailSent column.
    }
}

createOrderTimer = new System.Timers.Timer();                       //Set up the timer

//set the interval,make the timer enabled and assign the elapsed event of timer
createOrderTimer.Interval = Convert.ToDouble(ConfigurationManager.AppSettings["ServiceRunInterval"].ToString());
createOrderTimer.Enabled = true;
createOrderTimer.Elapsed += new System.Timers.ElapsedEventHandler(createOrderTimer_Elapsed);

This code connects to the database table. Fetch all the rows from the table and foreach row it checks whether mail is already sent or not if not then checks whether today is persons birthday or not, if it is then call the sendmail function (this will see this function shortly) for sending the mail. It is having one more condition that is if today is monday then it also check the the birthdays that fall on saturday and sunday and send mail to them also. After that it calls the function “UpdateRecord” for updating the “isMailSent” column and finally it creates a timer which will be used for stopping the service.

Step 8:  Create a function for sending mail names “sendmail” and copy the below cod in it.

public void sendmail(DataRow dr)
{
String mailServer = ConfigurationManager.AppSettings["MailServer"].ToString();
String mailtxt="";
MailMessage mail = new MailMessage();
mail.IsBodyHtml = true;
mail.To.Add(dr["email"].ToString());
mail.From = new MailAddress(ConfigurationManager.AppSettings["MailFromAddress"].ToString());
mail.Subject = "Happy Birthday";
mailtxt = "<font face='verdana' color='#FF9900'><b>"+"Hi "+dr["name"].ToString()+"," + "</b></font><br><br>";
mailtxt=mailtxt+"<font face='verdana' color='#FF0000'><b>"+"Wishing you a very HAPPY BIRTHDAY........and many more." + "</b></font><br><br>";
mailtxt=mailtxt+"<font face='verdana' color='#008080'><b>"+"May today be filled with sunshine and smile, laughter and love." + "</b></font><br><br>";
mailtxt=mailtxt+"<font face='verdana' color='#0000FF'><b>Cheers!" + "<br><br>";
mail.Body = mailtxt;
mail.Priority = MailPriority.Normal;
SmtpClient client = new SmtpClient(mailServer);

client.Send(mail);
}

Note: You may be thinking why we are keeping the track of to whom mail is already sent or not. The answer is because system can start many times in a day and every-time system starts, service also start automatically and if date matches it will sent, we surely don’t want this. So in order to avoid multiple mail we are keeping track of whether mail is sent or not.

Step 9: Create another function for updating records.

public void updateRecord(DataRow dr)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
    string updateCmd = "update RSJ_bdrecord set isMailSent=1 where id=@ID";
    SqlCommand cmd = new SqlCommand(updateCmd, con);
    cmd.Parameters.AddWithValue("@ID", dr["id"]);
    try
    {
        con.Open();
        cmd.ExecuteNonQuery();              //Updating the isMailSent column.
    }
    catch (Exception ex)
    {
        WriteToLogFile(ex.Message);         //In case of any exception write in the log file.
    }
    finally
    {
        con.Close();
    }
}

Step 10: Function for writing to the log file.

public void WriteToLogFile(string content)
{
    //if the directory does not exist create the directory
    if (!(Directory.Exists(ConfigurationManager.AppSettings["LogFilePath"].ToString())))
    {
        Directory.CreateDirectory(ConfigurationManager.AppSettings["LogFilePath"].ToString());
    }

    //get the path for the log file
    StringBuilder strLogFilePath = new StringBuilder();
    strLogFilePath.Append(ConfigurationManager.AppSettings["LogFilePath"].ToString());
    strLogFilePath.Append(@"\LogFile.txt");

    //if the file doesnot exist create the file
    if (!File.Exists(strLogFilePath.ToString()))
    {
        FileStream fsErrorLog = new FileStream(strLogFilePath.ToString(), FileMode.OpenOrCreate, FileAccess.ReadWrite);
        fsErrorLog.Close();
    }

    //write to the log file
    StreamWriter writer = new StreamWriter(strLogFilePath.ToString(), true);
    writer.WriteLine("********************************************************************");
    writer.WriteLine("Date " + DateTime.Now);
    writer.WriteLine("Content: " + content);
    writer.WriteLine("********************************************************************");
    writer.Close();
}

Step 11: Now the last function. Create a new function which is called when timer stops.

protected void createOrderTimer_Elapsed(object sender, System.Timers.ElapsedEventArgs args)
{
    //Stop the timer
    createOrderTimer.Stop();

    ServiceController controller = new ServiceController("BirthdayWisher");

    // Stop the service
    controller.Stop();
}

Step 12: Now we are done with the coding part. Finally, create an installer for this service and install the service in your system. For details about creating installer and installing the service refer to this post. Creating Windows Service in C#

Note: Set the start type to Automatic in the service installer. So that it can start automatically whenever system starts.

You can also add an event to check if it is “31st of December”. If it is them update table and set”isMailSent” column’s value equal to 0(Zero), which makes our table ready for the next year.

This is it for now….

Happy Coding….