none
Scheduler RRS feed

  • Question

  • Hi Experts,

    I have 5 SQL files that need to be executed on varying frequencies , say 3-5 second each.. Could you please support on any possible way to develop a scheduler example, will be great support as am still in learning phase of C#.

    Thanks

    Priya

    Monday, November 18, 2019 11:51 AM

Answers

  • Very basic scheduler could be implemented by using following sample.

    public class SqlScheduler : IDisposable
    {
        private readonly string connectionString;
        private readonly System.Collections.Generic.Dictionary<string, DateTime> sqlFiles;
        private readonly System.Collections.Generic.Dictionary<string, double> intervals;
        private System.Timers.Timer timer;
    
        public SqlScheduler(string connectionString, System.Collections.Generic.IDictionary<string, double> sqlFiles)
        {
            this.connectionString = connectionString;
            this.timer = new System.Timers.Timer();
            this.timer.Interval = TimeSpan.FromSeconds(1).TotalMilliseconds;
            this.timer.Elapsed += OnTimerElapsed;
            this.sqlFiles = new System.Collections.Generic.Dictionary<string, DateTime>();
            this.intervals = new Dictionary<string, double>();
            foreach (var command in sqlFiles)
            {
                this.sqlFiles.Add(command.Key, DateTime.Now);
                this.intervals.Add(command.Key, command.Value);
            }
        }
    
        private void OnTimerElapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            DateTime time = e.SignalTime;
    
            // Get files to execute where specified secods or more has passed since last execution.
            string[] files = sqlFiles.Where(keyValue => time.Subtract(keyValue.Value).TotalSeconds >= intervals[keyValue.Key])
                                        .Select(keyValue => keyValue.Key).ToArray();
    
            foreach (string file in files)
            {
                try
                {
                    ExecuteSqlFile(file, time);
                }
                catch (Exception)
                {
                    time = sqlFiles[file];
                }
                finally
                {
                    sqlFiles[file] = time;
                }
            }
        }
    
        private void ExecuteSqlFile(string file, DateTime time)
        {
            // Print out to console for now.
            Console.WriteLine($"{file} {time.ToLongTimeString()}");
    
            //string sql = ReadSql(command);
            //using (var sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString))
            //using (var sqlCommand = new System.Data.SqlClient.SqlCommand(sql, sqlConnection))
            //{
            //    sqlConnection.Open();
            //    sqlCommand.ExecuteNonQuery();
            //    sqlConnection.Close();
            //}
        }
    
        private string ReadSql(string file)
        {
            using (StreamReader sr = new StreamReader(file))
                return sr.ReadToEnd();
        }
    
        public void Stop()
        {
            if (timer != null)
                timer.Stop();
        }
    
        public void Start()
        {
            if (timer != null)
                timer.Start();
        }
    
        public void Dispose()
        {
            if (timer != null)
            {
                timer.Stop();
                timer.Elapsed -= OnTimerElapsed;
                timer.Dispose();
                timer = null;
            }
        }
    }

    Usage in console application:

    string connectionString = "";
    using (SqlScheduler scheduler = new SqlScheduler(connectionString, new Dictionary<string, double>() { { "File1.sql", 3.3 }, { "File2.sql", 4.2 }, { "File3.sql", 5.1 } }))
    {
        scheduler.Start();
    
        Console.ReadKey();
    
        scheduler.Stop();
    }

    • Marked as answer by Priya Bange Tuesday, November 19, 2019 6:41 AM
    Monday, November 18, 2019 12:56 PM

