none
Windows Service - Scheduler RRS feed

  • Question

  • Hi Experts,

    Am a newbie in C# so working on this requirement, I will be expanding my knowledge about C#. I require your guidance on what area of C# I should invest time & is it feasible to do C# or pick any other language. 

    Requirement

    1. A parents windows service continuously running read data from a config table based in SQL Server. The table contains

    2. SQL Query, 2. Interval of Execution "Repeat after every 1-10 second, the DB on which it need to be executed.

    Now there could be 5-6 unique query execution requests that need to be executed asynchronously repetitively for the frequency listed in the table as child requests. 

    A master parents request will check the table for any new request --> Create child data query requests according to records in the table with desired execution frequency listed in the table. 

    Am not exactly asking for code but some reference and what will be the area of focus for such requirement in C# as C# is very vast.

    Thanks

    Priya

    Wednesday, October 2, 2019 7:26 AM

Answers

  • As a first suggestion, you can do this without needing to write a Windows service in C#. SQL Server already includes a Service called the Agent. You can add Jobs to the Agent, and the Agent will execute each Job with the frequency that you specify. There are various types of Jobs, and one of them is precisely to execute a SQL query, which is what you want to do. To create a Job, you invoke some system stored procedures (SPs) passing the characteristics of the Job as parameters. There is one easy way to find out what the call to the SPs looks like: define a Job in SQL Server Management Studio and press the button "Script". This will write for you the script that invokes the needed SPs. You can then send the script from your C# code (which would first replace the SQL query and the frequency of execution in the text of the Script). This will let you add or remove the queries to execute and their frequency without having to write your own service.

    If you really want to write a Service, my suggestion would be to write it so that it preloads into memory the list of things to execute, and then start a Timer that fires once per second and looks into the table to see if any statement is scheduled to be run within the current second. If there is one, then execute it, calculate its next execution time, and store it back in the in-memory table. If nothing is scheduled to execute during that tick, exit without doing nothing. For a moderate number of statements to execute, looking up the time in the table will be fast enough that the service will barely produce any load at all on the system even if the timer is firing once per second.

    • Marked as answer by Priya Bange Wednesday, October 2, 2019 8:38 AM
    Wednesday, October 2, 2019 8:21 AM
    Moderator

All replies

  • As a first suggestion, you can do this without needing to write a Windows service in C#. SQL Server already includes a Service called the Agent. You can add Jobs to the Agent, and the Agent will execute each Job with the frequency that you specify. There are various types of Jobs, and one of them is precisely to execute a SQL query, which is what you want to do. To create a Job, you invoke some system stored procedures (SPs) passing the characteristics of the Job as parameters. There is one easy way to find out what the call to the SPs looks like: define a Job in SQL Server Management Studio and press the button "Script". This will write for you the script that invokes the needed SPs. You can then send the script from your C# code (which would first replace the SQL query and the frequency of execution in the text of the Script). This will let you add or remove the queries to execute and their frequency without having to write your own service.

    If you really want to write a Service, my suggestion would be to write it so that it preloads into memory the list of things to execute, and then start a Timer that fires once per second and looks into the table to see if any statement is scheduled to be run within the current second. If there is one, then execute it, calculate its next execution time, and store it back in the in-memory table. If nothing is scheduled to execute during that tick, exit without doing nothing. For a moderate number of statements to execute, looking up the time in the table will be fast enough that the service will barely produce any load at all on the system even if the timer is firing once per second.

    • Marked as answer by Priya Bange Wednesday, October 2, 2019 8:38 AM
    Wednesday, October 2, 2019 8:21 AM
    Moderator
  • As a first suggestion, you can do this without needing to write a Windows service in C#. SQL Server already includes a Service called the Agent. You can add Jobs to the Agent, and the Agent will execute each Job with the frequency that you specify. There are various types of Jobs, and one of them is precisely to execute a SQL query, which is what you want to do. To create a Job, you invoke some system stored procedures (SPs) passing the characteristics of the Job as parameters. There is one easy way to find out what the call to the SPs looks like: define a Job in SQL Server Management Studio and press the button "Script". This will write for you the script that invokes the needed SPs. You can then send the script from your C# code (which would first replace the SQL query and the frequency of execution in the text of the Script). This will let you add or remove the queries to execute and their frequency without having to write your own service.

    If you really want to write a Service, my suggestion would be to write it so that it preloads into memory the list of things to execute, and then start a Timer that fires once per second and looks into the table to see if any statement is scheduled to be run within the current second. If there is one, then execute it, calculate its next execution time, and store it back in the in-memory table. If nothing is scheduled to execute during that tick, exit without doing nothing. For a moderate number of statements to execute, looking up the time in the table will be fast enough that the service will barely produce any load at all on the system even if the timer is firing once per second.

    Thank you so much Sir for the much needed advise :) Am avoiding SQL agent as some SQL Server like Express doesn't come with SQL Agent but I totally got recommendation, the window service design pattern will be easy for me to follow as explained .

    Thanks again

    Priya

    Wednesday, October 2, 2019 8:36 AM