Post
Ask Question

Email Notification Automated

Email Notification

Report status mailing to the appropriate team with the following code generation.

Here there are 100 stores and when sending the file to NRI, that NRI sends the file to another environment. We have to check whether the file was successfully transferred to source environment or not.

So, let’s start our programming code,

 Step 1  - Program.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.IO;  
  7. using System.Configuration;  
  8. using System.Data.SqlClient;  
  9. using System.Net.Mail;  
  10. using System.Data;  
  11. namespace Automated_email {  
  12.     class Program {  
  13.         static void Main(string[] args) {  
  14.             //args = new string[] { "DRA#Successful" ,"ISPD#Successful','Complete" };  
  15.             DateTime d = DateTime.Now.Date;  
  16.             string FilePath = "";  
  17.             string session_path = "";  
  18.             session_path = session_path + ConfigurationManager.AppSettings["path"];  
  19.             string dat = d.ToString("D");  
  20.             int p = 0, q = 0;  
  21.             List < clsSessionDtls > lstSessioDtl = new List < clsSessionDtls > ();  
  22.             DataSet dsStat = new DataSet();  
  23.             for (int i = 0; i < args.Length; i++) {  
  24.                 lstSessioDtl.Add(new clsSessionDtls(args[i].Split('#')[0].ToString(), args[i].Split('#')[1].ToString()));  
  25.             }  
  26.             foreach(clsSessionDtls sessionDtls in lstSessioDtl) {  
  27.                 p++;  
  28.                 q = 0;  
  29.                 var connectionString = ConfigurationManager.ConnectionStrings["Email"].ConnectionString;  
  30.                 //Query for generating Session Status  
  31.                 var cmd = "select s.tid,s.Store_No " + ", CASE " + "WHEN q.tid is null THEN 'No' " + " ELSE 'Yes' " + "END Session_Status" + " from stores s left outer join " + "(select distinct Store_Number, (select TID from Stores where [Store_No] = Store_Number) as TID " + "from[dbo].[Job_Session_Summary] where " + " Session_ID in (select[Session_ID] from[dbo].[Job_Sessions] where name = '" + sessionDtls.strSessionName + "') and" + " [LogStatus] in('" + sessionDtls.strCondSql + "') ) q on s.tid = q.tid where s.status='E' and s.[Store_No] <> 'Host'; ";  
  32.                 dsStat.Tables.Add(DataExtraction.ReadTable(connectionString, cmd));  
  33.                 FilePath = session_path + sessionDtls.strSessionName + dat + ".csv";  
  34.                 foreach(DataTable dt in dsStat.Tables) {  
  35.                     q++;  
  36.                     if (p == q) {  
  37.                         FileWriting.WriteToFile(dt, FilePath, false, ","); //Creation of csv file for Session status  
  38.                         break;  
  39.                     }  
  40.                 }  
  41.             }  
  42.             EmailSending.email(dsStat, lstSessioDtl); //Mailing the status  
  43.         }  
  44.     }  
  45.     public class clsSessionDtls {  
  46.         private string _strSessionName;  
  47.         public string strSessionName {  
  48.             get {  
  49.                 return _strSessionName;  
  50.             }  
  51.             // set { _strSessionName = value; }  
  52.         }  
  53.         private string _strCondSql;  
  54.         public string strCondSql {  
  55.             get {  
  56.                 return _strCondSql;  
  57.             }  
  58.             // set { _strCondSql = value; }  
  59.         }  
  60.         public clsSessionDtls(string sessionName, string stringCondSQL) {  
  61.             _strSessionName = ConfigurationManager.AppSettings[sessionName].ToString();  
  62.             _strCondSql = stringCondSQL;  
  63.         }  
  64.     }  
  65. }  

Step 2

Now we have to write the email body format.

FileWriting.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.IO;  
  7. using System.Configuration;  
  8. using System.Data.SqlClient;  
  9. using System.Net.Mail;  
  10. using System.Data;  
  11. namespace Automated_email {  
  12.     class FileWriting {  
  13.         public static void WriteToFile(DataTable dataSource, string fileOutputPath, bool firstRowIsColumnHeader = false, string seperator = ";") {  
  14.             using(var sw = new StreamWriter(fileOutputPath, false)) {  
  15.                 int icolcount = dataSource.Columns.Count;  
  16.                 if (!firstRowIsColumnHeader) {  
  17.                     for (int i = 0; i < icolcount; i++) {  
  18.                         sw.Write(dataSource.Columns[i]);  
  19.                         if (i < icolcount - 1) sw.Write(seperator);  
  20.                     }  
  21.                     sw.Write(sw.NewLine);  
  22.                 }  
  23.                 foreach(DataRow drow in dataSource.Rows) {  
  24.                     for (int i = 0; i < icolcount; i++) {  
  25.                         if (!Convert.IsDBNull(drow[i])) sw.Write(drow[i].ToString());  
  26.                         if (i < icolcount - 1) sw.Write(seperator);  
  27.                     }  
  28.                     sw.Write(sw.NewLine);  
  29.                 }  
  30.                 sw.Flush();  
  31.                 sw.Close();  
  32.             }  
  33.         }  
  34.     }  
  35. }  

