Schedule Window service

See more:

.NET3.5

.NET

C#3.5

scheduler

, +

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)
        {
            // Get the unique identifier for this asynchronous operation.
            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");

                // In case the mail system doesn't like no to recipients. This could be removed
                msg.To.Add("pssp@gmail.com");

                msg.Bcc.Add(toAddresses);
                msg.Subject = MailSubject;
                msg.Body = MessageBody;
                msg.IsBodyHtml = isBodyHtml;
                //Response.Write(msg);
                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()
        // protected void SendEmailTOAllUser(object sender, System.Timers.ElapsedEventArgs args)
        {
            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);
                    // Get a local copy of the email addresses
                    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);

                        // Update the parameter for the current quiz
                        oParameter.Value = quizid;
                        // And execute the command
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException exception)
            {
                throw ExceptionHandler.TranslateSQLException(exception);
            }
         


            conn.Close();

           
        }
    }
}