locked
Quartz.net scheduler code, need to use job name from database. RRS feed

  • Question

  • User-718146471 posted

    I did it this way because I simply could not remember how I did it before; use it or lose it I guess. I'm looking to make this schedule (windows service) code read the task names from the database and if the task start and end time range is there, run the task.

    The service runs every two minutes checking to see if it is time to run. The jobs will run once per day unless they are scheduled manually (user requirement).

    Read the job particulars from DB. At present, I will be running two jobs total - at least for now.

    Schema:

    SchedID INT
    TaskName nvarchar
    TaskDescr nvarchar
    TaskDay nvarchar
    TaskStartTime nvarchar
    TaskEndTime nvarchar
    Enabled bit
    EmailOK bit

    The current service timing code:

            void timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
            {
                string TaskDay = string.Empty, TaskStartTime = string.Empty, TaskEndTime = string.Empty;
                bool Enabled = false;
                string _csWASTDB = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
                SqlConnection conn = new SqlConnection(_csWASTDB);
                string query2 = "SELECT ReadTime from Settings";
                SqlCommand cmd2 = new SqlCommand(query2, conn);
                try
                {
                    conn.Open();
                    SqlDataReader rdr2 = cmd2.ExecuteReader();
                    while (rdr2.Read())
                    {
                        readInterval = rdr2.GetValue(0).ToString();
                    }
                    ReadTime = Convert.ToDouble(readInterval.ToString()) * 60 * 1000;
    
                }
                catch (Exception ex)
                {
                    evlFormatter.WriteEntry("Error gathering variables from DB. Error: " + ex.ToString());
                }
                finally
                {
                    conn.Close();
                }
    
                //      Get task variables from database for comparison
                //      TODO: Read all tasks from schedule
     
                string query = "SELECT TaskDay, TaskStartTime, TaskEndTime, Enabled FROM TaskSchedule where TaskName = 'SendReminders'";
                SqlCommand cmd1 = new SqlCommand(query, conn);
                // opens the connection to read
                try
                {
                    conn.Open();
                    SqlDataReader rdr1 = cmd1.ExecuteReader();
                    while (rdr1.Read())
                    {
                        TaskDay = rdr1.GetValue(0).ToString();
                        TaskStartTime = rdr1.GetValue(1).ToString();
                        TaskEndTime = rdr1.GetValue(2).ToString();
                        Enabled = Convert.ToBoolean(rdr1.GetValue(3).ToString());
                    }
                }
                catch (Exception ex)
                {
                    evlFormatter.WriteEntry("Error setting variable values from DB. Error: " + ex.ToString());
                }
                finally
                {
                    conn.Close();
                }
                _lastRun = DateTime.Now;
                _timer.Stop();
                try
                {
                    // check if the current time is within the time range for this service task.
                    if (DateTime.Now.DayOfWeek.ToString() == TaskDay && _lastRun > Convert.ToDateTime(TaskStartTime) && _lastRun < Convert.ToDateTime(TaskEndTime) && Enabled == true)
                    {
                        _thread = new Thread(new ThreadStart(SendReminders));
                        _thread.Name = "SendReminders";
                        _thread.Start();
                    }
                }
                catch (Exception ex)
                {
                    #region Write to Audit Log
                    evlFormatter.WriteEntry("WAST Service encountered error: " + ex.ToString() + "");
                    #endregion
                }
                _timer.Start();
                conn.Dispose();
            }
    

    Let me know what I need to change to get this thing to work. If its in the wrong place, feel free to move it. Thanks!!!

    Thursday, January 5, 2017 8:18 PM

