question

ognsystems-5428 avatar image
0 Votes"
ognsystems-5428 asked CarstenRiedel answered

optimize code ideas (Bulk mailing)

Hi Guys,
Please find attached code i have written to do bulk mailing, the idea is to read the table record, send email and update record. I would like to optimize it since sending an email and updating records seems very expensive.
I would appreciate any ideas .
Thanks in advance.

 using System;
 using System.Configuration;
 using System.Data;
 using System.Data.SqlClient;
 using System.Net;
 using System.Net.Mail;
    
 namespace InvalidPostalAddressEmail
 {
     static class Program
     {
         static readonly string MailServer = ConfigurationManager.AppSettings["MailServer"];
         static readonly int Port = Convert.ToInt32(ConfigurationManager.AppSettings["Port"]);
         static readonly bool EnableSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["EnableSsl"]);
         static readonly string UserName = ConfigurationManager.AppSettings["smtpuserName"];
         static readonly string PassWord = ConfigurationManager.AppSettings["smtppassWord"];
         static readonly string ToAddress = ConfigurationManager.AppSettings["toAddress"];
         static readonly string FromAddress = ConfigurationManager.AppSettings["fromAddress"];
    
         static void Main()
         {
             try
             {
                 string connection = ConfigurationManager.AppSettings["SQLDbConnection"];
                 DataTable dtable = new DataTable();
                 SqlConnection con = new SqlConnection(connection);
                 string selectSQL =
                     "Select * from employees ";
                 SqlCommand cmd = new SqlCommand(selectSQL, con);
                 SqlDataAdapter sda = new SqlDataAdapter(cmd) {SelectCommand = {CommandTimeout = 0}};
                 cmd.CommandType = CommandType.Text;
                 cmd.Connection = con;
                 con.Open();
                 int rowsReturned = sda.Fill(dtable);
                 con.Close();
                 if (rowsReturned > 0 && dtable.Rows.Count > 0)
                 {
                     foreach (DataRow dRow in dtable.Rows)
                     {
                         decimal recId = dRow.Field<decimal>("id");
                         decimal numberOfEmailsSent = dRow.Field<decimal>("numEmailSent");
                         string emailAddress = dRow.Field<string>("email");
                         string firstName = dRow.Field<string>("PTFNAME");
                         string lastName = dRow.Field<string>("PTLNAME");
                         string city = dRow.Field<string>("PTCITY");
                         string state = dRow.Field<string>("PTSTATE");
                         string addr = dRow.Field<string>("ADDR");
                         string zipcode = dRow.Field<string>("ZIPCODE");
                         string emailTemplate = System.IO.File.ReadAllText(@"C:\InvalidAddress\EmailBody.html");
                         emailTemplate = emailTemplate.Replace("%PTFNAME%", firstName);
                         emailTemplate = emailTemplate.Replace("%PTFLNAME%", lastName);
                         emailTemplate = emailTemplate.Replace("%ADDR%", addr);
                         emailTemplate = emailTemplate.Replace("%PTCITY%", city);
                         emailTemplate = emailTemplate.Replace("%PTSTATE%", state);
                         emailTemplate = emailTemplate.Replace("%ZIPCODE%", zipcode);
    
                         EmailPdf(emailAddress, emailTemplate);
                         //Update Records
                         string sSql = "UPDATE employees set STATUS='E', date= GETDATE(), numEmailSent= " + numberOfEmailsSent + 1 + " where id=" + recId;
                         SqlCommand cmdu = new SqlCommand(sSql, con);
                         cmdu.Connection = con;
                         cmdu.CommandTimeout = 0;
                         con.Open();
                         cmdu.ExecuteNonQuery();
                         con.Close();
                     }
                 }
             }
             catch (Exception e)
             {
                 Console.WriteLine(e);
                 throw;
             }
         }
    
         static void EmailPdf(string emailAddress, string emailTemplate)
         {
             try
             {
                 SmtpClient smtp;
                 MailMessage mail = new MailMessage { Body = emailTemplate, IsBodyHtml = true };
                 // Change to use email address from the database
                 mail.To.Add(emailAddress != "" ? new MailAddress(emailAddress) : new MailAddress(ToAddress));
                 mail.From = new MailAddress(FromAddress);
                 mail.Subject = "Invalid Address - Please Verify";
                 
                 using (smtp = new SmtpClient())
                 {
                     smtp.Host = MailServer;
                     smtp.Port = Port;
                     smtp.EnableSsl = EnableSsl;
                     smtp.UseDefaultCredentials = false;
                     smtp.Credentials = new NetworkCredential(UserName, PassWord);
                     smtp.Send(mail);
                 }
    
             }
             catch (Exception)
             {
                 // ignored
             }
         }
     }
 }


dotnet-csharp
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe try using a single SmtpClient object for all of emails. Do not re-create it for each row.

Also execute con.Open at the beginning and con.Close at the end. Do not open and close the connection multiple times.

Read the template file before the loop and keep it in some variable. Do not re-read it.

See if this gives a perceptible improvement.


0 Votes 0 ·
TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered

In the current situation, we modify the content of html in the console app. It is no different from a normal text document. Apart from reading and writing it as it is now, I can't think of any optimization methods.

Regarding sending emails in batches, you can add multiple email addresses at once in the mail.To.Add method, like this:

      mail.To.Add("test1@outlook.com,test2@outlook.com");

But in this way, each recipient will be able to see the email addresses of all the people who received this email, which may lead to information leakage and make them unhappy.

It might be better to use mail.Bcc.


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

