1

I have gone through some of the answers in this website for automatic mail sending in particular date.

I am using vs 2010 and I have created a project which is not hosted.

I have created a table for appointments with email id,Start date and start time.

The mail should be sent automatically to the email id of person one hour before an appointment.

How should I do this?

I have tried using window service but did not work for me.

 protected override void OnStart(string[] args)
    {
        string dateonly = DateTime.Now.ToString("yyyy-MM-dd");
        string timeonly = DateTime.Now.ToString("HH:mm tt");
        string source = "Data Source=localhost;Initial Catalog=PRO;Integrated Security=SSPI;";
        SqlConnection con = new SqlConnection(source);
        con.Open();

        SqlCommand cmd = new SqlCommand("select * from Appointments where CONVERT(varchar(10),StartDate,101)=CONVERT(varchar(10),GETDATE(),101)", con);
        cmd.Parameters.AddWithValue("@date", dateonly);
        cmd.Parameters.AddWithValue("@time", timeonly);
        cmd.ExecuteNonQuery();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds); //Filling table with user data
        SmtpClient client = new SmtpClient();
        client.DeliveryMethod = SmtpDeliveryMethod.Network;
        client.EnableSsl = true;
        client.Host = "smtp.gmail.com";
        client.Port = 587; //Google mail port
        System.Net.NetworkCredential credentials = new System.Net.NetworkCredential("email", "password");
        client.UseDefaultCredentials = false;
        client.Credentials = credentials;
        for each (Data Row Dr in ds.Tables[0].Rows)
        {

            MailMessage mm = new MailMessage();
            mm.To.Add(dr["Email"].ToString());
            mm.From = new MailAddress("email");
            mm.Subject = "Sending Auto Mail ";
            mm.Body = "This email has been send automatically through Windows Service to remind you about your Appointment in next one hour ";

            client.Send(mm);
            mm.Dispose();
        }      
    }

    protected override void OnStop()
    {
    }
user2691425
  • 9
  • 1
  • 4

3 Answers3

0

There are many things that could be the error here. The ones I have identified are:

The SQL statement you run looks strange. You pass in two parameters, but neither of them are used in the statement. You convert dates into varchars. You'd typically want to check the date in the database against a range of one hour from the current date and time.

Something like this would make more sense:

SELECT * FROM appointments WHERE startdate BETWEEN @i1 AND @i2

where you set @i1 to DateTime.Now and @i2 to DateTime.Now.AddHours(1).

It's probably a good idea to keep track of which appointments you've already notified as well. Otherwise you'll have some really annoyed customers.

In a Windows Service, the OnStart method only runs when the service is started. In order for this to work, you would have to periodically restart the service.

To make the service work as intended you have to add a timer to the service, that would on a given interval trigger a call to a method that contains the code you currently have in the OnStart metod. A simpler way is to create a console application with the email sending code and run it periodically using a scheduled task.

Community
  • 1
  • 1
PHeiberg
  • 28,455
  • 6
  • 51
  • 79
0

First option: You can put while loop in OnStart method will 1 minute sleep, which will check appointments from database and send emails, something like:

while (true)
{
    Thread.Slee(60 * 1000); // 60 seconds
    // here put your code to get appointments and send emails
}

Second option: You can use scheduler to sending emails at specified time, but you still need to check for new appointments in database and add it to scheduler. You can look at FluentScheduler (simpler and fluent API) or Quartz.net (more complex).

Michał Zalewski
  • 2,432
  • 1
  • 20
  • 33
0

You can try this method. I am Not Sure but it is work in my project.

public void SendOneDayAgoEmail(TimeSpan tsDiff) { Thread.Sleep(tsDiff); sendmail();

    TimeSpan ts10 = new TimeSpan(10, 0, 0);//10
    Thread othread = new Thread(() => SendEmailThatDay(ts10));
    othread.IsBackground = true;
    othread.Start();
}
public void SendEmailThatDay(TimeSpan tsDiff)
{
    Thread.Sleep(tsDiff);
    sendmail();
}

================================== otherwise also use this method

System.Threading.Thread tre = new System.Threading.Thread(new ThreadStart(MyFunction));
tre.Start();
System.Threading.Thread.Sleep(10000);
if (tre.IsAlive)
tre.Abort();
//The you have your function
void MyFunction()
{
//Do process
}
Mohit Kotak
  • 61
  • 1
  • 10