Step 3

Email information: what should be there, what is the condition for success, failure query status and sending email with attachment .

Email Sending.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.IO;  
  7. using System.Configuration;  
  8. using System.Data.SqlClient;  
  9. using System.Net.Mail;  
  10. using System.Data;  
  11. using System.Net.NetworkInformation;  
  12. namespace Automated_email {  
  13.     class EmailSending {  
  14.         public static void email(DataSet dataSource, List < clsSessionDtls > lstSessioDtl) {  
  15.             DateTime d = DateTime.Now.Date;  
  16.             string da = d.ToString("MM/dd/yyyy");  
  17.             string td = d.ToString("yyyyMMdd");  
  18.             string dat = d.ToString("D");  
  19.             string FilePath = "";  
  20.             string session_path = "";  
  21.             int p = 0, q = 0;  
  22.             session_path = session_path + ConfigurationManager.AppSettings["path"];  
  23.             try {  
  24.                 MailMessage mail = new MailMessage();  
  25.                 SmtpClient SmtpServer = new SmtpClient(ConfigurationManager.AppSettings["Host"]);  
  26.                 mail.From = new MailAddress(ConfigurationManager.AppSettings["from"]);  
  27.                 string[] to = ConfigurationManager.AppSettings["to"].Split(',');  
  28.                 for (int a = 0; a < to.Length; a++) {  
  29.                     mail.To.Add(to[a].ToString());  
  30.                 }  
  31.                 mail.Subject = ConfigurationManager.AppSettings["status"];  
  32.                 string body = "<table>";  
  33.                 body = body + "<tr><td><b><br> Date : " + da + "</br></td></b></tr>";  
  34.                 foreach(clsSessionDtls sessionDtls in lstSessioDtl) {  
  35.                     p++;  
  36.                     q = 0;  
  37.                     System.Net.Mail.Attachment attachment;  
  38.                     FilePath = session_path + sessionDtls.strSessionName + dat + ".csv";  
  39.                     attachment = new System.Net.Mail.Attachment(FilePath);  
  40.                     mail.Attachments.Add(attachment);  
  41.                     body = body + "<br><tr><td><b>" + sessionDtls.strSessionName + "</b><b> Status: -</b>";  
  42.                     foreach(DataTable dt in dataSource.Tables) {  
  43.                         q++;  
  44.                         if (p == q) {  
  45.                             int rowcount = dt.Rows.Count;  
  46.                             int failed = 0;  
  47.                             DataRow[] rows;  
  48.                             rows = dt.Select("Session_Status = 'No'");  
  49.                             failed = rows.Length;  
  50.                             body = body + "<b><font color='red'> Failed : " + failed.ToString() + "/" + rowcount.ToString() + "</b><font color='black'>";  
  51.                             int success = rowcount - failed;  
  52.                             body = body + "<b><font color='green'> Success : " + success.ToString() + "/" + rowcount.ToString() + "</b><font color='black'></td></tr> </br>";  
  53.                             body = body + "<table border=" + 1 + " cellpadding=" + 0 + " cellspacing=" + 0 + " width = " + 400 + ">";  
  54.                             if (success != rowcount) body = body + "<tr><td><b>TID List</b></td><td><b>Store Number</b></td><td><b>Reason for Failure</b></td></tr>";  
  55.                             foreach(DataRow row in dt.Select("Session_Status='No'")) {  
  56.                                 body = body + "<td>" + row["tid"].ToString() + "</td><td>" + row["Store_No"].ToString() + "</td>";  
  57.                                 Ping myPing = new Ping();  
  58.                                 try {  
  59.                                     PingReply reply = myPing.Send(("T" + row["tid"].ToString()), 1000);  
  60.                                     string file_path = ConfigurationManager.AppSettings["file_path"].ToString();  
  61.                                     if (sessionDtls.strSessionName.Equals(ConfigurationManager.AppSettings["DRA"].ToString())) {  
  62.                                         string dra = file_path + row["tid"].ToString() + @ "\";  
  63.                                         string fil = ConfigurationManager.AppSettings["DRA_file"].ToString();  
  64.                                         string ac = "";  
  65.                                         string[] files = Directory.GetFiles(dra, fil, SearchOption.TopDirectoryOnly);  
  66.                                         foreach(string s in files) {  
  67.                                             ac = ac + s;  
  68.                                         }  
  69.                                         if (ac.Length == 0) body = body + "<td> Files not received from NRI </td>";  
  70.                                     } else if (sessionDtls.strSessionName.Equals(ConfigurationManager.AppSettings["ISPD"].ToString())) {  
  71.                                         string ispd = file_path + row["tid"].ToString() + @ "\";  
  72.                                         string ac = "";  
  73.                                         string fil = ConfigurationManager.AppSettings["ISPD_file"].ToString();  
  74.                                         string[] files = Directory.GetFiles(ispd, fil, SearchOption.TopDirectoryOnly);  
  75.                                         foreach(string s in files) {  
  76.                                             ac = ac + s;  
  77.                                         }  
  78.                                         if (ac.Length == 0) body = body + "<td> Files not received from NRI </td>";  
  79.                                     } else if (sessionDtls.strSessionName.Equals(ConfigurationManager.AppSettings["ISPW"].ToString())) {  
  80.                                         string ispw = file_path + row["tid"].ToString() + @ "\";  
  81.                                         string ac = "";  
  82.                                         string fil = ConfigurationManager.AppSettings["ISPW_file"].ToString();  
  83.                                         string[] files = Directory.GetFiles(ispw, fil, SearchOption.TopDirectoryOnly);  
  84.                                         foreach(string s in files) {  
  85.                                             ac = ac + s;  
  86.                                         }  
  87.                                         if (ac.Length == 0) body = body + "<td> Files not received from NRI </td>";  
  88.                                     }  
  89.                                 } catch (Exception e) {  
  90.                                     body = body + "<td> Network Issue</td>";  
  91.                                 }  
  92.                                 body = body + "</tr>";  
  93.                             }  
  94.                             body = body + "</table>";  
  95.                             break;  
  96.                         }  
  97.                     }  
  98.                 }  
  99.                 body = body + "</table>";  
  100.                 mail.Body = body;  
  101.                 mail.IsBodyHtml = true;  
  102.                 SmtpServer.Port = Convert.ToUInt16(ConfigurationManager.AppSettings["port"].ToString());  
  103.                 SmtpServer.UseDefaultCredentials = false;  
  104.                 SmtpServer.EnableSsl = false;  
  105.                 SmtpServer.Send(mail);  
  106.             } catch (Exception ex) {  
  107.                 Console.WriteLine(ex.ToString());  
  108.             }  
  109.         }  
  110.     }  
  111. }  