All replies

  • Hello,

    Look at Quartz.NET Scheduler library. Jobs are the core class, see the following examples. We use the library for a windows service with Oracle and SQL-Server but have no simple examples to provide.

    Beings that you are still learning C# this library or similar library will need time to learn, study up on Interfaces prior to jumping in.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, November 18, 2019 12:10 PM
    Moderator
  • Very basic scheduler could be implemented by using following sample.

    public class SqlScheduler : IDisposable
    {
        private readonly string connectionString;
        private readonly System.Collections.Generic.Dictionary<string, DateTime> sqlFiles;
        private readonly System.Collections.Generic.Dictionary<string, double> intervals;
        private System.Timers.Timer timer;
    
        public SqlScheduler(string connectionString, System.Collections.Generic.IDictionary<string, double> sqlFiles)
        {
            this.connectionString = connectionString;
            this.timer = new System.Timers.Timer();
            this.timer.Interval = TimeSpan.FromSeconds(1).TotalMilliseconds;
            this.timer.Elapsed += OnTimerElapsed;
            this.sqlFiles = new System.Collections.Generic.Dictionary<string, DateTime>();
            this.intervals = new Dictionary<string, double>();
            foreach (var command in sqlFiles)
            {
                this.sqlFiles.Add(command.Key, DateTime.Now);
                this.intervals.Add(command.Key, command.Value);
            }
        }
    
        private void OnTimerElapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            DateTime time = e.SignalTime;
    
            // Get files to execute where specified secods or more has passed since last execution.
            string[] files = sqlFiles.Where(keyValue => time.Subtract(keyValue.Value).TotalSeconds >= intervals[keyValue.Key])
                                        .Select(keyValue => keyValue.Key).ToArray();
    
            foreach (string file in files)
            {
                try
                {
                    ExecuteSqlFile(file, time);
                }
                catch (Exception)
                {
                    time = sqlFiles[file];
                }
                finally
                {
                    sqlFiles[file] = time;
                }
            }
        }
    
        private void ExecuteSqlFile(string file, DateTime time)
        {
            // Print out to console for now.
            Console.WriteLine($"{file} {time.ToLongTimeString()}");
    
            //string sql = ReadSql(command);
            //using (var sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString))
            //using (var sqlCommand = new System.Data.SqlClient.SqlCommand(sql, sqlConnection))
            //{
            //    sqlConnection.Open();
            //    sqlCommand.ExecuteNonQuery();
            //    sqlConnection.Close();
            //}
        }
    
        private string ReadSql(string file)
        {
            using (StreamReader sr = new StreamReader(file))
                return sr.ReadToEnd();
        }
    
        public void Stop()
        {
            if (timer != null)
                timer.Stop();
        }
    
        public void Start()
        {
            if (timer != null)
                timer.Start();
        }
    
        public void Dispose()
        {
            if (timer != null)
            {
                timer.Stop();
                timer.Elapsed -= OnTimerElapsed;
                timer.Dispose();
                timer = null;
            }
        }
    }

    Usage in console application:

    string connectionString = "";
    using (SqlScheduler scheduler = new SqlScheduler(connectionString, new Dictionary<string, double>() { { "File1.sql", 3.3 }, { "File2.sql", 4.2 }, { "File3.sql", 5.1 } }))
    {
        scheduler.Start();
    
        Console.ReadKey();
    
        scheduler.Stop();
    }

    • Marked as answer by Priya Bange Tuesday, November 19, 2019 6:41 AM
    Monday, November 18, 2019 12:56 PM
  • You've been working on this for weeks.  Surely you must have some part of this working already.  What do you have?

    Tim Roberts | Driver MVP Emeritus | Providenza &amp; Boekelheide, Inc.

    Monday, November 18, 2019 7:05 PM
  • Hi Sir, 

    Yes, I do have something that's working but its consuming way to much resources 15-20 percent :( so am thinking to rewrite it and looking for other scheduler patterns. 

    Thanks 

    Priya

    Tuesday, November 19, 2019 6:41 AM
  • There's a built-in maintenance plan support in SQL Server. But then if your code uses too much CPU you should look for ways to optimize your SQL first. 


    Visual C++ MVP

    Tuesday, November 19, 2019 2:43 PM
  • There's a built-in maintenance plan support in SQL Server. But then if your code uses too much CPU you should look for ways to optimize your SQL first. 


    Visual C++ MVP

    Hello, 

    Thanks a lot for the advice, the SQL operations are simple SELECT from a table with WHERE clause with appropriate index's and execution plan display seeks.

    The problem is with multiple threads spawning up I guess. 

    Wednesday, November 20, 2019 4:01 PM