In this section, we will see how to write the Windows Service application used to retrieve the mail details from an SQL Server table and send it to the users. We will start by creating a Visual C# Windows Service application named MailQueueProcessingService as shown in the following dialog box.
Once the project is created, we will rename the service class from Service1 to MailService class. Then we add a timer component to the MailService class and name it timerProcessingService. We also set the Interval property of the timer component to 3000 as shown in the following properties window.
In the Elapsed event of the timer, we will add the following lines of code.
private void timerProcessingService_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
StringBuilder
mailQueueIDs = new StringBuilder();
EventLog.WriteEntry("Elapsed");
//Get all the mails from the database
DataSet
mailDataSet = GetMails();
DataTable
mailTable = mailDataSet.Tables[0];
foreach (DataRow row in
mailTable.Rows)
{
string from = (string)row["FromMailAddress"];
string toList = (string)row["ToMailAddress"];
string ccList = (string)row["CCList"];
string bccList = (string)row["BccList"];
string subject = (string)row["Subject"];
int importance = (int)row["Priority"];
string body = (string)row["Body"];
string attachments = (string)row["Attachments"];
int mailQueueID = (int)row["MailQueueID"];
SendMail(from,toList,ccList,bccList,subject,importance,
body,attachments);
if (mailQueueIDs.Length > 0)
mailQueueIDs.Append(",");
mailQueueIDs.Append(mailQueueID.ToString()); }
//Update the status for all the Sent mails
UpdateMailStatus(mailQueueIDs.ToString());
}
In the above lines of code, we start by retrieving all the unsent mails from the MailQueue table using the helper method named GetMails. We will look at the implementation of this method in a moment. Once we get all the unsent mails in the form of a DataSet, we then get reference to the DataTable object into a local variable. Then we loop through all the rows contained in the DataTable object using a for...each construct. Inside the for...each loop, we invoke the SendMail helper method for every row in the DataTable object to send the mail. While looping through the DataTable, we also append the identifier that is used to identify the row in the MailQueue table to a StringBuilder object. Finally, we invoke another private method named UpdateMailStatus to update the status of those mails from 0 to 1 to indicate that they have already been sent.
GetMails Method
As the name suggests, this method is used to retrieve all the unsent mail from the MailQueue table. To accomplish this, this method executes a stored procedure named usp_GetMailMessage, which is defined as shown below.
CREATE procedure usp_GetMailMessage
as
begin
set nocount on
select * from MailQueue where MailSent = 0
end
The stored procedure returns all the records from the MailQueue table where the MailSent status is 0.
The GetMails method starts off by retrieving the connection string from the database. It then creates an instance of the SqlDataAdapter object and passes in the name of the stored procedure and the SqlConnection object as its arguments. Then it invokes the Fill method of SqlDataAdapter object to execute the stored procedure and return the results of the stored procedure execution in the form of a DataSet object. Finally it returns the DataSet object back to the calling method.
private DataSet
GetMails()
{
System.Configuration.AppSettingsReader
configurationAppSettings = new
System.Configuration.AppSettingsReader();
string connString =
((string)(configurationAppSettings.GetValue("connectionString",
typeof(string))));
DataSet
mailDataSet = new DataSet();
using (SqlConnection sqlConn = new SqlConnection(connString))
{
SqlDataAdapter
adapter = new
SqlDataAdapter("usp_GetMailMessage",sqlConn);
adapter.Fill(mailDataSet);
}
return mailDataSet;
}
UpdateMailStatus method
The UpdateMailStatus method basically updates the status of sent mails from 0 to 1. It does that by executing a stored procedure named usp_UpdateMailMessage, which is defined as follows:
CREATE procedure usp_UpdateMailMessage
@MailQueueIDs varchar(7000)
as
begin
set nocount on
declare @sql varchar(8000)
if @MailQueueIDs = ''
select @MailQueueIDs = null
select @SQL = 'UPDATE MailQueue SET MailSent = 1 WHERE
MailQueueID IN (' + @MailQueueIDs + ')'
exec (@SQL)
end
As you can see from the stored procedure definition, it accepts a comma-separated list of MailQueue Identifiers as an argument. The UpdateMailStatus method executes the stored procedure using an SqlCommand object. After creating the SqlCommand object, it then appends the MailQueueIDs parameter to it using the SqlParameter object.
private void UpdateMailStatus(string
mailQueueIDs)
{
System.Configuration.AppSettingsReader
configurationAppSettings = new
System.Configuration.AppSettingsReader();
string connString =
((string)(configurationAppSettings.GetValue("connectionString",
typeof(string))));
SqlConnection
sqlConn = new SqlConnection(connString);
sqlConn.Open();
SqlCommand
command = new
SqlCommand("usp_UpdateMailMessage", sqlConn);
command.CommandType
= CommandType.StoredProcedure;
//Set the MailQueueIDs Parameter
SqlParameter
paramMailQueueIDs = new
SqlParameter("@MailQueueIDs",
SqlDbType.VarChar,7000);
paramMailQueueIDs.Direction
= ParameterDirection.Input;
paramMailQueueIDs.Value
= mailQueueIDs;
command.Parameters.Add(paramMailQueueIDs); //Execute the stored procedure
command.ExecuteNonQuery();
sqlConn.Close();
}
SendMail Method
The SendMail method is the one that actually sends mails to the users by using the SmtpMail class that is contained in the System.Web.Mail namespace. It does that by creating an instance of the MailMessage object and setting its properties to appropriate values. Some of the important properties exposed by the MailMessage object include From, To, Cc, Bcc, Subject, Body, BodyFormat, and Priority.
private void SendMail(string
from, string toList, string
ccList,string bccList, string
subject, int importance, string body, string
attachments)
{
string attachmentsPath ;
//Convert the integer based importance to MailPriority
MailPriority
priority = (MailPriority)importance;
MailMessage
mail = new MailMessage();
mail.From =
from;
mail.To =
toList;
mail.Cc =
ccList;
mail.Bcc =
bccList;
mail.Subject
= subject;
mail.Body =
body;
mail.BodyFormat
= MailFormat.Html;
mail.Priority
= priority;
attachments
= attachments.Trim();
System.Configuration.AppSettingsReader
configurationAppSettings = new
System.Configuration.AppSettingsReader();
attachmentsPath
=
((string)(configurationAppSettings.GetValue("attachmentsLocation",
typeof(string))));
After setting all the properties of the MailMessage object, it then checks to see if any attachments need to be sent along with the mail. If so, it then splits the variable that contains the comma separated list of attachments into an array by using the Split method. Then it loops through the array and creates an instance of the MailAttachment object, passing in the path of the attachment to its constructor. Then it adds the MailAttachment object to the Attachments collection of MailMessage object.
if (attachments.Length !=0)
{
//Get the list of colon separated attachments into an array
string[] arr = attachments.Split(',');
foreach (string str in arr)
{
//Concatenate the path where attachments are stored,
with the actual file name
MailAttachment
attachment = new
MailAttachment(attachmentsPath
+ "\\" + str );
mail.Attachments.Add(attachment);
}
}
Finally, it sends the mail using the Send method of the SmtpMail class.
//Send the mail
SmtpMail.Send(mail);
}
Now that we have seen the implementation of the mail processing component as well as the Windows Service, we are ready to look at the client application that will be used to test the functionalities of the above components.
