public IEnumerable<Email> sentEmail(Email items) { objConn = objDB.EstablishConnection(); List<Email> sentMail = new List<Email>(); string sqlEmail = "SELECT s.StaffEmail FROM staffs s"; sqlEmail += " INNER JOIN staffrole sr ON sr.StaffRoleID = s.StaffRoleID"; sqlEmail += " WHERE sr.StaffRoleID = '" + Convert.ToInt32(items.staffRoleID.ToString()) + "'"; DataTable dtSentMail = objDB.List(sqlEmail, objConn); string sqlPatchInfor = "SELECT p.PatchsName,"; sqlPatchInfor += " CONCAT(sv.SoftwareVersionName, '.', p.PatchsVersionNumber) AS SoftwareVersion,"; sqlPatchInfor += " st.SoftwareTypeName, p.PatchsDescription,"; sqlPatchInfor += " DATE_FORMAT(p.PatchsInsertDate, '%d %M %Y') AS PatchsInsertDate,"; sqlPatchInfor += " DATE_FORMAT(p.PatchsUpdateDate, '%d %M %Y') AS PatchsUpdateDate,"; sqlPatchInfor += " p.PatchsInsertBy, p.PatchsUpdateBy"; sqlPatchInfor += " FROM patchparentversion pv"; sqlPatchInfor += " INNER JOIN patchs p ON p.PatchsID = pv.PatchsID"; sqlPatchInfor += " INNER JOIN softwareversion sv ON sv.SoftwareVersionID = pv.SoftwareVersionID"; sqlPatchInfor += " INNER JOIN softwaretype st ON st.SoftwareTypeID = pv.SoftwareTypeID"; sqlPatchInfor += " INNER JOIN staffs s ON s.StaffID = pv.StaffID"; sqlPatchInfor += " INNER JOIN staffrole sr ON sr.StaffRoleID = s.StaffRoleID"; sqlPatchInfor += " WHERE p.PatchsID = (SELECT MAX(PatchsID) FROM patchparentversion pv"; sqlPatchInfor += " INNER JOIN staffs s ON s.StaffID = pv.StaffID"; sqlPatchInfor += " WHERE s.StaffID = '" + Convert.ToInt32(items.staffID.ToString()) + "')"; objConn.Close(); if (dtSentMail.Rows.Count > 0) { string hostAddr = ConfigurationManager.AppSettings["Host"].ToString(); string mailAuthen = ConfigurationManager.AppSettings["MailAuthen"].ToString(); string passAuthen = ConfigurationManager.AppSettings["PassAuthen"].ToString(); MailMessage mailMessage = new MailMessage(); mailMessage.From = new MailAddress(mailAuthen); mailMessage.Subject = "HELLO"; mailMessage.Body = "THIS IS A TEST"; mailMessage.IsBodyHtml = true; for(int i = 0; i < dtSentMail.Rows.Count; i++) { string staffEmail = dtSentMail.Rows[i]["StaffEmail"].ToString(); string[] splitRecipients = staffEmail.Split(','); foreach(var mailTo in splitRecipients) { mailMessage.To.Add(mailTo); } } SmtpClient smtp = new SmtpClient(); smtp.Host = hostAddr; smtp.EnableSsl = true; NetworkCredential networkCre = new NetworkCredential(); networkCre.UserName = mailAuthen; networkCre.Password = passAuthen; smtp.UseDefaultCredentials = true; smtp.Credentials = networkCre; smtp.Port = 465; try { smtp.Send(mailMessage);//ON ERROR Console.WriteLine("Message Sent"); } catch (Exception ex) { Console.WriteLine(ex.Message.ToString()); } } return sentMail; }
public IEnumerable<Email> SentEmail(Email items) { return repository.sentEmail(items); }