public void UpdateMailAttemptsAndStatus(int notificationId, bool mailStatus, int maxAtempts)
{
try
{
OracleConnection connection = GetDatabaseConnection();
using (connection)
{
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.InitialLONGFetchSize = 1000;
command.CommandText = "ACE_EMAIL_SPOOLER_PKG.UpdateEmailStatus";
command.CommandType = CommandType.StoredProcedure;
OracleParameter objcmdParameter = new OracleParameter("p_notificationId", notificationId);
objcmdParameter.DbType = DbType.Int32;
objcmdParameter.Direction = ParameterDirection.Input;
command.Parameters.Add(objcmdParameter);
objcmdParameter = new OracleParameter("mailStatus", Convert.ToInt16(mailStatus));
objcmdParameter.DbType = DbType.Int32;
objcmdParameter.Direction = ParameterDirection.Input;
command.Parameters.Add(objcmdParameter);
objcmdParameter = new OracleParameter("maxAttempts", maxAtempts);
objcmdParameter.DbType = DbType.Int32;
objcmdParameter.Direction = ParameterDirection.Input;
command.Parameters.Add(objcmdParameter);
connection.Open();
int returnValue = command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
LoggingUtility.WriteLog(ELogLevel.ERROR, "Error in UpdateMailAttemptsAndStatus() " + ex.Message.ToString());
}
}
public bool TestSMTPConnection()
{
bool hostAvailable = false;
try
{
TcpClient smtpTestClient = new TcpClient();
string smtpHost = Program.appConfig[Constants.SMTPHost];
int port = Convert.ToInt32(Program.appConfig[Constants.SMTPPort]);
smtpTestClient.Connect(smtpHost, port);
if (smtpTestClient.Connected)//connection is established
{
NetworkStream netStream = smtpTestClient.GetStream();
StreamReader sReader = new StreamReader(netStream);
if (sReader.ReadLine().Contains("220"))//host is available for communication
{
hostAvailable = true;
LoggingUtility.WriteLog(ELogLevel.INFO, "SMTP Server Connection Successful. ");
}
smtpTestClient.Close();
}
}
catch (Exception ex)
{
LoggingUtility.WriteLog(ELogLevel.ERROR, "SMTP Server Connection Error. " + ex.Message);
Console.WriteLine("SMTP Server Connection Error: " + ex.Message);
throw ex;
}
return(hostAvailable);
}
public DataTable GetEmailNotificationsWithAttachment()
{
DataTable dataTable = null;
try
{
OracleConnection connection = GetDatabaseConnection();
using (connection)
{
OracleDataAdapter adapter = new OracleDataAdapter();
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.InitialLONGFetchSize = 1000;
command.CommandText = "ACE_EMAIL_SPOOLER_PKG.GetEmailWithAttachments";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("T_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
adapter.SelectCommand = command;
dataTable = new DataTable();
adapter.Fill(dataTable);
}
}
catch (Exception ex)
{
LoggingUtility.WriteLog(ELogLevel.ERROR, "Error in GetEmailNotificationsWithAttachment() " + ex.Message.ToString());
}
return(dataTable);
}
public void UpdateEmailErrorLogs(int notificationId, string emailerror, bool IsConnectionSuccess)
{
try
{
OracleConnection connection = GetDatabaseConnection();
using (connection)
{
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.InitialLONGFetchSize = 1000;
command.CommandText = "ACE_EMAIL_SPOOLER_PKG.LogEmailErrors";
command.CommandType = CommandType.StoredProcedure;
OracleParameter objcmdParameter = new OracleParameter("p_notificationId", notificationId);
objcmdParameter.DbType = DbType.Int32;
objcmdParameter.Direction = ParameterDirection.Input;
command.Parameters.Add(objcmdParameter);
objcmdParameter = new OracleParameter("strErrMessage", emailerror);
objcmdParameter.DbType = DbType.String;
objcmdParameter.Direction = ParameterDirection.Input;
command.Parameters.Add(objcmdParameter);
objcmdParameter = new OracleParameter("IsConnectionSuccess", Convert.ToInt32(IsConnectionSuccess));
objcmdParameter.DbType = DbType.Int32;
objcmdParameter.Direction = ParameterDirection.Input;
command.Parameters.Add(objcmdParameter);
connection.Open();
int returnValue = command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
LoggingUtility.WriteLog(ELogLevel.ERROR, "Error in UpdateEmailErrorLogs() " + ex.Message.ToString());
}
}
public bool TriggerEmail(Email email)
{
bool sendStatus = false;
try
{
string from = Program.appConfig[Constants.SenderAddress];
string userName = Program.appConfig[Constants.UserName];
string password = Program.appConfig[Constants.Password];
string smtpHost = Program.appConfig[Constants.SMTPHost];
int port = Convert.ToInt32(Program.appConfig[Constants.SMTPPort]);
string to = email.To;
string subject = email.Subject;
string body = email.Body;
string attachmentName = email.AttachmentName;
byte[] attachmentData = email.AttachmentData;
MailMessage message = new MailMessage();
message.IsBodyHtml = true;
message.From = new MailAddress(from, "");
message.To.Add(new MailAddress(to));
if (!string.IsNullOrEmpty(email.Bcc))
{
message.Bcc.Add(email.Bcc);
}
message.Subject = subject;
message.Body = body;
message.IsBodyHtml = false;
if (!string.IsNullOrEmpty(attachmentName) && attachmentData != null)
{
MemoryStream dataStream = new MemoryStream(attachmentData);
Attachment attachment = new Attachment(dataStream, attachmentName);
ContentType content = attachment.ContentType;
content.MediaType = MediaTypeNames.Text.RichText;
message.Attachments.Add(attachment);
}
using (var client = new System.Net.Mail.SmtpClient(smtpHost, port))
{
client.Credentials = new NetworkCredential(userName, password);
client.EnableSsl = true;
Console.WriteLine("Attempting to send email...");
client.Send(message);
LoggingUtility.WriteLog(ELogLevel.INFO, "Email sent to " + email.To.ToString());
sendStatus = true;
Console.WriteLine("Email sent to " + email.To.ToString());
}
}
catch (Exception ex)
{
sendStatus = false;
LoggingUtility.WriteLog(ELogLevel.ERROR, "Email was not sent" + ex.Message);
Console.WriteLine("The email was not sent : " + ex.Message);
throw ex;
}
return(sendStatus);
}
public void ProcessEmails()
{
bool IsConnectionSuccess = TestConnection();
LoggingUtility.WriteLog(ELogLevel.INFO, "Emails processing started");
ProcessEmailNotifications(IsConnectionSuccess);
ProcessEmailNotificationsWithAttachments(IsConnectionSuccess);
LoggingUtility.WriteLog(ELogLevel.INFO, "Emails processing completed");
}
static void Main(string[] args)
{
LoggingUtility.WriteLog(ELogLevel.INFO, "Application Start");
Console.WriteLine("Application Start");
GetAppSettings();
BL bl = new BL();
bl.ProcessEmails();
LoggingUtility.WriteLog(ELogLevel.INFO, "Application Complete");
Console.WriteLine("Application Start");
}
private OracleConnection GetDatabaseConnection()
{
OracleConnection connection = null;
try
{
connection = new OracleConnection(connectionString);
}
catch (Exception ex)
{
LoggingUtility.WriteLog(ELogLevel.ERROR, "Error in GetDatabaseConnection() " + ex.Message.ToString());
}
return(connection);
}
public void ProcessEmailNotificationsWithAttachments(bool IsConnectionSuccess)
{
LoggingUtility.WriteLog(ELogLevel.INFO, "Processing attachment emails started");
Email email;
DataTable emailNotificationsData = dbContext.GetEmailNotificationsWithAttachment();
if (emailNotificationsData != null && emailNotificationsData.Rows.Count > 0)
{
foreach (DataRow notification in emailNotificationsData.Rows)
{
int notificationId = 0;
try
{
notificationId = Convert.ToInt32(notification["NOTIFICATIONID"]);
email = new Email();
email.To = Convert.ToString(notification["MailTo"]);
if (!string.IsNullOrEmpty(Convert.ToString(notification["EmailTEMPLATEID"])) && !string.IsNullOrEmpty(Convert.ToString(notification["EVENTDATE"])))
{
email.Bcc = Program.appConfig[Constants.ACCEASecretariat].ToString();
}
email.Subject = Convert.ToString(notification["MailSubject"]);
email.Body = Convert.ToString(notification["MailBody"]);
email.AttachmentName = Convert.ToString(notification["COMMFILENAME"]);
email.AttachmentData = (byte[])(notification["Communicationfile"]);
bool result = sendMail.TriggerEmail(email);
dbContext.UpdateMailAttemptsAndStatus(notificationId, result, Convert.ToInt32(Program.appConfig[Constants.MaxRetryAttempts]));
}
catch (Exception ex)
{
LoggingUtility.WriteLog(ELogLevel.ERROR, "ProcessEmailNotificationsWithAttachments() | Error in sending emails : " + notificationId.ToString() + " " + ex.Message.ToString());
dbContext.UpdateEmailErrorLogs(notificationId, ex.Message.ToString(), IsConnectionSuccess);
}
}
}
else
{
LoggingUtility.WriteLog(ELogLevel.INFO, "No pending emails with attachment for process");
}
}