Step 4

Data extraction from Sql query from sql server .

DataExtraction.cs 

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.IO;  
  7. using System.Configuration;  
  8. using System.Data.SqlClient;  
  9. using System.Net.Mail;  
  10. using System.Data;  
  11. namespace Automated_email {  
  12.     class DataExtraction {  
  13.         public static DataTable ReadTable(string connectionString, string selectQuery) {  
  14.             var returnValue = new DataTable();  
  15.             var conn = new SqlConnection(connectionString);  
  16.             try {  
  17.                 conn.Open();  
  18.                 var command = new SqlCommand(selectQuery, conn);  
  19.                 using(SqlDataAdapter adapter = new SqlDataAdapter(command)) {  
  20.                     adapter.Fill(returnValue);  
  21.                 }  
  22.             } catch (Exception ex) {  
  23.                 Console.WriteLine(ex.Message);  
  24.                 throw ex;  
  25.             } finally {  
  26.                 if (conn.State == ConnectionState.Open) conn.Close();  
  27.             }  
  28.             return returnValue;  
  29.         }  
  30.     }  
  31. }  

App Setting

There three sessions always running every day at 6 pm.

Every session's stored files has a unique extension, where the files are saved to a particular path.

App.Config 

  1. <?xml version="1.0" encoding="utf-8" ?>  
  2. <configuration>  
  3.     <connectionStrings>  
  4.         <add name="Email" connectionString="Server=172.22.124.15;Database=seventransfer;User Id=seventransfer;Password=Tr@n$f9User!2" /> </connectionStrings>  
  5.     <appSettings>  
  6.         <add key="DRA" value="BT - DRA Download Hourly " />  
  7.         <add key="DRA_file" value="rcd.odtx.isptxn.*" />  
  8.         <add key="file_path" value="\\172.22.124.13\csfOutbound\" />  
  9.         <add key="ISPD" value="BT - ISP Masters Daily 0100" />  
  10.         <add key="ISPD_file" value="rcd.dltx.isptxn.*" />  
  11.         <add key="ISPW" value="BT - ISP Masters Weekly 0100" />  
  12.         <add key="ISPW_file" value="rcd.wktx.isptxn.*" />  
  13.         <add key="path" value="D:\Smoha063\" />  
  14.         <add key="Host" value="711mail.7-11.com" />  
  15.         <add key="port" value="25" />  
  16.         <add key="from" value="[email protected]" />  
  17.         <add key="to" value="Sanghmitra.Mohanty @cognizant.com" />  
  18.         <add key="status" value="7-Transfer Daily Status Report - storestg domain" /> </appSettings>  
  19.     <startup>  
  20.         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /> </startup>  
  21. </configuration>