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
}
}
}
}