Hi,
I am really struggling with an issue and will be grateful if you expert can provide some good solution.
My problem is that i need to write a window service that could look for some detail in database and send mail...I have written a service but
i want it to run every hour.
How can i achieve this??Below is the code.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Net.Mail;
using System.Data.SqlClient;
using System.Net;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Configuration;
namespace ConsoleApplication1
{
class Email
{
public String timestamp;
static bool mailSent = false;
static void Main(string[] args)
{
Email e = new Email();
e.SendEmailTOAllUser();
}
private static void SendCompletedCallback(object sender, AsyncCompletedEventArgs e)
{
String token = (string)e.UserState;
String timestamp;
if (e.Error != null)
{
throw new Exception(e.ToString());
}
else
{
timestamp = DateTime.Now.ToString();
}
mailSent = true;
}
protected void SendEmail(string toAddresses, string fromAddress, string MailSubject, string MessageBody, bool isBodyHtml, string username)
{
SmtpClient sc = new SmtpClient("SMTP (MAIL) ADDRESS");
MailMessage msg = new MailMessage();
try
{
MailMessage msg = new MailMessage();
msg.From = new MailAddress("pssp@gmail.com", "OUR SYSTEM");
msg.To.Add("pssp@gmail.com");
msg.Bcc.Add(toAddresses);
msg.Subject = MailSubject;
msg.Body = MessageBody;
msg.IsBodyHtml = isBodyHtml;
sc.Send(msg);
sc.SendCompleted += new
SendCompletedEventHandler(SendCompletedCallback);
ConsoleApplication1.Error.LogSuccess(string.Concat(new object[] { "--Success mail sent for --<br>", username, "<br>", "Mail Sent to :", msg.To, "at timestamp ", timestamp }));
Console.WriteLine("mailsent for" + username);
}
catch (Exception exception)
{
ConsoleApplication1.Error.LogEmailException(string.Concat(new object[] { "--ERROR SENDING EMAIL for --<br>", username, "<br>", "Problem that arrises is for email: :", msg.To, exception.InnerException, "<br>", exception.InnerException.Message, "*", System.Environment.MachineName, "*" }));
}
}
protected void SendEmailTOAllUser()
{
string connString =ConfigurationManager.ConnectionStrings["CharityManagement"].ConnectionString;
string cmdText = "SELECT id FROM dbo.table WHERE iSent <> 1";
var sbEmailAddresses = new System.Text.StringBuilder(1000);
var quizIds = new List<string>();
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
while (reader.Read())
{
quizIds.Add(reader.GetString(0));
}
}
reader.Close();
}
}
catch (SqlException exception)
{
throw ExceptionHandler.TranslateSQLException(exception);
}
string cmdText2 = "SELECT Username FROM dbo.UserDetail";
try
{
using (SqlCommand cmd = new SqlCommand(cmdText2, conn))
{
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
while (reader.Read())
{
var sName = reader.GetString(0);
if (!string.IsNullOrEmpty(sName))
{
if (sbEmailAddresses.Length != 0)
{
sbEmailAddresses.Append(",");
}
sbEmailAddresses.Append(sName).Append("@companyname.com");
}
}
}
reader.Close();
}
}
catch (SqlException exception)
{
throw ExceptionHandler.TranslateSQLException(exception);
}
string cmdText3 = "UPDATE dbo.table SET iSent = 1 WHERE id = @quizid";
try
{
using (SqlCommand cmd = new SqlCommand(cmdText3, conn))
{
var oParameter = cmd.Parameters.Add("@QuizID", SqlDbType.NChar);
var sEMailAddresses = sbEmailAddresses.ToString();
foreach (string quizid in quizIds)
{
string link = "<a> test </a>";
string body = @" Please try to participate in the new short safety quiz "
+ link +
@"<br /> <br />
This email was generated using the <a href='http://pmv/pssp/Default.aspx'>PMOD Safety Services Portal </a>.
Please do not reply to this email.
";
SendEmail(sEMailAddresses, "", "Notification Email Subject", body, true, quizid);
oParameter.Value = quizid;
cmd.ExecuteNonQuery();
}
}
}
catch (SqlException exception)
{
throw ExceptionHandler.TranslateSQLException(exception);
}
conn.Close();
}
}
}