Untested, see if this helps

 using System;
 using System.Data;
 using System.Data.SqlClient;
 using System.IO;
 using System.Net;
 using System.Net.Mail;
 using System.Text;
 using static System.Configuration.ConfigurationManager;
    
 namespace InvalidPostalAddressEmail
 {
     class Program
     {
         private static readonly StringBuilder _stringBuilder = new StringBuilder();
         static void Main(string[] args)
         {
    
             var dt = new DataTable();
             var emailBodyFileName = @"C:\InvalidAddress\EmailBody.html";
                
             if (!File.Exists(emailBodyFileName))
             {
                 Console.WriteLine($"Failed to find {emailBodyFileName}");
                 Console.ReadLine();
                 return;
             }
                
             var emailBody = File.ReadAllText(emailBodyFileName);
    
             try
             {
                 using (var con = new SqlConnection(AppSettings["SQLDbConnection"]))
                 {
                     using (var cmd = new SqlCommand() {Connection = con, CommandText = "SELECT * FROM employees "})
                     {
                         con.Open();
                         dt.Load(cmd.ExecuteReader());
                         if (dt.Rows.Count <= 0) return;
                         // date = GETDATE() should be a default value in the table
                         cmd.CommandText = "UPDATE employees SET STATUS='E',numEmailSent = @numEmailSent WHERE id = @Id";
                         cmd.Parameters.Add("@numEmailSent", SqlDbType.Int);
                         cmd.Parameters.Add("@Id", SqlDbType.Int);
    
                         foreach (DataRow row in dt.Rows)
                         {
                                   
                             string emailTemplate = emailBody;
                                    
                             emailTemplate = emailTemplate.Replace("%PTFNAME%", row.Field<string>("PTFNAME"));
                             emailTemplate = emailTemplate.Replace("%PTFLNAME%", row.Field<string>("PTLNAME"));
                             emailTemplate = emailTemplate.Replace("%ADDR%", row.Field<string>("ADDR"));
                             emailTemplate = emailTemplate.Replace("%PTCITY%", row.Field<string>("PTCITY"));
                             emailTemplate = emailTemplate.Replace("%PTSTATE%", row.Field<string>("PTSTATE"));
                             emailTemplate = emailTemplate.Replace("%ZIPCODE%", row.Field<string>("ZIPCODE"));
    
                             EmailOperations.Instance.BodyTemplate = emailTemplate;
                             var (success, exception) = EmailOperations.Instance.EmailPdf(row.Field<string>("email"));
                                    
                             if (success)
                             {
                                 cmd.Parameters["@numEmailSent"].Value = row.Field<decimal>("numEmailSent") + 1;
                                 cmd.Parameters["@Id"].Value = row.Field<decimal>("id");
                                 cmd.ExecuteNonQuery();
                             }
                             else
                             {
                                 _stringBuilder.AppendLine($"Failed sending message\n{exception.Message}");
                             }
                         }
                     }
                 }
             }
             catch (Exception exception)
             {
                 _stringBuilder.AppendLine(exception.Message);
             }
    
             if (_stringBuilder.Length >0)
             {
                 File.WriteAllText("unhandledErrors.txt", _stringBuilder.ToString());
             }
         }
     }
    
     internal sealed class EmailOperations
     {
         private static readonly Lazy<EmailOperations> Lazy = 
             new Lazy<EmailOperations>(() => new EmailOperations());
    
         static readonly string MailServer = AppSettings["MailServer"];
         static readonly int Port = Convert.ToInt32(AppSettings["Port"]);
         static readonly bool EnableSsl = Convert.ToBoolean(AppSettings["EnableSsl"]);
         static readonly string UserName = AppSettings["smtpuserName"];
         static readonly string PassWord = AppSettings["smtppassWord"];
         static readonly string ToAddress = AppSettings["toAddress"];
         static readonly string FromAddress = AppSettings["fromAddress"];
    
         private static SmtpClient _smtpClient;
         private static string _subject = "Invalid Address - Please Verify";
    
         public string BodyTemplate { get; set; }
    
         private EmailOperations()
         {
             _smtpClient = new SmtpClient
             {
                 Host = MailServer,
                 Port = Port,
                 EnableSsl = EnableSsl,
                 UseDefaultCredentials = false,
                 Credentials = new NetworkCredential(UserName, PassWord)
             };
         }
    
         public static EmailOperations Instance => Lazy.Value;
    
         /// <summary>
         /// Send email
         /// </summary>
         /// <param name="emailAddress">email address</param>
         /// <returns>Named value tuple, success, exception</returns>
         public (bool, Exception) EmailPdf(string emailAddress)
         {
             try
             {
                 var mail = new MailMessage { Body = BodyTemplate, IsBodyHtml = true };
    
                 // Change to use email address from the database
                 mail.To.Add(emailAddress != "" ? new MailAddress(emailAddress) : new MailAddress(ToAddress));
                 mail.From = new MailAddress(FromAddress);
                 mail.Subject = _subject;
    
                 _smtpClient.Send(mail);
                    
                 return (false, null);
                    
             }
             catch (Exception exception)
             {
                 return (false, exception);
             }
         }
     }
        
 }

Also,, might want to look at MailKit with is the replacement System.Net.Mail


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarstenRiedel avatar image
0 Votes"
CarstenRiedel answered

If you really what to optimize the execution. You need to trace the execution times.

So... https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.stopwatch?view=net-5.0 is your friend...

or a Paid version of https://www.jetbrains.com/profiler/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.