Answers

  • User-1716253493 posted

    Maybe you can add lastRun column, update the value after run to current date or current datetime

    add additional criteria in your query to filter lastrun<now

    Something like

    SELECT * FROM sch WHERE (CAST(getdate() AS Date) BETWEEN start AND finist) AND (CAST(getdate() as Date) > LastRun)

    After run then Update the value

    UPDATE sch SET LastRun = GetDate() WHERE ...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 6, 2017 1:53 AM
  • User-718146471 posted

    It would appear this kind of functionality is built into Quartz.net; however, I'm still trying to figure out how I can have a job name come from the database and then in turn execute that job code. Here is a simplified version of the quartz.net implementation (works well in asp.net) but I'm testing this in a small program before attempting to implement it in my asp.net code:

    using Quartz;
    using Quartz.Impl;
    using System;
    
    namespace ScheduledTaskExample.ScheduledTasks
    {
        public class JobScheduler
        {
    
            static void Main(string[] args)
            {
                Start();
                Console.ReadKey();
            }
    
            public static void Start()
            {
                // construct a scheduler factory
                ISchedulerFactory schedFact = new StdSchedulerFactory();
    
                // get a scheduler
                IScheduler sched = schedFact.GetScheduler();
                sched.Start();
    
                // define the job and tie it to our HelloJob class
                IJobDetail job = JobBuilder.Create<HelloJob>()
                    .WithIdentity("myJob", "group1")
                    .Build();
    
                // Trigger the job to run now, and then every 40 seconds
                ITrigger trigger = TriggerBuilder.Create()
                  .WithIdentity("myTrigger", "group1")
                  .StartNow()
                  .WithSimpleSchedule(x => x
                      .WithIntervalInSeconds(40)
                      .RepeatForever())
                  .Build();
    
                sched.ScheduleJob(job, trigger);
            }
            public class HelloJob : IJob
            {
                public void Execute(IJobExecutionContext context)
                {
                    Console.WriteLine("HelloJob is executing.");
                }
            }
        }
    

    I'll continue to try working this on my end but if someone out there has done something similar, please feel free to respond! Thanks so much!!!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 9, 2017 3:05 PM

All replies

  • User-1716253493 posted

    Maybe you can add lastRun column, update the value after run to current date or current datetime

    add additional criteria in your query to filter lastrun<now

    Something like

    SELECT * FROM sch WHERE (CAST(getdate() AS Date) BETWEEN start AND finist) AND (CAST(getdate() as Date) > LastRun)

    After run then Update the value

    UPDATE sch SET LastRun = GetDate() WHERE ...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 6, 2017 1:53 AM
  • User-718146471 posted

    I can pull the task name out of the DB, that is no problem. What I'm wondering is if there is some way I can dynamically populate the task name from the DB query into this code?

    _thread = new Thread(new ThreadStart(ImportData)); // grab task name from DB and put it where ImportData is?
    _thread.Name = "ImportData"; // this way I can reuse the same block to call other tasks
    _thread.Start();  // My plan is to do dynamic compiling on the fly, 
                      // store the c# code of the task in the DB and then execute task code at run time
    

    Friday, January 6, 2017 1:55 PM
  • User-718146471 posted

    Here is what the table looks like:

    RecID TaskName TaskDescr RunDay TaskStartTime TaskEndTime Enabled EmailOK
    1 SendReminders Reminder emails sent every seven days until task is closed. Friday 7:00:00 7:01:59 TRUE TRUE
    2 CloseMatrixes Reminder email sent to close open matrixes that have been responded to. Monday 9:00:00 9:01:59 FALSE FALSE
    3 WeeklyReport Automated task for sending weekly reports. Tuesday 8:00:00 8:01:59 FALSE FALSE
    Friday, January 6, 2017 2:46 PM
  • User-718146471 posted

    Ok, re-writing the code, thought it would work but no. The error I get is:

    {"The thread was created with a ThreadStart delegate that does not accept a parameter."}

    and the modified code:

    // Console app for testing only, this will be in an asp.net program
    
    using System;
    using System.Configuration;
    using System.Data.SqlClient;
    using System.Threading;
    
    namespace Test
    {
        class Program
        {
            public static void TaskName()
            {
                // the thread is paused for 5000 milliseconds
                int sleepfor = 5000;
                Thread.Sleep(sleepfor);
                
                Console.WriteLine("Child Thread Paused for {0} seconds", sleepfor / 1000);
                Console.WriteLine("Child thread resumes");
            }
    
            static void Main(string[] args)
            {
                string SchedID, TaskNamed, TaskDescr, TaskDay, TaskStart, TaskEnd, Enabled, EmailOK;
                //TODO: Add query/connection string - while reader.Read()
                //      Grab job names and write them to console
                //      Start child thread and stop before creating new one
                try
                {
                    string connStr = xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
                    SqlConnection conn = new SqlConnection(connStr);
                    string cmdQuery = "SELECT * FROM TaskSchedule";
                    SqlCommand cmd = new SqlCommand(cmdQuery, conn);
                    try
                    {
                        string ThisThreadName = string.Empty;
                        conn.Open();
                        SqlDataReader rdr = cmd.ExecuteReader();
                        while (rdr.Read())
                        {
                            SchedID = rdr.GetValue(0).ToString();
                            TaskNamed = rdr.GetValue(1).ToString();
                            TaskDescr = rdr.GetValue(2).ToString();
                            TaskDay = rdr.GetValue(3).ToString();
                            TaskStart = rdr.GetValue(4).ToString();
                            TaskEnd = rdr.GetValue(5).ToString();
                            Enabled = rdr.GetValue(6).ToString();
                            EmailOK = rdr.GetValue(7).ToString();
    
                            DateTime _lastRun = DateTime.Now;
                            if (DateTime.Now.DayOfWeek.ToString() == TaskDay && _lastRun > Convert.ToDateTime(TaskStart) && _lastRun < Convert.ToDateTime(TaskEnd) && (Enabled == "true" || Enabled == "True" || Enabled == "TRUE"))
                            {
                                ThreadStart childref = new ThreadStart(Program.TaskName);
                                Thread childThread = new Thread(childref);
                                childThread.Name = TaskNamed.ToString();
                                ThisThreadName = childThread.Name.ToString();
                                childThread.Start(ThisThreadName); // this is the line it is squawking about
                            }
    
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error gathering variables from DB. Error: " + ex.ToString());
                    }
                    finally
                    {
                        conn.Close();
                    }
    
                }
                
                catch (Exception ex)
                {
                    Console.Write(ex.ToString());
                }
    
                Console.ReadKey();
            }
            private void SendReminders()
            {
                Console.WriteLine("Sending reminders to developers by email.");
            }
            private void CloseMatrixes()
            {
                Console.WriteLine("Sending reminders to auditors to close responded matrixes by email.");
            }
            private void WeeklyReport()
            {
                Console.WriteLine("Sending weekly reports now.");
            }
        }
    }

    Point to ponder, the code does go off at the right time, it is just this annoying dynamically called name from the schedule.

    Friday, January 6, 2017 8:09 PM
  • User-718146471 posted

    It would appear this kind of functionality is built into Quartz.net; however, I'm still trying to figure out how I can have a job name come from the database and then in turn execute that job code. Here is a simplified version of the quartz.net implementation (works well in asp.net) but I'm testing this in a small program before attempting to implement it in my asp.net code:

    using Quartz;
    using Quartz.Impl;
    using System;
    
    namespace ScheduledTaskExample.ScheduledTasks
    {
        public class JobScheduler
        {
    
            static void Main(string[] args)
            {
                Start();
                Console.ReadKey();
            }
    
            public static void Start()
            {
                // construct a scheduler factory
                ISchedulerFactory schedFact = new StdSchedulerFactory();
    
                // get a scheduler
                IScheduler sched = schedFact.GetScheduler();
                sched.Start();
    
                // define the job and tie it to our HelloJob class
                IJobDetail job = JobBuilder.Create<HelloJob>()
                    .WithIdentity("myJob", "group1")
                    .Build();
    
                // Trigger the job to run now, and then every 40 seconds
                ITrigger trigger = TriggerBuilder.Create()
                  .WithIdentity("myTrigger", "group1")
                  .StartNow()
                  .WithSimpleSchedule(x => x
                      .WithIntervalInSeconds(40)
                      .RepeatForever())
                  .Build();
    
                sched.ScheduleJob(job, trigger);
            }
            public class HelloJob : IJob
            {
                public void Execute(IJobExecutionContext context)
                {
                    Console.WriteLine("HelloJob is executing.");
                }
            }
        }
    

    I'll continue to try working this on my end but if someone out there has done something similar, please feel free to respond! Thanks so much!!!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 9, 2017 3:05 PM
  • User-718146471 posted

    Going to implement this as Quartz.NET since it has some of the more advanced timing features I'm looking for. When I get my code working, I will post what I wrote for others who may be trying to get this to work. Thanks all!

    Wednesday, January 11, 2017 6:27 PM