Sir please have a look on my code
I am getting below error: Sql Connection is not comming from app.config.
SendEmailHrams; Object reference not set to an instance of an object.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
<configuration>
<connectionStrings>
<add name="HramsConWeb" connectionString="Data Source=SqlServerName;User ID=sa;Password=................;Connect Timeout=30;Encrypt=True;Trust Server Certificate=True;Application Intent=ReadWrite;Multi Subnet Failover=False" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
</configuration>
namespace SendEmailHrams
{
public partial class Service1 : ServiceBase
{
SqlConnection SqlCn;
SqlCommand SqlCm;
SqlDataReader SqlDr;
SqlDataAdapter SqlDa;
DataTable SqlDt;
public Timer ScheduleTimer;
public static string EmailSubject;
public static string EmailBody;
public static string EmailFromName;
public static string EmailFromPassword;
public static string EmailHostName;
public static int EmailPort;
public static string EmailContactPerson;
public static string EmailTo;
public static string EmailCc;
public static string EmailBcc;
public static string EmailTypeCode;
public static string EmailAttachmentStatus;
public static Boolean TransactionStatus;
public static string SqlQry;
public static string Path;
public static string constr;
public Service1()
{
InitializeComponent();
}
protected override void OnStart(string[] args)
{
//System.Diagnostics.Debugger.Launch();
//EmailTypeCode = "EMAIL-00013";
//GetEmailType();
Thread startThread = new Thread(new ThreadStart(ScheduleService));
startThread.Start();
}
public void ScheduleService()
{
try
{
ScheduleTimer = new Timer(new TimerCallback(SendMail));
//ScheduleTimer = new Timer(new TimerCallback(OpenPage));
DateTime scheduleTime = DateTime.MinValue;
scheduleTime = DateTime.Parse("10:35 AM");
if (DateTime.Now > scheduleTime)
scheduleTime = scheduleTime.AddMinutes(5);
TimeSpan timeSpan = scheduleTime.Subtract(DateTime.Now);
int dueMilli = Convert.ToInt32(timeSpan.TotalMilliseconds);
ScheduleTimer.Change(dueMilli, Timeout.Infinite);
}
catch (Exception ex)
{
WriteErrorLog(ex);
}
}
public void OpenPage(object e)
{
System.Diagnostics.Process.Start("https://sghram.com/dailyemailreport.aspx");
}
public void SendMail(object e)
{
try
{
EmailSubject = "Happy Birthday";
EmailFromName = "noreply@xyz.com";
EmailFromPassword = "Tebdbdbd232@";
EmailHostName = "mail.xyz.com";
EmailPort = 587;
TransactionStatus = true;
EmailTypeCode = "EMAIL-00013";
GetEmailType();
if (TransactionStatus == true)
{
constr = ConfigurationManager.ConnectionStrings["HramsConWeb"].ConnectionString;
SqlCn = new SqlConnection(constr);
SqlCn.Open();
//SqlConnection SqlCn = new SqlConnection("Data Source=stalliondms.com,2985;"
// + "Initial Cataloge=HRAMS;"
// + "User Id=HraMsWeb;"
// + "password=HraMis@Ng#2022");
//var connection =
// System.Configuration.ConfigurationManager.
// ConnectionStrings["HramsConWeb"].ConnectionString;
//SqlCn = new SqlConnection(connection);
//SqlCn.Open();
//SqlQry = "select StaffCode ,StaffName ,DateOfBirth,StaffEmailId as Email " +
// "from StaffMaster " +
// "where StaffCategory='EXPATRIATE' and DATEPART(m, DateOfBirth)=DATEPART(m,GETDATE()) AND " +
// "DATEPART(d, DateOfBirth) = DATEPART(d, GETDATE()) ";
SqlQry = "select top(5) StaffCode ,StaffName ,DateOfBirth,StaffEmailId as Email " +
"from StaffMaster " +
"where StaffCategory='EXPATRIATE' order by staff code ";
SqlCm = new SqlCommand(SqlQry, SqlCn);
SqlDt = new DataTable();
SqlDa = new SqlDataAdapter(SqlCm);
SqlDa.Fill(SqlDt);
if (SqlDt.Rows.Count > 0)
{
// Record Found
// Loop througn each Employee and send email.
foreach (DataRow dr in SqlDt.Rows)
{
string StaffName = dr["StaffName"].ToString();
EmailTo = dr["Email"].ToString().Trim();
EmailSubject = "Happy Birthday";
EmailBody = "Dear " + StaffName;
Path = "";
Path = "\\BirthdayImage\\BirthdayStallion1.gif";
//Path = Server.MapPath(@"BirthdayImage/ICABirthday.gif");
//path = Server.MapPath(@"BirthdayImage/ICAAnniversary.gif");
LinkedResource Img = new LinkedResource(Path, MediaTypeNames.Image.Jpeg);
Img.ContentId = "MyImage";
string str = "Dear " + StaffName + @"<html>
<body body bgcolor=""#efdede"">
<br>
<img src=cid:MyImage id='img' alt='' width='500px' height='500px'/>
<br>
<br>
<p>Thanks & Regards,<br>HR-ADMIN</br></p>
</body>
</html> ";
AlternateView AV = AlternateView.CreateAlternateViewFromString(str, null, MediaTypeNames.Text.Html);
AV.LinkedResources.Add(Img);
//AV.LinkedResources.Add(Img2);
// Send Email New
MailMessage message = new MailMessage();
SmtpClient smtp = new SmtpClient();
message.From = new MailAddress(EmailFromName);
//======== Define multiple TO
string[] ToMuliId = EmailTo.Split(';');
foreach (string ToEMailId in ToMuliId)
{
message.To.Add(new MailAddress(ToEMailId)); //adding multiple TO Email Id
}
//======== Define multiple CC
if (EmailCc == "NA")
{
}
else
{
string[] CCId = EmailCc.Split(';');
foreach (string CCEmail in CCId)
{
message.CC.Add(new MailAddress(CCEmail)); //Adding Multiple CC email Id
}
}
//======== Define multiple BCC
if (EmailBcc == "NA")
{
}
else
{
string[] BCCId = EmailBcc.Split(';');
foreach (string BCCEmail in BCCId)
{
message.Bcc.Add(new MailAddress(BCCEmail)); //Adding Multiple CC email Id
}
}
//======== end Define multiple
message.To.Add(new MailAddress("arvind@stalliongroup.com")); //Adding Multiple CC email Id
message.CC.Add(new MailAddress("arvind@stalliongroup.com")); //Adding Multiple CC email Id
message.Bcc.Add(new MailAddress("arvind@stalliongroup.com")); //Adding Multiple CC email Id
EmailSubject = EmailSubject + " " + StaffName;
message.Subject = EmailSubject;
message.IsBodyHtml = true;
//message.Body = "Testing Body";
message.AlternateViews.Add(AV);
smtp.Port = EmailPort;
smtp.Host = EmailHostName;
smtp.EnableSsl = false;
smtp.UseDefaultCredentials = false;
smtp.Credentials = new NetworkCredential(EmailFromName, EmailFromPassword);
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.Send(message);
this.ScheduleService();
// End Send Email New
}
// End Loop
}
else
{
// No Record found
}
// End Check row found
//this.ScheduleService();
// End Send Email New
// Emial Code Found
}
else
{
// No Email type code
}
// End Transaction Status
//SqlCn.Close();
}
catch (Exception ex)
{
WriteErrorLog(ex);
}
}
// End Send email function
public static void WriteErrorLog(Exception ex)
{
StreamWriter sw = null;
try
{
//sw = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\LogFile.txt", true);
sw = new StreamWriter("\\BirthdayImage\\" + "LogFile.txt", true);
sw.WriteLine(DateTime.Now.ToString() + ": " + ex.Source.ToString().Trim() + "; " + ex.Message.ToString().Trim());
sw.Flush();
sw.Close();
}
catch
{
}
}
public static void WriteErrorLog(string Message)
{
StreamWriter sw = null;
try
{
//sw = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\LogFile.txt", true);
sw = new StreamWriter("\\BirthdayImage\\" + "LogFile.txt", true);
sw.WriteLine(DateTime.Now.ToString() + ": " + Message);
sw.Flush();
sw.Close();
}
catch
{
}
}
protected void GetEmailType()
{
try
{
constr = ConfigurationManager.ConnectionStrings["HramsConWeb"].ConnectionString;
//var connection =
// System.Configuration.ConfigurationManager.
// ConnectionStrings["HramsConWeb"].ConnectionString;
////SqlConnection SqlCn = new SqlConnection("Data Source=stalliondms.com,2985;"
//// + "Initial Cataloge=HRAMS;"
//// + "User Id=HraMsWeb;"
//// + "password=HraMis@Ng#2022");
//SqlCn = new SqlConnection(connection);
SqlCn = new SqlConnection(constr);
SqlCn.Open();
SqlCm = new SqlCommand("FindEmailType", SqlCn);
SqlCm.CommandType = CommandType.StoredProcedure;
SqlCm.Parameters.AddWithValue("EmailCode", EmailTypeCode);
SqlDr = SqlCm.ExecuteReader();
if (SqlDr.Read())
{
//EmailCode = SqlDr["EmailCode"].ToString();
//EmailSubject = SqlDr["EmailSubject"].ToString();
EmailContactPerson = SqlDr["EmailContactPerson"].ToString();
EmailTo = SqlDr["EmailTo"].ToString();
EmailCc = SqlDr["EmailCc"].ToString();
EmailBcc = SqlDr["EmailBcc"].ToString();
EmailAttachmentStatus = SqlDr["EmailAttachmentStatus"].ToString();
TransactionStatus = true;
}
else
{
//EmailCode = "";
EmailSubject = "";
EmailContactPerson = "";
EmailTo = "";
EmailCc = "";
EmailAttachmentStatus = "";
TransactionStatus = false;
}
}
catch (Exception ex)
{
TransactionStatus = false;
}
// Catch
finally
{
SqlCn.Close();
//SqlCm.Dispose();
//SqlDr.Dispose();
}
// End Finally
}
// End Get Email Type Code
protected override void OnStop()
{
ScheduleTimer.Dispose();
}
}
}
